MySQL Bugs: #25891: Creation of temporary table with key causing error 124

Bug #25891	Creation of temporary table with key causing error 124
Submitted:	27 Jan 2007 14:17	Modified:	9 Jan 2008 18:31
Reporter:	Matthew Clark	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.36-BK, 5.0.27	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	124, myisam, regression, storage engine, temporary

Description:
This issue has occured in the following releases of MySQL 
5.0.20, 5.0.27 
on RedHat EL4. 

I'm creating a temporary table with a simple index using something like the following: 

CREATE TEMPORARY TABLE mydb.mytmptable KEY(mykeyfield) ENGINE MyISAM SELECT myfield1, myfield2, myfield3 FROM mytbl1, mytbl2... and so on. 

I then do a query that will make use of the mykeyfield index along with a JOIN on a non-temporary table. The error comes back 'got error 124 from storage engine'. 

If I run REPAIR TABLE mydb.mytmptable the second query then works with myisam. 
The second query also works if I use the HEAP or INNODB engine in the first query. 

Any thoughts? This combination of queries also works in V4.1.20.

How to repeat:
It doesn't always happen - seems to be to do with the number of rows returned.

Thank you for a problem report. Please, send the exact query that gives you this error. Send EXPLAIN results for it also. To put it short: we need a complete test case, as small as possible.

I have just noticed that the SELECT part of the query had SQL_BUFFER_RESULT set (the query is used elsewhere without creating a tmp table).  Removing the SQL_BUFFER_RESULT appears to fix the problem.  

Is SQL_BUFFER_RESULT incompatible with CREATE TEMPORARY TABLE x SELECT...? Or is this a bug?  Seems like a bug as it works with other storage engines.

The following simplified query causes the corrupt index:

CREATE TEMPORARY TABLE testTmp (KEY (uniqueRef)) ENGINE MyISAM SELECT SQL_BUFFER_RESULT mjb.uniqueRef, mjb.orderDate, mjb.accountID FROM mjb WHERE orderDate BETWEEN "2007-01-01" AND "2007-01-15 23:59:59" ORDER BY mjb.accountID, mjb.uniqueRef;

The following query then causes error 124 in storage engine:

SELECT * FROM testTmp ORDER BY uniqueRef limit 50;

To fix, 

REPAIR testTmp;

Hope that helps - the explain for the original SELECT is :

           id: 1
  select_type: Single
        table: mjb
         type: range
possible_keys: orderDate
          key: orderDate
      key_len: 8
          ref: NULL
         rows: 20468
        Extra: Using where; Using temporary; Using filesort

Matthew.

Verified with latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> 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 3
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from Table1;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (1.33 sec)

mysql> show create table Table1;
+--------+----------------------------------------------------------------------
-----------------------------------------------------------------------+
| Table  | Create Table
                                                                       |
+--------+----------------------------------------------------------------------
-----------------------------------------------------------------------+
| Table1 | CREATE TABLE `Table1` (
  `id` int(11) NOT NULL auto_increment,
  `data` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------
-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create temporary table ttemp (KEY (id)) engine=MyISAM select sql_buffer_result * from Table1 order by id;
Query OK, 524288 rows affected (4.02 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> select * from ttemp order by id limit 50;
ERROR 1030 (HY000): Got error 124 from storage engine

You can populate Table1 with any arbitrary rows - it does not matter, really.

If it is intended limitation of sql_buffer_result or temporary tables usage, it should be explicitely described in the manual, at http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html, for example.

Workaround is to not use sql_buffer_result (or use a different engine)

Yes - that was mentioned in my post on 28 Jan 07.