Description:
MySQL marks a table as crashed when executing 'CHECK TABLE' after executing DELETE queries.
How to repeat:
1) Load in following SQL dump file (mysql test < dump.sql):
------dump.sql-------
DROP TABLE IF EXISTS answers;
CREATE TABLE answers (
a_id int(11) NOT NULL default '0',
status_type smallint(6) NOT NULL default '0',
UNIQUE KEY answers$a_id (a_id)
) TYPE=MyISAM;
/*!40000 ALTER TABLE answers DISABLE KEYS */;
LOCK TABLES answers WRITE;
INSERT INTO answers VALUES (47,4),(54,4),(55,4),(56,4),(57,5),(58,5),(59,5),(60,5),(61,5),(62,5),(63,5),(64,5),(65,5),(66,5),(67,5),(68,5),(69,5),(70,5),(71,5);
UNLOCK TABLES;
/*!40000 ALTER TABLE answers ENABLE KEYS */;
DROP TABLE IF EXISTS links;
CREATE TABLE links (
to_node varchar(15) binary NOT NULL default '',
from_node varchar(255) binary NOT NULL default '',
access_time datetime NOT NULL default '0000-00-00 00:00:00',
strength int(11) NOT NULL default '0',
static_strength int(11) default NULL,
KEY links$from_node (from_node),
KEY links$to_node (to_node)
) TYPE=MyISAM;
/*!40000 ALTER TABLE links DISABLE KEYS */;
LOCK TABLES links WRITE;
INSERT INTO links VALUES ('47','60','2004-09-03 01:47:34',171,NULL),('70','60','2004-09-03 01:47:34',192,NULL),('70','56','2004-09-03 01:47:34',184,NULL),('54','62','2004-09-03 01:47:34',194,NULL),('65','60','2004-09-03 01:47:34',170,NULL),('64','63','2004-09-03 01:47:34',199,NULL),('56','64','2004-09-03 01:47:34',195,NULL),('63','70','2004-09-03 01:47:34',174,NULL),('63','66','2004-09-03 01:47:34',201,NULL),('55','71','2004-09-03 01:47:34',171,NULL),('67','62','2004-09-03 01:47:34',197,NULL),('70','68','2004-09-03 01:47:34',175,NULL),('62','69','2004-09-03 01:47:34',170,NULL),('66','65','2004-09-03 01:47:34',199,NULL),('58','70','2004-09-03 01:47:34',180,NULL),('58','66','2004-09-03 01:47:34',176,NULL),('65','68','2004-09-03 01:47:34',199,NULL),('57','69','2004-09-03 01:47:34',170,NULL),('54','47','2004-09-03 01:47:34',200,NULL),('60','56','2004-09-03 01:47:34',181,NULL),('71','54','2004-09-03 01:47:34',172,NULL),('63','55','2004-09-03 01:47:34',195,NULL),('55','56','2004-09-03 01:47:34',204,NULL),('70','57','2004-09-03 01:47:34',176,NULL),('66','54','2004-09-03 01:47:34',204,NULL),('68','56','2004-09-03 01:47:34',197,NULL),('71','70','2004-09-03 01:47:34',178,NULL),('71','66','2004-09-03 01:47:34',182,NULL),('63','67','2004-09-03 01:47:34',196,NULL),('67','63','2004-09-03 01:47:34',196,NULL),('70','69','2004-09-03 01:47:34',177,NULL),('66','70','2004-09-03 01:47:34',184,NULL),('62','47','2004-09-03 01:47:34',196,NULL),('56','54','2004-09-03 01:47:34',201,NULL),('71','55','2004-09-03 01:47:34',171,NULL),('63','56','2004-09-03 01:47:34',195,NULL),('47','62','2004-09-03 01:47:34',196,NULL),('70','62','2004-09-03 01:47:34',188,NULL),('70','58','2004-09-03 01:47:34',180,NULL),('66','55','2004-09-03 01:47:34',202,NULL),('58','60','2004-09-03 01:47:34',179,NULL),('58','56','2004-09-03 01:47:34',180,NULL),('65','62','2004-09-03 01:47:34',195,NULL),('69','54','2004-09-03 01:47:34',171,NULL),('60','69','2004-09-03 01:47:34',170,NULL),('56','70','2004-09-03 01:47:34',184,NULL),('56','66','2004-09-03 01:47:34',202,NULL),('71','67','2004-09-03 01:47:34',171,NULL),('55','69','2004-09-03 01:47:34',170,NULL),('66','71','2004-09-03 01:47:34',182,NULL),('66','67','2004-09-03 01:47:34',202,NULL),('69','70','2004-09-03 01:47:34',177,NULL),('69','66','2004-09-03 01:47:34',179,NULL),('70','47','2004-09-03 01:47:34',181,NULL),('60','62','2004-09-03 01:47:34',173,NULL),('60','58','2004-09-03 01:47:34',179,NULL),('56','55','2004-09-03 01:47:34',204,NULL),('71','56','2004-09-03 01:47:34',171,NULL),('55','62','2004-09-03 01:47:34',170,NULL),('70','63','2004-09-03 01:47:34',174,NULL),('70','59','2004-09-03 01:47:34',181,NULL),('62','64','2004-09-03 01:47:34',195,NULL),('66','60','2004-09-03 01:47:34',177,NULL),('66','56','2004-09-03 01:47:34',202,NULL),('69','55','2004-09-03 01:47:34',170,NULL),('64','70','2004-09-03 01:47:34',180,NULL),('64','66','2004-09-03 01:47:34',199,NULL),('56','71','2004-09-03 01:47:34',171,NULL),('63','69','2004-09-03 01:47:34',176,NULL),('59','70','2004-09-03 01:47:34',181,NULL),('59','66','2004-09-03 01:47:34',176,NULL),('66','68','2004-09-03 01:47:34',201,NULL),('60','47','2004-09-03 01:47:34',171,NULL),('69','71','2004-09-03 01:47:34',172,NULL),('69','67','2004-09-03 01:47:34',176,NULL),('60','59','2004-09-03 01:47:34',204,NULL),('64','55','2004-09-03 01:47:34',196,NULL),('56','60','2004-09-03 01:47:34',181,NULL),('71','57','2004-09-03 01:47:34',171,NULL),('55','63','2004-09-03 01:47:34',195,NULL),('67','54','2004-09-03 01:47:34',170,NULL),('70','64','2004-09-03 01:47:34',180,NULL),('62','65','2004-09-03 01:47:34',195,NULL),('54','70','2004-09-03 01:47:34',177,NULL),('54','66','2004-09-03 01:47:34',204,NULL),('66','57','2004-09-03 01:47:34',178,NULL),('69','60','2004-09-03 01:47:34',170,NULL),('69','56','2004-09-03 01:47:34',175,NULL),('56','68','2004-09-03 01:47:34',197,NULL),('71','69','2004-09-03 01:47:34',172,NULL),('67','70','2004-09-03 01:47:34',175,NULL),('67','66','2004-09-03 01:47:34',202,NULL),('66','69','2004-09-03 01:47:34',179,NULL),('62','54','2004-09-03 01:47:34',194,NULL),('54','55','2004-09-03 01:47:34',201,NULL),('57','54','2004-09-03 01:47:34',178,NULL),('64','56','2004-09-03 01:47:34',195,NULL),('71','62','2004-09-03 01:47:34',171,NULL),('55','64','2004-09-03 01:47:34',196,NULL),('59','60','2004-09-03 01:47:34',204,NULL),('70','65','2004-09-03 01:47:34',180,NULL),('59','56','2004-09-03 01:47:34',183,NULL),('62','70','2004-09-03 01:47:34',188,NULL),('62','66','2004-09-03 01:47:34',202,NULL),('54','71','2004-09-03 01:47:34',172,NULL),('66','62','2004-09-03 01:47:34',202,NULL),('54','67','2004-09-03 01:47:34',170,NULL),('66','58','2004-09-03 01:47:34',176,NULL),('58','59','2004-09-03 01:47:34',176,NULL),('57','70','2004-09-03 01:47:34',176,NULL),('57','66','2004-09-03 01:47:34',178,NULL),('69','57','2004-09-03 01:47:34',170,NULL),('56','69','2004-09-03 01:47:34',175,NULL),('67','71','2004-09-03 01:47:34',171,NULL),('47','54','2004-09-03 01:47:34',200,NULL),('70','54','2004-09-03 01:47:34',177,NULL),('62','55','2004-09-03 01:47:34',170,NULL),('54','60','2004-09-03 01:47:34',176,NULL),('54','56','2004-09-03 01:47:34',201,NULL),('66','47','2004-09-03 01:47:34',201,NULL),('60','65','2004-09-03 01:47:34',170,NULL),('56','58','2004-09-03 01:47:34',180,NULL),('63','64','2004-09-03 01:47:34',199,NULL),('47','70','2004-09-03 01:47:34',181,NULL),('47','66','2004-09-03 01:47:34',201,NULL),('70','66','2004-09-03 01:47:34',184,NULL),('62','71','2004-09-03 01:47:34',171,NULL),('62','67','2004-09-03 01:47:34',197,NULL),('66','63','2004-09-03 01:47:34',201,NULL),('54','68','2004-09-03 01:47:34',170,NULL),('66','59','2004-09-03 01:47:34',176,NULL),('65','70','2004-09-03 01:47:34',180,NULL),('65','66','2004-09-03 01:47:34',199,NULL),('57','71','2004-09-03 01:47:34',171,NULL),('69','62','2004-09-03 01:47:34',170,NULL),('68','65','2004-09-03 01:47:34',199,NULL),('60','54','2004-09-03 01:47:34',176,NULL),('56','47','2004-09-03 01:47:34',198,NULL),('55','54','2004-09-03 01:47:34',201,NULL),('47','55','2004-09-03 01:47:34',198,NULL),('70','55','2004-09-03 01:47:34',176,NULL),('62','60','2004-09-03 01:47:34',173,NULL),('54','57','2004-09-03 01:47:34',178,NULL),('60','70','2004-09-03 01:47:34',192,NULL),('60','66','2004-09-03 01:47:34',177,NULL),('64','62','2004-09-03 01:47:34',195,NULL),('56','63','2004-09-03 01:47:34',195,NULL),('68','54','2004-09-03 01:47:34',170,NULL),('56','59','2004-09-03 01:47:34',183,NULL),('55','70','2004-09-03 01:47:34',176,NULL),('55','66','2004-09-03 01:47:34',202,NULL),('70','71','2004-09-03 01:47:34',178,NULL),('70','67','2004-09-03 01:47:34',175,NULL),('59','58','2004-09-03 01:47:34',176,NULL),('66','64','2004-09-03 01:47:34',199,NULL),('54','69','2004-09-03 01:47:34',171,NULL),('69','63','2004-09-03 01:47:34',176,NULL),('68','70','2004-09-03 01:47:34',175,NULL),('68','66','2004-09-03 01:47:34',201,NULL),('67','69','2004-09-03 01:47:34',176,NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE links ENABLE KEYS */;
DROP TABLE IF EXISTS links_status_fix;
CREATE TABLE links_status_fix (
from_node varchar(255) binary NOT NULL default ''
) TYPE=MyISAM;
/*!40000 ALTER TABLE links_status_fix DISABLE KEYS */;
LOCK TABLES links_status_fix WRITE;
INSERT INTO links_status_fix VALUES ('57'),('58'),('59'),('60'),('60'),('60'),('62'),('62'),('62'),('63'),('63'),('64'),('64'),('66'),('66'),('66'),('66'),('67'),('68'),('68'),('69'),('69'),('69'),('70'),('70'),('70'),('70'),('71'),('71'),('71');
UNLOCK TABLES;
/*!40000 ALTER TABLE links_status_fix ENABLE KEYS */;
---end of dump.sql---
2) In MySQL 4.0.20 run the following sql statements:
DELETE links FROM links l, answers a WHERE l.to_node = a.a_id AND a.status_type = 5 AND l.from_node NOT LIKE 'http%';
DELETE links FROM links l, links_status_fix f WHERE l.from_node=f.from_node;
In MySQL 4.1 & 5.0 run the following sql statements:
DELETE l FROM links l, answers a WHERE l.to_node = a.a_id AND a.status_type = 5 AND l.from_node NOT LIKE 'http%';
DELETE l FROM links l, links_status_fix f WHERE l.from_node=f.from_node;
Notice that the second statement fails with "ERROR 1034: Incorrect key file for table: 'l'. Try to repair it"
Then a check table results in:
check table links;
+-----------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------------------------+
| foo.links | check | warning | Table is marked as crashed |
| foo.links | check | status | OK |
+-----------+-------+----------+----------------------------+
2 rows in set (0.00 sec)