InnoDB Corruption common in MySQL
I am now recovering corrupt InnoDB files in MySQL for the 6th time in the last year. Not a good sign considering all the press I have seen about how InnoDB should be default storage engine in MySQL and how superior it is with its added features such as transactions.
First let me give a quick rundown of the recovery history:
– I have been using MySQL in production environments for almost 10 years now. I have had 1 MyISAM table corruption in that time and that was a result of using a Beta version of MySQL. The fix was to run the fix SQL statements and problem solved. I installed a beta version because I was a newbie at the time and no longer do that.
– I have had to fix 6 InnoDB corruption issues on MySQL in the last year on completely different servers. Note that once InnoDB corrupts, it either disables the MySQL process from starting or it disables ALL InnoDB tables in all databases on the servers. Either way an entire server is screwed.
– Corruption has happened on FreeBSD 6.1, 6.2, 6.3, and Win 2k3 Svr on completely different hardware.
– Mysql versions have ranged from 4.1 through 5.1 (various sub versions)
– Recovery of InnoDB has never been simple. Every time it has required several hours working with tools and spending several hours recovering the data. That means additional down time.
– Corruption generally happens after a graceful reboot, but not always. None of the corruption has been a result of power outages or other more “abusive” treatment of the server in question.
In short, MySQL has had a history of being fast and rock solid. InnoDB has shown that it is fragile and buggy – at least in MySQL. With all of the problems it has caused me, I have begun converting any InnoDB tables to MyISAM and adding the skip-innodb line to the MySQL Configuration files to turn it completely off. I am not surprised that my headaches have gone away. This is the last server that has InnoDB on it. New installs will have InnoDB turned off immediately.
I just can’t see how you can promote a technology that has this many problems on this many different servers and operating systems. If you don’t need InnoDB, do yourself a favor and turn it off.
