MySQL Bugs: #25521: optimize table, delete, show table status leads to table losing it's .MYD

Bug #25521	optimize table, delete, show table status leads to table losing it's .MYD
Submitted:	10 Jan 2007 12:04	Modified:	9 Apr 2007 19:31
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.34	OS:	Windows (w2k3sp1)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	corruption, myd, Optimize

Description:
When running a simple script containing these queries;

o) insert into t1 .......
o) select _id from t1 ...
o) delete from t1 ....
o) show table status like 't1' 
o) optimize table t1

in 10 threads, the end result is queries failing with
070110 13:56:45 [ERROR] mysqld-max-nt: Table '.\test\t1' is marked as crashed and last (automatic?) repair failed

The directory contents is this:
2007/01/10  01:55 PM             8,976 t1.frm
2007/01/10  01:56 PM             4,096 t1.MYI
2007/01/10  01:56 PM               184 t1.TMD

So, it requires human intervention to replace the MYD.

How to repeat:
compile and run testcase.c.
see top of file for host,user,password,database, and gcc commandline example.

So far, i only repeated this on Windows 2003.  Am still trying on linux, and will update the bug report soon.

Suggested fix:
.

found this bug while trying to repeat bug #25042

compile and run to reproduce the problem

Attachment: testcase.c (text/plain), 5.00 KiB.

running testcase.c for 60 minutes on suse 9.3 didn't cause a problem.  on windows, a problem occurred in < 5 minutes so, for now i'd say it's a windows only bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23138

ChangeSet@1.2486, 2007-03-28 21:09:16+05:00, svoj@mysql.com +1 -0
  BUG#25521 - optimize table, delete, show table status leads to table
              losing it's .MYD
  
  When OPTIMIZE TABLE is completed it attempts to rename temporary
  file to original name. This step may fail on windows when a file
  is opened. As a result data file might be deleted and optimized
  copy of file (table_name.MYD) remains.
  
  This situation is handled properly by my_delete_allow_opened, so
  use it instead of my_delete when attempting to rename a file on
  windows.
  
  No suitable test case for this bug.

Ok from me.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Noted in 5.0.40, 5.1.18 changelogs.

OPTIMIZE TABLE might fail on Windows when it attempts to rename a
temporary file to the original name if the original file had been 
opened, resulting in loss of the .MYD file.