Bug #87356 rename table that created with data directory to another database would be wrong
Submitted: 9 Aug 2017 10:45 Modified: 9 Aug 2017 12:32
Reporter: ohm hong Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[9 Aug 2017 10:45] ohm hong
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.
[9 Aug 2017 12:32] MySQL Verification Team
Hello hong!

Thank you for the report.

Thanks,
Umesh