SRM not starting due to error "Data truncation: Data too long for column 'content' at row 1"
Article ID: 111734
Updated On:
Products
Issue/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
Environment
Cause
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.
Resolution
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 configchangelog 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
Feedback
