Bug #43284 optimize table, not enough free space: no error return, but idles
Submitted: 1 Mar 2009 3:10 Modified: 7 Sep 2009 3:38
Reporter: Ben Bucksch Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any (Ubuntu 8.04)
Assigned to: CPU Architecture:Any

[1 Mar 2009 3:10] Ben Bucksch
If optimize table does not have enough free disk space (I guess as much free space as the table size), mysql writes about that to syslog, but the SQL command ("optimize table") does not return an error and the SQL client issuing the command is never informed about it. It just sits there and waits forever (or until there is free space, which is not likely to happen anytime soon).

I expect that an SQL command which cannot complete returns with an error. optimize table indeed does return success and error codes and messages, normally. Just not in this case.

When I issue such a command, I wait for its completion. I have no reason to look in syslog. I wait for a long time, because optimize table can take a long time.

It's like asking somebody to do shopping, and he says nothing, he just walks away, but actually doesn't go shopping, but goes to school, and writes a letter and puts it in the letterbox saying that he could not go shopping because he had to go to school. Normal reaction is to be mad and demand that he should have *said* so (as soon as possible, and as directly as possible), not write a letter. syslog is fine, but only as additional measure, not a replacement of informing the client who issued the command. It's not an excuse that he's go shopping 3 days later, either: I needed the eggs *now*, in fact waited for them, and everything else halted in the meantime (table locked).

How to repeat:
1) table with 400 MB size on disk
2) partiton (with mysql tables) has 250 MB free space
3) mysql client, "optimize table foo;"
4) wait forever
5) check /var/log/syslog
[7 Sep 2009 3:38] Susanne Ebrecht
Verified as described.

I tested with 2 GB table including indexes and just 1 GB space left on disc ...

Tested both 5.0 and 5.1.

MySQL just hanging ...
[7 Dec 2011 10:16] Andrew Mason
Confirmed this behaviour, using 5.0.92-rs ("Distributed by Rackspace").

Though my version did not resume operation when space was freed - it just hung forever.