MySQL Bugs: #55068: Table is marked as crashed and should be repaired

Bug #55068	Table is marked as crashed and should be repaired
Submitted:	8 Jul 2010 6:07	Modified:	14 Aug 2010 14:51
Reporter:	priya varadharajan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	MYISAM engine, table corruption

Description:
I am getting the error :

"MySQL table marked as crashed and should be repaired"

I do a CHECK table and REPAIR table to solve this error. This error doesn't occur often but in the last 4-5 days I have got it twice.

I am using MYISAM engine and there are about 10 tables in the database and the above error occurs with only one table. I am inserting a lot of entries into this table using Stored Procedure say more than 2000 entries(rows) per minute.

When I looked at the error log, the following message was present :

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Incorrect key file for table '.\sample_database\table1.MYI'; try to repair it.

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Table '.\sample_database\table1.MYI' is marked as crashed and should be repaired.

I just browsed through and figured out a few possible causes for the corruption of tables:

1. The mysqld process is killed in the middle of a write - But in my case this has not happened. Had this occurred then there should be an message in the log as 'mysqld restarted'.But there was no such message.

2.An unexpected computer shutdown occurs (for example, the computer is turned off). - There was no such problem.

3.Hardware failures - No hardware failure in my case.

4.You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time. - Not sure about this. If this could be the reason how to go about in checking whether an external program has modified a table.

5.A software bug in the MySQL or MyISAM code. -??

How to repeat:
Not sure. Doesn't occur frequently.

Suggested fix:
REPAIR the table.

What exact version of MySQL server, 5.1.x, do you use? Please, upload your error log also, compressed.

Error Log

Attachment: Error.rar (application/octet-stream, text), 2.54 KiB.

I am using 5.1.23 version of mysql

Many bugs were fixed since 5.1.23. Please, upgrade to current version, 5.1.48 (read the manual about proper upgrade procedures) and check if this problem ever happens again with it.

Sir, we have been using this for a real-time project and we need to close the project at the earliest. This would mean a lot of change to come in.Is there any other good solution other than going for an upgrade? Thanks.

In your error log I see that problem started some time after formally clean shutdown:

100706 18:54:02  InnoDB: Starting shutdown...
100706 18:54:04  InnoDB: Shutdown completed; log sequence number 0 74686
100706 18:54:04 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

100706 18:54:08  InnoDB: Started; log sequence number 0 74686
100706 18:54:09 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
100706 18:54:09 [Note] Event Scheduler: Loaded 0 events
100706 18:54:09 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.23-rc-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
100706 20:35:20 [ERROR] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Incorrect key file for table '.\cornet_ai_server\eventinfo.MYI'; try to repair it
100706 20:35:20 [ERROR] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Incorrect key file for table '.\cornet_ai_server\eventinfo.MYI'; try to repair it
100707 10:25:47 [ERROR] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Table '.\cornet_ai_server\eventinfo' is marked as crashed and should be repaired

Can you find out from application logs or general query log maybe what statements were executed at 100706 20:35:20?

If you repaired the table successfully, please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for this eventinfo table in the cornet_ai_server database.

I am sorry ..I dont have general query log enabled.

Here is the SHOW CREATE TABLE
 CREATE TABLE `eventinfo` (
`SensorID` varchar(40) NOT NULL,
`SensorName` varchar(32) NOT NULL,
`RuleId` varchar(40) NOT NULL,
`RuleName` varchar(64) NOT NULL,
`ViewId` varchar(40) NOT NULL,
`ViewName` varchar(64) NOT NULL,
`NoofSlices` varchar(1) NOT NULL,
`FileName` varchar(128) NOT NULL,
`OV_EVENT_TYPE` int(1) NOT NULL,
`RootDir` varchar(128) NOT NULL,
`EventAction` varchar(1) NOT NULL,
`MailID` varchar(100) NOT NULL,
`PhoneNo` decimal(10,0) NOT NULL,
`Timestamp` varchar(32) NOT NULL,
`UserAction` varchar(24) DEFAULT 'Not Accepted')
ENGINE=MyISAM DEFAULT CHARSET=latin1 |

SHOW TABLE STATUS:

| eventinfo | MyISAM | 10 | Dynamic | 20944 | 271 | 14535532 | 281474976710655 | 1024 |   8840656 |  NULL | 2010-07-06 15:59:15 | 2010-07-08 13:32:20 | NULL       | latin1_swedish_ci |     NULL |

This error occurred yesterday and hence these results are not immediately taken after the REPAIR. I have taken it now. 

Will this help?

Is there an anti-virus program which you can disable for a time for test purposes?. Thanks in advance.

I am running McAfee anti virus in my system. But the problem is as this issue is not easily reproducible, I am afraid as for how long can I stop my anti virus.

You suspect the anti virus to read into the mysql files and hence the issue?

Please test disabling the anti-virus application. Thanks in advance.

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".