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

IMPORT TABLESPACE not possible if source table had column moved in INSTANT mode

    XMLWordPrintable

Details

    Description

      Assume that we have created a table with a certain column order:

       CREATE TABLE t1 (id INT PRIMARY KEY, i2 INT, i1 INT) ENGINE=INNODB; 

      but later decide to change that order, and take advantage of this being possible as an instant operation in MariaDB 10.4 and later:

       ALTER TABLE t1 MODIFY COLUMN i2 INT AFTER i1, ALGORITHM=INSTANT; 

      Now lets assume we want to create a copy of this table, and use tablespace import for that. Let's start by create the new copy as a copy of the old table schema, and name it t2:

       CREATE TABLE t2 LIKE t1; 

      Now to get the data over, too, lets start by discarding the new tables tablespace right away:

       ALTER TABLE t2 DISCARD TABLESPACE; 

      and flush the original table for export:

       FLUSH TABLE t1 FOR EXPORT; 

      Then copy over the .ibd and .cfg files:

      cd $datadir/db_dir
      cp t1.ibd t2.ibd
      cp t1.cfg t2.cfg
      chown mysql:mysql t2.*
      

      then unlock t1 and try to import the data into t2:

      UNLOCK TABLES;
      ALTER TABLE t2 IMPORT TABLESPACE;
      

      This should succeed, as both tables look the same on the SQL level, but fails with:

      ERROR 1808 (HY000): Schema mismatch (Index field name i1 doesn't match tablespace metadata field name i2 for field position 3)
      

      Attachments

        Issue Links

          Activity

            People

              kevg Eugene Kosov (Inactive)
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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