Friday evening the web server that hosts the blog suffered a critical failure and reboot. Almost all of the services came back just fine, except for the database had become corrupt. Unfortunately, I was busy over the weekend (Happy Birthday Jules! Congratulations Olympians!) and was only able to restore the database tonight. We’re back up and good to go now though.
For those of you interested, the problem seems to stem from the fact that I (briefly) ran this database on an alpha release of mysql 6.0, which has since been pulled by MySQL in favour of focusing on the 5.5 code branch. I was able to successfully migrate the database from 6.0 to 5.5, or so I’d thought since it had run fine for several months. However, when the reboot occurred, the database would not come back up. It seems that mysql 6.0 had more or re-arranged character and collation sets, and my tables had all been set to use “collation ID #45″ or #46. However, these collation sets don’t exist in mysql 5.5. How the databases and tables were able to work for months in this way I have no idea. One interesting aspect of this particular issue is that backups are largely useless because under most circumstances they are exported without modification, preserving the problem in the backups.
In the end the solution was to light up a Xen domU (posts about Xen coming soon, I promise) and install mysql 6.0 there (the mysql60-server port having been removed from all current ports collections), migrate the data to the temporary server, and then re-export the databases back to the original host with a higher compatibility level.