RENAME TABLE fails with "Table ... already exists" error with not existent table

[Sveta Smirnova]

Trying to verify bug #1597497 found this one.

Description:
RENAME TABLE fails with "Table ... already exists" error with not existent table

How to repeat:
show tables;
Tables_in_test
XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K
_product_instance_new
customer_profile
organisation
product_specification

mysqltest: At line 152: query 'RENAME TABLE `_product_instance_new` TO `product_instance`' failed: 1050: Table './test/product_instance' already exists

Test case will be attached.

[markus_albe (markus-albe)]

For what it's worth, I reproduced (with PS 5.6.27) using MTR queries

...snip...
msandbox@localhost (empty) > SHOW TABLES;
+------------------------------------------------------------------+
| Tables_in_empty |
+------------------------------------------------------------------+
| XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K |
| _product_instance_new |
| customer_profile |
| organisation |
| product_specification |
+------------------------------------------------------------------+
5 rows in set (0.00 sec)

msandbox@localhost (empty) > SYSTEM ls -lha data/empty/;
total 828K
drwx------ 2 marcos.albe percona 4.0K Jul 20 18:54 .
drwx------ 8 marcos.albe percona 4.0K Jul 20 18:54 ..
-rw-rw---- 1 marcos.albe percona 9.9K Jul 20 18:54 customer_profile.frm
-rw-rw---- 1 marcos.albe percona 176K Jul 20 18:54 customer_profile.ibd
-rw-rw---- 1 marcos.albe percona 61 Jul 20 18:54 db.opt
-rw-rw---- 1 marcos.albe percona 9.3K Jul 20 18:54 organisation.frm
-rw-rw---- 1 marcos.albe percona 112K Jul 20 18:54 organisation.ibd
-rw-rw---- 1 marcos.albe percona 9.4K Jul 20 18:54 _product_instance_new.frm
-rw-rw---- 1 marcos.albe percona 192K Jul 20 18:54 _product_instance_new.ibd
-rw-rw---- 1 marcos.albe percona 13K Jul 20 18:54 product_specification.frm
-rw-rw---- 1 marcos.albe percona 96K Jul 20 18:54 product_specification.ibd
-rw-rw---- 1 marcos.albe percona 9.3K Jul 20 18:54 XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K.frm
-rw-rw---- 1 marcos.albe percona 176K Jul 20 18:54 XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K.ibd
RDS: msandbox@localhost (empty) > SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%product_instance%' OR name LIKE '%XK4P3A%';
+----------+------------------------------------------------------------------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+------------------------------------------------------------------------+------+--------+-------+-------------+------------+---------------+
| 228 | empty/XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K | 1 | 20 | 200 | Antelope | Compact | 0 |
| 225 | empty/_product_instance_new | 1 | 21 | 197 | Antelope | Compact | 0 |
| 200 | test/_product_instance_new | 1 | 21 | 172 | Antelope | Compact | 0 |
+----------+------------------------------------------------------------------------+------+--------+-------+-------------+------------+---------------+
3 rows in set (0.00 sec)

RDS: msandbox@localhost (empty) > RENAME TABLE `_product_instance_new` TO `product_instance`;
ERROR 1050 (42S01): Table './em...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1729