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)
• Status: Can't repeat
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.15
• OS: Any
• CPU Architecture: Any

[7 Dec 2017 9:15] zhang simon

Description:
The path is wrong in SYS_DATAFILES  when rename a innodb table to a different database

How to repeat:
================ test
create database zy;
create table test.t1(c1 int) engine=innodb;
select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%';
select name from information_schema.INNODB_SYS_TABLES where name like '%t1%';
select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%';
--echo rename table;
rename table test.t1 to zy.t1;
select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%';
select name from information_schema.INNODB_SYS_TABLES where name like '%t1%';
select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%';

================ result
create database zy;
create table test.t1(c1 int) engine=innodb;
select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%';
name
test/t1
select name from information_schema.INNODB_SYS_TABLES where name like '%t1%';
name
test/t1
select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%';
SPACE	PATH
23	./test/t1.ibd
rename table;
rename table test.t1 to zy.t1;
select name from information_schema.INNODB_SYS_TABLESPACES where name like '%t1%';
name
zy/t1
select name from information_schema.INNODB_SYS_TABLES where name like '%t1%';
name
zy/t1
select * from information_schema.INNODB_SYS_DATAFILES where path like '%t1%';
SPACE	PATH
23	./test/t1.ibd

After rename table the path in information_schema.INNODB_SYS_DATAFILES should be ./zy/t1.ibd

Suggested fix:
row_rename_table_for_mysql
    =>  row_make_new_pathname(table, new_name) //  the first parameter should not use the old table.

[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 <[e-mail]>
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<[e-mail]>
    RB: 15751