Skip navigation links
14.2.4.7 Troubleshooting InnoDB Data Dictionary Operations »
Section Navigation      [Toggle]

14.2.4.6. Starting InnoDB on a Corrupted Database

To investigate database page corruption, you might dump your tables from the database with SELECT ... INTO OUTFILE. Usually, most of the data obtained in this way is intact. Serious corruption might cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, use the innodb_force_recovery option to force the InnoDB storage engine to start up while preventing background operations from running, so that you can dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server: 

[mysqld]
innodb_force_recovery = 4

innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery follow. A larger number includes all precautions of smaller numbers. If you can dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

  • 1 (SRV_FORCE_IGNORE_CORRUPT)

    Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  • 2 (SRV_FORCE_NO_BACKGROUND)

    Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

  • 3 (SRV_FORCE_NO_TRX_UNDO)

    Does not run transaction rollbacks after crash recovery.

  • 4 (SRV_FORCE_NO_IBUF_MERGE)

    Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.

  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

    Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

  • 6 (SRV_FORCE_NO_LOG_REDO)

    Does not do the redo log roll-forward in connection with recovery.

    With this value, you might not be able to do queries other than a basic SELECT * FROM t, with no WHERE, ORDER BY, or other clauses. More complex queries could encounter corrupted data structures and fail.

    If corruption within the table data prevents you from dumping the entire table contents, a query with an ORDER BY primary_key DESC clause might be able to dump the portion of the table after the corrupted part.

The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

You can SELECT from tables to dump them, or DROP or CREATE tables even if forced recovery is used. If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing mass import or ALTER TABLE: kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Vector Thorn on July 9 2011 10:01am[Delete] [Edit]

I ran into a problem where, when dealing with HUGE tables (location tables for http://Stiggler.com ), there was an innodb page error, and mysql would try over and over to repair it, and would inform me that it could not repair it (and would then try again, etc).

I came to this page after receiving the "error 2002 Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect" error, and read the instructions, and had some luck with them, without even getting all the way through the steps.

I put the force_recovery mode to 1, then restarted mysqld, exported the entire database (i expected to get an error when it got to the bad table, but i never had a problem). After dumping the database, i removed the force_recovery option from my.cnf and restarted the service, and after a few moments, it started back up, and the problem was gone.

I suspect that dumping the database to disk may have cleaned the filesystem's cache, maybe? Anyway, before dropping tables, try seeing if just exporting the database and restarting in normal mode will work (it may not; i may have just gotten lucky).

Also, in the documentation above, there is a period (.) missing at the end of the first sentence of the second paragraph: "innodb_force_recovery is 0 by default (normal startup without forced recovery)PERIOD_SHOULD_BE_HERE The permissible nonzero".

Posted by honeybee xu on January 21 2012 11:25pm[Delete] [Edit]

totally agree with you, I dumped my currupted table too, that resolved the problem without reloading it

Add your own comment.