Bug #48859 Create temporary, alter, analyze, table yields 'Incorrect key file for table'
Submitted: 18 Nov 2009 9:33 Modified: 21 Jan 2011 9:12
Reporter: Tor Didriksen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[18 Nov 2009 9:33] Tor Didriksen
Description:
For the test case below we get
ERROR HY000: Incorrect key file for table ....

See also discussion for Bug#47338

CREATE TEMPORARY TABLE t1 (f2 INT, f1 INT, PRIMARY KEY (f1)) ENGINE = MyISAM;

INSERT t1 ( f1 ) VALUES ( 5 );
INSERT t1 ( f1 ) VALUES ( 6 );

ALTER TABLE t1 ENGINE = MyISAM;
ANALYZE TABLE t1;

SELECT f1,f2 FROM t1 A WHERE f1 BETWEEN 0 AND 1;

How to repeat:
Run test case in Description.
[18 Nov 2009 11:01] Valeriy Kravchuk
Verified just as described with recent 6.0.14 from bzr:

77-52-1-11:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TEMPORARY TABLE t1 (f2 INT, f1 INT, PRIMARY KEY (f1)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT t1 ( f1 ) VALUES ( 5 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT t1 ( f1 ) VALUES ( 6 );
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> ALTER TABLE t1 ENGINE = MyISAM;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT f1,f2 FROM t1 A WHERE f1 BETWEEN 0 AND 1;
ERROR 126 (HY000): Incorrect key file for table 'ar/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/#sql731e_1_4.MYI'; try to repair it

This does NOT happen with 5.1.42, so we have a regression bug here.

With -debug binaries I've got assertion failure:

Version: '6.0.14-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
091118 12:57:30 [ERROR] /Users/openxs/dbs/6.0-codebase/libexec/mysqld: Incorrect key file for table 'ar/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/#sql731e_1_4.MYI'; try to repair it
Assertion failed: (next_insert_id == 0), function ha_external_lock, file handler.cc, line 5501.
[21 Jan 2011 9:12] Tor Didriksen
Not reproducible in 5.1 5.5 or trunk. Closing.