############################################## # 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16711934 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16711934 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16711934 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 7 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 c4 3 13 16711934 1 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 test/t1 8 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c5 1 13 16711934 1 c2 2 13 16711934 0 c3 3 13 16711934 0 c4 4 13 16711934 1 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 test/t1 9 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 1 c1 1 13 16712190 0 c5 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 1 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 test/t1 9 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 c4 5 13 16711934 0 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 test/t1 8 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c5 2 13 16711934 0 c2 3 13 16711934 0 c3 4 13 16711934 0 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 test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c6 0 13 16711934 0 c1 1 13 16712190 0 c2 2 13 16711934 0 c3 3 13 16711934 0 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 test/t1 6 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c3 2 13 16711934 0 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 test/t1 6 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 1 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 test/t1 6 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16712190 0 c2 1 13 16711934 0 c7 2 13 16711934 0 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;