Bug #25048 ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to repair it
Submitted: 13 Dec 2006 18:46 Modified: 3 Jan 2007 11:38
Reporter: Ken Hall Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.27/5.0BK/5.1b OS:Microsoft Windows (Windows/Linux)
Assigned to: Sergey Vojtovich
Tags: bfsm_2006_12_21, corruption, myisam

[13 Dec 2006 18:46] Ken Hall
Description:
When the enclosed script is run, it will create two tables, populate them, perform a delete quick and will result in a corrupted myi file.

When the enclosed script is run on a 4.1.12a server or an a 5.0.24 server, this is the result:
+--------------------------+--------+----------+---------------------------------------+
| Table                    | Op     | Msg_type | Msg_text                              |
+--------------------------+--------+----------+---------------------------------------+
| serverdb30.anomfoundcopy | repair | warning  | Number of rows changed from 0 to 8966 |
| serverdb30.anomfoundcopy | repair | status   | OK                                    |
+--------------------------+--------+----------+---------------------------------------+
2 rows in set

+----------+
| count(*) |
+----------+
|     8966 |
+----------+
1 row in set

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 38 rows affected

+----------+
| count(*) |
+----------+
|     8928 |
+----------+
1 row in set

+--------------------------+-------+----------+----------+
| Table                    | Op    | Msg_type | Msg_text |
+--------------------------+-------+----------+----------+
| serverdb30.anomfoundcopy | check | status   | OK       |
+--------------------------+-------+----------+----------+
1 row in set

Empty set
================================================================================

When run on the 5.0.27 server we get:
+-------------------------+--------+----------+---------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                              |
+-------------------------+--------+----------+---------------------------------------+
| anomcrash.anomfoundcopy | repair | warning  | Number of rows changed from 0 to 8966 |
| anomcrash.anomfoundcopy | repair | status   | OK                                    |
+-------------------------+--------+----------+---------------------------------------+
2 rows in set

+----------+
| count(*) |
+----------+
|     8966 |
+----------+
1 row in set

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

ERROR 126 : Incorrect key file for table '.\anomcrash\anomfoundcopy.MYI'; try to repair it
+----------+
| count(*) |
+----------+
|     8966 |
+----------+
1 row in set

+-------------------------+-------+----------+----------------------------+
| Table                   | Op    | Msg_type | Msg_text                   |
+-------------------------+-------+----------+----------------------------+
| anomcrash.anomfoundcopy | check | warning  | Table is marked as crashed |
| anomcrash.anomfoundcopy | check | error    | Found 8965 keys of 8966    |
| anomcrash.anomfoundcopy | check | error    | Corrupt                    |
+-------------------------+-------+----------+----------------------------+
3 rows in set

Empty set

================================================================================

How to repeat:
Run the attached SQL script.
[13 Dec 2006 18:48] Ken Hall
my.ini contents and information about the server configuration

Attachment: my.ini_and_machine_information.txt (text/plain), 11.13 KiB.

[13 Dec 2006 18:53] Ken Hall
script which will reproduce the error

Attachment: reproducableError.sql (text/plain), 66.86 KiB.

[13 Dec 2006 19:17] Miguel Solorzano
Thank you for the bug report. Verified on Windows and Linux servers
5.0 and 5.1:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 126 (HY000): Incorrect key file for table '.\test\anomfoundcopy.MYI'; try to repair it
+----------+
| count(*) |
+----------+
|      572 |
+----------+
1 row in set (0.00 sec)

+--------------------+-------+----------+----------------------------+
| Table              | Op    | Msg_type | Msg_text                   |
+--------------------+-------+----------+----------------------------+
| test.anomfoundcopy | check | warning  | Table is marked as crashed |
| test.anomfoundcopy | check | status   | OK                         |
+--------------------+-------+----------+----------------------------+
2 rows in set (0.00 sec)

Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.32    |
+-----------+
1 row in set (0.03 sec)

mysql>
-----------------------------------------------------------------------------------------
ERROR 126 (HY000): Incorrect key file for table './db1/anomfoundcopy.MYI'; try to repair it
+----------+
| count(*) |
+----------+
|      572 | 
+----------+
1 row in set (0.00 sec)

+-------------------+-------+----------+----------------------------+
| Table             | Op    | Msg_type | Msg_text                   |
+-------------------+-------+----------+----------------------------+
| db1.anomfoundcopy | check | warning  | Table is marked as crashed | 
| db1.anomfoundcopy | check | status   | OK                         | 
+-------------------+-------+----------+----------------------------+
2 rows in set (0.01 sec)

Empty set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.32-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> 
-------------------------------------------------------------------------------------------
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 38 rows affected (0.01 sec)

+----------+
| count(*) |
+----------+
|      534 |
+----------+
1 row in set (0.00 sec)

+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| test.anomfoundcopy | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Empty set (0.00 sec)

mysql> select version()
    -> ;
+--------------+
| version()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql>
[19 Dec 2006 10:12] Shane Bester
slightly smaller testcase

Attachment: testcase1.sql (text/x-delimtext), 11.29 KiB.

[19 Dec 2006 15:25] Shane Bester
testcase with fake data

Attachment: testcase2.sql (text/x-delimtext), 6.16 KiB.

[19 Dec 2006 15:51] Shane Bester
debug trace of the failing DELETE statement

Attachment: bug25048.zip (application/x-zip-compressed, text), 45.40 KiB.

[19 Dec 2006 19:23] Shane Bester
testcase with just 3 int columns and keys

Attachment: testcase3.sql (text/x-delimtext), 23.66 KiB.

[26 Dec 2006 11:51] 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/17386

ChangeSet@1.2352, 2006-12-26 15:54:50+04:00, svoj@mysql.com +3 -0
  BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to
              repair it
  
  Multi-table delete that is optimized with QUICK_RANGE reports table
  corruption.
  
  DELETE statement must not use KEYREAD optimization, and sets
  table->no_keyread to 1. This was ignored in QUICK_RANGE optimization.
  
  With this fix QUICK_RANGE optimization honors table->no_keyread
  value and does not enable KEYREAD when it is requested.
[26 Dec 2006 13:44] 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/17387

ChangeSet@1.2352, 2006-12-26 17:47:30+04:00, svoj@mysql.com +4 -0
  BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to
              repair it
  
  Multi-table delete that is optimized with QUICK_RANGE reports table
  corruption.
  
  DELETE statement must not use KEYREAD optimization, and sets
  table->no_keyread to 1. This was ignored in QUICK_RANGE optimization.
  
  With this fix QUICK_RANGE optimization honors table->no_keyread
  value and does not enable KEYREAD when it is requested.
[27 Dec 2006 0:12] Antony Curtis
Pushed to 5.1.15-beta repository
[27 Dec 2006 2:23] Antony Curtis
Pushed to 5.0.34 repository
[3 Jan 2007 11:38] MC Brown
An entry has been added to the 5.0.34 and 5.1.15 changelogs.
[3 Jan 2007 11:39] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.34 and 5.1.15 changelogs.
[15 Jan 2007 13:03] Shane Bester
Sergey can you comment on all possible scenarios that this bug could have been found?  What about UPDATE/DELETE/INSERT/REPLACE statements ?
[15 Jan 2007 14:21] Sergey Vojtovich
Shane,

only multi-table DELETE in 5.0 and up was affected.
[8 Dec 2009 7:39] gavin zhang
Now is Dec 2009,My mysql version is 5.1.34 ,the problem is exist yet.
why?

Incorrect key file for table './db_hbv5/cms_brand_activity.MYI'; try to repair it
update cms_brand_activity set view_count =view_count+1 where id='8843'

I must run
  linux#myisamchk -r -f hbv5/cms_brand_activity.MYI 
Repair it,
But I'm afraid it's fraily .I