2021
MariaDBERRORNotableQueryHIGH confidence
Cannot reopen table in the same query
Production Risk
Low — query is rejected.
What this means
A table was referenced more than once in a single query in a way that MySQL cannot resolve. This commonly occurs when a TEMPORARY TABLE is referenced multiple times in the same SELECT, or a table is used in a self-join without aliases.
Why it happens
- 1Referencing a TEMPORARY TABLE multiple times in the same SELECT.
- 2Self-join without table aliases causing ambiguous references.
- 3Attempting to UPDATE and SELECT the same TEMPORARY TABLE simultaneously.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TEMPORARY TABLE tmp (id INT); SELECT * FROM tmp t1, tmp t2; -- can't reopen temporary table
expected output
ERROR 2021 (HY000): Can't reopen table: 'tmp'.
Fix 1
Use a regular (non-temporary) table for multi-reference queries
Use a regular (non-temporary) table for multi-reference queries
CREATE TABLE tmp_regular (id INT); SELECT * FROM tmp_regular t1, tmp_regular t2;
Why this works
Regular tables can be referenced multiple times in the same query.
Fix 2
Use a subquery or CTE to avoid re-opening the temp table
Use a subquery or CTE to avoid re-opening the temp table
WITH cte AS (SELECT * FROM tmp) SELECT * FROM cte t1, cte t2;
Why this works
CTEs materialise the result and can be referenced multiple times.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 2021 ER_CANT_REOPEN_TABLE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev