Bug #22384 DELETE FROM table causes "Incorrect key file for table"
Submitted: 15 Sep 2006 9:42 Modified: 10 Oct 2006 18:33
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.0BK,4.1BK,5.0BK,5.1BK OS:Any (*)
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: corruption, crash, keyfile, myisam, table

[15 Sep 2006 9:42] Shane Bester
Description:
Creating a table from mysqldump export, then running 
"DELETE FROM `t1` WHERE `id` not in (SELECT 1)" causes keyfile to crash.

CREATE TABLE `t1` (
  `id` INT,
  `url` TEXT NOT NULL,
   KEY `url` (`url`(512))
) ENGINE=MyISAM  MAX_ROWS=100000000;

shell> mysql -uroot test -P3307  < newsql.sql
ERROR 126 (HY000) at line 2936: Incorrect key file for table './test/t1.MYI'; try to repair it

mysql> check table t1 extended;
+---------+-------+----------+----------------------------+
| Table   | Op    | Msg_type | Msg_text                   |
+---------+-------+----------+----------------------------+
| test.t1 | check | warning  | Table is marked as crashed |
| test.t1 | check | error    | Found 156 keys of 177      |
| test.t1 | check | error    | Corrupt                    |
+---------+-------+----------+----------------------------+
3 rows in set (0.02 sec)

How to repeat:
Simple testcase is uploaded. See private comment for URL.

Suggested fix:
.
[16 Sep 2006 23:11] MySQL Verification Team
**NEW TESTCASE** Import into mysql using command line client

Attachment: bug22384_testcase.zip (application/zip, text), 161.79 KiB.

[17 Sep 2006 8:22] MySQL Verification Team
ANOTHER TESTCASE that can show key file crash when imported.

Attachment: bug22384_testcase2.zip (application/zip, text), 148.65 KiB.

[17 Sep 2006 10:34] MySQL Verification Team
Yes another slightly different testcase. import into mysql

Attachment: bug22384_testcase3.zip (application/zip, text), 190.85 KiB.

[28 Sep 2006 8:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12687

ChangeSet@1.2196, 2006-09-28 10:20:32+02:00, istruewing@chilla.local +1 -0
  Bug#22384 - DELETE FROM table causes "Incorrect key file for table"
  
  Deletes on a big index could crash the index when it needs to
  shrink the index.
  
  Put back a negation operator ('!') that vanished between
  3.23 and 4.0.
[28 Sep 2006 9:21] Ingo Strüwing
My test case which seems too big for the test suite.

Attachment: bug22384.test.gz (application/x-tar, text), 148.63 KiB.

[28 Sep 2006 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12694

ChangeSet@1.2196, 2006-09-28 11:41:38+02:00, istruewing@chilla.local +1 -0
  Bug#22384 - DELETE FROM table causes "Incorrect key file for table"
  
  Deletes on a big index could crash the index when it needs to
  shrink.
  
  Put a forgotten negation operator in.
  
  No test case. It is too big for the test suite. And it does not
  work with 4.0, only with higher versions. It is attached to the
  bug report.
[29 Sep 2006 8:23] Eric de Ruiter
I'm also getting the same error in my development database (running mysql 5.0.21), the production database (4.1.11) doesn't seem to have this problem.

The table looks like this:

CREATE TABLE `relation` (
  `parent_type` enum(<47 items>) collate utf8_unicode_ci NOT NULL,
  `parent_id` int(10) unsigned NOT NULL default '0',
  `child_type` enum(<47 items>) collate utf8_unicode_ci NOT NULL,
  `child_id` int(10) unsigned NOT NULL default '0',
  `level` smallint(5) unsigned NOT NULL default '0',
  KEY `child_id` (`child_id`),
  KEY `parent_id` (`parent_id`),
  KEY `parent` (`parent_id`,`parent_type`),
  KEY `child_type` (`child_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The table currently holds about 176000 records (I was not able to reproduce with an almost empty table, but I'll try shortly if I can come up with a reasonable test case).

A query causing the error looks like this:

DELETE FROM relation USING relation WHERE (1=1) AND ( `child_type`='Project' AND `child_id`='336'); 

The result is:

[126]: Incorrect key file for table './development/relation.MYI'; try to repair it

The strange thing is that this error does not occur if I rewrite the query to:

DELETE FROM relation WHERE (1=1) AND ( `child_type`='Project' AND `child_id`='336'); 

And also the error disappears if I remove some of the indexes (but I was unable to see the pattern, with one or two indexes the error doesn't seem to come up, but with some combinations of 3 or more indexes the error comes up).

If I have time I will try to come up with a reasonable testcase.
[29 Sep 2006 8:36] Ingo Strüwing
In agreement with Sergey Vojtovich and Calvin Sun the patch can be taken as approved. I still was not able to make a reasonable small test case let alone one that works for 4.0.
[29 Sep 2006 10:07] MySQL Verification Team
smallest testcase I can come up with on 5.0

Attachment: t1_120859_ark.zip (application/x-zip-compressed, text), 106.58 KiB.

[29 Sep 2006 10:29] MySQL Verification Team
i am still trying to reproduce issue in 4.0 now. will post more details later.
[29 Sep 2006 10:34] MySQL Verification Team
repeated in 4.0.27-debug
[INFO]   12:28:34 [line 0226] - 000002563 successful queries.  000000000 failed queries (2563.000000 QPS).
[INFO]   12:29:34 [line 0226] - 000145304 successful queries.  000000000 failed queries (2382.032787 QPS).
[INFO]   12:30:34 [line 0226] - 000285897 successful queries.  000000000 failed queries (2362.785124 QPS).
[INFO]   12:31:34 [line 0226] - 000357335 successful queries.  000000000 failed queries (1974.226519 QPS).
[INFO]   12:32:34 [line 0226] - 000471997 successful queries.  000000000 failed queries (1958.493776 QPS).
[INFO]   12:33:34 [line 0226] - 000586720 successful queries.  000000000 failed queries (1949.235880 QPS).
[ERROR]  12:34:08 [line 1362] - query failed (1034) - Incorrect key file for table: 't1'. Try to repair it: DELETE FROM `t1` WHERE `c1` != ' [...]
[INFO]   12:34:34 [line 0226] - 000651376 successful queries.  000000001 failed queries (1804.368421 QPS).
[INFO]   12:35:34 [line 0226] - 000766797 successful queries.  000000001 failed queries (1821.372922 QPS).
[INFO]   12:36:35 [line 0226] - 000880064 successful queries.  000000001 failed queries (1829.656965 QPS).
[ERROR]  12:37:12 [line 1362] - query failed (1034) - Incorrect key file for table: 't1'. Try to repair it: DELETE FROM `t1` WHERE `c1` != ' [...]

I need more time to make a small testcase. The above testcase consisted of 400000 rows. Will post it on weekend if possible.
[29 Sep 2006 17:09] Ingo Strüwing
Pushed to 5.1-engines, 5.0-engines, 4.1-engines, and 4.0.28.
[3 Oct 2006 8:54] Sergey Vojtovich
Fixed in 4.0.28, 4.1.22, 5.0.26, 5.1.12.
[4 Oct 2006 20:47] Paul DuBois
Noted in 4.0.28, 4.1.22, 5.0.26, 5.1.12 changelogs.

Deleting from a large index could cause index corruption when it
needed to shrink.
[9 Oct 2006 5:30] Sergey Vojtovich
BUG#22460 was marked as duplicate of this bug.
[9 Oct 2006 6:17] MySQL Verification Team
ingo, what are all possible situations this bug can be exposed? can UPDATE also trigger the bug??
[9 Oct 2006 9:44] Ingo Strüwing
Yes, indeed, you are right. A code search showed that this can happen on

- DELETE
- UPDATE
    - when a key must be deleted (as it changed
      and must be inserted at another place)
    - when a key must be "un-inserted" for a duplicate in a
      later index or if the new record is longer than the
      old one and would exceed the data file size limit
- INSERT
    - when a key must be "un-inserted" for a duplicate in a
      later index or the record would exceed the data
      file size limit
- REPAIR TABLE (!!!)
    - when a duplicate key is found
- myisamchk
    - when sorting the records by an index
[10 Oct 2006 7:43] James Day
Paul, please mention the other causes in the release note. Possibly:

Deleting entries from a large MyISAM index could cause index corruption when it needed to shrink. Deletes from an index can happen when a record is deleted, when a key changes and must be moved and when a key must be un-inserted because of a duplicate key. This can also happen in REPAIR TABLE when a duplicate key is found and in myisamchk when sorting the records by an index. (Bug#22384)

This appears significant enough to merit an early mention in the bug fix list.
[10 Oct 2006 18:33] Paul DuBois
Modified the changelog entries to use James' wording
and moved them up in the list.