InnoDB provides no means to repair corrupted table space. Once a table got corrupt 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 case you can repair InnoDB table space much faster.
UPDATE: If corruption is in PRIMARY index check 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 ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.
So, before starting a whole story with innodb_force_recovery, dump, drop, create and reload a table check the MySQL error log. Who knows, maybe it can give clues how repair the table space without rebuilding it from scratch.
Check where the corruption is
Let me illustrate my point. As usually we will work with actor table from sakila database. It has 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 in 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 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 wrong InnoDB page checksum
There is innochecksum tool in MySQL distribution that can test
offline InnoDB tablespace.
We slightly modified innochecksum and added option -f that means
if the checksum of a page is wrong, 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 -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 corrupted tablespace
Now start MySQL. innodb_force_recovery option should be disabled.
When MySQL starts, drop index idx_actor_last_name:
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 index idx_actor_last_name 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.
The post Repair Corrupted InnoDB Table with Corruption in Secondary Index appeared first on Backup and Data Recovery for MySQL.