How can I fix database corruption on an InnoDB table?
Article ID: 10405
Updated On:
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
Resolution
1. On the SRM server, browse to $SPECROOT\mysql\my-spectrum.cnf
Replace $specroot with spectrum install path.
2. turn on the forced recovery, by adding the following to the [mysqld] section:
innodb_force_recovery = 1
* see note 1. below
3. run a mysqlcheck on the entire db to see exactly which tables are corrupted.
4. export the corrupted tables.
mysqldump -defaults-file=../my-spectrum.cnf -uroot -proot reporting bucketactivitylog > dump.sql
5. drop the corrupted 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 -proot reporting < dump.sql
* 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
Feedback
