subject: Resolving Error 1146 Post Innodb Table Corruption [print this page] Corruption in InnoDB table results in an error message, which renders all stored records inaccessible. Few main causes for InnoDB table corruption are unexpected MySQL Server shutdown, virus infection, and MySQL bugs. To overcome this error message, a database user needs to restore the table from 'InnoDB Hot Backup'. However, if the user has not created any hot backup of his/her InnoDB tables, then he/she needs to use in-built repair command(s) to repair the corrupt tables. But, if the repair command(s) falls short of repairing the table, then the user needs to use an effective MySQL Repair application.
Consider, your InnoDB table gets corrupted due to improper shutdown of the server. Now, when you try 'InnoDB force recovery' with value '2', you encounter the below error message stating that the actual pages do not match with the number of pages in the header:
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files.
Error: tablespace size stored in header is 978944 pages, but
InnoDB: the sum of data file sizes is only 948992 pages
070724 0:11:30 InnoDB: Started; log sequence number 671 2302454239
While 'Show Table' displays the correct number of InnoDB tables, the below error message appears when you try to access any particular table:
To resolve the above error message and to access the records saved in the InnoDB table, follow the below steps:
Restore the table from a valid backup.
However, if the backup does not exist, then you need to use an effective MySQL Database Repair application.
Such MySQL Recovery tools use effective repair techniques to repair a corrupt InnoDB table and restore it at specified location. The read only tools perform safe repair by not deleting or modifying the original data saved in the InnoDB table. These applications can be downloaded from the Internet.
Stellar Phoenix Database Recovery For MySQL repairs both InnoDB and MyISAM tables after every logical crash scenario. Supported by MySQL 5.x and 4.x, the MySQL Database Repair application serves as a self-explanatory application. It is designed for Windows 7, Vista, XP, 2003 and 2000 operating systems. It's demo version is also available for free, so a user can see and test the software before buying it.
