Can’t UNION ALL on a temporary table?

I’m trying to run the following simple test- creating a temp table, and then UNIONing two different selections:

CREATE TEMPORARY TABLE tmp 
SELECT * FROM people;

SELECT * FROM tmp
UNION ALL
SELECT * FROM tmp;

But get a #1137 - Can't reopen table: 'tmp'

I thought temp tables were supposed to last the session. What’s the problem here?

Answer

This error indicates that the way in which Mysql tables manages the temporary tables has been changed which in turn affects the joins, unions as well as subqueries. To fix mysql error can’t reopen table, try out the following solution:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;

After this you can perform the union operation.

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

http://www.mysqlrepair.org/mysqlrepair/cant-reopen-table.php