MySQL Server Crash: “InnoDB: Error: trying to access page number … which is outside the tablespace

When attempting to start MySQL after a crash – such as after a power failure, an error like the following occurs:

130227 11:54:17 InnoDB: Assertion failure in thread 4096 in file 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.
130227 11:54:17 – mysqld got signal 11 ;

This is caused by corruption of the InnoDB data file at the file system level.

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of changes written to disk are not guaranteed, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command  hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

The following steps can be taken to recover from the corruption:

While MySQL is shut down, create a backup by copying all the database files.

This is very important as the use of innodb_force_recovery can potentially cause further corruption.

Try starting MySQL with innodb_force_recovery enabled. It is likely that a large value will be needed. In recovery mode, attempt to dump as much of the data as possible after which InnoDB must then be re-initialised. This procedure is also described in the Reference Manual: Starting InnoDB on a Corrupted Database.

Recover from a backup. This is often the only solution that will work. If you choose this solution, it is recommended to first make a backup of the corrupted files in case further attempts to recover data from them is required.