Bug #76142 InnoDB tablespace import fails when importing table w/ different data directory
Submitted: 4 Mar 2015 11:25 Modified: 4 Mar 2015 11:57
Reporter: Aurimas Mikalauskas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:MySQL 5.6, 5.6.25 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] Umesh Shastry
Hello Aurimas,

Thank you for the report.

Thanks,
Umesh
[4 Mar 2015 11:58] Umesh Shastry
// 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] Umesh Shastry
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.