Alarm table is out of sync

Products

• CA Spectrum

Issue/Introduction

The alarm table from the reporting database is out of sync.

Error message in the tomcat log file (stdout.log)
May 13, 2020 21:56:03.996 (SRM/AlarmHandler/bucketReader) (SRM_Alarms) - (ERROR) - Unknown exception encountered while processing name events:  processing halted for all servers

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT event_ID, time, event_seq, model_key, vardata, type, model_h, domain, event_key FROM alarmbucket16777216_1581444953573 WHERE event_seq BETWEEN ? AND ?  ORDER BY event_seq]; SQL state [HY000]; error code [126]; Incorrect key file for table '.\reporting\alarmbucket16777216_1581444953573.MYI'; try to repair it; nested exception is java.sql.SQLException: Incorrect key file for table '.\reporting\alarmbucket16777216_1581444953573.MYI'; try to repair it
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:732)
 at com.aprisma.spectrum.app.repmgr.dc.db.dao.jdbc.JdbcEventBucketDAO.findEvents(Unknown Source)
 at com.aprisma.spectrum.app.repmgr.dc.event.handler.AbstractBucketReader.readBucket(Unknown Source)
 at com.aprisma.spectrum.app.repmgr.dc.event.handler.BucketReaderScheduler$ScheduledReader.run(Unknown Source)
 at com.aprisma.util.thread.JobQueue.runJobThread(JobQueue.java:221)
 at com.aprisma.util.thread.JobQueue.access$000(JobQueue.java:15)
 at com.aprisma.util.thread.JobQueue$JobRunnable.run(JobQueue.java:24)
 at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Incorrect key file for table '.\reporting\alarmbucket16777216_1581444953573.MYI'; try to repair it
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
 at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
 ... 10 more

Error message found in the MySQL server log file:
200330 15:30:10 [ERROR] D:/win32app/Spectrum/mysql/bin/mysqld.exe: Incorrect key file for table '.\reporting\alarmbucket16777216_1581444953573.MYI'; try to repair it
200330 17:38:55 [ERROR] D:/win32app/Spectrum/mysql/bin/mysqld.exe: Incorrect key file for table '.\reporting\alarmbucket16777216_1581444953573.MYI'; try to repair it

Environment

Release : 10.2

Component : Spectrum Reporting

Resolution

1. Open a bash shell (bash -login)

2. Navigate to the $SPECROOT/mysql/bin/ directory.
cd mysql/bin

3. Launch the MySQL command prompt:
./mysql -uroot -proot reporting

4. Run the following MySQL query to see the last synchronization time:
mysql> REPAIR TABLE alarmbucket16777216_1581444953573 USE_FRM;

5. Stop/start the Spectrum Tomcat service (in Windows Services)

6. After two hours or more, run the following MySQL query to see if the alarm_sync_time has incremented:
alarm_sync_time: 2020-02-11 11:32:13 (this is your current value)

mysql> SELECT * FROM landscape\G