SRM not starting due to error "Data truncation: Data too long for column 'content' at row 1"

Document ID : KB000111734
Last Modified Date : 21/08/2018
Show Technical Document Details
Introduction:
SRM is not starting.  In the tomcat log we see:
(SRM/Startup/Container) (ReportManager) - (ERROR) - Error occurred while logging custom configuration changes
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'content' at row 1
Background:
This error means that a record could not be inserted into the database, as it was too long for the defined length of that field in the DB schema.
We will need to alter the field size manually before it can continue but it normally points to the name of a field and table and in this error, it does not.
Environment:
Any version of CA Spectrum or specifically any application with mySQL.
Instructions:
The error points us to the problematic field but not the table and the table that contains the column 'content' is found with following query: 

mysql> select * from information_schema.columns where table_schema = 'reporting' and column_name like '%content%' order by table_name,ordinal_position; 
+---------------+--------------+-----------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ 
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | 
+---------------+--------------+-----------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ 
| def           | reporting    | configchangelog | content | 4               | NULL           | NO          | blob      | 65535                    | 65535                  | NULL              | NULL          | NULL               | NULL           | blob        |            |       | select,insert,update,references | | 
+---------------+--------------+-----------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ 
1 row in set (0.02 sec) 

The query returned that the table is configchagelog and the data type is BLOB with a CHARACTER_MAXIMUM_LENGTH of 65535.
We need to find out what is waiting to be inserted that is too large and stopped mysql, so trace needs to be enabled on mysql as follows: 

from a bash or linux shell, log into mysql and run 
set global general_log='on'; 
restart tomcat.

After the error has reoccurred, review the log at $SPECROOT/mysql/data/<servername>.log 
the line responsible will be altering the configchangelog table, so in the example, searching on this we find 25 hits similar to
QueryINSERT INTO reporting.configchangelog (filename, last_modified, content) VALUES ('E:\\Spectrum\\custom\\repmgr\\config\\events\\example.xml', <followed by the contents of the xml file>

looking in the named directory and files, many are larger than the 65535 character limit of the content field.

mysql> desc configchangelog; 
+---------------+------------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+---------------+------------------+------+-----+---------+----------------+ 
| id | int(10) unsigned | NO | PRI | NULL | auto_increment | 
| filename | varchar(255) | NO | | NULL | | 
| last_modified | bigint(20) | NO | | NULL | | 
| content | blob | NO | | NULL | | 
+---------------+------------------+------+-----+---------+----------------+ 
4 rows in set (0.01 sec) 

the 2 options are to remove the offending files and kill whatever code is generating them if it customised and not needed.
Or increase the filed size to accommodate larger amount of text.

A BLOB can be 65535 bytes (64 KB) maximum. If you need more consider using: a MEDIUMBLOB for 16777215 bytes (16 MB) a LONGBLOB for 4294967295 bytes (4 GB). 
Please be aware that using  MEDIUMBLOB or LONGBLOB  may make your db extremely large, if there are a lot of these entries in the table.

The table can be changed via mySQL as follows:

mysql> alter table configchangelog modify content MEDIUMBLOB; 
Query OK, 47 rows affected (0.08 sec) 
Records: 47 Duplicates: 0 Warnings: 0 

mysql> desc configchangelog; 
+---------------+------------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+---------------+------------------+------+-----+---------+----------------+ 
| id | int(10) unsigned | NO | PRI | NULL | auto_increment | 
| filename | varchar(255) | NO | | NULL | | 
| last_modified | bigint(20) | NO | | NULL | | 
| content | mediumblob | YES | | NULL | | 
+---------------+------------------+------+-----+---------+----------------+ 
4 rows in set (0.00 sec) 
 
Additional Information:
For more details on mySQL logging, see:
https://comm.support.ca.com/kb/how-to-turn-on-mysql-general-query-logging-without-restarting-the-mysql-server/kb000048011 

or
https://dev.mysql.com/doc/refman/8.0/en/query-log.html