Thursday, April 9, 2009

An obscure bug

We have begun running a tool that determines whether tables on slaves and masters match. This tool is similar to mk-table-checksum with a few optimizations from the Google patch:
We soon found an InnoDB table with rows in the secondary index that were missing from the PRIMARY index. How can this be? My guess is that one or more disk writes to the PRIMARY index were lost. InnoDB stores an LSN and checksum on each page. That page for which writes were lost had a valid old version on disk. When that page is read, the checksum is still valid and there is no way to determine that the LSN is correct unless the last write for that page is recorded in the current transaction log or the doublewrite buffer.

ZFS would detect this error as it stores a page checksum separate from the disk page. RAID 10 might help. If writes for one of the copies was lost, then as long as your are lucky enough to read the page from the good side of the mirror, you will get the good data. But that might not be comforting. What else would help to detect and or correct this?

4 comments:

Jeremy Zawodny said...

Mark,

Very interesting. What filesystem and RAID hardware versions are you using?

Mark Callaghan said...

SW RAID 0. But if this is a misdirected write I will blame the disk.

Arjen Lentz said...

On a sidenote, what does InnoDB do when you look up the row by the secondary index? Does it catch this at all, like chuck an error because it can't complete the PK lookup from the PK value in the secondary index?

Mark Callaghan said...

Innodb does index only, so when we scanned only the secondary index we got the desired data. If a query then went back to the PK from there, I think we got a core file.

Post a Comment