MySQL Bugs: #19260: Incorrect key file for table and marked as crashed

Bug #19260	Incorrect key file for table and marked as crashed
Submitted:	21 Apr 2006 14:36	Modified:	11 Jun 2006 8:35
Reporter:	Surinder Singh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.20-nt	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
I'm getting the following error:
"Table '.\cwgc\cm_payment' is marked as crashed and should be repaired"
The only way I can get rid of this error is by doing a repair.

This has now happened twice. First time was on 10th April. Second time was yesterday. Otherwise the database has been running fine since December 2005.
Before this error occurs, the following error message appears:
"Incorrect key file for table '.\cwgc\cm_payment.MYI'; try to repair it"

There are 39 tables in the database and the "cm_payment" table is the only one being corrupted. This table and several others are updated every night by a batch routine. The existing records are first deleted using "DELETE FROM cm_payment", then about 15,000 records are immediately added to the cm_payment table using the INSERT statement. The error seems to occur while it's part way through this insert procedure.

What could be causing this error? If the table is being corrupted in this way, what could cause this? Could it be a bug in MySQL? Or is it more likely to be a hardware problem?

How to repeat:
This is not repeatable, happens once in a blue moon. The structure of the table, if it helps, is:

CREATE TABLE `cm_payment` (
  `ContractID` varchar(9) NOT NULL default '',
  `PaymentDate` date NOT NULL default '0000-00-00',
  `CreditOrDebit` char(1) NOT NULL default '',
  `InputDate` date default NULL,
  `PaymentNo` varchar(6) default NULL,
  `PaymentValueNet` double default '0',
  `PaymentValueGross` double default NULL,
  PRIMARY KEY  (`ContractID`,`PaymentDate`,`CreditOrDebit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Suggested fix:
Repairing the table temporarily fixes the problem, but it will occur again, just don't know when.

I will try the latest version 5.0.20, hoping that it will provide a permanent solution. If this doesn't solve it, then I will investigate the hardware. May also try reproducing the problem on another server by continuously running the batch routine.

Thank you for a problem report. Have you created that table in 5.0.18 or it was created in some older version (4.x.y) and then binaries upgrade was performed?

Anyway, we need a set of steps to demonstrate the problem each and every time to be able to verify and fix it.

Thanks for your reply. This error occurred again on the night of 21st April, the day this bug report was submitted. This was after I had upgraded to MySQL version 5.0.20. The tables were not binary copies from 4.x.y. They were created using an export/import facility, which uses CREATE and INSERT statements. I've now created a fresh "cm_payment" table and will see who it goes.

I will try to create this problem on a different server, by running the batch process continuously.

Thank you for the additional information. I hope, your tests will give some more ideas on how to repeat. Can it be a hardware issue? Please, send the  SHOW TABLE STATUS results for the table.

The problem occurred again last night. That makes it 3 days times in a row. The server has Symantec AntiVirus running with AutoProtect enabled. Could antivirus software cause this kind of problem? I will disable it and see how it goes.

Here is SHOW TABLE STATUS for this table, after doing a repair:

Name = cm_payment
Engine = MyISAM
Version = 10
Row_format = Dynamic
Rows = 10822
Avg_row_length = 40
Data_length = 434476
Max_data_length = 281474976710655
Index_length = 221184
Data_free = 0
Auto_increment = [NULL]
Create_time = 2006-04-24 11:26:55
Update_time = 2006-04-25 09:47:45
Check_time = 2006-04-25 09:47:45
Collation = latin1_swedish_ci
Checksum = [NULL]
Create_options = 
Comment =

> The server has Symantec AntiVirus running with AutoProtect enabled.

Please, try to disable it (or at least exclude all mysql directories and binaries from any checks) and run your server for some time. Inform about the results.

> Could antivirus software cause this kind of problem?

It is possible, I think.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Excluding MySQL database files from the anti-virus check didn't make any difference. But we solved it my rescheduling the backup. The backup process coincided with the batch process, and it might have locked some files used by MySQL. We changed the time the backup occurs to 15 minutes after the batch process finishes, and we haven't had a table crash for nearly a month. So this issue has been resolved. Thanks for your help.

Closed as not a bug in MySQL code.