How to solve MySQL can't reopen table error

MySQL will trigger an error when you refer to a temporary table more than one time in a single query.

For example, suppose you create a temporary table named top_students from the students table as shown below:

CREATE TEMPORARY TABLE top_students 
  AS SELECT * FROM students LIMIT 10;

You can’t refer to the temporary table top_students twice in a single query like this:

mysql> SELECT * FROM top_students JOIN top_students AS table_two;
ERROR 1137 (HY000): Can't reopen table: 'top_students'

The ERROR 1137 above is a known issue with MySQL that hasn’t got any fix since 2008.

There are three ways you can work around this issue:

• Using a Common Table Expression instead of a temporary table
• Creating multiple temporary tables
• Creating a permanent table with a special TEMP_ prefix

Let’s walk through the solutions one by one, starting with using a Common Table Expression.

Using a Common Table Expression instead of a temporary table

MySQL recommends you use a Common Table Expression (or CTE) instead of a temporary table when you need to refer to a temporary table more than once.

CTE is a temporary result set that you can refer multiple times in another SQL statements like SELECT, INSERT, UPDATE, and DELETE statements.

Here’s an example of creating a CTE with the alias top_students:

WITH top_students AS (SELECT * FROM students LIMIT 10)
SELECT * FROM top_students JOIN top_students AS table_two;

The difference between a CTE and a temporary table is that a temporary table can be created first and then referred to later.

A CTE must be executed right before any statement that needs to use it, making a CTE even more temporary than a temporary table.

One downside of this method is that you need to add the CTE to each SQL statement that requires the result set:

WITH top_students AS (SELECT * FROM students LIMIT 10)
SELECT ....

WITH top_students AS (SELECT * FROM students LIMIT 10)
SELECT ....

While if you use a temporary table, you can create once and refer multiple times:

CREATE TEMPORARY TABLE top_students 
  AS SELECT * FROM students LIMIT 10;

SELECT ....
SELECT ....
SELECT ....

Creating multiple temporary tables

Another way to solve the can't reopen table error is to create multiple temporary tables that you need to refer to.

Here’s how you may do it:

CREATE TEMPORARY TABLE top_students_a 
  AS SELECT * FROM students LIMIT 10;

CREATE TEMPORARY TABLE top_students_b 
  AS SELECT * FROM students LIMIT 10;

SELECT * FROM top_students_a JOIN top_students_b AS table_two; 

Because the SELECT statement above refers to two different tables, then the error won’t be triggered.

Unlike CTEs, these temporary tables are still available after creation, so you can refer to them later as long as you’re in the same session.

Creating a permanent table with a special TEMP_ prefix

The last solution that you may want to try is to create a regular table so that you can refer to the same table more than once.

To distinguish a temporary table from other permanent tables, you can add a TEMP_ prefix to the table as follows:

CREATE TABLE TEMP_top_students 
  AS SELECT * FROM students LIMIT 10;

Now you can refer to the table more than once in a single query:

SELECT * FROM TEMP_top_students JOIN TEMP_top_students AS table_two; 

But the downside of this method is that the table won’t be removed automatically when the session ends.

You need to drop the table after you’re done using it:

DROP TABLE TEMP_top_students;

And those are the three ways you can solve the MySQL can't reopen table issue when using temporary tables.

I hope this tutorial has been useful for you. 🙏