Bug #88806 | The path is wrong when rename a innodb table to a different database | ||
---|---|---|---|
Submitted: | 7 Dec 2017 9:15 | Modified: | 8 Dec 2017 1:37 |
Reporter: | zhang simon (OCA) | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.7.15 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Dec 2017 9:15]
zhang simon
[7 Dec 2017 12:47]
MySQL Verification Team
Hello Zhang, Thank you for the report and test case. I'm not able to locate the bug which fixed this but this is no longer repeatable on 5.7.20. Could you please confirm at your end with 5.7.20? [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database zy; Query OK, 1 row affected (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> create table test.t1(c1 int) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%'; +---------+ | name | +---------+ | test/t1 | +---------+ 1 row in set (0.00 sec) mysql> select name from information_schema.INNODB_SYS_TABLES where name like '%t1%'; +---------+ | name | +---------+ | test/t1 | +---------+ 1 row in set (0.00 sec) mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%'; +-------+---------------+ | SPACE | PATH | +-------+---------------+ | 23 | ./test/t1.ibd | +-------+---------------+ 1 row in set (0.00 sec) mysql> rename table test.t1 to zy.t1; Query OK, 0 rows affected (0.00 sec) mysql> select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%'; +-------+ | name | +-------+ | zy/t1 | +-------+ 1 row in set (0.00 sec) mysql> select name from information_schema.INNODB_SYS_TABLES where name like '%t1%'; +-------+ | name | +-------+ | zy/t1 | +-------+ 1 row in set (0.00 sec) mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%'; +-------+-------------+ | SPACE | PATH | +-------+-------------+ | 23 | ./zy/t1.ibd | +-------+-------------+ 1 row in set (0.01 sec) Thanks, Umesh
[8 Dec 2017 1:37]
zhang simon
This bug have been fixed by this commit commit 45c933ac19c73a3e9c756a87ee1ba18ba1ac564c Author: Aakanksha Verma <aakanksha.verma@oracle.com> Date: Tue Mar 21 10:31:43 2017 +0530 Bug #25189192 ERRORS WHEN RESTARTING MYSQL AFTER RENAME TABLE. PROBLEM While renaming table innodb doesn't update the InnoDB Dictionary table INNODB_SYS_DATAFILES incase there is change in database while doing rename table. Hence on a restart the server log shows error that it couldnt find table with old path before rename which has actually been renamed. So the errors would only vanish if we update the system tablespace FIX Update the innodb dictionary table with new path in the case there is not a change in the table but the database holding the table as well. Reviewed-by: Jimmy Yang<Jimmy.Yang@oracle.com> RB: 15751