MySQL – Finding and Fixing InnoDB Index Corruption

innodbMySQL

I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a secondary index is returning incomplete results. Querying a child table by the parent id was returning 498 rows when it should have returned 504. Querying the missing 6 rows by primary key worked, and the correct parent id was returned, so the problem is with the secondary index.

This problem is highly concerning to me since presumably even if all of the data on the slave matches the master I will still get incorrect results from some queries run on the slave.

My brute force solution was to rebuild the entire table like this:

alter table my_table engine = innodb;

That resolved the issue for this specific table, but I'm left with the following questions:

1) How can I determine if other tables have similar index corruption?
2) What's the most efficient way to fix the corrupt indexes?

I've found some good resources online for finding and fixing InnoDB data corruption, but haven't found anything relevant for InnoDB index corruption.

I looked in the MySQL error log and didn't find a smoking gun. I did find some troubling InnoDB errors. I'm assuming this is a separate issue, but it could be related:

130109  9:46:41  InnoDB: unable to find a record to delete-mark
InnoDB: tuple DATA TUPLE: 2 fields;
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a1c03bd; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a052a77; asc   *w;;

Best Answer

Thanks Rolando and Michael for your responses.

To close the loop, here's the answer I came up with for my original questions:

Q: How can I determine if other tables have similar index corruption?
A: Use CHECK TABLE. I ran mysqlcheck -c on all of the relevant InnoDB tables to find out which ones had index corruption
Q: What's the most efficient way to fix the corrupt indexes?
A: Use OPTIMIZE TABLE to rebuid the InnoDB table that have corrupt indexes. This causes a complete table rebuild which fixes the corruption.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks

MySQL – Preventing Index Corruption

I suspect that the MySQL query optimizer occasionally choose a sub-optimal path. I'm not certain why it would sometimes use the index and sometimes not, nor why it seemed related to the index in question.

I think the query itself could not be fully covered by the index (it included an ORDER BY that I did not cover). I made a change to the query to ORDER BY student.created_at and added an index on (created_at, school_id, student_status_id) and everything looks good now.

Best Answer

Related Solutions

Mysql – Clarification on thesql deadlock situation

SUGGESTION

MySQL – Preventing Index Corruption

Related Question