Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

2 Answers 2

up vote 2 down vote accepted

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.
share|improve this answer

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.

share|improve this answer
    
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 Jan 14 '13 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 Jan 14 '13 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 Jan 15 '13 at 17:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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