Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A query on a temporary table fails with "Can't reopen table"

ErrorERROR 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.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1Can'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. 2The 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

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

Did this fix it?

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\G

2Fix

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.