A new version of MySQL has been released by Oracle to correct a bug where MySQL uses all the available disk space when changing a record.

The bug is limited to tables based on MySQL’s InnoDB storage engine.

Hartmut Holzgraefe of SkySQL showed in his blog how the bug can eat the disk space. In a table with two indexes, this SQL uses up all the available space:

UPDATE t1 SET id2 = id2 + 1, b = null 
      WHERE a is null and id1 = 2;

What happens is a bit of a mystery, because the InnoDB tablespace expands even though there’s no requirement for extra disk space because of extra data or increased index size.

According to Peter Laursen of Webyog,  there don’t seem to be a lot of options. If innodb_file_per_table is not being used your only choice seems to be to restore to a new server or InnoDB instance having dumped your files.

If you are using innodb_file_per_table option, Lauren suggests that using Optimize Table on the affected tables might free up the disk space that was incorrectly used.

He points out, though, that this still requires there to be enough disk space free to run Optimize Table. Holzgraefe suggests that switching off the index_merge optimizer feature seems to be a viable workaround:

optimizer_switch = 'index_merge=off';

However, the situation is far from ideal. As Laursen points out,

"The lack of willingness of Oracle here to expose the exact conditions for this bug to affected users (and how to repair it should they be affected) is the real problem here".

The new release, MySQL 5.5.25a, does however claim to have resolved the problem.