MySQL Bugs: #44890: Foreign Keys: rename fails on temporary table

Bug #44890	Foreign Keys: rename fails on temporary table
Submitted:	14 May 2009 23:38	Modified:	18 May 2009 15:27
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha	OS:	Linux (SUSE 11 64-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk-stage.

I start the server with mysqld --foreign-key-all-engines=1.

I can't rename a temporary table which has
the same name as a persistent foreign-key table.

This affects Milestone 14 of the foreign keys project.

How to repeat:
create table t1 (s1 int primary key);
create table t2 (s1 int references t1(s1)
 on update restrict on delete restrict);
create temporary table t2 (s1 int);
rename table t2 to t3;

Sample run:

mysql> create table t1 (s1 int primary key);
Query OK, 0 rows affected (0.25 sec)

mysql> create table t2 (s1 int references t1(s1)
    ->  on update restrict on delete restrict);
Query OK, 0 rows affected (0.17 sec)

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

mysql> rename table t2 to t3;
ERROR 1811 (HY000): Foreign key error: you cannot use 'RENAME TABLE' statement because't2' table is in a relationship

Thank you for the bug report. Verified as described:

miguel@hegel:~/dbs/6.1st$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.1.0-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (s1 int primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 (s1 int references t1(s1)
    ->  on update restrict on delete restrict);
Query OK, 0 rows affected (0.18 sec)

mysql> create temporary table t2 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> rename table t2 to t3;
ERROR 1811 (HY000): Foreign key error: you cannot use 'RENAME TABLE' statement because 't2' table is in a relationship
mysql>

Additional investigation shows that our RENAME TABLE statement cannot be used to rename temporary tables. The 6.0 manual says (see
http://dev.mysql.com/doc/refman/6.0/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;
---

I.e. in the above situation RENAME TABLE is supposed to rename non-temporary table and not temporary table as you have expected (and this is what actually happens if you remove foreign key definitions from the example).

Therefore I think an error message that you get in is the appropriate error message for this situation.

I am closing this report as "Not a Bug".

Please feel free to re-open it if you disagree with my analysis!