# # Scenario 1: # Create a small table with row_format compressed and adding/dropping instant column should fail # CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPRESSED; INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5); ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN a, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; # # Scenario 2: # Create a small table and adding spatial types with not null should fail # CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5); ALTER TABLE t1 ADD COLUMN c1 POINT NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN d1 LINESTRING NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN e1 POLYGON NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN j1 GEOMETRY NOT NULL, ALGORITHM = INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; # # Scenario 3: # Create a temporary table and adding/dropping instant column should fail # CREATE TEMPORARY TABLE tmp1(a INT, b INT); ALTER TABLE tmp1 ADD COLUMN c int, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY. ALTER TABLE tmp1 DROP COLUMN b, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY. DROP TABLE tmp1; # # Scenario 4: # Trying to add/drop instant columns for data-dictionary tables should fail # ALTER TABLE mysql.plugin ADD COLUMN c1 INT, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE mysql.plugin DROP COLUMN dl, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE mysql.user ADD COLUMN c1 INT, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE mysql.user DROP COLUMN Select_priv, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. # # Scenario 5: # Trying to add/drop instant column to a table having FTS index # CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a,b)); INSERT INTO t1 VALUES('test1', 'test2'); ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN b, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; # # Scenario 6: # INSTANT ADD/DROP column with key # CREATE TABLE t1 (c1 INT, c2 INT); ALTER TABLE t1 ADD COLUMN c3 INT KEY, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; CREATE TABLE t1 (c1 INT KEY, c2 INT); ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c1, ADD PRIMARY KEY (c2), ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; CREATE TABLE t1 (c1 INT KEY, c2 INT); ALTER TABLE t1 ADD COLUMN c3 INT UNIQUE KEY, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD COLUMN c3 INT, ADD INDEX idx (c3), ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; CREATE TABLE t1 (c1 INT KEY, c2 INT, INDEX idx(c2)); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. DROP TABLE t1; # # Scenario 7: # Maximum row versions (MAX_ROW_VERSION is 64) # CREATE TABLE t1 (c1 int); # INSTANT ADD COLUMNS to table to make it reach the maximum versions # Table TOTAL_ROW_VERSIONS should be 64 (MAX) # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 68 0 64 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c2 1 6 1027 1 c3 2 6 1027 1 c4 3 6 1027 1 c5 4 6 1027 1 c6 5 6 1027 1 c7 6 6 1027 1 c8 7 6 1027 1 c9 8 6 1027 1 c10 9 6 1027 1 c11 10 6 1027 1 c12 11 6 1027 1 c13 12 6 1027 1 c14 13 6 1027 1 c15 14 6 1027 1 c16 15 6 1027 1 c17 16 6 1027 1 c18 17 6 1027 1 c19 18 6 1027 1 c20 19 6 1027 1 c21 20 6 1027 1 c22 21 6 1027 1 c23 22 6 1027 1 c24 23 6 1027 1 c25 24 6 1027 1 c26 25 6 1027 1 c27 26 6 1027 1 c28 27 6 1027 1 c29 28 6 1027 1 c30 29 6 1027 1 c31 30 6 1027 1 c32 31 6 1027 1 c33 32 6 1027 1 c34 33 6 1027 1 c35 34 6 1027 1 c36 35 6 1027 1 c37 36 6 1027 1 c38 37 6 1027 1 c39 38 6 1027 1 c40 39 6 1027 1 c41 40 6 1027 1 c42 41 6 1027 1 c43 42 6 1027 1 c44 43 6 1027 1 c45 44 6 1027 1 c46 45 6 1027 1 c47 46 6 1027 1 c48 47 6 1027 1 c49 48 6 1027 1 c50 49 6 1027 1 c51 50 6 1027 1 c52 51 6 1027 1 c53 52 6 1027 1 c54 53 6 1027 1 c55 54 6 1027 1 c56 55 6 1027 1 c57 56 6 1027 1 c58 57 6 1027 1 c59 58 6 1027 1 c60 59 6 1027 1 c61 60 6 1027 1 c62 61 6 1027 1 c63 62 6 1027 1 c64 63 6 1027 1 c65 64 6 1027 1 # An INSTANT ADD COLUMN should result in error now. ALTER TABLE t1 ADD COLUMN c66 INT, ALGORITHM=INSTANT; ERROR HY000: Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. # ADD COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly ALTER TABLE t1 ADD COLUMN c66 INT; # Table would have been rebuilt # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 69 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c2 1 6 1027 0 c3 2 6 1027 0 c4 3 6 1027 0 c5 4 6 1027 0 c6 5 6 1027 0 c7 6 6 1027 0 c8 7 6 1027 0 c9 8 6 1027 0 c10 9 6 1027 0 c11 10 6 1027 0 c12 11 6 1027 0 c13 12 6 1027 0 c14 13 6 1027 0 c15 14 6 1027 0 c16 15 6 1027 0 c17 16 6 1027 0 c18 17 6 1027 0 c19 18 6 1027 0 c20 19 6 1027 0 c21 20 6 1027 0 c22 21 6 1027 0 c23 22 6 1027 0 c24 23 6 1027 0 c25 24 6 1027 0 c26 25 6 1027 0 c27 26 6 1027 0 c28 27 6 1027 0 c29 28 6 1027 0 c30 29 6 1027 0 c31 30 6 1027 0 c32 31 6 1027 0 c33 32 6 1027 0 c34 33 6 1027 0 c35 34 6 1027 0 c36 35 6 1027 0 c37 36 6 1027 0 c38 37 6 1027 0 c39 38 6 1027 0 c40 39 6 1027 0 c41 40 6 1027 0 c42 41 6 1027 0 c43 42 6 1027 0 c44 43 6 1027 0 c45 44 6 1027 0 c46 45 6 1027 0 c47 46 6 1027 0 c48 47 6 1027 0 c49 48 6 1027 0 c50 49 6 1027 0 c51 50 6 1027 0 c52 51 6 1027 0 c53 52 6 1027 0 c54 53 6 1027 0 c55 54 6 1027 0 c56 55 6 1027 0 c57 56 6 1027 0 c58 57 6 1027 0 c59 58 6 1027 0 c60 59 6 1027 0 c61 60 6 1027 0 c62 61 6 1027 0 c63 62 6 1027 0 c64 63 6 1027 0 c65 64 6 1027 0 c66 65 6 1027 0 DROP TABLE t1; CREATE TABLE t1 (c1 int); INSERT INTO t1(c1) VALUES(1); SELECT * FROM t1; c1 1 # INSTANT ADD COLUMNS to table to make it reach the maximum versions # An INSTANT DROP COLUMN should result in error now. ALTER TABLE t1 DROP COLUMN c65, ALGORITHM=INSTANT; ERROR HY000: Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. INSERT INTO t1(c1) VALUES(2); SELECT * FROM t1; c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c35 c36 c37 c38 c39 c40 c41 c42 c43 c44 c45 c46 c47 c48 c49 c50 c51 c52 c53 c54 c55 c56 c57 c58 c59 c60 c61 c62 c63 c64 c65 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # DROP COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly ALTER TABLE t1 DROP COLUMN c65; INSERT INTO t1(c1) VALUES(3); SELECT * FROM t1; c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c35 c36 c37 c38 c39 c40 c41 c42 c43 c44 c45 c46 c47 c48 c49 c50 c51 c52 c53 c54 c55 c56 c57 c58 c59 c60 c61 c62 c63 c64 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # Table would have been rebuilt # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 67 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 6 1027 0 c2 1 6 1027 0 c3 2 6 1027 0 c4 3 6 1027 0 c5 4 6 1027 0 c6 5 6 1027 0 c7 6 6 1027 0 c8 7 6 1027 0 c9 8 6 1027 0 c10 9 6 1027 0 c11 10 6 1027 0 c12 11 6 1027 0 c13 12 6 1027 0 c14 13 6 1027 0 c15 14 6 1027 0 c16 15 6 1027 0 c17 16 6 1027 0 c18 17 6 1027 0 c19 18 6 1027 0 c20 19 6 1027 0 c21 20 6 1027 0 c22 21 6 1027 0 c23 22 6 1027 0 c24 23 6 1027 0 c25 24 6 1027 0 c26 25 6 1027 0 c27 26 6 1027 0 c28 27 6 1027 0 c29 28 6 1027 0 c30 29 6 1027 0 c31 30 6 1027 0 c32 31 6 1027 0 c33 32 6 1027 0 c34 33 6 1027 0 c35 34 6 1027 0 c36 35 6 1027 0 c37 36 6 1027 0 c38 37 6 1027 0 c39 38 6 1027 0 c40 39 6 1027 0 c41 40 6 1027 0 c42 41 6 1027 0 c43 42 6 1027 0 c44 43 6 1027 0 c45 44 6 1027 0 c46 45 6 1027 0 c47 46 6 1027 0 c48 47 6 1027 0 c49 48 6 1027 0 c50 49 6 1027 0 c51 50 6 1027 0 c52 51 6 1027 0 c53 52 6 1027 0 c54 53 6 1027 0 c55 54 6 1027 0 c56 55 6 1027 0 c57 56 6 1027 0 c58 57 6 1027 0 c59 58 6 1027 0 c60 59 6 1027 0 c61 60 6 1027 0 c62 61 6 1027 0 c63 62 6 1027 0 c64 63 6 1027 0 DROP TABLE t1;