Description:
We keep getting errors on tables that are highly volatile. The error message, using CHECK TABLE reveals the following:
mysql> check table import;
+-----------------------------+-------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+-------+----------+---------------------------------------------------------+
| siouxcityjournal_com.import | check | warning | Table is marked as crashed |
| siouxcityjournal_com.import | check | warning | Not used space is supposed to be: 467060 but is: 461676 |
| siouxcityjournal_com.import | check | error | record delete-link-chain corrupted |
| siouxcityjournal_com.import | check | error | Corrupt |
+-----------------------------+-------+----------+---------------------------------------------------------+
4 rows in set (4.41 sec)
The problem is easily remedied with REPAIR TABLE, but oftentimes issues a warning like so:
mysql> repair table import;
+-----------------------------+--------+----------+----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+--------+----------+----------------------------------------+
| siouxcityjournal_com.import | repair | warning | Number of rows changed from 246 to 245 |
| siouxcityjournal_com.import | repair | status | OK |
+-----------------------------+--------+----------+----------------------------------------+
2 rows in set (1.69 sec)
It seems to occur only with highly active transaction tables. I use LOCK TABLES for all transactions. The table is a MyISAM table format. I also use REPLACE syntax for many operations - but I don't know if that is related or not. The problem has been around since 4.0.12. I've included the table description via DESCRIBE TABLE:
mysql> describe import;
+--------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------------------+-------+
| article_id | varchar(32) binary | | PRI | | |
| category | varchar(100) | | PRI | | |
| subcategory | varchar(100) | | PRI | | |
| is_published | enum('NO','YES') | | | NO | |
| priority | tinyint(3) unsigned | | | 0 | |
| slug | varchar(255) | YES | | NULL | |
| headline | varchar(255) | | | | |
| byline | varchar(255) | YES | | NULL | |
| content | text | | | | |
| publish_time | datetime | | | 0000-00-00 00:00:00 | |
| archive_time | datetime | YES | | NULL | |
+--------------+---------------------+------+-----+---------------------+-------+
11 rows in set (0.00 sec)
Unfortunately, this is a production environment - I can't enable any modes for debug checking - so any suggestions would be appreciate. Thanks.
How to repeat:
This bug seems sporatic, it doesn't have any particular pattern, it just seems to happen every once in a while. We've actually tried to reproduce the bug without any success (creating SQL scripts that do massive amounts of DELETEs, INSERTs, and REPLACEs in differing orders and patterns and multiple times).