search cancel

Events are not getting updated in SRM - Spectrum Report Manager

book

Article ID: 242949

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

The reporting database is out of sync.

The following error was found in the $SPECROOT/tomcat/logs/stdout.log file:

May 30, 2022 18:54:56.227 (SRM/LandscapeManager/LandscapeThread_0) (ReportManager) - (ERROR) - Error initializing event polling
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT log_id FROM instanteventactivitylog WHERE landscape_h=? ORDER BY poll_start_time DESC LIMIT 1]; SQL state [HY000]; error code [145]; Table '.\reporting\instanteventactivitylog' is marked as crashed and should be repaired; nested exception is java.sql.SQLException: Table '.\reporting\instanteventactivitylog' is marked as crashed and should be repaired
 at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:750)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)
 at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:921)
 at com.aprisma.spectrum.app.repmgr.dc.db.dao.jdbc.JdbcEventActivityLogDAO.findLastInstLogEntry(JdbcEventActivityLogDAO.java:116)
 at com.aprisma.spectrum.app.repmgr.dc.event.EventPoller.init(EventPoller.java:370)
 at com.aprisma.spectrum.app.repmgr.dc.event.EventPollerFactory.createOrGetEventPoller(EventPollerFactory.java:107)
 at com.aprisma.spectrum.app.repmgr.dc.landscape.LandscapeManager.setupEventPoller(LandscapeManager.java:231)
 at com.aprisma.spectrum.app.repmgr.dc.landscape.LandscapeManager.access$200(LandscapeManager.java:40)
 at com.aprisma.spectrum.app.repmgr.dc.landscape.LandscapeManager$LandscapeThread.run(LandscapeManager.java:109)
Caused by: java.sql.SQLException: Table '.\reporting\instanteventactivitylog' is marked as crashed and should be repaired
 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:722)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
 ... 11 more



Cause

The instanteventactivitylog table is corrupted.

Environment

Release : 21.2

Component : Spectrum Reporting

Resolution

1st option - repair the corrupted table:

oscommandline> mysqlcheck -u<username> -p<password> <database_name> [<table_name>]

$ cd $SPECROOT/mysql/bin/

$ ./mysqlcheck --repair --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting instanteventactivitylog

 

===================================

2nd option - recreate the corrupted table and initialize the reporting database:

1. First stop the Spectrum Tomcat service.

2. Drop the corrupted table.

mysql> drop table instanteventactivitylog;

3. Ensure the physical files associated with the corrupted table were removed. (instanteventactivitylog.MYD, instanteventactivitylog.MYI, and instanteventactivitylog.frm)

4. Recreate the table by running the following syntax:

CREATE TABLE `instanteventactivitylog`(`log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `landscape_h` int(10) unsigned NOT NULL, `poll_start_time` datetime NOT NULL, `poll_end_time` datetime DEFAULT NULL, `nbr_of_events` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`log_id`), KEY `landscape_h` (`landscape_h`)) ENGINE=MyISAM AUTO_INCREMENT=32613 DEFAULT CHARSET=utf8;

5. Initialize the reporting db.

$ cd $SPECROOT/bin/ 

./RpmgrInitializeLandscape root MySqlR00t -initHist 45 -all

6. Start the Spectrum Tomcat service.

Additional Information

1. Open a bash shell on the OneClick + SRM machine

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

3. Run the following syntax to connect to the MySQL command line:

$ ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting -A (Linux)
$ ./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting -A (Linux) (Spectrum 21.2.4 onwards)
$ ./mysql.exe -uroot -proot reporting (Windows)
$ ./mysql.exe -uroot -pMySqlR00t reporting (Windows) (Spectrum 21.2.4 onwards)