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

Cannot import into 10.6.5 a table exported from 10.3.14 due to precise type mismatch on varchar

    XMLWordPrintable

    Details

      Description

      In brief, trying to import a simple InnoDB test table created on a 10.3.14 server into a 10.6.5 server fails with:

      ERROR 1808 (HY000): Schema mismatch (Column the_data precise type mismatch, it's 0X2D000F in the table and 0X8000F in the tablespace meta file)
      

      I followed the instructions here:

      https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces-for-non-partitioned-tables

      Specific statements follow. On the 10.3.14 server:

      SELECT @@GLOBAL.innodb_default_row_format; /* dynamic */
      DROP DATABASE IF EXISTS MyDB;
      CREATE DATABASE MyDB;
      CREATE TABLE MyDB.t1 ( the_key INT PRIMARY KEY AUTO_INCREMENT, the_data VARCHAR(10) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
      INSERT INTO MyDB.t1 (the_data) VALUES ('here'),('there'),('everywhere');
      FLUSH TABLES MyDB.t1 FOR EXPORT;
      system cp -p /<datadir>/MyDB/t1.cfg /tests/innodb_import/.
      system cp -p /<datadir>/MyDB/t1.ibd /tests/innodb_import/.
      UNLOCK TABLES;
      

      And then, after copying the t1.cfg and t1.ibd files over to the target 10.6.5 server (also into the /tests/innodb_import folder, with appropriate ownership and permissions):

      SELECT @@GLOBAL.innodb_default_row_format; /* dynamic */
      DROP DATABASE IF EXISTS MyDB;
      CREATE DATABASE MyDB;
      CREATE TABLE MyDB.t1 ( the_key INT PRIMARY KEY AUTO_INCREMENT, the_data VARCHAR(10) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
      ALTER TABLE MyDB.t1 DISCARD TABLESPACE;
      system cp -p /tests/innodb_import/t1.ibd /<datadir>/MyDB/t1.ibd
      system cp -p /tests/innodb_import/t1.cfg /<datadir>/MyDB/t1.cfg
      ALTER TABLE MyDB.t1 IMPORT TABLESPACE;
      ERROR 1808 (HY000): Schema mismatch (Column the_data precise type mismatch, it's 0X2D000F in the table and 0X8000F in the tablespace meta file)
      

      The error comes when trying to import the table. I also tried it without the .cfg (only copying the .ibd to the target directory and not the .cfg) and with that I get a different error:

      ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x21.)
      

      And I also tried the whole thing shutting down the source server (with innodb_fast_shutdown = 0) before copying, with the same results (in that case I only copied the .ibd file).

      I read with interest MDEV-15225, however it seems mostly focused on temporal types which I'm not using here. It does suggest, however, that there may be data types that cause problems between versions other than just temporal, and mentions a three-file (with the .frm) import as a possible general solution to the issue (MDEV-26137) and the ongoing work to streamline the import process (MDEV-11658).

      But in all that, I don't see a work-around for my specific issue. Is there one? Or am I going about it wrong?

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            dragle Daniel Ragle
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.