Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1697

LP #1548597: InnoDB tablespace import fails when importing table w/ different data directory

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Jericho Rivera last update 24-06-2016 04:18:00

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

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: