MySQL Bugs: #17490: OPTIMZE TABLE doesn't run if table is locked

Bug #17490	OPTIMZE TABLE doesn't run if table is locked
Submitted:	16 Feb 2006 19:15	Modified:	9 May 2006 18:05
Reporter:	Matthew Lord	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.26, 4.0.18, 5.0.18	OS:	Any (all)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
If you have a table write locked and you run a CHECK TABLE prior to an OPTIMIZE TABLE the OPTIMIZE does nothing.

How to repeat:
check table topterms;
optimize table topterms;

lock table topterms write;
check table topterms; 
optimize table topterms;
unlock tables; 

# This table will be attached to the bug
# Because the table has some index corruption it's easy to see the problem

Suggested fix:
optimize table should then proceed with it's optimization routines when it sees that the table is "up to date" due to a check table.

It seems like the index file comes from a different table. One that has a third column of type blob(10) or text(10). I compared SHOW CREATE TABLE and myisamchk -dvvv. After creating a correct index file with REPAIR TABLE ... USE_FRM everything works as expected. I'll attach my test output.

After a normal repair I could repeat the problem, but looking at the repair result the table has been emptied. And it was not possible to insert a row into the table. The server crashes as it expects a blob column where there is just uninitialized data. So I would say the repair was not successful. Hence the tests are meaningless.

Another thing is that swapping the two test cases from the "how to repeat" section, makes the non-locked CHECK TABLE reporting "upt to date". Both cases seem logical as there is no table modification between the two checks.

Funny that the verifier didn't notice this. ;-)

Should I have missed something, feel free to reopen the bug with a more verbose sequence of commands for repetition.

My test result

Attachment: bug17490.out (application/octet-stream, text), 2.72 KiB.