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

    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:
                9 Start watching this issue

                Dates

                • Created:
                  Updated: