SRM Database ERROR processing events for domain spectrum table doesn't exit

Products

• DX NetOps
• CA Spectrum

Issue/Introduction

I had to restart the oneclick and after that I have an alarm that gives an error processing events.

Jul 20, 2023 17:11:52.158 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Unable to save events into staging table 
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT IGNORE INTO alarmbucket4194304_1689883809870 (event_ID, domain, model_h, time, type, model_key, vardata, event_key) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'reporting.alarmbucket4194304_1689883809870' doesn't exist

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'reporting.alarmbucket4194304_1689883809870' doesn't exist
 ... 13 more
 
Jul 20, 2023 17:11:53.354 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Database ERROR processing events for domain <SpectroSERVER>.  Will not poll domain

 

Environment

Release : Any version

Cause

Somehow the SRM got lost after reboot and it was trying to insert alarm data into an alarm bucket file that was already destroyed.

1. First check if this particular bucket table files exist in the $SPECROOT/mysql/data/reporting/ directory:

• alarmbucket4194304_1689883809870.frm
• alarmbucket4194304_1689883809870.MYD
• alarmbucket4194304_1689883809870.MYI

The bucket files no longer exist.

2. Then check if this bucket table exists in the MySQL reporting db:

cd $SPECROOT/mysql/bin/

./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting -A     (Linux - Spectrum 22.2.5 and above)
./mysql.exe -uroot -pMySqlR00t reporting     (Windows  - Spectrum 22.2.5 and above)

mysql> show tables like 'alarmbucket4194304_1689883809870';

The bucket table no longer exits.

3. Then check if this bucket table name is in the bucketactivitylog table and the destroy_time column value:

cd $SPECROOT/mysql/bin/

./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting -A     (Linux - Spectrum 22.2.5 and above)
./mysql.exe -uroot -pMySqlR00t reporting     (Windows  - Spectrum 22.2.5 and above)

mysql> select * from bucketactivitylog where bucket_table_name='alarmbucket4194304_1689883809870' \G

The bucket table was already processed. The destroy_time column is not NULL.

It may happen that the bucket table was not even created.

Resolution

As the reporting.alarmbucket4194304_1689883809870 table does not exist in the $SPECROOT/mysql/data/reporting/ directory, in the MySQL reporting database and it is already marked as destroyed in the bucketactivitylog, restarted the Spectrum Tomcat service and MySQL service.

1. Stop the Spectrum Tomcat service

$SPECROOT/tomcat/bin/stopTomcat.sh

2. Stop the Spectrum MySQL service

$SPECROOT/bin/cmdC localhost 2 MYSQL

3. Start the Spectrum MySQL service

$SPECROOT/bin/launchinstdbapp localhost MYSQL y MYSQL.OUT 

4. Start the Spectrum Tomcat service

$SPECROOT/tomcat/bin/startTomcat.sh