Oh My Gotcha!

For anyone considering an upgrade to MySQL 5.1 BEWARE BEWARE BEWARE I just spent the morning working with a web host who also spent the night after what should have been a simple upgrade completely fried several databases! We did find the workaround though...

It seems that if you upgrade MySQL from versions < 5.0.62 to 5.1 the process will cause some tables to get corrupted (sometimes many will work - some will some wont) and then not allow them to be repaired. Considering that this isn't something one would expect from an upgrade (especially from software like MySQL that has such a great track record) it came as quite a shock.

The error that appeared was Incorrect key file for table '.....'; try to repair it which basically just means that the file is corrupt.

How To Fix It?
If you're reading this BEFORE you upgrade then the key is to take a database extract before upgrading the server, upgrade the server, and then reload the data.

If you're reading this AFTER your upgrade and facing this exact problem - you can save your data! Install a copy of MySQL 5.0.62 somewhere - copy all the mysql files over to that database and then do an extract (mysqldump) from there. You can then reload this dumpfile into the 5.1 instance and everything should work (it did for us anyway)

... Sorry if this got a bit technical for those that normally read this blog but this seems such a major issue I wanted to be sure everyone knew About it.

---