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:

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

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: