Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Repair Corrupted InnoDB Table with Corruption in Secondary Index
+2 Vote Up -0 Vote Down

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.

by

The post Repair Corrupted InnoDB Table with Corruption in Secondary Index appeared first on Backup and Data Recovery for MySQL.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.