Description:
MySQL5.7.19 fixed Bug #25189192 ERRORS WHEN RESTARTING MYSQL AFTER RENAME TABLE.
But it fixed incompletely. If the table creates with data directory then renames the table to other database, the result is wrong.
How to repeat:
CASE 1: rename table to other database, and the table name is same
mysql> CREATE DATABASE test_jfg;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE test_jfg2;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE test_jfg.test (a int unsigned NOT NULL PRIMARY KEY) DATA DIRECTORY='/home/ohmhong/alt_dir';
Query OK, 0 rows affected (0.03 sec)
mysql> RENAME TABLE test_jfg.test TO test_jfg2.test;
ERROR 1025 (HY000): Error on rename of './test_jfg/test' to './test_jfg2/test' (errno: 184 - Tablespace already exists)
It shouldn't return error.
CASE 2: rename table to other database, and the table name is not same
mysql> RENAME TABLE test_jfg.test TO test_jfg2.test2;
Query OK, 0 rows affected (0.11 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE "%test%";
+-------+-----------------------+
| SPACE | PATH |
+-------+-----------------------+
| 80 | ./test_jfg2/test2.ibd |
+-------+-----------------------+
3 rows in set (0.05 sec)
## then restart, the log shows:
2017-08-09T10:34:22.348040Z 0 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 80 was updated to use file /home/ohmhong/alt_dir/test_jfg/test2.ibd.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE "%test%";
+-------+----------------------------------------------+
| SPACE | PATH |
+-------+----------------------------------------------+
| 80 | /home/ohmhong/alt_dir/test_jfg/test2.ibd |
+-------+----------------------------------------------+
the path is also wrong.
