Bug #76142 | InnoDB tablespace import fails when importing table w/ different data directory | ||
---|---|---|---|
Submitted: | 4 Mar 2015 11:25 | Modified: | 6 Jan 2020 16:03 |
Reporter: | Aurimas Mikalauskas | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | MySQL 5.6, 5.6.25, 5.6.45, 5.7.27, 8.0.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | Contribution, import, innodb, Tablespace |
[4 Mar 2015 11:25]
Aurimas Mikalauskas
[4 Mar 2015 11:57]
MySQL Verification Team
Hello Aurimas, Thank you for the report. Thanks, Umesh
[4 Mar 2015 11:58]
MySQL Verification Team
// 5.6.25 (commit: 2a49a14c188208db1801a8941e94ff32cba1209a, build-date: 2015-03-02 09:57:16 +0100) [ushastry@ushastry]/export/umesh/mysql-5.6.25: bin/mysql -uroot -p -S /tmp/mysql_ushastry.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) .. mysql> use test Database changed mysql> CREATE TABLE `user` ( -> `username` varchar(20) NOT NULL, -> `name` varchar(255) NOT NULL, -> `password` varchar(255) NOT NULL, -> UNIQUE KEY `XPKadmin_user` (`username`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `user_copy` ( -> `username` varchar(20) NOT NULL, -> `name` varchar(255) NOT NULL, -> `password` varchar(255) NOT NULL, -> UNIQUE KEY `XPKadmin_user` (`username`) -> ) ENGINE=InnoDB DATA DIRECTORY = '/tmp/mysql' DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `user_copy` DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec) mysql> mysql> FLUSH TABLE `user` FOR EXPORT; Query OK, 0 rows affected (0.00 sec) mysql> \! cp /export/umesh/mysql-5.6.25/76142/test/user.cfg /tmp/mysql/test/user_copy.cfg mysql> \! cp /export/umesh/mysql-5.6.25/76142/test/user.ibd /tmp/mysql/test/user_copy.ibd mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `user_copy` IMPORT TABLESPACE; ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x6 and the meta-data file has 0x1) // extract from error log (hostname changed to ####) 2015-03-04 12:51:47 22724 [Note] InnoDB: Importing tablespace for table 'test/user' that was exported from host '####' 2015-03-04 12:51:47 22724 [Note] InnoDB: Discarding tablespace of table "test"."user_copy": Generic error
[20 Feb 2016 0:28]
Pura Vida
What showing below is a workaround for the problem: mysql> use test Database changed mysql> CREATE TABLE `user` ( -> `username` varchar(20) NOT NULL, -> `name` varchar(255) NOT NULL, -> `password` varchar(255) NOT NULL, -> UNIQUE KEY `XPKadmin_user` (`username`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `user_copy` ( -> `username` varchar(20) NOT NULL, -> `name` varchar(255) NOT NULL, -> `password` varchar(255) NOT NULL, -> UNIQUE KEY `XPKadmin_user` (`username`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE `user_copy` DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLE `user` FOR EXPORT; Query OK, 0 rows affected (0.00 sec) mysql> \! cp -ip /var/lib/mysql/test/user.cfg /var/lib/mysql/test/user_copy.cfg mysql> \! cp -ip /var/lib/mysql/test/user.ibd /root/mysqldir/test/user_copy.ibd mysql> \! ln -s /root/mysqldir/test/user_copy.ibd /var/lib/mysql/test/user_copy.ibd mysql> -- symlink is only for "IMPORT TABLESPACE" mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `user_copy` IMPORT TABLESPACE; Query OK, 0 rows affected (0.02 sec) mysql> \! ls -ls /root/mysqldir/test/user* /var/lib/mysql/test/user* 96 -rw-rw---- 1 mysql mysql 98304 Feb 20 00:17 /root/mysqldir/test/user_copy.ibd 4 -rw-rw---- 1 mysql mysql 464 Feb 20 00:14 /var/lib/mysql/test/user_copy.cfg 12 -rw-rw---- 1 mysql mysql 8636 Feb 20 00:14 /var/lib/mysql/test/user_copy.frm 0 lrwxrwxrwx 1 root root 33 Feb 20 00:15 /var/lib/mysql/test/user_copy.ibd -> /root/mysqldir/test/user_copy.ibd 12 -rw-rw---- 1 mysql mysql 8636 Feb 20 00:14 /var/lib/mysql/test/user.frm 96 -rw-rw---- 1 mysql mysql 98304 Feb 20 00:14 /var/lib/mysql/test/user.ibd mysql> -- and the symlink is still in place mysql> \! rm /var/lib/mysql/test/user_copy.ibd mysql> \! echo /root/mysqldir/test/user_copy.ibd > /var/lib/mysql/test/user_copy.isl mysql> \! chown mysql:mysql /var/lib/mysql/test/user_copy.isl mysql> \! rm /var/lib/mysql/test/user_copy.cfg mysql> -- final file check mysql> \! ls -ls /root/mysqldir/test/user* /var/lib/mysql/test/user* 96 -rw-rw---- 1 mysql mysql 98304 Feb 20 00:17 /root/mysqldir/test/user_copy.ibd 12 -rw-rw---- 1 mysql mysql 8636 Feb 20 00:14 /var/lib/mysql/test/user_copy.frm 4 -rw-r--r-- 1 mysql mysql 34 Feb 20 00:19 /var/lib/mysql/test/user_copy.isl 12 -rw-rw---- 1 mysql mysql 8636 Feb 20 00:14 /var/lib/mysql/test/user.frm 96 -rw-rw---- 1 mysql mysql 98304 Feb 20 00:14 /var/lib/mysql/test/user.ibd mysql> -- verify mysql> show tables like 'user%'; +------------------------+ | Tables_in_test (user%) | +------------------------+ | user | | user_copy | +------------------------+ 2 rows in set (0.00 sec) mysql> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `username` varchar(20) NOT NULL, `name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, UNIQUE KEY `XPKadmin_user` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table user_copy\G *************************** 1. row *************************** Table: user_copy Create Table: CREATE TABLE `user_copy` ( `username` varchar(20) NOT NULL, `name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, UNIQUE KEY `XPKadmin_user` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from user_copy; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
[29 Jun 2016 11:14]
Laurynas Biveinis
Bug 76142 fix for 5.6 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-5.6.patch (application/octet-stream, text), 8.26 KiB.
[29 Jun 2016 11:15]
Laurynas Biveinis
Bug 76142 fix for 5.7 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-5.7.patch (application/octet-stream, text), 8.83 KiB.
[8 Aug 2016 3:57]
Laurynas Biveinis
These also fix bug 82480
[2 Jun 2017 2:00]
Laurynas Biveinis
Bug 76142 and 82480 fix for 8.0.1 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-82480-8.0.1.patch (application/octet-stream, text), 9.82 KiB.
[24 Jul 2017 4:25]
Laurynas Biveinis
The contributed fix fails to remove $MYSQL_TMP_DIR/test directory at its end, breaking e.g. innodb.create_tablespace on the same MTR worker afterwards. A 8.0.2 refresh will fix this.
[5 Aug 2017 7:17]
Laurynas Biveinis
Bug 76142 fix for 8.0.2 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-8.0.2.patch (application/octet-stream, text), 9.87 KiB.
[2 Feb 2018 5:27]
Laurynas Biveinis
Bug 76142 and 82480 fix for 8.0.4 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-82480-8.0.4.patch (application/octet-stream, text), 9.87 KiB.
[13 Jun 2018 12:26]
Laurynas Biveinis
Bug 76142 and 82480 fix for 8.0.11 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug76142-8.0.11.patch (application/octet-stream, text), 10.15 KiB.
[14 Jun 2018 5:07]
MySQL Verification Team
Thank you for the contributions! Regards, Umesh
[17 Nov 2018 3:54]
Derek Perkins
I'm confused why the submitted patches haven't been accepted after so long. I'm still dealing with this on 8.0.13.
[6 Jan 2020 16:03]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.6.48, 5.7.29, 8.0.19 release, and here's the changelog entry: "A tablespace import operation that failed due to the source and destination tables being defined with different DATA DIRECTORY clauses reported an insufficiently descriptive schema mismatch error. Moreover, if a .cfg file was not present, the same operation would raise an assertion failure. A more informative error message is now reported in both cases before the import operation is terminated due to the data directory mismatch." The "Importing InnoDB Tables" prerequisites documentation has been updated to indicate that source and destination DATA DIRECTORY clauses must match. https://dev.mysql.com/doc/refman/5.6/en/innodb-table-import.html#innodb-table-import-prere... Thank you for the bug report.
[7 Dec 2023 7:16]
Saroj Mahanta
I am trying to restore/import database from the backups. I have .ibd and .frm files. And also the .sql file with all the table creation script inside it. Method followed : A. Installed a new mysql database. B. Created a database with same name as it was there in the source database. C. From the .sql file i picked one table creation script, run it in the new mysql database. D. alter table <tablename> discard tablespace; E. went to the server and copied the tablename.ibd from the backedup directory to the current mysql data directory , ie /var/lib/mysql/confluence/ F. chown mysql:mysql <tablename>.ibd G. Went to the mysql prompt and run Alter table <tablename> import tablespace. H. I get and error, message as below. ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.) i manage to restore some of the ibd files, but some parent tables with more number of columns, unable to restore them. Kindly advise.