Crash Recovery Failed To Repair Corrupt Innodb Database
MySQL is considered to be the most stable application that is used to create and maintain databases. However, there are still certain problems that are encountered, like the application might crash and result in corruption of database. Such situations occur during operating system malfunction, MySQL bugs, and power failure. In most situations of application malfunction, a MySQL database user encounters an error message, after which the entire database becomes unmountable. In such situations, MySQL automatically attempts to perform a crash recovery to comprehensively repair the database. While the recovery is successful in most cases, it might also fail, in a few situations. To access the database in such cases, a database user needs to restore the data from an up-to-date database backup. However, in case no backup is available or backup is not displaying satisfactory results, the user needs to opt for an effective third-party Repair MySQL application.
To get into the depth of the above situation, consider a practical case wherein you are working on your InnoDB database and encounter a power failure. Now, when you try to mount your database, it does not open. On viewing the logs, you view the below message:
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the double write
InnoDB: buffer...
100125 23:19:51 InnoDB: Started; log sequence number 0 115363767
100125 23:19:51 [Note] Event Scheduler: Loaded 0 events....
After the above error message, the records saved in the InnoDB records become inaccessible. Moreover, crash recovery that is used to recover the database also fails.
Cause:
The root cause for the occurrence of the above error message is corruption of InnoDB database due to improper system shutdown.
Resolution:
To resolve the above error message and to access the database, you need to follow the mentioned below guidelines:
Run innodb_force_recovery = 4 command to repair the database.
If the command fails, restore the database from an updated backup.
In case of deficient or no backup, use a commercial Repair MySQL Database software to repair the database. Such MySQL Repair applications use effective scanning techniques to repair any logically damaged database and restore it at your specified location.
MySQL is a user-friendly tool, that is used by most MySQL database users to repair their InnoDB and MyISAM database. Compatible with MySQL 5.x and 4.x, the MySQL Repair software does not harm the original database, but merely reads data from it and stores it in a new file that is created by the user.
To get into the depth of the above situation, consider a practical case wherein you are working on your InnoDB database and encounter a power failure. Now, when you try to mount your database, it does not open. On viewing the logs, you view the below message:
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the double write
InnoDB: buffer...
100125 23:19:51 InnoDB: Started; log sequence number 0 115363767
100125 23:19:51 [Note] Event Scheduler: Loaded 0 events....
After the above error message, the records saved in the InnoDB records become inaccessible. Moreover, crash recovery that is used to recover the database also fails.
Cause:
The root cause for the occurrence of the above error message is corruption of InnoDB database due to improper system shutdown.
Resolution:
To resolve the above error message and to access the database, you need to follow the mentioned below guidelines:
Run innodb_force_recovery = 4 command to repair the database.
If the command fails, restore the database from an updated backup.
In case of deficient or no backup, use a commercial Repair MySQL Database software to repair the database. Such MySQL Repair applications use effective scanning techniques to repair any logically damaged database and restore it at your specified location.
MySQL is a user-friendly tool, that is used by most MySQL database users to repair their InnoDB and MyISAM database. Compatible with MySQL 5.x and 4.x, the MySQL Repair software does not harm the original database, but merely reads data from it and stores it in a new file that is created by the user.
Source...