Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15225

Can't import .ibd file with temporal type format differing from mysql56_temporal_format

    XMLWordPrintable

    Details

      Description

      Let's say that we have two servers. One of the servers has a table with a temporal type that was created with MySQL 5.5 or earlier, and the other is a MariaDB 10.1 server that has mysql56_temporal_format=ON (the default). If we want to import a .ibd file for a table with temporal types from the first server into the second server, it seems difficult to do in a safe manner without setting mysql56_temporal_format=OFF and restarting mysqld.

      We can simulate this problem by doing the following:

      • Set mysql56_temporal_format=OFF and restart mysqld.
      • Create a table with a temporal type:

      USE db1;
       
      CREATE TABLE dt_test (
         id int AUTO_INCREMENT PRIMARY KEY,
         dt datetime
      );
       
      INSERT INTO dt_test VALUES (1, NOW());
      

      • Set mysql56_temporal_format=ON (the default) and restart mysqld.
      • Create a second copy of the table:

      USE db2;
       
      CREATE TABLE dt_test (
         id int AUTO_INCREMENT PRIMARY KEY,
         dt datetime
      );
      

      • Discard the tablespace of the new copy:

      ALTER TABLE db2.dt_test DISCARD TABLESPACE;
      

      • Flush the tablespace of the old copy for export:

      FLUSH TABLES db1.dt_test FOR EXPORT;
      

      • Copy the files to the new database:

      sudo cp /var/lib/mysql/db1/dt_test.ibd /var/lib/mysql/db2/
      sudo cp /var/lib/mysql/db1/dt_test.cfg /var/lib/mysql/db2/
      sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.ibd
      sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.cfg
      

      • Attempt to import the tablespace:

      CREATE TABLE db2.dt_test …;
      ALTER TABLE db2.dt_test DISCARD TABLESPACE;
      ALTER TABLE db2.dt_test IMPORT TABLESPACE;
      

      This will fail:

      MariaDB [db2]> ALTER TABLE dt_test IMPORT TABLESPACE;
      ERROR 1808 (HY000): Schema mismatch (Column dt precise type mismatch.)
      

      Update: Can we please support the following simplified workflow:

      sudo cp /var/lib/mysql/db1/dt_test.frm /var/lib/mysql/db2/
      sudo cp /var/lib/mysql/db1/dt_test.ibd /var/lib/mysql/db2/
      sudo cp /var/lib/mysql/db1/dt_test.cfg /var/lib/mysql/db2/
      sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.frm
      sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.ibd
      sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.cfg
      

      and simply

      ALTER TABLE db2.dt_test IMPORT TABLESPACE;
      

      to have InnoDB automatically create its table definition based on the .frm and optionally .cfg files, with no need to execute CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE? That would create all temporal-type columns exactly as is.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              kevg Eugene Kosov
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              3 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated: