MySQL Bugs: #12291: ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql518e_1

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

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>

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

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

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

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

Noted in 5.0.14 changelog.

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.

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.

For a reproducible case of the hard drive space exhaustion, see http://bugs.mysql.com/bug.php?id=24413

Recently looked the posting. great job  ?