We saw corruption on the events table of the Archive Manager as follows:
Error opening the DDM database. One or more tables are corrupt.
Recovery options:
1. Run `ArchMgr -repair` to attempt recovery
2. Load a valid DDM savefile using `ddm_load <savefile>`
3. Initialize the DDM database using `ddm_load -i <LANDSCAPE_HANDLE>`
Segmentation fault (core dumped)
However the "ArchMgr -repair" did not resolve the issue and gave the following errors.
ERROR TRACE at ModelArchDBImp.cc(4814): doSqlQuery/mysql_query: Failure executing query:
SHOW COLUMNS FROM event LIKE 'node_id' - Table './ddmdb/event' is marked as crashed and last (automatic?) repair failed
Database corruption detected:
ddmdb.event - Record at pos: xxxxxxxxx is not remove-marked
ddmdb.event - record delete-link-chain corrupted
ddmdb.event - Corrupt
Repairing...
ddmdb.event - Can't create new tempfile: './ddmdb/event.TMD'
ddmdb.event - Operation failed
May 08 09:13:53 : ArchMgr successfully repaired database.
We verified that there was enough free disk space to run the utility and tried to initialize the database but again got errors.
./ddm_load -i 0xxxxxxxx
SPC-SHD-29021:
ERROR TRACE at ModelArchDBImp.cc(4814): doSqlQuery/mysql_query: Failure executing query:
SHOW COLUMNS FROM event LIKE 'node_id' - Table './ddmdb/event' is marked as crashed and last (automatic?) repair failed
We then tried unsuccessfully to use the mySQLcheck utility, to force a repair of the table but got the following errors.
./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -proot --repair --force ddmdb
ddmdb.attribute OK
ddmdb.db_descriptor OK
ddmdb.event
error : Can't create new tempfile: './ddmdb/event.TMD'
status : Operation failed
All MySQL databases that use MyISAM storage e.g. the DDMDB prior to Spectrum 10.3, or DDMDB in 10.3 and above which have not been converted to InnoDB.
This resolution does not apply to DDMDB that have been converted to InnoDB engine in 10.3 and above, and also not the SRM Reporting database which has been using InnoDB engine since Spectrum 9.3. For InnoDB engine database repair, use MySQLCheck - see reference below in "Additional Information"
The resolution is to use the offline mysql repair utility myISAMchk
1. Stop mySQL (* please see below for more details)
2. Stop the archive manager
3. From a shell Navigate to $SPECROOT/mysql/bin
4. Execute: ./myisamchk -r -n -f ../data/ddmdb/event.MYI
5. restart mySQL (** please see below for more details)
* you will need to use commandC to stop mySQL - otherwise processd will restart it straight away.
from SPECROOT/bin, to stop mySQL run
./cmdC localhost 2 MYSQL
** When ready to restart mySQL, run
./launchinstdbapp localhost MYSQL y MYSQL.OUT
If repairing an InnoDB engine database, use mysqlcheck. See MySQL manual for reference: https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html