Dealing with corrupted InnoDB data

January 19, 2016

Author

Miguel Angel Nieto

MySQL

Share this Post:

Data 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:

&gt; select * from t limit 4;<br>+---+--------+<br>| i | c      |<br>+---+--------+<br>| 1 | Miguel |<br>| 2 | Angel  |<br>| 3 | Miguel |<br>| 4 | Angel  |<br>+---+--------+<br><br>&gt; select count(*) from t;<br>+----------+<br>| count(*) |<br>+----------+<br>|  2097152 |<br>+----------+

1	> select * from t limit 4;<br>+---+--------+<br>\| i \| c \|<br>+---+--------+<br>\| 1 \| Miguel \|<br>\| 2 \| Angel \|<br>\| 3 \| Miguel \|<br>\| 4 \| Angel \|<br>+---+--------+<br><br>> select count() from t;<br>+----------+<br>\| count() \|<br>+----------+<br>\| 2097152 \|<br>+----------+

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

&gt; select * from t where i=2097151;<br>ERROR 2006 (HY000): MySQL server has gone away

1	> select * from t where i=2097151;<br>ERROR 2006 (HY000): MySQL server has gone away

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

2016-01-13 08:01:48 7fbc00133700 InnoDB: uncompressed page, stored checksum in field1 2912050650, calculated checksums for field1: crc32 1490770609, innodb 1549747911, none 3735928559, stored checksum in field2 1670385167, calculated checksums for field2: crc32 1490770609, innodb 2416840536, none 3735928559, page LSN 0 130051648, low 4 bytes of LSN at page end 1476903022, page number (if stored to page already) 4651, space id (if created with &gt;= MySQL-4.1.1 and stored already) 7<br>InnoDB: Page may be an index page where index id is 22<br>InnoDB: (index "PRIMARY" of table "test"."t")<br>InnoDB: Database page corruption on disk or a failed<br>InnoDB: file read of page 4651.<br>InnoDB: You may have to recover from a backup.<br>InnoDB: It is also possible that your operating<br>InnoDB: system has corrupted its own file cache<br>InnoDB: and rebooting your computer removes the<br>InnoDB: error.<br>InnoDB: If the corrupt page is an index page<br>InnoDB: you can also try to fix the corruption<br>InnoDB: by dumping, dropping, and reimporting<br>InnoDB: the corrupt table. You can use CHECK<br>InnoDB: TABLE to scan your table for corruption.<br>InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html<br>InnoDB: about forcing recovery.<br>InnoDB: Database page corruption on disk or a failed<br>InnoDB: file read of page 4651.<br>InnoDB: You may have to recover from a backup.<br>2016-01-13 08:01:48 7fbc00133700 InnoDB: Page dump in ascii and hex (16384 bytes):<br> len 16384; hex ad925dda0000122b0000122affffffff0000000007c06e4045bf00000000000000000<br>[...]

2016-01-13 08:01:48 7fbc00133700 InnoDB: uncompressed page, stored checksum in field1 2912050650, calculated checksums for field1: crc32 1490770609, innodb 1549747911, none 3735928559, stored checksum in field2 1670385167, calculated checksums for field2: crc32 1490770609, innodb 2416840536, none 3735928559, page LSN 0 130051648, low 4 bytes of LSN at page end 1476903022, page number (if stored to page already) 4651, space id (if created with >= MySQL-4.1.1 and stored already) 7 InnoDB: Page may be an index page where index id is 22 InnoDB: (index "PRIMARY" of table "test"."t") InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 4651. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 4651. InnoDB: You may have to recover from a backup. 2016-01-13 08:01:48 7fbc00133700 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex ad925dda0000122b0000122affffffff0000000007c06e4045bf00000000000000000 [...]

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:

http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

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:

&gt; create table t2 like t;<br>&gt; insert into t2 select * from t;<br>ERROR 1034 (HY000): Incorrect key file for table 't'; try to repair it<br>&gt; insert into t2 select * from t;<br>ERROR 1712 (HY000): Index t is corrupted

1	> create table t2 like t;<br>> insert into t2 select * from t;<br>ERROR 1034 (HY000): Incorrect key file for table 't'; try to repair it<br>> insert into t2 select * from t;<br>ERROR 1712 (HY000): Index t is corrupted

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

# mysqldump -uroot -pmsandbox --port 5623 -h 127.0.0.1 --all-databases &gt; dump.sql<br>Error: Couldn't read status information for table t ()

1	# mysqldump -uroot -pmsandbox --port 5623 -h 127.0.0.1 --all-databases > dump.sql<br>Error: Couldn't read status information for table t ()

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:

2015-07-08 10:25:25 315 [ERROR] Unknown/unsupported storage engine: InnoDB<br>2015-07-08 10:25:25 315 [ERROR] Aborting

1	2015-07-08 10:25:25 315 [ERROR] Unknown/unsupported storage engine: InnoDB<br>2015-07-08 10:25:25 315 [ERROR] Aborting

Bugs are reported and verified here: https://bugs.mysql.com/bug.php?id=77654

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.

https://www.percona.com/doc/percona-server/5.6/reliability/innodb_corrupt_table_action.html

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. 🙂