enterprise-support team mailing list archive

[Jericho Rivera <jericho.rivera@xxxxxxxxxxx>]

Public bug reported:

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).

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: Confirmed


** Tags: i64944

** Bug watch added: MySQL Bug System #76142
   http://bugs.mysql.com/bug.php?id=76142

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=76142
   Importance: Unknown
       Status: Unknown

** Changed in: percona-server
       Status: New => Confirmed

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1548597

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

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1548597/+subscriptions