SRM will not start missing devicemodule

Products

• CA eHealth
• CA Spectrum

Issue/Introduction

The following error is seen in the $SPECROOT/tocmat/logs/catalina.out (Linux) or stdout.log (Windows) preventing SRM from starting:

Caused by: com.aprisma.util.mysql.ScriptRunnerException: Error occurred with SQL Script
  Script: /opt/spectrum/Install-Tools/rpmgr/sql/srmdbapi/ddl/create_v_dim_device_module.sql
  Stdout:

  Stderr:
ERROR 1146 (42S02) at line 21 in file: '/opt/spectrum/Install-Tools/rpmgr/sql/srmdbapi/ddl/create_v_dim_device_module.sql': Table 'reporting.devicemodule' doesn't exist

        at com.aprisma.util.mysql.SQLScriptRunner.doRunSQLScript(SQLScriptRunner.java:251)
        at com.aprisma.util.mysql.SQLScriptRunner.runScript(SQLScriptRunner.java:158)
        ... 74 more

Environment

Release : Any release of Spectrum

Component : Spectrum Core / SpectroSERVER

Resolution

1. Log into the Spectrum Report Manager (SRM) system as the user that owns the Spectrum installation

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

2. Shutdown the Spectrum tomcat process

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 reporting

4. Enter the following command at the mysql prompt to ensure the devicemodule table is dropped:

drop table devicemodule;

5. Enter the following command at the mysql prompt to rebuild the devicemodule table (This is all one line)

CREATE TABLE `devicemodule` (`module_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `model_key` int(10) unsigned DEFAULT NULL, `module_index` int(10) DEFAULT NULL, `module_name` varchar(255) DEFAULT NULL, `serial_nbr` varchar(255) DEFAULT NULL, `software_rev` varchar(255) DEFAULT NULL, PRIMARY KEY (`module_id`), KEY `key_idx` (`model_key`,`module_index`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6. Enter the following command at the mysql prompt to verify the table is created:

show create table devicemodule;

7. If it does not return an error, exit mysql

8. Start the Spectrum tomcat process and verify the error no longer occurs