What do I do if innodb_force_recovery does not fix corruption of a MySQL database?

Document ID : KB000010486
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Corruption of InnoDB databases is not very common but can be difficult to fix.  

One option is to use innodb_force_recovery;  see the Additional Information sections below.

If that does not work, what can we do?

Background:

As an example, due to corruption of MySQL, we were unable to start the MySQL service and forced recovery did not allow to export the corrupt table below:

mysqldump -defaults-file=../my-spectrum.cnf -uroot -proot reporting bucketactivitylog > dump.sql 

mysqldump: Got error: 145: Table './reporting/bucketactivitylog' is marked as crashed and should be repaired when doing LOCK TABLES 

 

 

 

 

 

Environment:
All currently supported versions of Spectrum use both InnoDB and MyISAM tables.
Instructions:

Option A.

1. Delete the corrupt table.  

2. Start MySQL.  (if it does not start, proceed to Option B).

3. RpmgrInitializelandscape 

4. then will create the corrupt table using mysql command.  In this example, we face corruption of the bucketactivitylog table, so the following instructions are specific to that table.

CREATE TABLE `bucketactivitylog` ( `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `bucket_table_name` varchar(255) NOT NULL, `landscape_h` int(10) unsigned NOT NULL, `handler_name` varchar(255) NOT NULL, `finished_inserting_events` tinyint(1) NOT NULL DEFAULT '0', `creation_time` datetime DEFAULT NULL, `destroy_time` datetime DEFAULT NULL, `event_log_id` int(10) unsigned NOT NULL, PRIMARY KEY (`log_id`), KEY `bucket_table_name` (`bucket_table_name`), KEY `landscape_h` (`landscape_h`), KEY `handler_name` (`handler_name`), KEY `finished_inserting_events` (`finished_inserting_events`), KEY `creation_time` (`creation_time`), KEY `event_log_id` (`event_log_id`) ENGINE=InnoDB

 

Option B.

The following should only be performed as a last resort, as it deletes all the report data, i.e. if we still cannot start mysql, after deleting  the corrupt table.

1. delete the ibdata file 

2.  Start MySQL.  (if it does not start, proceed to Option C).

3.  Run RpmgrInitializelandscape 

 

Option C.

1. Is this a virtual system?  

If yes, then there should be an option to reset the virtual image, to an earlier version of OneClick/SRM.

2.  Run RpmgrInitializelandscape 

 

Option D.

1. The final option is to uninstall OneClick/SRM from this server and reinstall.

OC and SRM need to be uninstalled together, as they are installed together.

2.  Run RpmgrInitializelandscape 

 

 

Additional Information:

How to run RpmgrInitializelandscape:

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.TEC521258.html 

 

TEC1169495: How can I fix database corruption on an InnoDB table?