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)
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 4.1, 5.0, 5,1, next bzr
• OS: Any
• 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.