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;;
  • Are you using innodb_file_per_table ??? Commented Jan 10, 2013 at 16:28
  • Yes, innodb_file_per_table is ON, and innodb_file_format is Antelope
    – Ike Walker
    Commented Jan 10, 2013 at 16:32
  • Did you run STOP SLAVE; before making the snapshot ??? Commented Jan 10, 2013 at 16:33
  • No, but I do run FLUSH TABLES WITH READ LOCK so that's not necessary.
    – Ike Walker
    Commented Jan 10, 2013 at 16:38
  • That's completely incorrect. FLUSH TABLES WITH READ LOCK does not sufficiently quiesce InnoDB. Commented Jan 10, 2013 at 17:15

2 Answers 2


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.
  • 2
    OPTIMIZE command rebuils indexes? Because I get this status output: Table does not support optimize, doing recreate + analyze instead
    – Tobia
    Commented Feb 5, 2018 at 8:35
  • 1
    I agree Tobia - then it drops your table and if its messed up it cant re-build and you loose everything and need to restore again.
    – BilliD
    Commented May 14, 2018 at 19:04

You may have the easiest solution there is. However, I would love to clarify some things:

Making a snapshot with a running MySQL Instance could affect the one file that is respsonsible for secondary index manipulation: ibdata1.

The system tablespace ibdata1 is the home of 7 classes of InnoDB Information

  • Data Pages (if innodb_file_per_table disabled)
  • Index Pages (if innodb_file_per_table disabled)
  • Data Dictionary (Included List of Tables and Their TableSpace IDs)
  • Double Write Buffer (Provides Checksum Info to Prevent Data Corruption)
  • Insert Buffer (Changes to Secondary Indexes)
  • Redo Logs
  • Undo Logs
  • Pictorial Representation

The pivotal classes I would worry about are the Double Write Buffer and the Insert Buffer. Doing a Live Snapshot with either of these not properly written will introduce data corruption.

Doing FLUSH TABLES WITH READ LOCK; does not halt writes to ibdata1 as one would think. I wrote about this subject before. I used to think it did, until it was pointed about by fellow DBA.SE member @ShlomiNoach.

Think about the InnoDB Buffer Pool. You would have to flush every dirty page out of it to get everything quiesced to disk. The following forces all dirty pages out on a per-table basis:

  • SET GLOBAL innodb_fast_shutdown = 0; followed by service mysql stop
  • SET GLOBAL innodb_max_dirty_pages_pct = 0; and wait until 1% of Buffer Pool is dirty
  • mysqldump

Also, do not forget that binary logs and relay logs depend on the OS for flushing.

An EC2 Snapshot is not MySQL-Aware in these respects, no more than an LVM snapshot would be. That is why backup software such as CDP's R1Soft has a MySQL Module for such occasions.

Contrariwise, an Amazon RDS MYSQL Instance is aware and built for such InnoDb-centric scenarios. Only if there are active MyISAM tables in the RDS Instance would FLUSH TABLES WITH READ LOCK; be a necessary evil to manually perform.

In regards to your original question, when you ran ALTER TABLE my_table ENGINE=InnoDB; you simply rebuilt index pages reading from data pages of the table, most likely bypassing the ibdata1 Insert Buffer. This is why that worked for you.

If you can do a mysqldump with --single-transaction --master-data=1 on the Master, scp the mysqldump to the slave, and do it without Financial Charges, that would be a safer method for setting up the EC2 Slave.

If you must do the snapshot thing, please to do this on the Slave:

  • Run SET GLOBAL innodb_fast_shutdown = 0;
  • service mysql stop
  • Do your snapshot
  • service mysql start
  • Add innodb_fast_shutdown to /etc/my.cnf

Additional Information: The smaller ib_logilfe0 and ib_logfile1 are, the faster the shutdown.

I hope this explains a lot.

UPDATE 2013-01-14 10:36 EDT

You recently asked in the comment section

how can I tell if a database has index corruption?

Keep in mind you are using EC2 and not RDS. With RDS, Amazon is responsible for the holistic state of the VM and the MySQL Instance. With EC2, Amazon is responsible for the VM only. The holistic state of the MySQL Instance now rests with you. You may want to port the database to RDS because it comes with the additional bells and whistles to guard against such corruption.

  • Hi Rolando. This is all good information. I'll shut down MySQL before snapshotting in the future and hopefully this will avoid index corruption.
    – Ike Walker
    Commented Jan 14, 2013 at 15:12
  • My remaining question is: how can I tell if a database has index corruption? In this case I was lucky enough to notice incorrect query results, but how do I know this doesn't affect other slave DBs?
    – Ike Walker
    Commented Jan 14, 2013 at 15:13
  • Thanks for your response. At this point my focus is on determining if it's possible to detect this type of index corruption in a MySQL database, regardless of whether it's on EC2, RDS, managed hosting, etc. Do you have any idea on how to tell if a given DB has this type of index corruption?
    – Ike Walker
    Commented Jan 15, 2013 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.