How to query the Spectrum Archive Manager (DDM) database for long event message strings

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

How to query the Spectrum Archive Manager (DDM) database for long event message strings

There are some events with long message string (in the vardata_string field) which causes a failure to insert the event into the reporting.event table.

The following is an example error seen in the $SPECROOT/SS/DDM/ARCHMGR.OUT file:

May 02, 2018 09:38:00.784 (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 = 1524374934000 
May 02, 2018 09:38:00.784 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - event record (mh=0x133dde, type=0x6330076, time=2018-04-22 02:28:54.0) 

This MySQL query will help you to identify the problematic event codes and delete them from the ddmdb.event table.

How to find the event code with long event message string (in the vardata_string field)

Environment

Any version of Spectrum

Resolution

1. Log into the SpectroSERVER system as the user that owns the Spectrum installation

2. If on Windows, start a bash shell by running "bash -login"

3. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

    ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb -A (Linux)

    ./mysql.exe -uroot -proot ddmdb (Windows)

4. Run the following MySQL query:

    select HEX(model_h),HEX(type) as 'Event Type', CHAR_LENGTH(vardata_string) as 'Character length' from event where CHAR_LENGTH(vardata_string) > 65535 limit 50;

5. If any are found, you can then run the following mysql command at the mysql prompt to delete the event from the database. (The following example assumes the output from the above command returned the 0x6330076 event id):

    DELETE FROM event WHERE type=0x6330076;

Additional Information

This is the syntax to select this event message from the DDM database (confirm if you get 1 row) :

SELECT * FROM event WHERE model_h=0x216984 and type=0x10d05 and UNIX_TIMESTAMP('2021-07-30 18:34:44');

This is the syntax to delete this event message from the DDM database:

DELETE FROM event WHERE model_h=0x216984 and type=0x10d05 and UNIX_TIMESTAMP('2021-07-30 18:34:44');