How can I fix database corruption on an InnoDB table?
search cancel

How can I fix database corruption on an InnoDB table?

book

Article ID: 10405

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

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