Spectrum Archive Manager missing ddmdb.event table
Article ID: 121312
Updated On:
Products
Issue/Introduction
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
Resolution
First, try to initialize the DDM database from $SPECROOT/SS/DDM:
./ddm_load.pl -il landscapehandle
If that works, just start the ArchMgr with the ./startSS.pl script.
If that fails, recreate the ddmdb.event table and then reinitialize the ddmdb with the proceeding steps:
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 -p<PASSWD> 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 -p<PASSWD> 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
Feedback
