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:

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

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.

mysql> select * from actor where last_name like 'k%';
ERROR 2013 (HY000): Lost connection to MySQL server during query

Let’s check the error log,

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.
2014-07-14 20:18:44 7f060bfff700 InnoDB: Page dump in ascii and hex (16384 bytes):
...

then a dump of the page 4 goes:

...
InnoDB: End of page dump
2014-07-14 20:18:44 7f060bfff700 InnoDB: uncompressed page, stored checksum in field1 466524752, calculated checksums for field1: crc32 3446110311, innodb 337
7018271, none 3735928559, stored checksum in field2 2011246900, calculated checksums for field2: crc32 3446110311, innodb 2011246900, none 3735928559, page LS
N 0 650316121, low 4 bytes of LSN at page end 650316121, page number (if stored to page already) 4, space id (if created with >= MySQL-4.1.1 and stored already) 1042
InnoDB: Page may be an index page where index id is 2575
InnoDB: (index "idx_actor_last_name" of table "sakila"."actor")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.

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.

InnoDB: Ending processing because of a corrupt database page.
2014-07-14 20:18:44 7f060bfff700  InnoDB: Assertion failure in thread 139663947855616 in file buf0buf.cc line 4367
InnoDB: We intentionally generate a memory trap.
...
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f060bffed40 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bb80c]
/usr/sbin/mysqld(handle_fatal_signal+0x479)[0x6504b9]
/lib64/libpthread.so.0[0x3f9200f710]
/lib64/libc.so.6(gsignal+0x35)[0x3f91c32925]
/lib64/libc.so.6(abort+0x175)[0x3f91c34105]
/usr/sbin/mysqld[0xa4994f]
/usr/sbin/mysqld[0xa5f7c8]
/usr/sbin/mysqld[0xa441c2]
/usr/sbin/mysqld[0xa26e78]
/usr/sbin/mysqld[0xa2d3d9]
/usr/sbin/mysqld[0x9250b7]
/usr/sbin/mysqld(_ZN7handler27multi_range_read_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0xcf)[0x593f6f]
/usr/sbin/mysqld(_ZN10DsMrr_impl16dsmrr_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0x48)[0x59c0d8]
/usr/sbin/mysqld[0x7d79dc]
/usr/sbin/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyybN8st_order10enum_orderE+0xaee)[0x7e9c5e]
/usr/sbin/mysqld[0x823c6f]
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x637)[0x826327]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x22f)[0x6f566f]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x165)[0x6f5f15]
/usr/sbin/mysqld[0x559ee0]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1cec)[0x6cfe3c]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5c8)[0x6d5078]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfa7)[0x6d6807]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x162)[0x6a4882]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6a4970]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0xb0dc13]
/lib64/libpthread.so.0[0x3f920079d1]
/lib64/libc.so.6(clone+0x6d)[0x3f91ce8b5d]

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.

mysql> alter table actor drop index idx_actor_last_name;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: sakila

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

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:

# bzr branch lp:undrop-for-innodb
# cd lp:undrop-for-innodb
# make all
cc -g -O3 -I./include -c stream_parser.c
cc -g -O3 -I./include  -pthread -lm stream_parser.o -o stream_parser
flex  sql_parser.l
bison  -o sql_parser.c sql_parser.y
sql_parser.y: conflicts: 6 shift/reduce
cc -g -O3 -I./include -c sql_parser.c
cc -g -O3 -I./include -c c_parser.c
cc -g -O3 -I./include -c tables_dict.c
cc -g -O3 -I./include -c print_data.c
cc -g -O3 -I./include -c check_data.c
cc -g -O3 -I./include  sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm
cc -g -O3 -I./include -o innochecksum_changer innochecksum.c

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.

# ./innochecksum_changer actor.ibd
page 4 invalid (fails new style checksum)
page 4: new style: calculated = 0xC949359F; recorded = 0x1BCE9A50

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

# ./innochecksum_changer -f actor.ibd
page 4 invalid (fails new style checksum)
page 4: new style: calculated = 0xC949359F; recorded = 0x1BCE9A50
fixing new checksum of page 4
page 4 invalid (fails old style checksum)
page 4: old style: calculated = 0x13A4E945; recorded = 0x77E13134
fixing old checksum of page 4
page 5 invalid (fails old style checksum)
page 5: old style: calculated = 0x51B9AB00; recorded = 0x00000000
fixing old checksum of page 5
page 6 invalid (fails old style checksum)
page 6: old style: calculated = 0x51B9AB00; recorded = 0x00000000
fixing old checksum of page 6

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:

mysql> alter table actor drop index idx_actor_last_name;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

For extra security you may want to rebuild the table:

mysql> alter table actor engine innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now you may add the idx_actor_last_name index again:

mysql> ALTER TABLE actor ADD KEY `idx_actor_last_name` (`last_name`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, the InnoDB tablespace is nice and clean.

Have a question? Ask the experts!

Previous Post Next Post