MySQL InnoDB Corruption after power outage, possible to recover?
I recently started trying to get Redmine up and running after a power outage that seems to have corrupted our InnoDB database in MySQL. Redmine had an extensive set of documentation that I would like to get even if redmine isn't able to run. The service fails on startup. I have tried inserting innodb_force_recovery = 4 per the documentation from the url in the error log. (also tried 1 thru 6 as I have backed up all directories after the corruption) I have verified through "mysqld-nt --print-defaults" that it is starting with the recovery option in the params.
The machine is running Windows Server 2003 SP2, Xeon E5335 with 2GB RAM, MySQL is not mirrored to another machine, nor is the machine a mirror. I do not have any backups because the previous person did not set them up.
Here is the error log:
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100308 14:50:01 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...
100308 14:50:02 InnoDB: Error: page 7 log sequence number 0 935521175
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
100308 14:50:02 InnoDB: Error: page 2 log sequence number 0 935517607
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
100308 14:50:02 InnoDB: Error: page 11 log sequence number 0 935517607
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
100308 14:50:02 InnoDB: Error: page 5 log sequence number 0 972973045
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
100308 14:50:02 InnoDB: Error: page 6 log sequence number 0 972984051
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
100308 14:50:02 InnoDB: Error: page 1577 log sequence number 0 972737368
InnoDB: is in the future! Current system log sequence number 0 933419020.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
InnoDB: Error: trying to access page number 4294965119 in space 0,
InnoDB: space name .\ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
100308 14:50:02InnoDB: Assertion failure in thread 960 in file .\fil\fil0fil.c line 3959
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
100308 14:50:02 [ERROR] mysqld-nt: Got signal 11. Aborting!
100308 14:50:02 [ERROR] Aborting
100308 14:50:02 [Note] mysqld-nt: Shutdown complete
I recently ran into this problem and was not able to "recover" per se, but was able to use mysqldump while innodb_force_recovery was set and later re-create the DB with it. If you are not able to dump out your data due to errors, see:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/
For a strategy to find the 'bad' data, and dump out the 'good' stuff.