Spectrum Archive Manager missing ddmdb.event table

Document ID : KB000121312
Last Modified Date : 16/11/2018
Show Technical Document Details
Issue:
Spectrum Archive Manager will not start. The following error is seen in the $SPECROOT/SS/DDM/ARCHMGR.OUT file:

Nov 14 10:27:00 ERROR TRACE at ModelArchDBImp.cc(4969): doSqlQuery/mysql_query: Failure executing query:
SHOW COLUMNS FROM event LIKE 'node_id' - Table 'ddmdb.event' doesn't exist

Nov 14 10:27:00 ERROR TRACE at ModelArchDBImp.cc(4969): doSqlQuery/mysql_query: Failure executing query:
SHOW COLUMNS FROM event LIKE 'vardata_string' - Table 'ddmdb.event' doesn't exist
Environment:
Any Spectrum version installed on any support operating system
Resolution:
Recreate the ddmdb.event table and then reinitialize the ddmdb.

Prior to recreating the ddmdb.event table, you will need to know how the ddmdb.event table was created for you version of Spectrum. Do the following on another SpectroSERVER system with the same Spectrum version and patches installed:

1. Log into the Spectrum system as the user that owns the Spectrum installation 

2. On windows start a bash shell by running "bash -login" 

3. cd to the $SPECROOT/mysql/bin directory and log into mysql: 

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

4. At the mysql> prompt, enter the following command to find out how the ddmdb.event table was created:

show create table event;

The following is an example of the output from a Spectrum 10.3.0 install with no patches installed:

CREATE TABLE `event` ( 
`model_h` int(10) unsigned NOT NULL, 
`utime` int(11) NOT NULL, 
`counter` int(10) unsigned NOT NULL, 
`clk_seq` smallint(5) unsigned NOT NULL, 
`version` tinyint(4) NOT NULL, 
`node_id` char(6) NOT NULL, 
`user_key` int(10) unsigned NOT NULL, 
`type` int(10) unsigned NOT NULL, 
`severity` int(10) unsigned NOT NULL, 
`vardata_string` mediumtext, 
PRIMARY KEY (`model_h`,`utime`,`counter`,`clk_seq`,`version`,`node_id`), 
KEY `event_ie1` (`utime`,`counter`), 
KEY `event_ie2` (`type`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50500 PARTITION BY RANGE COLUMNS(utime) (PARTITION EVENT_7_11_2018 VALUES LESS THAN (1541653200) ENGINE = InnoDB, PARTITION EVENT_8_11_2018 VALUES LESS THAN (1541739600) ENGINE = InnoDB, PARTITION EVENT_9_11_2018 VALUES LESS THAN (1541826000) ENGINE = InnoDB, PARTITION EVENT_10_11_2018 VALUES LESS THAN (1541912400) ENGINE = InnoDB, PARTITION EVENT_11_11_2018 VALUES LESS THAN (1541998800) ENGINE = InnoDB, PARTITION EVENT_12_11_2018 VALUES LESS THAN (1542085200) ENGINE = InnoDB, PARTITION EVENT_13_11_2018 VALUES LESS THAN (1542171600) ENGINE = InnoDB, PARTITION EVENT_14_11_2018 VALUES LESS THAN (1542258000) ENGINE = InnoDB, PARTITION EVENT_15_11_2018 VALUES LESS THAN (1542344400) ENGINE = InnoDB) */


The following is an example of the output from a Spectrum 10.2.3 install with no patches installed:

CREATE TABLE `event` (
  `model_h` int(10) unsigned NOT NULL,
  `utime` int(11) NOT NULL,
  `counter` int(10) unsigned NOT NULL,
  `clk_seq` smallint(5) unsigned NOT NULL,
  `version` tinyint(4) NOT NULL,
  `node_id` char(6) NOT NULL,
  `user_key` int(10) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `severity` int(10) unsigned NOT NULL,
  `vardata_string` mediumtext,
  PRIMARY KEY (`model_h`,`utime`,`counter`,`clk_seq`,`version`,`node_id`),
  KEY `event_ie1` (`utime`,`counter`),
  KEY `event_ie2` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


Now that you have the information on how the ddmdb.event table is created, you can create the ddmdb.event table on the system where you are seeing the issue occur by doing the following:

1. Log into the Spectrum system as the user that owns the Spectrum installation 

2. On windows start a bash shell by running "bash -login" 

3. cd to the $SPECROOT/mysql/bin directory and log into mysql: 

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

4. At the mysql> prompt, enter the following command to drop the event table: 

drop table event; 

5. At the mysql> prompt, enter the command to recreate the ddmdb.event table as noted previously.

NOTE: Make sure to include a semicolon ; character at the end of the CREATE command.

For example, if running Spectrum 10.3.0 with no patches, run the following CREATE command with the semi colon ; at the end

CREATE TABLE `event` ( 
`model_h` int(10) unsigned NOT NULL, 
`utime` int(11) NOT NULL, 
`counter` int(10) unsigned NOT NULL, 
`clk_seq` smallint(5) unsigned NOT NULL, 
`version` tinyint(4) NOT NULL, 
`node_id` char(6) NOT NULL, 
`user_key` int(10) unsigned NOT NULL, 
`type` int(10) unsigned NOT NULL, 
`severity` int(10) unsigned NOT NULL, 
`vardata_string` mediumtext, 
PRIMARY KEY (`model_h`,`utime`,`counter`,`clk_seq`,`version`,`node_id`), 
KEY `event_ie1` (`utime`,`counter`), 
KEY `event_ie2` (`type`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50500 PARTITION BY RANGE COLUMNS(utime) (PARTITION EVENT_7_11_2018 VALUES LESS THAN (1541653200) ENGINE = InnoDB, PARTITION EVENT_8_11_2018 VALUES LESS THAN (1541739600) ENGINE = InnoDB, PARTITION EVENT_9_11_2018 VALUES LESS THAN (1541826000) ENGINE = InnoDB, PARTITION EVENT_10_11_2018 VALUES LESS THAN (1541912400) ENGINE = InnoDB, PARTITION EVENT_11_11_2018 VALUES LESS THAN (1541998800) ENGINE = InnoDB, PARTITION EVENT_12_11_2018 VALUES LESS THAN (1542085200) ENGINE = InnoDB, PARTITION EVENT_13_11_2018 VALUES LESS THAN (1542171600) ENGINE = InnoDB, PARTITION EVENT_14_11_2018 VALUES LESS THAN (1542258000) ENGINE = InnoDB, PARTITION EVENT_15_11_2018 VALUES LESS THAN (1542344400) ENGINE = InnoDB) */;


6. Exit mysql

7. Reinitialize the ddmdb using the ddm_load command
    a. cd to the $SPECROOT/SS directory and enter the following command to note the landscape handle

../SS-Tools/lh_set

    b. cd to the $SPECROOT/SS/DDM directory
    c. Enter the following command to reinitialize the ddmdb where <LH> is the decimal value of the landscape handle noted above:

./ddm_load -i <LH>

8. Start the Archive Manager