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:
None 
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
Description:
It's impossible to import a tablespace if table definition has a different data directory. For example, having data exported with the following table definition:

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

won't import into a table that has this table definition:

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 DATA DIRECTORY='/alternate/location' DEFAULT CHARSET=latin1

Tablespace import will fail with the following or similar error:

mysql> alter table `user` import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x6 and the meta-data file has 0x1)

MySQL error log shows this error:

2015-03-04 13:22:27 600 [Note] InnoDB: Importing tablespace for table 'test/user' that was exported from host 'Hostname'
2015-03-04 13:22:27 600 [Note] InnoDB: Discarding tablespace of table "test"."user_copy": Generic error

How to repeat:
USE test

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;

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;

ALTER TABLE `user_copy` DISCARD TABLESPACE;

FLUSH TABLE `user` FOR EXPORT;

\! cp <path>/test/user.cfg /tmp/mysql/test/user_copy.cfg
\! cp <path>/test/user.ibd /tmp/mysql/test/user_copy.ibd

UNLOCK TABLES;

ALTER TABLE `user_copy` IMPORT TABLESPACE;

Suggested fix:
Ignore certain table flags for the table import, such as this one preventing the import when different data directory was used.
[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.