############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", 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 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, 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 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, 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 8 0 3 2 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, 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 7 0 4 2 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c3 3 13 16711934 1 1 0 !hidden!_dropped_v4_p4_c2 7 13 16711934 0 0 4 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v4_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, 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 5 2 3 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 6 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 7 13 16711934 0 1 5 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; !hidden!_dropped_v4_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, 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 5 0 6 2 2 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 5 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 6 13 16711934 0 1 5 !hidden!_dropped_v6_p6_c0 7 13 16711934 0 2 6 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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_v4_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 7 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; !hidden!_dropped_v6_p6_c0 8 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_added=2;version_dropped=6; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 DROP TABLE t1; # ------------------------------------------------------------ # ONLINE DDL on table having INSTANT ADD/DROP Column # ------------------------------------------------------------ # --------------------------- # ONLINE DDL 1 : NO PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # --------------------------- # ONLINE DDL 2 : PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 ADD PRIMARY KEY (c2), ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # ----------------------- # ONLINE DDL 3 : # ----------------------- create table t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; Insert into t1 values ("r1c1", "r1c2"); Select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "c3_def", algorithm=instant; Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def Insert into t1 values ("r2c1", "r2c2", "r2c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Alter table t1 add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c1", "r3c2", "r3c3"); Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 r3c4 r3c1 r3c2 r3c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t1 SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 r3c4 c1_upd r3c2 r3c3 ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", 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 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, 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 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, 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 8 0 3 2 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, 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 7 0 4 2 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c3 3 13 16711934 1 1 0 !hidden!_dropped_v4_p4_c2 7 13 16711934 0 0 4 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v4_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, 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 5 2 3 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 6 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 7 13 16711934 0 1 5 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; !hidden!_dropped_v4_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, 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 5 0 6 2 2 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 5 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 6 13 16711934 0 1 5 !hidden!_dropped_v6_p6_c0 7 13 16711934 0 2 6 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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_v4_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 7 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; !hidden!_dropped_v6_p6_c0 8 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_added=2;version_dropped=6; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 DROP TABLE t1; # ------------------------------------------------------------ # ONLINE DDL on table having INSTANT ADD/DROP Column # ------------------------------------------------------------ # --------------------------- # ONLINE DDL 1 : NO PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # --------------------------- # ONLINE DDL 2 : PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 ADD PRIMARY KEY (c2), ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # ----------------------- # ONLINE DDL 3 : # ----------------------- create table t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; Insert into t1 values ("r1c1", "r1c2"); Select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "c3_def", algorithm=instant; Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def Insert into t1 values ("r2c1", "r2c2", "r2c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Alter table t1 add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c1", "r3c2", "r3c3"); Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 r3c4 r3c1 r3c2 r3c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t1 SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 r3c4 c1_upd r3c2 r3c3 ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=COMPACT; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", 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 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, 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 7 0 2 2 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, 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 8 0 3 2 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, 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 7 0 4 2 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 c3 3 13 16711934 1 1 0 !hidden!_dropped_v4_p4_c2 7 13 16711934 0 0 4 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v4_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, 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 5 2 3 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c0 0 13 16711934 1 2 0 c1 1 13 16711934 0 0 0 c4 2 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 6 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 7 13 16711934 0 1 5 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c0 1 MYSQL_TYPE_STRING 0 Visible default=63305f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=2; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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; !hidden!_dropped_v4_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, 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 5 0 6 2 2 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 1 3 0 !hidden!_dropped_v4_p4_c2 5 13 16711934 0 0 4 !hidden!_dropped_v5_p5_c3 6 13 16711934 0 1 5 !hidden!_dropped_v6_p6_c0 7 13 16711934 0 2 6 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=3; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; 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_v4_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=4; !hidden!_dropped_v5_p5_c3 7 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_added=1;version_dropped=5; !hidden!_dropped_v6_p6_c0 8 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_added=2;version_dropped=6; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c4 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 DROP TABLE t1; # ------------------------------------------------------------ # ONLINE DDL on table having INSTANT ADD/DROP Column # ------------------------------------------------------------ # --------------------------- # ONLINE DDL 1 : NO PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # --------------------------- # ONLINE DDL 2 : PK CHANGE # --------------------------- CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2"); SELECT * FROM t1; c0 c1 c2 def_c0 r1c1 r1c2 r2c0 r2c1 r2c2 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r3c0", "r3c2"); SELECT * FROM t1; c0 c2 def_c0 r1c2 r2c0 r2c2 r3c0 r3c2 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 ADD PRIMARY KEY (c2), ALGORITHM=INPLACE; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Insert query INSERT INTO t1 VALUES ("r4c0", "r4c2"); # Update query # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3) UPDATE t1 SET c0="c0_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c0 c2 c0_upd r1c2 c0_upd r2c2 c0_upd r3c2 c0_upd r4c2 DROP TABLE t1; # ----------------------- # ONLINE DDL 3 : # ----------------------- create table t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; Insert into t1 values ("r1c1", "r1c2"); Select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "c3_def", algorithm=instant; Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def Insert into t1 values ("r2c1", "r2c2", "r2c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Alter table t1 add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c1", "r3c2", "r3c3"); Select * from t1; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 r3c4 r3c1 r3c2 r3c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t1 force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t1 SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t1; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 r3c4 c1_upd r3c2 r3c3 ########### # CLEANUP # ########### DROP TABLE t1;