MySQL Recovery Blows

I'm running MythTV at home. Since May it has replaced our trustworthy ReplayTV in the living room as the primary TV viewing apparatus.

Thus far its worked rather well for us, despite some odd user interface quirks that probably have more to do with the way I designed my Pronto panels than with MythTV itself.

But its use of MySQL is another story...

Tonight MySQL filled the volume I have the database stored on. What was a 4 GB disk volume went down to 0% free at 9:21 pm on Sunday. MySQL didn't handle this very well and crashed. :-(

But to make matters worse I have the default Gentoo+MythTV MySQL setup, which means all MyISAM tables and binary logging. After the great crash I moved the database to a dedicated volume of larger size and mounted that volume at the old location (/var/lib/mysql). However MySQL found a way to corrupt its log files during the crash and was unable to start. It kept demanding a non-existant log file.

I wound up having to check every table by hand with myisamchk, wipe out the logs, move the database to a temporary location, create a new database, delete the new database, then move the real database back. Why? Because even though the tables were all recovered the server kept demanding a missing log file and there was no obvious way to create a new log file to fill in its place. If the server really was logging it wouldn't have found a way to lose its log file in the first place.

I should make it clear that the host, the disks, the filesystem were all online and available. 62 days of uptime. This crash wasn't due to a power failure or disk corruption. Its purely a software bug in MySQL.

Recovery is one of those areas that you just want to always work. Oracle has had it working for many years (though I still have seen Oracle databases not recover but its pretty rare). PostgreSQL has also had pretty good recovery for a long time. These MySQL yahoos are still in the stone ages when it comes to recovery. Yet MythTV's only available database backend is MySQL. Arrgh!

I hate crappy database software. I had to give up on my OpenLDAP installation as it kept eating its Berkley DB database. I don't have time or the energy to try and recover the thing every time I want to use it. Now its looking like I might have to give up MythTV because of its equally bad database backend. No wonder why I only use PostgreSQL for my projects.