MySQL Bugs: #3954: Executing CHECK TABLE during heavy loads will crash the DB

Bug #3954	Executing CHECK TABLE during heavy loads will crash the DB
Submitted:	1 Jun 2004 19:13	Modified:	17 Aug 2004 12:54
Reporter:	daniel lo	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	mysql-server-4.0.18_1	OS:	FreeBSD (FreeBSD 5.1-RELEASE-p11)
Assigned to:		CPU Architecture:	Any

Description:
We had a cronjob that would execute "CHECK TABLE [table name] QUICK FAST" on all MyISAM tables every 2 hours.  Peridoically, about once or twice a week a table (or the db) would crash.  This crash happens 80% of the time during heavy usage times, and occurance appears to be "random".

The table that crashed would have an invalid row count or would

mysql> check table XXXXXXX fast quick;
| Table | Op | Msg_type | Msg_text |
| XXXXXXX  | check | warning | Table is marked as crashed |
| XXXXXXX  | check | warning | Found 27264 deleted space in delete link chain. Should be 27256 |
| XXXXXXX  | check | error | Found more than the expected 64 deleted rows in delete link chain |
| XXXXXXX  | check | error | record delete-link-chain corrupted |
| XXXXXXX  | check | error | Corrupt |

mysql> repair table XXXXXXX;
| Table | Op | Msg_type | Msg_text |
| XXXXXXX | repair | warning | Number of rows changed from 10074977 to 10499615 |
| XXXXXXX | repair | status | OK |
+----------------------+--------+----------+--------------------------------------------------+
2 rows in set (17 min 8.80 sec)

I appologise that we cannot provide a trace for this.

We were not able to reproduce this problem on a test server, because we are unable to 'stress' the test server.

Regards,

-daniel

How to repeat:
How to reproduce: create a new table, and repeatedly insert/select/delete from the table in large quantites (33% CPU usage), and duing the stressing repeatly execute "CHECK TABLE [table name] QUICK FAST" on the table (every 1 seconds or so).  Using this method we were able to get the db to crash 40% of the time.

Please try repeating the same with most recent version 4.0.2, but make sure this is binary build by our team (downloaded from www.mysql.com) and not compiled from source like from ports.

Side note:
Why do you need such regular CHECK at all? If you experience corruptions often that might be caused by something outside of MySQL like filesystem problem for example. You should better investigate when and how does this happens. 

Of course there is chance that you are hiting a bug in MySQL too, but with recent 4.0 releases this is very unlikely.

A better approach would be start mysqld with --myisam-recover option, but it should be used just as a temporary measure.

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