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

Products

CA Spectrum DX NetOps

Issue/Introduction

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

Release: Any
Component: Spectrum Report Manager/Jasper

Cause

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

mysql> desc 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.02 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 -p<PASSWD> 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.

- nav to %SPECROOT%/mysql/bin
- log into MySQL:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting

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

mysql> ALTER TABLE event MODIFY event_msg MEDIUMTEXT;
mysql> exit;

3. Restart the tomcat service.

4. Verify that processing has continued for this landscape.

This can be 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.exe -uroot -p<PASSWD> reporting (Windows)

OR

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting -A (Linux)

Once connected, type: select * from landscape\G

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