MySQL ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

by Andrew Martin

When setting up a query using a temporary lookup table, I got this error:

ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;  
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;  
Query OK, 3228659 rows affected (2.01 sec)  
Records: 3228659  Duplicates: 0  Warnings: 0

Then the query is easy:

SELECT COUNT(1) cnt, journal_invoice_ref  
FROM tmp_journals  
GROUP BY journal_date  
HAVING cnt > 10000

UNION

SELECT COUNT(1) cnt, journal_invoice_ref  
FROM tmp_journals_2  
GROUP BY journal_invoice_ref  
HAVING cnt < 10