> ## Content Index
> Fetch the complete content index at: https://www.binarysludge.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# MySQL ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'
- URL: https://www.binarysludge.com/2010/03/18/mysql-error-1137-hy000-cant-reopen-table/
- Published: 2010-03-18T15:00:49.000Z
- Updated: 2018-04-17T21:21:38.000Z
- Author: Andrew Martin
- Tags: error, MySQL, temporary table, #Import 2026-10-22 12:56

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

```