Dealing with corrupted InnoDB data

MySQLData corruption! It can happen. Maybe because of a bug or storage problem that you didn’t expect, or MySQL crashes when a page checksum’s result is different from what is expected. Either way, corrupted InnoDB data can and does occur. What do you do then?

Let’s look at the following example and see what can be done when you face this situation.

We have some valuable data:

One day the query you usually run fails and your application stops working. Even worse, it causes the crash already mentioned:

Usually, this is the point when panic starts. The error log shows:

OK, our database is corrupted and it is printing the page dump in ASCII and hex. Usually, the recommendation is to recover from a backup. In case you don’t have one, the recommendation would be the same as the one given by the error log. When we hit corruption, the first thing we should try is dumping the data and then re-importing to another server (if possible). So, how we can read a corrupted TABLE and avoid the crash? In most cases, the  innodb_force_recovery  option will help us. It has values from 1 to 6. They are documented here:

The idea is to start with 1. If that doesn’t work, proceed to 2. If it fails again, then go to 3 . . . until you find a value that allows you to dump the data. In this case I know that the problem is a corrupted InnoDB page, so a value of 1 should be enough:

“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.”

We add innodb_force_recovery=1 and restart the service. Now it’s time to try and dump our data with mysqldump. If the corruption is even worse you need to keep trying different modes. For example, I have this error:

innodb_force_recovery=1 doesn’t work here. It doesn’t allow me to dump the data:

but in my test server, it seems that innodb_force_recovery=3  helps.

This procedure sounds good and usually works. The problem is that the feature is mostly broken after 5.6.15. innodb_force_recovery values greater or equal 4 won’t allow the database to start:

Bugs are reported and verified here:

That means that if you have Insert Buffer, Undo Log or Redo log corruption (values 4, 5 and 6) you can’t continue. What to do?

  • You can install an older version of MySQL (previous to 5.6.15) to use higher values of innodb_force_recovery. Modes 4, 5 and 6 can corrupt your data (even more) so they are dangerous. If there are no backups this is our only option, so my recommendation would be to make a copy of the data we have now and then proceed with higher values of innodb_force_recovery.


  • If you are using Percona Server, innodb_corrupt_table_action  can be used to dump the data. You can use the value “salvage”. When the option value is salvage, XtraDB allows read access to a corrupted tablespace, but ignores corrupted pages.

If you can’t still dump your data, then you should try more advance solutions like Undrop for InnoDB. Also, it would be good idea to start planning to create regular database backups.    🙂

Share this post