What were the conditions and what is the fix?

MySQL 5.5.25a has been released with the fix for the bug http://bugs.mysql.com/bug.php?id=65745. This bug – introduced in 5.5.25 – could ultimately have the result that a single UPDATE statement made all disk space on the system unavailable. The InnoDB tablespace(s) grow(s)  even though data and indexes do not require more disk space.

This is of course an ugly bug – but ‘s… happens‘ they say. So it is not the purpose of this to ridicule or expose anyone. After all it was fixed  as soon as possible after the bug was reported. But after the fix has been released it is still unclear what exact conditions will trigger the effect.

Kolbe Kegel from SkySQL who reported the bug report, provided this CREATE TABLE statement (the simplest he could find to reproduce the issue)

id1 int NOT NULL,
id2 int NOT NULL,
a int,
b int,
PRIMARY KEY (id1,id2),
KEY (id1, a)

.. and further added that “removing any further row, column or index makes the problem disappear“. But WHY? Why will it help to drop the index `id1` or the column `b` for instance?

Also what is the fix for this for users affected? If not innodb_file_per_table is used I cannot find any other solution than to dump everything and restore to a fresh server or InnodDB instance (if innodb_file_per_table is used, I think OPTIMIZE TABLE for affected table(s) will free the ‘hijacked’ diskspace to the system – provided that there is still enough diskspace to execute OPTIMIZE TABLE, of course).

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. Release notes http://dev.mysql.com/doc/refman/5.5/en/news-5-5-25a.html just say “A regression bug in the optimizer could cause excessive disk usage for UPDATE statements.“. This does not really help anyone to understand and to cope with the bug.