XtraDB/InnoDB Recovery Modes

The innodb_force_recovery server system variable sets the XtraDB/InnoDB recovery mode. A mode of 0 is normal use, while the higher the mode, the more stringent the restrictions. Higher modes incorporate all limitations of the lower modes.

With a recovery mode < 4, only corrupted pages should be lost. With 4, secondary indexes could be corrupted. With 5, results could be inconsistent and secondary indexes could be corrupted (even if they were not with 4). A value of 6 leaves pages in an obsolete state, which might cause more corruption.

To recover the tables, you can execute SELECTs to dump data, and DROP TABLE to remove corrupted tables.

The following modes are available:

Recovery Modes

• 0
  • 0 is the default mode while XtraDB/InnoDB is running normally. It is the only mode permitting changes to the data.

• 1
  • 1 (SRV_FORCE_IGNORE_CORRUPT) allows the the server to keep running even if corrupt pages are detected. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.

• 2
  • 2 (SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge.

• 3
  • 3 (SRV_FORCE_NO_TRX_UNDO) does not roll back transactions after the crash recovery.

• 4
  • 4 (SRV_FORCE_NO_IBUF_MERGE) does not calculate tables statistics and prevents insert buffer merges.

• 5
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the undo logs when starting.

• 6
  • 6 (SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.