############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## # Create a table with 2 columns. [c1, c2] CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "a"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 a # Add a new column at the end. [c1, c2, +c3] ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "b", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 a default_c3 row2_c1 b row2_c3 # Add a new column in between. [c1, +c4, c2, c3] ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c2` enum('a','b','c') DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; DROP TABLE t1; # ------------------------------------------------------------ # Scenarios 3 : Error scenarios # ------------------------------------------------------------ # ------------------------------------------------------------ # Scenario 3.1 : Target table has INSTANT, source doesn't # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=REDUNDANT;; ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.2 : Both has INSTANT, but different instant version # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.3 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.4 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 row2_c4 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.5 : Both has INSTANT, but different default value # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;; ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!) DROP TABLE t1; ########### # CLEANUP # ########### ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ # Create a table with 2 columns. [c1, c2] CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "a"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 a # Add a new column at the end. [c1, c2, +c3] ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "b", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 a default_c3 row2_c1 b row2_c3 # Add a new column in between. [c1, +c4, c2, c3] ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c2` enum('a','b','c') DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; DROP TABLE t1; # ------------------------------------------------------------ # Scenarios 3 : Error scenarios # ------------------------------------------------------------ # ------------------------------------------------------------ # Scenario 3.1 : Target table has INSTANT, source doesn't # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=DYNAMIC;; ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.2 : Both has INSTANT, but different instant version # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.3 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.4 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 row2_c4 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.5 : Both has INSTANT, but different default value # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;; ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!) DROP TABLE t1; ########### # CLEANUP # ########### ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # Create a table with 2 columns. [c1, c2] CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "a"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 a # Add a new column at the end. [c1, c2, +c3] ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "b", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 a default_c3 row2_c1 b row2_c3 # Add a new column in between. [c1, +c4, c2, c3] ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c2` enum('a','b','c') DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL a default_c3 row2_c1 NULL b row2_c3 row3_c1 row3_c4 c row3_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c2 2 6 766 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; CREATE TABLE t2 like t1; DROP TABLE t1; ALTER TABLE t2 RENAME to t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) NOT NULL, `c4` char(20) DEFAULT NULL, `c3` char(20) DEFAULT 'default_c3', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; c1 c4 c3 row1_c1 NULL default_c3 row2_c1 NULL row2_c3 row3_c1 row3_c4 row3_c3 row4_c1 row4_c4 row4_c3 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 2 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c4 1 13 16711934 1 2 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3 # DD Metadata of table NAME SE_PRIVATE_DATA t1 discard=0; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2; c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3; DROP TABLE t1; # ------------------------------------------------------------ # Scenarios 3 : Error scenarios # ------------------------------------------------------------ # ------------------------------------------------------------ # Scenario 3.1 : Target table has INSTANT, source doesn't # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=COMPACT;; ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.2 : Both has INSTANT, but different instant version # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.3 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.4 : Both has INSTANT, but different col count # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 row2_c4 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;; ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.) DROP TABLE t1; # ------------------------------------------------------------ # Scenario 3.5 : Both has INSTANT, but different default value # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; INSERT INTO t1 values ("row1_c1", "row1_c2"); SELECT * FROM t1 ORDER BY c1; c1 c2 row1_c1 row1_c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 default_c3 row2_c1 row2_c2 row2_c3 # EXPORT FLUSH TABLE t1 FOR EXPORT; # Copy cfg/ibd files into temp location UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;; ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT; # IMPORT ALTER TABLE t1 DISCARD TABLESPACE; # Copy cfg/ibd files from temp location ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!) DROP TABLE t1; ########### # CLEANUP # ###########