SRMArchiverLog merge error seen in Spectrum Report Manager log file

Products

• CA eHealth
• CA Spectrum

Issue/Introduction

SRMArchiverLog merge error seen in Spectrum Report Manager (SRM) $SPECROOT/tomcat/logs/stdout.log (Windows) catalina.out (Linux):

(SRM/Startup/Container) (SRMArchiverLog) - (ERROR) - Could not alter archive table storage engine to MERGE :
Caused by: oorg.springframework.jdbc.UncategorizedSQLException: StatementCallback;
uncategorized SQLException for SQL [ALTER TABLE archive.event_mrg ENGINE =     ];
SQL state [HY000]; error code [1572]; Engine cannot be used in partitioned tables;
nested exception is java.sql.SQLException: Engine cannot be used in partitioned tables

This does not seem to cause any noticable issues.

Cause

The event_mrg table in the archive database did not get created properly

Environment

Release : Any

Component : Spectrum Reporting

Resolution

1. Log into the SRM 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 archive

4. Enter the following command at the mysql prompt:

DROP TABLE IF EXISTS `event_mrg`;

5. Enter the following command at the mysql prompt (this is one long command):

CREATE TABLE `event_mrg` (
  `event_key` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` binary(26) NOT NULL,
  `landscape_h` int(10) unsigned NOT NULL,
  `model_key` int(10) unsigned NOT NULL DEFAULT '0',
  `time` datetime NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `creator_id` int(10) unsigned NOT NULL,
  `event_msg` text,
  `server_precedence` int(10) unsigned DEFAULT '0',
  `year` smallint(4) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `source_table` varchar(64) NOT NULL,
  `load_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`event_key`),
  UNIQUE KEY `event_id_model_key_idx` (`event_id`,`model_key`),
  KEY `landscape_idx` (`landscape_h`),
  KEY `model_key_idx` (`model_key`),
  KEY `time_idx` (`time`),
  KEY `landscape_and_time_idx` (`landscape_h`,`time`),
  KEY `type_idx` (`type`),
  KEY `creator_id_idx` (`creator_id`),
  KEY `idx_event_time` (`year`,`month`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=500;

6. Enter quit at the mysql prompt to log out of mysql

7. Restart tomcat on the SRM system

8. Verify the errors are no longer seen in the stdout/catalina.out file

Additional Information

Note: starting from Spectrum 21.2.4 the mysql root's default password is not longer "root" but "MySqlR00t", so to login to mysql you may need to run:

./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t archive