Spectrum Report Manager missing table error

Document ID : KB000118320
Last Modified Date : 24/10/2018
Show Technical Document Details
Issue:
Spectrum Report Manager (SRM)  is not starting. The $SPECROOT/tomcat/logs/catalina.out (Linux and Solaris) stdout.log (Windows) is showing an error stating a table doesn't exist. For example:

ERROR 1146 (42S02) at line 31 in file: 'D:\win32app\Spectrum\Install-Tools\netqos\sql\permit_admin_for_netqos_integ.sql': Table 'mysql.servers' doesn't exist

The above error states the servers table in the mysql database does not exist.
Environment:
Any version of Spectrum running on any supported operating system.
Resolution:
Using the above as an example, the mysql.servers table is either missing or corrupt.

To check if the mysql.servers table is missing:

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 mysql

4. At the mysql prompt, enter "show tables;" to see if the servers table is there. The following is an example of the output if the servers table is missing:

User-added image

To check if the mysql.servers table is corrupt

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 mysql

4. At the mysql prompt, enter "show tables;" to see if the servers table is there. The following is an example of the output if the servers table is present:

User-added image

5. If the services table is present, enter "check table servers;" to check the status of the table. The following is an example of a corrupt servers table:

User-added image

Recreate the servers table if corrupt or missing:

1. The first thing you will need to know is how the table was created. On an SRM system that is not having the problem with the table:
     a. Login into the system as the user that owns the Spectrum installation
     b. If on Windows, start a bash shell by running "bash -login"
     c. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

    ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot mysql

    d. At the mysql prompt, enter "show create table servers;" to see how the table was created. The following is the output:

User-added image

    e. Note the entire "CREATE TABLE" command used to create the table

2. The next thing it to create the table on the SRM system where the issue is occurring:
     a. Login into the system as the user that owns the Spectrum installation
     b. If on Windows, start a bash shell by running "bash -login"
     c. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

    ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot mysql

    d. If the table is corrupt, enter "drop table servers;" to remove the table
    e. Enter the "CREATE TABLE" command noted from step 1d above to recreate the table.

User-added image

NOTE: You will need to include a semi colon character ; at the end of the last line of the command

    f. Run "show tables;" to verify the servers table exists

3. Stop and restart tomcat