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:Microsoft Windows (w2k3sp1)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: corruption, myd, Optimize

[10 Jan 2007 12:04] Shane Bester
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:
.
[10 Jan 2007 12:04] Shane Bester
found this bug while trying to repeat bug #25042
[10 Jan 2007 12:05] Shane Bester
compile and run to reproduce the problem

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

[10 Jan 2007 12:52] Shane Bester
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.
[28 Mar 2007 12:13] Bugs System
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.
[29 Mar 2007 7:18] Ingo Strüwing
Ok from me.
[30 Mar 2007 17:28] Bugs System
Pushed into 5.1.18-beta
[30 Mar 2007 17:30] Bugs System
Pushed into 5.0.40
[9 Apr 2007 19:31] Paul Dubois
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.