MySQL Bugs: #43284: optimize table, not enough free space: no error return, but idles

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:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any (Ubuntu 8.04)
Assigned to:		CPU Architecture:	Any

Description:
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.

Advocacy:
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

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 ...

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.

Hey everyone,

I recently encountered Bug #43284 while using the "optimize table" command in MySQL, and I wanted to share my emotions and knowledge on this topic.

The issue occurs when there is not enough free disk space for the optimization process to complete. Although MySQL writes about this limitation to the syslog, the SQL command itself doesn't return an error. As a result, the SQL client that issued the command just sits there indefinitely, waiting for the operation to finish, which might never happen if there is no additional free space.

In my opinion, it's frustrating when an SQL command fails silently without informing the user. When I execute a command, I anticipate it to return with an error if it cannot complete successfully. Normally, the "optimize table" command provides success and error codes, but unfortunately, not in this particular case.

When I issue such a command, I patiently wait for its completion, unaware of any issues unless I actively check the syslog. This state of affairs is like asking someone to go shopping, and they walk away barring saying anything, only to later inform you thru a letter that they couldn't go shopping due to the fact they had to go to school. It's natural to feel frustrated and demand immediate and direct communication rather than finding out through indirect means like syslog, like this https://ecommercefastlane.com/leveraging-the-potential-of-the-metaverse-in-retail-pioneeri...