Re: Random table crashes with 5.0.x in production

[Beat Vontobel]

Yes, two of the table crashes happened on the same table with heavy concurrent reads, but writes on that table occur only once every ten minutes (some few hundred UPDATEs and REPLACEs per update). The table is actually sort of a FIFO buffer that always holds values for the last 24 hours with some pre-computed values and so it's pretty small with only about 12'200 rows (that's fixed) but under constant use. It has 5 indexes (two of them, including the PRIMARY KEY, spanning two columns).

When I tried to analyze what happened I found these two entries in the mysqld.log from yesterday:

051128 14:56:16 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './meteonews/anetz10s.MYI'; try to repair it
051128 14:56:16 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './meteonews/anetz10s.MYI'; try to repair it

They unfortunately went by unnoticed by anybody and after that everything worked fine for almost half a day more (the table still under heavy use). But during the night these messages suddenly popped up and all further access to the table was of course blocked:

051129 1:01:05 [ERROR] /usr/local/mysql/bin/mysqld: Table './meteonews/anetz10s' is marked as crashed and should be repaired
(repeated many thousand times)

As I was sleeping at that time ;-) somebody else did a REPAIR TABLE without keeping its output or the output of a CHECK TABLE done before. So I unfortunately can't tell you what exactly was broken in the table.

But I sent an e-mail to everybody at our company to send me the output of CHECK and REPAIR TABLE if this should happen again - so I hope at least then I should get some more information.