Can’t reopen table: ‘t1′

Posted on July 19, 2010, 10:24 pm, by Rhys, under DBA, MySQL.

I’m quite often jumping between MySQL and SQL Server so remembering the quirks and limitations of each system can be difficult. With MySQL, if you attempt to reference a temporary table more than once in the same query, you will encounter the following error; 

Error Code : 1137
Can't reopen table: 't1’

The following provides an example of this…

?View Code MYSQL
USE test;
 
CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);
 
SELECT *
FROM test AS t1
INNER JOIN test AS t2
     ON t1.Id = t2.Id;

It’s not just self-joins that have this issue UNIONS do as well;

?View Code MYSQL
SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test AS t2;

There’s a thread over on Stackoverflow discussing this problem. Here’s a solution I commonly use to get around the problem;

?View Code MYSQL
USE test;
 
# Create temp table
CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);
 
# Insert some test data
INSERT INTO test (Id) VALUES (1),(2),(3),(4),(5);
 
# Clone the table. This will do structure & indices but no data.
CREATE TEMPORARY TABLE test2 LIKE test;
 
# Insert the data into the new table
INSERT INTO test2
SELECT Id
FROM test;
 
# Now our queries will work if we use the tables clone
SELECT *
FROM test AS t1
INNER JOIN test2 AS t2
      ON t1.Id = t2.Id;
 
SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test2 AS t2;
 
# Clean up
DROP TEMPORARY TABLE IF EXISTS test;
DROP TEMPORARY TABLE IF EXISTS test2;

Comment (RSS)  |  Trackback

Leave a Reply

 
«
»