InnoDB tablespace import fails when importing table w/ different data directory

Bug #1548597 reported by Jericho Rivera
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Fix Released
Medium
Laurynas Biveinis
5.7
Fix Released
Medium
Laurynas Biveinis

Bug Description

Original bug filed by Aurimas in upstream bug tracker.

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.

Additional request to also provide a fix for partitioned tables (moving a partition from fast storage to slower
storage).

Changed in percona-server:
status: New → Confirmed
tags: added: innodb upstream
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1697

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.