InnoDB tablespace import fails when importing table w/ different data directory
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=
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>/
\! cp <path>/
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 |
https:/ /github. com/percona/ percona- server/ pull/615, https:/ /github. com/percona/ percona- server/ pull/616