Present Perfect

One of our customers mailed us to say that they had some database corruption in one of their main tables, and obviously our friend Murphy had forgotten to take backups, and could we take a look at it ?

Obviously it's always way more fun to poke at someone else's train wreck than your own, and you might learn something that could save your bacon in the future, so I decided to give it a go in my spare time. I asked for a full tar of the system so I could chroot into it and do a post-mortem.

It became quite a time-consuming endeavour - not in actual time spent doing stuff, but waiting for things to happen. First waiting to get ftp details, get the password, figure out their iptables rules so I could actually log in, finding a machine with 150 GB free to transfer the image to, actually transferring the image (took the best part of a week), realizing they only gave me the db partition and not the actual system, when I prefer to have the real system to make my debugging easier, making a backup of all the data over our internal network to the new file server, ... Lots of little steps all taking one minute of work and various hours to complete.

But finally I had their system on one of ours, and I was able to chroot into it, start mysql, and run the query that brought down their server.

The table probably has millions of lines, and I was able to query about 20000 before it crashed. I tried varies things, REPAIR doesn't work on InnoDB anyway.

Since the table was InnoDB, I found a utility called innnodbchecksum and tried it. It spat out:

page 535 invalid (fails new style checksum)


So, one of the early pages is invalid, and MySQL just gives up after that.

I found this presentation that explained a bunch of things about the InnoDB database file format, and looked around for file format parsers.

Inspecting the innodbchecksum binary, it seems there are "old style" and "new style" checksums on pages. Reading the code, it seems the old-style matched, but the new-style didn't. So, one of the random ideas that popped into my head was to change the newstyle checksum on that page. I mean, one of the two seems fine, no ? Haven't tried that yet, saving it for later.

I tried a bunch of methods I found all over, including the interesting-looking innodb_force_recovery option which you can dial from 1 to 6, but that didn't help much either.

I checked if hachoir maybe had a tool to parse innodb files, because I've been looking for a good excuse to play with hachoir since forever, but no luck, although it sounds like a good match.

Surely someone must have already written some tool to look at corrupt innodb database files and recover the 99.99% of good pages out of it ?

Activating my network of MySQL-related contacts however brought me to this very interesting post that I hadn't found through Google. Excited, I followed the instructions. I noticed the instructions were for 0.3, and the latest version had moved and now was 0.4.

The tool basically worked, although something has changed, and I couldn't leave a comment on Chris' blog, so I hope trackback works for the people coming after me... Instead of doing

cd innodb-recovery-tool-0.3/
./create_defs.pl --user=root --password=mysql --db=test --table=t1 > table_defs.h

in step 3, I had to do:

cd percona-innodb-recovery-tool
./create_defs.pl --user=root --password=mysql --db=test --table=t1 > include/table_defs.h


If you don't write to the include dir, you end up with recovery results for a table called 'reptest' which is in the default include/table_defs.h, and not what you want.

Now the tool is taking satisfyingly long to complete, the output data looks like it's mostly intact, and hopefully I can make a customer happy for Christmas with a non-standard service that I hope they'll enjoy.

One of our customers mailed us to say that they had some database corruption in one of their main tables, and obviously our friend Murphy had forgotten to take backups,...