Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.4.18, 10.5.9
-
None
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
- duplicates
-
MDEV-18543 IMPORT TABLESPACE fails after instant DROP COLUMN
- Closed
- relates to
-
MDEV-27272 Crash on EXPORT/IMPORT tablespace with column added in the middle
- Closed