############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) 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 6 0 0 3 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 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; c3 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 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 0 0 0 c4 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 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 physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 9 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 9 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # 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 0 0 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=7;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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # 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 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=9;table_id=TABLE_ID;version_added=4; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # 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 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p9_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020202020202020202020202020202020202020202020202020202020202020;physical_pos=10;table_id=TABLE_ID;version_added=5; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; !hidden!_dropped_v5_p9_c7 11 MYSQL_TYPE_STRING 0 SE physical_pos=9;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 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 c7 2 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; c7 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020202020202020202020202020202020202020202020202020202020202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020202020202020202020202020202020202020202020202020202020202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) 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 6 0 0 3 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 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; c3 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 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 0 0 0 c4 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 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 physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 9 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 9 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # 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 0 0 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=7;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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # 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 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=9;table_id=TABLE_ID;version_added=4; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # 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 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p9_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=10;table_id=TABLE_ID;version_added=5; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; !hidden!_dropped_v5_p9_c7 11 MYSQL_TYPE_STRING 0 SE physical_pos=9;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 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 c7 2 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; c7 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) 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 6 0 0 3 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 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; c3 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 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 0 0 0 c4 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 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 physical_pos=5;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 9 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p8_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=7;table_id=TABLE_ID; 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_p8_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 9 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # 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 0 0 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=7;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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # 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 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=9;table_id=TABLE_ID;version_added=4; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # 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 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p8_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p5_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p7_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p9_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=6;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=10;table_id=TABLE_ID;version_added=5; 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_p8_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_dropped=1; !hidden!_dropped_v2_p5_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=5;version_dropped=2; !hidden!_dropped_v3_p3_c6 9 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p7_c3 10 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=4; !hidden!_dropped_v5_p9_c7 11 MYSQL_TYPE_STRING 0 SE physical_pos=9;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 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 c7 2 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; c7 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; # ------------------------------------------------------------ # Create a table with 3 columns. [c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible 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; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 # -----------------------ADD COLUMN TEST---------------------- # ------------------------------------------------------------ # Add a new column at the end. [c1, c2, c3, +c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_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 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 c4 r1c1 r1c2 r1c3 c4_def r2c1 r2c2 r2c3 r2c4 # ------------------------------------------------------------ # Add a new column in between. [c1, +c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL 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 2 3 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c5 1 13 16711934 1 2 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 c4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 5 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4"); SELECT * FROM t1 ORDER BY c1; c1 c5 c2 c3 c4 r1c1 NULL r1c2 r1c3 c4_def r2c1 NULL r2c2 r2c3 r2c4 r3c1 r3c5 r3c2 r3c3 r3c4 # ------------------------------------------------------------ # Add a new column at first. [+c6, c1, c5, c2, c3, c4] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL 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 9 0 3 3 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 1 3 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 1 2 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=3; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=6;table_id=TABLE_ID;version_added=2; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible default=63345f6465662020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;version_added=1; 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; INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # ------------------------------------------------------------ # Rebuild table. [c6, c1, c5, c2, c3, 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 9 0 0 6 6 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 c4 5 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 c6 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c4 6 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 c4 NULL r1c1 NULL r1c2 r1c3 c4_def NULL r2c1 NULL r2c2 r2c3 r2c4 NULL r3c1 r3c5 r3c2 r3c3 r3c4 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4 # -----------------------DROP COLUMN TEST--------------------- # ------------------------------------------------------------ # Drop a column from the end. [c6, c1, c5, c2, c3, -c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c4, 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 1 6 5 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c5 2 13 16711934 0 0 0 c2 3 13 16711934 0 0 0 c3 4 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 8 13 16711934 0 0 1 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c5 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c2 4 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 5 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 8 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c5 c2 c3 NULL r1c1 NULL r1c2 r1c3 NULL r2c1 NULL r2c2 r2c3 NULL r3c1 r3c5 r3c2 r3c3 r4c6 r4c1 r4c5 r4c2 r4c3 r5c6 r5c1 r5c5 r5c2 r5c3 # ------------------------------------------------------------ # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c5, 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 6 4 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c6 0 13 16711934 0 0 0 c1 1 13 16712190 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 7 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 8 13 16711934 0 0 2 # 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 c6 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_p7_c4 7 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3"); SELECT * FROM t1 ORDER BY c1; c6 c1 c2 c3 NULL r1c1 r1c2 r1c3 NULL r2c1 r2c2 r2c3 NULL r3c1 r3c2 r3c3 r4c6 r4c1 r4c2 r4c3 r5c6 r5c1 r5c2 r5c3 r6c6 r6c1 r6c2 r6c3 # ------------------------------------------------------------ # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 3 6 3 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c3 2 13 16711934 0 0 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 # 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 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible physical_pos=6;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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3 r4c1 r4c2 r4c3 r5c1 r5c2 r5c3 r6c1 r6c2 r6c3 r7c1 r7c2 r7c3 # ------------------------------------------------------------ # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", 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 4 6 3 7 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 4 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=4; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def INSERT INTO t1 values ("r8c1", "r8c2", "r8c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def r2c1 r2c2 c7_def r3c1 r3c2 c7_def r4c1 r4c2 c7_def r5c1 r5c2 c7_def r6c1 r6c2 c7_def r7c1 r7c2 c7_def r8c1 r8c2 r8c7 # -------------------------------------------------------------------------- # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3] # -------------------------------------------------------------------------- ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, 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 6 3 8 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 13 16711934 1 5 0 !hidden!_dropped_v1_p7_c4 6 13 16711934 0 0 1 !hidden!_dropped_v2_p4_c5 7 13 16711934 0 0 2 !hidden!_dropped_v3_p3_c6 8 13 16711934 0 0 3 !hidden!_dropped_v4_p6_c3 9 13 16711934 0 0 4 !hidden!_dropped_v5_p8_c7 10 13 16711934 0 4 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 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible default=63375f64656632202020;physical_pos=9;table_id=TABLE_ID;version_added=5; 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_v1_p7_c4 6 MYSQL_TYPE_STRING 0 SE physical_pos=7;version_dropped=1; !hidden!_dropped_v2_p4_c5 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; !hidden!_dropped_v3_p3_c6 8 MYSQL_TYPE_STRING 0 SE physical_pos=3;version_dropped=3; !hidden!_dropped_v4_p6_c3 9 MYSQL_TYPE_STRING 0 SE physical_pos=6;version_dropped=4; !hidden!_dropped_v5_p8_c7 10 MYSQL_TYPE_STRING 0 SE physical_pos=8;version_added=4;version_dropped=5; SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 # ------------------------------------------------------------ # Rebuild table. [c1, c2, c7] # ------------------------------------------------------------ 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 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16712190 0 0 0 c2 1 13 16711934 0 0 0 c7 2 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 1 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c7 3 MYSQL_TYPE_STRING 0 Visible 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 c2 c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7"); SELECT * FROM t1 ORDER BY c1; c1 c2 c7 r10c1 r10c2 r10c7 r1c1 r1c2 c7_def2 r2c1 r2c2 c7_def2 r3c1 r3c2 c7_def2 r4c1 r4c2 c7_def2 r5c1 r5c2 c7_def2 r6c1 r6c2 c7_def2 r7c1 r7c2 c7_def2 r8c1 r8c2 c7_def2 r9c1 r9c2 r9c7 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 c2 c3 r1c11 r1c2 c3_def SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=COPY; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2; CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t2 VALUES ("r1c1", "r1c2"); SELECT * FROM t2; c1 c2 r1c1 r1c2 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 BEGIN; SELECT * FROM t2; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1 = "r1c11"; SELECT * FROM t1; c1 r1c11 SELECT * FROM t1; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t1; DROP TABLE t2;