Friday, June 4, 2010

Resolving 'The log sequence number in ibdata files does not match' Error

When using InnoDB storage engine, the ibdata files serve as system tablespace containers. Along with internal system information, these files might also contain user indexes and data. If any of these file(s) get corrupt, the entire database can go inaccessible. The article mentions one of such possible scenarios and then solutions that can help you to Repair MySQL Database.

For more clarification, consider a case when a server running MySQL on InnoDB engine undergoes unexpected power outage. After this, when you attempt to start the MySQL database, it might not run and behave abruptly. On viewing the log report for the behavior, you observe the following entries along with the related time stamps and server name:

“InnoDB:The log sequence number in ibdata files does not match
InnoDB:The log sequence number in the ib_logfiles!
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 doublewrite
InnoDB: buffer...
InnoDB: Page directory corruption: supremum not pointed to..”

The above symptoms indicate table corruption in MySQL database, which might have occurred due to last unexpected power failure. More specifically, the ibdata file(s) have gone damaged.

Solution

If you encounter above stated or similar problems, try one of the suitable methods to solve it:
  • Check for the last available backup and restore your database.
  • In case backup is incomplete, try to repair the database using 'innodb_force_recovery = 4' repair command. However, the command may fail if extent of corruption is high.
  • If Innodb repair command has failed, you can delete the ibdata tablespace and recreate it after shutting down the server, if possible. Alternatively, you can rename the ibdata and the ib_log files. But doing so causes data loss. If this is not a suitable method, try the next solution.
  • To repair the corrupt MySQL database, use a third-party MySQL Repair utility. These are special tools that can Repair MySQL Database by running the most effective and safe scanning algorithms. Since they are equipped with an interactive design, any user, even with no technical knowledge, can use them easily.
Recovery For MySQL is a high-end tool that can repair both InnoDB and MyISAM databases. The MySQL database repair utility can recover both Windows and Linux stored database. Supporting MySQL 5.x and 4.x, this read-only tool maintains the original integrity of database because of non-destructive scanning algorithms.

Resource Link: http://ezinearticles.com/?Resolving-The-Log-Sequence-Number-in-Ibdata-Files-Does-Not-Match-Error&id=4392345

No comments:

Post a Comment