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

Products

• CA Spectrum
• DX NetOps

Issue/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?

NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

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 -p<PASSWD> 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.

Resolution

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 can I fix database corruption on an InnoDB table?


https://comm.support.ca.com/kb/how-can-i-fix-database-corruption-on-an-innodb-table/KB000010405