WL#235: Safety checks for OPTIMIZE TABLE

Status: Assigned   —   Priority: Medium

This is an extract from correspondence between Peter Zaitsev 
and Monty Widenius, thread "Optimize Folks", May 2001. 
Currently MySQL waits until disk space is freed for 
myisam tables for all situations exept "repair" and 
... "optimize". 
A table is marked as crashed and will not be repaired 
automatically even after disk space is freed. 
This situation is not really good, I think, because 
"repair" table is usually called in maintenace mode 
so you can handle the error, and "optimize" table may 
be used just to shrink tables in order to free some 
space, or (in my case) in order to make concurrent 
inserts work again. 
Also the idea of "optimize" table is that it should be 
a safe procedure; if it fails, it should leave the old 
copy in place. 
My (Peter Zaitsev's) suggessions are: 
1) may be it would be nice to wait for the disk problem to 
be resolved, as the problem happens in other cases, or 
maybe it would be nice to make this proposal a configuration 
option, if Monty decides that it would be better not to eat 
all disk space for this case. This would be easy to do with 
an option in "repair" table. 
2) "optimize" table may be changed a bit to make a copy of 
the table before running optimize on it. This will need a 
bit more disk space, and will be a bit slower, but will be 
much safer and will allow clients to read from the table 
during all the time while optimize is working. This, too, 
could be done with an option.