Bug #44364 The error 1017 while renaming the table

• Submitted: 20 Apr 2009 13:22
• Modified: 20 Apr 2009 14:19
• Reporter: pradeep somani
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.41-community-nt
• OS: Any
• CPU Architecture: Any
• Tags: 1017, rename table, temporary table

[20 Apr 2009 13:22] pradeep somani

Description:
Faced ERROR 1017 (HY000), while trying to rename the temporary table

Please find the details below

mysql> create temporary table e1(id int, fname char(30), lname char(30));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into e1(id,fname,lname) values (1,'bill','gates');
Query OK, 1 row affected (0.03 sec)

mysql> rename table e1 to e2;
ERROR 1017 (HY000): Can't find file: '.\mydb1\e1.frm' (errno: 2)

How to repeat:
1) create temporary table
mysql> create temporary table e1(id int, fname char(30), lname char(30));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into e1(id,fname,lname) values (1,'bill','gates');
Query OK, 1 row affected (0.03 sec)

2) Try to rename the table using rename 
mysql> rename table e1 to e2;
ERROR 1017 (HY000): Can't find file: '.\mydb1\e1.frm' (errno: 2)

[20 Apr 2009 13:32] pradeep somani

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

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

Please remove this bug listing

[22 Dec 2009 6:49] Sveta Smirnova

See also bug #1154

[27 Apr 2011 0:57] Jian Wu

I created a regular table by importing an existing mysql data file (source data.sql) but still have this error when I was trying to rename the table.