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 it expected. Either way, corrupted 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:
> select * from t limit 4; +---+--------+ | i | c | +---+--------+ | 1 | Miguel | | 2 | Angel | | 3 | Miguel | | 4 | Angel | +---+--------+ > select count(*) from t; +----------+ | count(*) | +----------+ | 2097152 | +----------+
One day the query you usually run fails and your application stops working. Even worse, it causes the crash already mentioned:
> select * from t where i=2097151; 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 >= 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, 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
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.”
and restart the service. Now it’s time to try and dump our data with
. If the corruption is even worse you need to keep trying different modes. For example, I have this error:
> create table t2 like t; > insert into t2 select * from t; ERROR 1034 (HY000): Incorrect key file for table 't'; try to repair it > insert into t2 select * from t; ERROR 1712 (HY000): Index t is corrupted
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 > dump.sql Error: Couldn't read status information for table t ()
but in my test server, it seems that
This procedure sounds good and usually works. The problem is that the feature is mostly broken after 5.6.15.
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 2015-07-08 10:25:25 315 [ERROR] Aborting
Bug 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 a older version of MySQL (previous to 5.6.15) to use higher values of
. 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
- If you are using Percona Server,
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.