| Bug #1577 | Corruption of MyISAM table | ||
|---|---|---|---|
| Submitted: | 16 Oct 2003 11:13 | Modified: | 18 Dec 2003 15:13 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | mysql-4.1.1-alpha-nightly-20031012 | OS: | GNU/Linux (Intel) |
| Assigned to: | CPU Architecture: | Any | |
[17 Oct 2003 5:36]
[ name withheld ]
any delete queries (like "delete from table1 where id=3563" and "delete from table1 where id in (12345,234)") corrupt table. mysql> check table idx; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | testtest.idx | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (9.51 sec) mysql> delete from idx where id in (535333,332122); ERROR 1034 (HY000): Incorrect key file for table: 'idx'; try to repair it mysql> check table idx; +--------------+-------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-----------------------------+ | testtest.idx | check | warning | Table is marked as crashed | | testtest.idx | check | error | Found 725291 keys of 725292 | | testtest.idx | check | error | Corrupt | +--------------+-------+----------+-----------------------------+ 3 rows in set (2.38 sec) mysql> repair table idx; +--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | testtest.idx | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (5 min 31.60 sec) mysql> check table idx; +--------------+-------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-------------------------------------------------------------------+ | testtest.idx | check | error | Found key at page 88717312 that points to record outside datafile | | testtest.idx | check | error | Corrupt | +--------------+-------+----------+-------------------------------------------------------------------+ 2 rows in set (14.29 sec) ...... mysql> check table idx; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | testtest.idx | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (9.87 sec) mysql> delete from idx where id=535333; ERROR 1034 (HY000): Incorrect key file for table: 'idx'; try to repair it mysql> check table idx; +--------------+-------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-----------------------------+ | testtest.idx | check | warning | Table is marked as crashed | | testtest.idx | check | error | Found 725290 keys of 725292 | | testtest.idx | check | error | Corrupt | +--------------+-------+----------+-----------------------------+ 3 rows in set (1.42 sec) mysql> repair table idx; +--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | testtest.idx | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (2 min 28.00 sec) mysql> check table idx; +--------------+-------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-------------------------------------------------------------------+ | testtest.idx | check | error | Found key at page 88717312 that points to record outside datafile | | testtest.idx | check | error | Corrupt | +--------------+-------+----------+-------------------------------------------------------------------+ 2 rows in set (5.55 sec)
[22 Oct 2003 12:41]
Dean Ellis
I am unable to duplicate this with the latest 4.1.1 BitKeeper sources (current as of today). All of the reported DELETE forms are functioning correctly. This was possibly a problem that has been corrected. Please try it with the latest sources and see if any of the DELETEs result in corruption. Thank you
[27 Oct 2003 5:29]
[ name withheld ]
4.1.1-alpha-nightly-20031026 snapshot still have this bug.
[18 Nov 2003 15:13]
Dean Ellis
I am still unable to duplicate this. Would it be possible for you to compress your tables and upload them to: ftp://support.mysql.com/pub/mysql/secret with a file name mentioning bug1577, so that we can test against your exact data?
[14 Feb 2005 22:54]
Bugs System
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".
Description: MyISAM table corrupts on query like: "delete from table1 where id in (select id from table2)". It happens only when table have more than ~~600000 rows, e.g. there is no such symptoms on small tables. How to repeat: There are three tables: create table hosts ( id int primary key auto_increment NOT NULL, host text, proto smallint unsigned, online bool default 0, statusOfIndex smallint default 0, lastIndexed DATETIME default 0, cacheTotalFiles bigint default 0, cacheTotalSize bigint default 0, UNIQUE(host(255),proto) ); create table idx ( id bigint primary key auto_increment NOT NULL, host int, dir bool, path text, size int, idxDate DATETIME, cs char(32), FULLTEXT(path), INDEX file (cs(32)) ); create table tmpidx ( id bigint primary key auto_increment NOT NULL, host int, dir bool, path text, size int, idxDate DATETIME, FULLTEXT(path), cs char(32), INDEX file (cs(32)) ); create table tmpt (id bigint primary key auto_increment NOT NULL); Table idx contain 725292 rows, tmpidx contain 33807. Content of idx and tmpidx differs partially. So... I'm trying to compare tables (using column 'cs' -- md5 checksums), and delete rows that exists in idx but not in tmpidx: mysql> insert into tmpt select idx.id from idx left join tmpidx using (cs) where idx.host=6 and tmpidx.id is NULL; Query OK, 6894 rows affected (10.84 sec) Records: 6894 Duplicates: 0 Warnings: 0 mysql> select count(id) from tmpt; +-----------+ | count(id) | +-----------+ | 6894 | +-----------+ 1 row in set (0.00 sec) mysql> check table idx; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | testtest.idx | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (17.35 sec) mysql> delete from idx where id in (select id from tmpt); ERROR 1034 (HY000): Incorrect key file for table: 'idx'; try to repair it mysql> check table idx; +--------------+-------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-----------------------------+ | testtest.idx | check | warning | Table is marked as crashed | | testtest.idx | check | error | Found 725291 keys of 725292 | | testtest.idx | check | error | Corrupt | +--------------+-------+----------+-----------------------------+ 3 rows in set (1.43 sec) mysql> repair table idx; +--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | testtest.idx | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (2 min 23.65 sec) mysql> check table idx; +--------------+-------+---------- +-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+---------- +-------------------------------------------------------------------+ | testtest.idx | check | error | Found key at page 88717312 that points to record outside datafile | | testtest.idx | check | error | Corrupt | +--------------+-------+---------- +-------------------------------------------------------------------+ I can recover table only by myisamchk utility (with mysqld turned off): # myisamchk -e -v idx.MYI Checking MyISAM file: idx.MYI Data records: 725292 Deleted blocks: 0 - check file-size - check key delete-chain block_size 1024: block_size 2048: - check record delete-chain No recordlinks - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 myisamchk: error: Found key at page 88717312 that points to record outside datafile - check records and index references myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'idx.MYI' is corrupted Fix it using switch "-r" or "-o" # myisamchk -r -v idx.MYI - recovering (with sort) MyISAM-table 'idx.MYI' Data records: 725292 - Fixing index 1 - Searching for keys, allocating buffer for 1310712 keys - Dumping 725292 keys - Fixing index 2 - Searching for keys, allocating buffer for 499320 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 599179 keys - Last merge and dumping keys