######### # SETUP # ######### # Stop the running the server # Copy and unzip the datadir. # Restart the server against the unzipped datadir # restart: --datadir=DATADIR --innodb-page-size=16k ############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## # ------------------------------------------------------------ # Start server with old datadir with # table t1_redundant having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # c4 INSTANT ADD column with default def_c4. # +------+------+--------+--------+ # | c1 | c2 | c3 | c4 | # +------+------+--------+--------+ # | r1c1 | r1c2 | c3_def | c4_def | # | r2c1 | r2c2 | r2c3 | c4_def | # | r3c1 | r3c2 | r3c3 | r3c4 | # +------+------+--------+--------+ # # table t2_redundant having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # +------+------+--------+ # | c1 | c2 | c3 | # +------+------+--------+ # | r1c1 | r1c2 | c3_def | # | r2c1 | r2c2 | r2c3 | # +------+------+--------+ # ------------------------------------------------------------ SHOW CREATE TABLE t1_redundant; Table Create Table t1_redundant CREATE TABLE `t1_redundant` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def', `c4` char(10) DEFAULT 'c4_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT # ------------------------------------------------------------ # Read rows from upgraded table t1_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_redundant 7 2 0 4 4 4 # 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 0 0 c4 3 13 16711934 1 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1_redundant instant_col=2; # 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; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Insert a row in upgraded table t1_redundant # ------------------------------------------------------------ INSERT INTO t1_redundant values ("r4c1", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_redundant SET c2="temp" where c1="r1c1"; SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_redundant SET c2="temp" where c1="r2c1"; SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_redundant SET c3="r1c3" where c1="r1c1"; SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_redundant SET c4="r2c4" where c1="r2c1"; SELECT * FROM t1_redundant ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 r2c4 r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # INSTANT DROP c2 # ------------------------------------------------------------ ALTER TABLE t1_redundant 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_redundant 6 2 1 4 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_redundant instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID; 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_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_redundant ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 INSERT INTO t1_redundant values ("r5c1", "r5c3", "r5c4"); SELECT * FROM t1_redundant ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 r5c1 r5c3 r5c4 # ------------------------------------------------------------ # INSTANT ADD c5 at the end # ------------------------------------------------------------ ALTER TABLE t1_redundant ADD COLUMN c5 char(10) default "c5_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_redundant 7 2 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 c5 3 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_redundant instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_redundant ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def INSERT INTO t1_redundant values ("r6c1", "r6c3", "r6c4", "r6c5"); SELECT * FROM t1_redundant ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def r6c1 r6c3 r6c4 r6c5 # ------------------------------------------------------------ # INSTANT ADD c6 somewhere in between # ------------------------------------------------------------ ALTER TABLE t1_redundant ADD COLUMN c6 char(10) default "c6_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_redundant 8 2 3 4 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c6 1 13 16711934 1 3 0 c3 2 13 16711934 1 0 0 c4 3 13 16711934 1 0 0 c5 4 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_redundant instant_col=2; # 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; c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=8;table_id=TABLE_ID;version_added=3; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID; c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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; !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_redundant ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 INSERT INTO t1_redundant values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5"); SELECT * FROM t1_redundant ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 r7c1 r7c6 r7c3 r7c4 r7c5 DROP TABLE t1_redundant; # ------------------------------------------------------------ # TEMP record with different possibilities # ------------------------------------------------------------ show create table t2_redundant; Table Create Table t2_redundant CREATE TABLE `t2_redundant` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT Select * from t2_redundant; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Insert into t2_redundant values ("r3c1", "r3c2", "r3c3"); Select * from t2_redundant; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 Alter table t2_redundant add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Insert into t2_redundant values ("r4c4", "r4c1", "r4c2", "r4c3"); Select * from t2_redundant; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r4c4 r4c1 r4c2 r4c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t2_redundant force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t2_redundant SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t2_redundant; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 c4_def c1_upd r3c2 r3c3 r4c4 c1_upd r4c2 r4c3 DROP TABLE t2_redundant; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ # ------------------------------------------------------------ # Start server with old datadir with # table t1_dynamic having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # c4 INSTANT ADD column with default def_c4. # +------+------+--------+--------+ # | c1 | c2 | c3 | c4 | # +------+------+--------+--------+ # | r1c1 | r1c2 | c3_def | c4_def | # | r2c1 | r2c2 | r2c3 | c4_def | # | r3c1 | r3c2 | r3c3 | r3c4 | # +------+------+--------+--------+ # # table t2_dynamic having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # +------+------+--------+ # | c1 | c2 | c3 | # +------+------+--------+ # | r1c1 | r1c2 | c3_def | # | r2c1 | r2c2 | r2c3 | # +------+------+--------+ # ------------------------------------------------------------ SHOW CREATE TABLE t1_dynamic; Table Create Table t1_dynamic CREATE TABLE `t1_dynamic` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def', `c4` char(10) DEFAULT 'c4_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # ------------------------------------------------------------ # Read rows from upgraded table t1_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_dynamic 7 2 0 4 4 4 # 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 0 0 c4 3 13 16711934 1 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1_dynamic instant_col=2; # 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; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Insert a row in upgraded table t1_dynamic # ------------------------------------------------------------ INSERT INTO t1_dynamic values ("r4c1", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_dynamic SET c2="temp" where c1="r1c1"; SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_dynamic SET c2="temp" where c1="r2c1"; SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_dynamic SET c3="r1c3" where c1="r1c1"; SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_dynamic SET c4="r2c4" where c1="r2c1"; SELECT * FROM t1_dynamic ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 r2c4 r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # INSTANT DROP c2 # ------------------------------------------------------------ ALTER TABLE t1_dynamic 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_dynamic 6 2 1 4 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_dynamic instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; 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_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_dynamic ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 INSERT INTO t1_dynamic values ("r5c1", "r5c3", "r5c4"); SELECT * FROM t1_dynamic ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 r5c1 r5c3 r5c4 # ------------------------------------------------------------ # INSTANT ADD c5 at the end # ------------------------------------------------------------ ALTER TABLE t1_dynamic ADD COLUMN c5 char(10) default "c5_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_dynamic 7 2 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 c5 3 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_dynamic instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_dynamic ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def INSERT INTO t1_dynamic values ("r6c1", "r6c3", "r6c4", "r6c5"); SELECT * FROM t1_dynamic ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def r6c1 r6c3 r6c4 r6c5 # ------------------------------------------------------------ # INSTANT ADD c6 somewhere in between # ------------------------------------------------------------ ALTER TABLE t1_dynamic ADD COLUMN c6 char(10) default "c6_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_dynamic 8 2 3 4 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c6 1 13 16711934 1 3 0 c3 2 13 16711934 1 0 0 c4 3 13 16711934 1 0 0 c5 4 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_dynamic instant_col=2; # 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; c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=3; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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; !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_dynamic ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 INSERT INTO t1_dynamic values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5"); SELECT * FROM t1_dynamic ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 r7c1 r7c6 r7c3 r7c4 r7c5 DROP TABLE t1_dynamic; # ------------------------------------------------------------ # TEMP record with different possibilities # ------------------------------------------------------------ show create table t2_dynamic; Table Create Table t2_dynamic CREATE TABLE `t2_dynamic` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC Select * from t2_dynamic; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Insert into t2_dynamic values ("r3c1", "r3c2", "r3c3"); Select * from t2_dynamic; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 Alter table t2_dynamic add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Insert into t2_dynamic values ("r4c4", "r4c1", "r4c2", "r4c3"); Select * from t2_dynamic; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r4c4 r4c1 r4c2 r4c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t2_dynamic force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t2_dynamic SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t2_dynamic; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 c4_def c1_upd r3c2 r3c3 r4c4 c1_upd r4c2 r4c3 DROP TABLE t2_dynamic; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # ------------------------------------------------------------ # Start server with old datadir with # table t1_compact having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # c4 INSTANT ADD column with default def_c4. # +------+------+--------+--------+ # | c1 | c2 | c3 | c4 | # +------+------+--------+--------+ # | r1c1 | r1c2 | c3_def | c4_def | # | r2c1 | r2c2 | r2c3 | c4_def | # | r3c1 | r3c2 | r3c3 | r3c4 | # +------+------+--------+--------+ # # table t2_compact having # c1, c2 normal columns # c3 INSTANT ADD column with default def_c3. # +------+------+--------+ # | c1 | c2 | c3 | # +------+------+--------+ # | r1c1 | r1c2 | c3_def | # | r2c1 | r2c2 | r2c3 | # +------+------+--------+ # ------------------------------------------------------------ SHOW CREATE TABLE t1_compact; Table Create Table t1_compact CREATE TABLE `t1_compact` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def', `c4` char(10) DEFAULT 'c4_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT # ------------------------------------------------------------ # Read rows from upgraded table t1_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_compact 7 2 0 4 4 4 # 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 0 0 c4 3 13 16711934 1 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1_compact instant_col=2; # 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; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Insert a row in upgraded table t1_compact # ------------------------------------------------------------ INSERT INTO t1_compact values ("r4c1", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_compact SET c2="temp" where c1="r1c1"; SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_compact SET c2="temp" where c1="r2c1"; SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 temp c3_def c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with no version # ------------------------------------------------------------ UPDATE t1_compact SET c3="r1c3" where c1="r1c1"; SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 c4_def r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # UPDATE : NOT IN PLACE for row with V1 # ------------------------------------------------------------ UPDATE t1_compact SET c4="r2c4" where c1="r2c1"; SELECT * FROM t1_compact ORDER BY c1; c1 c2 c3 c4 r1c1 temp r1c3 c4_def r2c1 temp r2c3 r2c4 r3c1 r3c2 r3c3 r3c4 r4c1 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # INSTANT DROP c2 # ------------------------------------------------------------ ALTER TABLE t1_compact 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_compact 6 2 1 4 3 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_compact instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; 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_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_compact ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 INSERT INTO t1_compact values ("r5c1", "r5c3", "r5c4"); SELECT * FROM t1_compact ORDER BY c1; c1 c3 c4 r1c1 r1c3 c4_def r2c1 r2c3 r2c4 r3c1 r3c3 r3c4 r4c1 r4c3 r4c4 r5c1 r5c3 r5c4 # ------------------------------------------------------------ # INSTANT ADD c5 at the end # ------------------------------------------------------------ ALTER TABLE t1_compact ADD COLUMN c5 char(10) default "c5_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_compact 7 2 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 0 0 c4 2 13 16711934 1 0 0 c5 3 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_compact instant_col=2; # 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; c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_compact ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def INSERT INTO t1_compact values ("r6c1", "r6c3", "r6c4", "r6c5"); SELECT * FROM t1_compact ORDER BY c1; c1 c3 c4 c5 r1c1 r1c3 c4_def c5_def r2c1 r2c3 r2c4 c5_def r3c1 r3c3 r3c4 c5_def r4c1 r4c3 r4c4 c5_def r5c1 r5c3 r5c4 c5_def r6c1 r6c3 r6c4 r6c5 # ------------------------------------------------------------ # INSTANT ADD c6 somewhere in between # ------------------------------------------------------------ ALTER TABLE t1_compact ADD COLUMN c6 char(10) default "c6_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_compact 8 2 3 4 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c6 1 13 16711934 1 3 0 c3 2 13 16711934 1 0 0 c4 3 13 16711934 1 0 0 c5 4 13 16711934 1 2 0 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1_compact instant_col=2; # 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; c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=3; c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID; c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2; 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; !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; SELECT * FROM t1_compact ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 INSERT INTO t1_compact values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5"); SELECT * FROM t1_compact ORDER BY c1; c1 c6 c3 c4 c5 r1c1 c6_def r1c3 c4_def c5_def r2c1 c6_def r2c3 r2c4 c5_def r3c1 c6_def r3c3 r3c4 c5_def r4c1 c6_def r4c3 r4c4 c5_def r5c1 c6_def r5c3 r5c4 c5_def r6c1 c6_def r6c3 r6c4 r6c5 r7c1 r7c6 r7c3 r7c4 r7c5 DROP TABLE t1_compact; # ------------------------------------------------------------ # TEMP record with different possibilities # ------------------------------------------------------------ show create table t2_compact; Table Create Table t2_compact CREATE TABLE `t2_compact` ( `c1` char(10) DEFAULT NULL, `c2` char(10) DEFAULT NULL, `c3` char(10) DEFAULT 'c3_def' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT Select * from t2_compact; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 Insert into t2_compact values ("r3c1", "r3c2", "r3c3"); Select * from t2_compact; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 Alter table t2_compact add column c4 char(10) default "c4_def" first, algorithm=INSTANT; Insert into t2_compact values ("r4c4", "r4c1", "r4c2", "r4c3"); Select * from t2_compact; c4 c1 c2 c3 c4_def r1c1 r1c2 c3_def c4_def r2c1 r2c2 r2c3 c4_def r3c1 r3c2 r3c3 r4c4 r4c1 r4c2 r4c3 # Make alter table wait SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2'; # Rebuild the table ALTER TABLE t2_compact force, algorithm=inplace;; # connection con1 SET DEBUG_SYNC = 'now WAIT_FOR s1'; # Update query UPDATE t2_compact SET c1="c1_upd"; # Let Alter table continue SET DEBUG_SYNC = 'now SIGNAL s2'; # connection default SELECT * FROM t2_compact; c4 c1 c2 c3 c4_def c1_upd r1c2 c3_def c4_def c1_upd r2c2 r2c3 c4_def c1_upd r3c2 r3c3 r4c4 c1_upd r4c2 r4c3 DROP TABLE t2_compact; ########### # CLEANUP # ########### # Shutdown server # Remove copied files # Restarting server to restore server state # restart