Bug #12291 ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql518e_1_2.MYI'; try to
Submitted: 1 Aug 2005 3:25 Modified: 17 Sep 2005 2:30
Reporter: Bob Rintel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.10/Bk source OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[1 Aug 2005 3:25] Bob Rintel
Description:
A simple query results in this error every time:

ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql518e_1_2.MYI'; try to repair it

How to repeat:
mysql> use test;
Database changed
mysql> create temporary table a (
    ->      x int,
    ->      y int);
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table b (
    ->      x int,
    ->      y int);
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table c (
    ->      x int,
    ->      primary key (x));
Query OK, 0 rows affected (0.01 sec)

mysql> insert a values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert b values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert c values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select A.x, C.x
    ->      from a A, b B, c C
    ->      where A.x = B.x and C.x >= A.y and C.x <= B.y;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql518e_1_2.MYI'; try to repair it
mysql>
[1 Aug 2005 4:11] Miguel Solorzano
Thank you for the bug repor. Instead of the error reported I
got a server crash with the latest Bk source 5.0:

050801  1:07:23 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.11-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132370864 (LWP 6646)]
mysqld: ha_myisam.cc:1202: virtual int ha_myisam::index_first(byte*): Assertion `inited==INDEX' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1132370864 (LWP 6646)]
0xffffe410 in ?? ()
(gdb) backtrace full
#0  0xffffe410 in ?? ()
No symbol table info available.
#1  0x437e85cc in ?? ()
No symbol table info available.
#2  0x00000006 in ?? ()
No symbol table info available.
#3  0x40214b75 in abort () from /lib/tls/libc.so.6
No symbol table info available.
#4  0x4020c903 in __assert_fail () from /lib/tls/libc.so.6
No symbol table info available.
#5  0x0829e76f in ha_myisam::index_first (this=0x8e2d8e8, buf=0x8e2d9c8 "ÿ\003") at ha_myisam.cc:1202
        error = 140602524
#6  0x0829937b in handler::read_range_first (this=0x8e2d8e8, start_key=0x0, end_key=0x8e2ef34, eq_range_arg=false, sorted=false)
    at handler.cc:2325
        result = 28
        _db_func_ = 0x5 <Address 0x5 out of bounds>
        _db_file_ = 0x8e2d9c8 "ÿ\003"
        _db_level_ = 1
        _db_framep_ = (char **) 0x81520d2
#7  0x0829903b in handler::read_multi_range_first (this=0x8e2d8e8, found_range_p=0x437e8878, ranges=0x8e2ef28, range_count=1, sorted=false, 
    buffer=0x0) at handler.cc:2199
        result = 137
        _db_func_ = 0x0
        _db_file_ = 0x1 <Address 0x1 out of bounds>
        _db_level_ = 149117064
        _db_framep_ = (char **) 0x0
#8  0x08287e45 in QUICK_RANGE_SELECT::get_next (this=0x8e2f6f8) at opt_range.cc:6181
        count = 1
        mrange_slot = (KEY_MULTI_RANGE *) 0x8e2ef48
        mrange_end = (KEY_MULTI_RANGE *) 0x8e2ef48
        result = 0
        mrange = (KEY_MULTI_RANGE *) 0x1
        start_key = (key_range *) 0x8e2ef28
        end_key = (key_range *) 0x8e2ef34
        _db_func_ = 0x8e2f6f8 "¨_a\b"
        _db_file_ = 0x1 <Address 0x1 out of bounds>
        _db_level_ = 256
        _db_framep_ = (char **) 0x1
#9  0x08290e2d in rr_quick (info=0x8e35184) at records.cc:165
[13 Sep 2005 20:16] 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/internals/29767
[15 Sep 2005 21:19] 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/internals/29961
[16 Sep 2005 13:07] Evgeny Potemkin
Optimizer did choose "Range checked for each record" for one of the tables.
  For first few loops over that table it choose sequential access, on later
  stage it choose to use index. Because table was previously initialized for 
  sequential access, it skips intitialization for index access, and when
  server tries to retrieve data error occurs.

Fixed in 5.0.14, cset 1.1935.83.1
[17 Sep 2005 2:30] Paul Dubois
Noted in 5.0.14 changelog.
[14 Nov 2005 23:28] Brian Sperlongano
I've observed this error on 5.0.15 on 64-bit Solaris 8 in a large table transfer stored procedure.  I'll try to come up with a simple test case.
[15 Nov 2005 14:52] Brian Sperlongano
Upon further observation I determined that the error I got was the result of a temporary table getting so large that it filled TMPDIR, generating the above error.  It would be nice if MySQL were able to detect a disk full and generate a less-cryptic error message.
[18 Nov 2006 13:50] Philip Stoev
For a reproducible case of the hard drive space exhaustion, see http://bugs.mysql.com/bug.php?id=24413
[21 Jan 2012 11:37] Tiesiugdief Tiesiugdief
Recently looked the posting. great job  ?