MySQL update fixes critical error

Oracle has released version 5.5.25a of its open source relational database MySQL and, according to the release notes, the only fix is a correction to a bug which consumed all the disk space when changing a record. The description of the bug in the bug tracking database provides more information: the issue affects tables using InnoDB, which has been the default storage engine since version 5.5 of MySQL. SkySQL developer Harmut Holzgraefe presented a test case which used a table with two indexes; in the test case the hard disk was filled by using the SQL command:

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

Although the bug has been corrected with the new version, Oracle has failed so far to explain how users can reduce the size of the potentially excessively large files. In a blog post and in the bug report comments, Peter Laursen offers some solutions.

If the disk space is being used by temporary tables, one possibility is to simply stop the server, empty the Temp directory and then restart the server. If InnoDB tables are the source of the disk space consumption, the problem is somewhat harder to resolve. Laursen suggests that running OPTIMIZE TABLE for each affected table should fix this, provided that the 'innodb_file_per_table' option is set and there is enough free disk space to execute the OPTIMIZE TABLE. If that is not the case, Laursen says that the only option is to dump all databases and restore them to a fresh server/InnoDB instance.

Laursen criticises Oracle for not providing details of the exact conditions that trigger the bug and what the fix actually does, noting that "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 short statement from the release notes – "A regression bug in the optimizer could cause excessive disk usage for UPDATE statements" – is deemed insufficient for anyone needing to cope with the bug.

The GPLv2-licensed community edition of MySQL 5.5.25a is available to download from the MySQL site.

(djwm)