# Create a table with 4 columns. [c1, c2, c3, c4] CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=redundant PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); SELECT * from t1; c1 c2 c3 c4 1 2 3 abcdefg 100 200 300 qwerty 200 300 400 asdfg # Add a new column at the end. [c1, c2, c3, c4, +c5] ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 c5 1 2 3 abcdefg c5_def 100 200 300 qwerty c5_def 200 300 400 asdfg c5_def 300 400 500 xxxxxx r4c5 # Add a new column in between. [c1, +c6, c2, c3, c4, c5] ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 DROP TABLE t1; # ------------------------------------------------------------ # A Partitioned table having multiple INSTANT ADD/DROP columns # and REORGNIZED partitions. # ------------------------------------------------------------ CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=redundant PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111); INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222); INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333); INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50)); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # ------------------------------------------------------------ # Scenario 3.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # ------------------------------------------------------------ # Scenario 3.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int NOT NULL, `name` varchar(50) DEFAULT NULL, `job` varchar(50) DEFAULT NULL, `dept` int DEFAULT NULL, `sal` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT /*!50100 PARTITION BY RANGE (`id`) (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 DROP TABLE t1; ########### # CLEANUP # ########### # Create a table with 4 columns. [c1, c2, c3, c4] CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=dynamic PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); SELECT * from t1; c1 c2 c3 c4 1 2 3 abcdefg 100 200 300 qwerty 200 300 400 asdfg # Add a new column at the end. [c1, c2, c3, c4, +c5] ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 c5 1 2 3 abcdefg c5_def 100 200 300 qwerty c5_def 200 300 400 asdfg c5_def 300 400 500 xxxxxx r4c5 # Add a new column in between. [c1, +c6, c2, c3, c4, c5] ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 DROP TABLE t1; # ------------------------------------------------------------ # A Partitioned table having multiple INSTANT ADD/DROP columns # and REORGNIZED partitions. # ------------------------------------------------------------ CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=dynamic PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111); INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222); INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333); INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50)); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # ------------------------------------------------------------ # Scenario 3.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # ------------------------------------------------------------ # Scenario 3.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int NOT NULL, `name` varchar(50) DEFAULT NULL, `job` varchar(50) DEFAULT NULL, `dept` int DEFAULT NULL, `sal` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY RANGE (`id`) (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 DROP TABLE t1; ########### # CLEANUP # ########### ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # Create a table with 4 columns. [c1, c2, c3, c4] CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=compact PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); SELECT * from t1; c1 c2 c3 c4 1 2 3 abcdefg 100 200 300 qwerty 200 300 400 asdfg # Add a new column at the end. [c1, c2, c3, c4, +c5] ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 c5 1 2 3 abcdefg c5_def 100 200 300 qwerty c5_def 200 300 400 asdfg c5_def 300 400 500 xxxxxx r4c5 # Add a new column in between. [c1, +c6, c2, c3, c4, c5] ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Scenario 1.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c2 c3 c4 c5 1 NULL 2 3 abcdefg c5_def 100 NULL 200 300 qwerty c5_def 200 NULL 300 400 asdfg c5_def 300 NULL 400 500 xxxxxx r4c5 400 r5c6 500 600 xxxxxx r5c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 9 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c2 2 6 1027 0 c3 3 6 1027 0 c4 4 5 16711932 0 c5 5 13 16711934 1 # ------------------------------------------------------------ # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # ------------------------------------------------------------ # Scenario 2.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # ------------------------------------------------------------ # Scenario 2.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int DEFAULT NULL, `c6` char(20) DEFAULT NULL, `c3` int DEFAULT NULL, `c4` text, `c5` char(20) DEFAULT 'c5_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT /*!50100 PARTITION BY RANGE ((`c1` * 2)) (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY c1; c1 c6 c3 c4 c5 1 NULL 3 abcdefg c5_def 100 NULL 300 qwerty c5_def 200 NULL 400 asdfg c5_def 300 NULL 500 xxxxxx r4c5 400 r5c6 600 xxxxxx r5c5 500 r6c6 700 xxxxxx r6c5 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c6 1 13 16711934 1 c3 2 6 1027 0 c4 3 5 16711932 0 c5 4 13 16711934 1 DROP TABLE t1; # ------------------------------------------------------------ # A Partitioned table having multiple INSTANT ADD/DROP columns # and REORGNIZED partitions. # ------------------------------------------------------------ CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=compact PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111); INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222); INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333); INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50)); ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # ------------------------------------------------------------ # Scenario 3.1 : Same table with tablespace discarded # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # ------------------------------------------------------------ # Scenario 3.2 : New table created with like # ------------------------------------------------------------ # EXPORT FLUSH TABLE t1 FOR EXPORT; backup: t1#p#p0 backup: t1#p#p1 backup: t1#p#p2 backup: t1#p#p3 UNLOCK TABLES; CREATE TABLE t2 like t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int NOT NULL, `name` varchar(50) DEFAULT NULL, `job` varchar(50) DEFAULT NULL, `dept` int DEFAULT NULL, `sal` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT /*!50100 PARTITION BY RANGE (`id`) (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */ DROP TABLE t1; ALTER TABLE t2 RENAME as t1; # IMPORT ALTER TABLE t1 discard tablespace; restore: t1#p#p0 .ibd and .cfg files restore: t1#p#p1 .ibd and .cfg files restore: t1#p#p2 .ibd and .cfg files restore: t1#p#p3 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1 ORDER BY id; id name job dept sal 10 aaa xyz1 1 NULL 20 bbb xyz2 2 NULL 30 ccc xyz3 3 NULL 40 ddd xyz4 4 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p0 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p1 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p2 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1#p#p3 8 0 22 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT id 0 6 1283 0 name 1 12 16711695 0 job 2 12 16711695 0 dept 3 6 1027 0 sal 4 6 1027 1 DROP TABLE t1; ########### # CLEANUP # ###########