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;;