A query on a temporary table fails with "Can't reopen table"
ERROR 1137 (HY000): Can't reopen table: 'temp_table'Short answer
Error 1137 means one query refers to the same TEMPORARY table more than once, for example in a self-join. MySQL does not allow that. Replace the temporary table with a WITH (CTE) in that query, or copy the data into a second temporary table and join the two.
Fix it fast
1Find out which cause you have
Check whether the table named in the error is a temporary table and how many times the failing query uses it.
SELECT * FROM temp_table JOIN temp_table AS t2;2Match what you see, then jump to its fix
- 1
Can't reopen table: 'temp_table' on a query that names the same temporary table under two aliases.The query refers to the temporary table twice (self-join, repeated subquery)Fix - 2
The error comes from calling a stored function, even though each statement inside it uses the table once.The same temporary table is used under different aliases inside a stored functionFix
- 1
What does the error look like?
ERROR 1137 (HY000): Can't reopen table: 'temp_table'
Cause 1 of 2
The query refers to the temporary table twice (self-join, repeated subquery)
1Check
Count the times the name appears in FROM, JOIN and subqueries of the single statement. The manual's own example is a join of the table with an alias of itself.
CREATE TEMPORARY TABLE t SELECT 1 AS col_a, 2 AS col_b;
SELECT * FROM t AS t1 JOIN t AS t2;2Fix
If the data is only needed for this query, build it as a common table expression. A CTE may be referenced several times in one statement.
WITH cte AS (SELECT 1 AS col_a, 2 AS col_b)
SELECT * FROM cte AS t1 JOIN cte AS t2;3Verify
Run the rewritten statement. It returns rows instead of error 1137.
WITH cte AS (SELECT 1 AS col_a, 2 AS col_b) SELECT COUNT(*) FROM cte AS t1 JOIN cte AS t2;Confirmed by dev.mysql.com
Cause 2 of 2
The same temporary table is used under different aliases inside a stored function
1Check
The manual says the error can also occur when a temporary table is referenced multiple times under different aliases in a stored function, even across different statements, and when it is referenced across a calling and a called function.
SHOW CREATE FUNCTION my_function\G2Fix
Make the function use the temporary table through one alias only, and do not read it from both a function and the function it calls. If the logic allows, use a CTE inside the function, or a permanent work table.
-- inside the function body, replace repeated temporary-table reads with a CTE
WITH cte AS (SELECT col_a, col_b FROM source_table WHERE batch_id = p_batch)
SELECT COUNT(*) INTO v_pairs FROM cte AS a JOIN cte AS b ON a.col_a < b.col_b;3Verify
Call the function again in a new session.
SELECT my_function(1);Confirmed by dev.mysql.com
How do I stop it coming back?
- Use a CTE for data that only one query needs, and keep temporary tables for work that spans several statements.
- Do not build self-joins on a TEMPORARY table; join a CTE or a permanent staging table instead.
- Keep stored functions free of shared temporary-table state.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix