1. Repair Corrupted InnoDB Tables With Secondary Index Corruption

Repair Corrupted InnoDB Tables With Secondary Index Corruption

UPDATE: We announced online data recovery from corrupt databases.

Check it out on https://recovery.twindb.com/

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and don’t support its open source versions anymore.

InnoDB provides no means to repair corrupted table space. Once a table got corrupted, the only way to repair MySQL files is to start it with innodb_force_recovery={4,5,6} in hope that you can dump the table, so you can rebuild the table space from scratch. At least this is what the manual says. But let’s take a closer look at InnoDB corruption. In some cases, you can repair InnoDB table space much faster.

UPDATE: If corruption is in the PRIMARY index, check this post: Recover Corrupt MySQL Database

What InnoDB Provides To Repair Tablespace Corruption

InnoDB doesn’t let you repair the table space, but you can rebuild secondary indexes with the ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.

So, before starting the whole story with innodb_force_recovery:

  • run mysql with innodb_force_recovery
  • dump the table’s data in a file
  • drop the table
  • create the empty table
  • reload the data from the dump

This is the traditional table repair method.

Who knows, maybe it can give clues on how to repair the table space without rebuilding it from scratch.

Check Where The Corruption Is

Let me explain. As usually, we work with the actor table from the sakila database. It has a secondary key on last_name which is good for our case:

So I went ahead and did some modifications on one page of the secondary index idx_actor_last_name. Any access to the index will lead to checksum test failure which is enough for InnoDB to crash.

Let’s check the error log,

then a dump of the page 4 goes:

InnoDB is even friendly enough to tell you that page 4 belongs to the secondary index idx_actor_last_name.

There are several other messages before it dies.

The key message however is that the corruption is in the secondary index idx_actor_last_name.

If you try to drop the index, MySQL will crash.

How To Fix The Wrong InnoDB Page Checksum

There is an innochecksum tool in the MySQL distribution that can test the offline InnoDB tablespace.
We slightly modified innochecksum and added option -f – that means if the checksum of a page is wrong, it will rewrite it in the InnoDB page header.

So, download the latest revision of TwinDB Recovery Toolkit and compile it:

Stop MySQL and make a copy of actor.ibd, just in case something goes wrong.
Now, test and fix checksums in actor.ibd. Page 4 is bad indeed.

And now add the -f option to fix the checksums.

Start MySQL And Fix The Corrupted Tablespace

Now, start MySQL. Innodb_force_recovery option should be disabled.

When MySQL starts, drop the idx_actor_last_name index:

For extra security you may want to rebuild the table:

Now you may add the idx_actor_last_name index again:

Now, the InnoDB tablespace is nice and clean.

Have a question? Ask the experts!

Previous Post Next Post