Bug #49855 Multi-table RENAME doesn't work with TMP tables.
Submitted: 21 Dec 2009 19:47 Modified: 22 Dec 2009 6:49
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:4.1, 5.0, 5,1, next bzr OS:Any
Assigned to: CPU Architecture:Any

[21 Dec 2009 19:47] Konstantin Osipov
Description:
A multi-table rename doesn't work if one of the tables being renamed
is temporary.

How to repeat:
mysql> rename table t1 to t3, t2 to t1, t3 to t2;
ERROR 1017 (HY000): Can't find file: './test/t2.frm' (errno: 2)
mysql> drop table if exists t1, t2, t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.11 sec)

mysql> create temporary table t2 like t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> rename table t1 to t3, t2 to t1, t3 to t2;
ERROR 1017 (HY000): Can't find file: './test/t2.frm' (errno: 2)
[21 Dec 2009 19:48] Konstantin Osipov
I'm using mysql-5.6. It's probably present in 4.0 and up (needs investigation, which I would like to request the verifier to perform).
[22 Dec 2009 6:43] Sveta Smirnova
Thank you for the report.

Verified as described. Problem exists in earlier versions (tested since 4.1)
[22 Dec 2009 6:49] Sveta Smirnova
This is not a bug actually.

See http://dev.mysql.com/doc/refman/5.1/en/rename-table.html:

You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:

mysql> ALTER TABLE orig_name RENAME new_name;

There is verified feature request: bug #1154 about this also.