How can I fix database corruption on an InnoDB table?
Article ID: 10405
Updated On: 01-23-2024
Products
Issue/Introduction
CA Spectrum uses mySQL databases for the Archive Manager (ddmdb) and the reporting databases, but both use different storage engines;
MyISAM for the ddmdb and a mixture of InnoDB and MyISAM for the reporting database.
InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater availability as database sizes grow. InnoDB tables crash much less frequently than MyISAM tables, but when they do, how do we fix them?
How to recognize InnoDB tables from MyISAM tables.
MyISAM tables are comprised of <table>.frm, <table>.MYD and <table>.MYI files.
InnoDB tables are comprised of <table>.from and <table>.ibd files.
This is seen by comparing the contents of the reporting database and the ddmdb in the SPECROOT/mysql/data directory.
Environment
All currently supported versions of Spectrum use both InnoDB and MyISAM tables.
Resolution
1. On the server with the corrupted db, make a copy of $SPECROOT\mysql\my-spectrum.cnf as we will edit it.
2. turn on the forced recovery, by adding the following to the [mysqld] section of my-spectrum.cnf:
innodb_force_recovery = 1
* see note 1. below
3. run a mysqlcheck on the entire db to see exactly which tables are corrupted. Below we specified the ddmdb, change this to "reporting" if needed.
linux:
./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb
windows
./mysqlcheck -uroot -p<password> ddmdb
4. export the corrupted tables. Here we are using ddmdb.event as an example - change that for the table you wish to repair. Be careful to remove the correct table.
mysqldump -defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb event > eventdump.sql
5. drop the corrupted tables.
log into mysql
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb
./mysql -uroot -p<password> ddmdb
use ddmdb;
drop table event;
verify it has been removed with:
show tables;
6. turn off forced recovery by either removing the line or commenting it out. Do not set it to 0.
# innodb_force_recovery = 1
7. import the exported tables from the Mysql dump in step 4.
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb < eventdump.sql
./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t ddmdb < eventdump.sql
8 run mysqlcheck again to verify that corruption was removed
./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
ddmdb.attribute OK
ddmdb.db_descriptor OK
ddmdb.event OK
ddmdb.landscape OK
ddmdb.model OK
ddmdb.model_attribute OK
ddmdb.model_name_extension OK
ddmdb.model_type OK
ddmdb.oid_suffix OK
ddmdb.partitionlog OK
ddmdb.statistic OK
ddmdb.statistic_string OK
ddmdb.statistic_ul64 OK
ddmdb.user_def OK
* note 1.
innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery are 1 to 6. A larger value includes the functionality of lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2.
If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
Additional Information
Forcing InnoDB Recovery:
https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
using mysqldump
https://knowledge.broadcom.com/external/article?articleNumber=72606
Feedback
