Report Manager event processing failed, with Data truncation errors: Data too long for column 'event_msg'.

Document ID : KB000008648
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Spectrum Report Manager has stopped polling on one domain.  The following error is in the catalina.out: 

 

Oct 16, 2017 09:35:30.047 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Failed to insert event into event table. SQL Exception code: 1406 SQL Exception message: Data truncation: Data too long for column 'event_msg' at row 1 Last known event time = 1507906054000 

Oct 16, 2017 09:35:30.047 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - event record (mh=xxx, type=0x10d05, time=2017-10-13 16:47:34.0) 

Oct 16, 2017 09:35:30.123 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Database ERROR processing events for domain yyy. Will not poll domain 

 

Environment:
64 bit Spectrum versions to 10.2.x
Cause:

The "event" table of the reporting database is "described" as follows: 

 

mysql> describe event; 

+-------------------+---------------------+------+-----+---------+----------------+ 

| Field | Type | Null | Key | Default | Extra | 

+-------------------+---------------------+------+-----+---------+----------------+ 

| event_key | bigint(20) unsigned | NO | PRI | NULL | auto_increment | 

| event_id | binary(26) | NO | MUL | NULL | | 

| landscape_h | int(10) unsigned | NO | MUL | NULL | | 

| model_key | int(10) unsigned | NO | MUL | 0 | | 

| time | datetime | NO | PRI | NULL | | 

| type | int(10) unsigned | NO | MUL | NULL | | 

| creator_id | int(10) unsigned | NO | MUL | NULL | | 

| event_msg | text | YES | | NULL | | 

| server_precedence | int(10) unsigned | YES | | 0 | | 

+-------------------+---------------------+------+-----+---------+----------------+ 

9 rows in set (0.00 sec) 

 

This suggests that the "event_msg" field is of the type "text", which means it can be 0-65,535 characters long.  The event code corresponds to an event, which list all models that are affected. Since we have some large customers with quite a lot of models, this could lead to a total event message size of more than 65535 characters.   With the advent of 64 bit Spectrum, the number of devices monitored by a single landscape, has increased dramatically.  As a result, events that list the devices as part of the event text, have also increased dramatically and are potentially too large for the mySQL data type text.

Resolution:

The mySQL data type TEXT can hold 65,535 characters.

MEDIUMTEXT can hold over 16 million.

 

Type | Maximum length 

-----------+------------------------------------- 

TINYTEXT | 255 (2 8−1) bytes 

TEXT | 65,535 (216−1) bytes = 64 KiB 

MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB 

LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB

 

as a workaround, change the data type of the event_msg field to mediumtext as below:

PLEASE NOTE:

The time taken by the query, may vary depending on the overall MySQL and system performance. Event table with a lot of records, may take a long time to run on servers where install requirements were not met.  Please allow it to run to completion, and avoid any mysql shutdown during the process, as it could cause event table corruptions and data loss. 

e.g. in testing, 2 minutes to alter an event table containing 308,870 records, on a low spec lab machine lab machine but it took 13 minutes in production, to do 40 millions records, when requirements were met.

 

1. Take the backup of the event table using the command ./mysqldump -uroot -proot reporting event)event.sql 

 

2. Increase the capacity of the column by changing from text to mediumtext. 

This will increase the max size from the current 65,535 characters to 16,777,215 for mediumtext. 

 

from the mysql interface in the reporting db use the following command. 

 

          ALTER TABLE event MODIFY event_msg MEDIUMTEXT; 

 

3. Restart the tomcat.

 

4. Verify that processing has continued for this landscape.

This can from the OneClick web administration page "Spectrum Status".

Or from the mySQL interface as follows:

 

from a linux or bash shell navigate to $SPECROOT/mysql/bin and open a mySQL interface as follows:

./mysql -uroot -proot reporting

 

Once connected, type: ./select * from landscape\G

from here we can check the time of the last update from each landscape processed in mySQL.

Additional Information:

The mySQL data type TEXT can hold 65,535 characters.  MEDIUMTEXT can hold over 16 million characters.

 

Type | Maximum length 

-----------+------------------------------------- 

TINYTEXT | 255 (2 8−1) bytes 

TEXT | 65,535 (216−1) bytes = 64 KiB 

MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB 

LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB

 

as a workaround, change the data type of the event_msg field to mediumtext as follows:

 

1. Take the backup of the event table using the command ./mysqldump -uroot -proot reporting event)event.sql 

2. Increase the capacity of the column by changing from text to mediumtext. 

This will increase the max size from the current 65,535 characters to 16,777,215 for mediumtext. 

 

from the mysql interface in the reporting db use the following command. 

 

          ALTER TABLE event MODIFY event_msg MEDIUMTEXT; 

 

3. Restart the tomcat.

4. Verify that processing has continued for this landscape.  This can from the OneClick web administration page "Spectrum Status".

Or from the mySQL interface as follows:

 

from a linux or bash shell navigate to

$SPECROOT/mysql/bin

and open a mySQL interface as follows:

 

./mysql -uroot -proot reporting

 Once connected, type:

 

select * from landscape\G

 

From here we can check the time of the last update from each landscape processed in mySQL.