############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## SET SESSION debug= '+d,show_dropped_column'; # Scenario 1 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 drop table t1; # Scenario 2 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 3 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1, algorithm=INSTANT; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 drop column c2, algorithm=INSTANT; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 4 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 5 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 char(10) default "def_c3" after c1; select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 insert into t1 value ("r2c1", "r2c3", "r2c2"); alter table t1 drop column c2; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 6 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c2 char(10) default "def_c3", drop column c2; select * from t1; c1 c2 r1c1 def_c3 insert into t1 value ("r2c1", "r2c2"); select * from t1; c1 c2 r1c1 def_c3 r2c1 r2c2 drop table t1; # Scenario 7 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 int after c1; select * from t1; c1 c3 c2 r1c1 NULL r1c2 insert into t1 value ("r2c1", 3, "r2c2"); select * from t1; c1 c3 c2 r1c1 NULL r1c2 r2c1 3 r2c2 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 NULL r2c1 3 drop table t1; # Scenario 8 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", NULL, "r1c3"); select * from t1; c1 c2 c3 r1c1 NULL r1c3 alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 9 CREATE TABLE t1 (C1 char(10), C2 char(10), C3 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c1; select * from t1; C2 C3 r1c2 r1c3 alter table t1 drop column c3; select * from t1; C2 r1c2 drop table t1; # Scenario 10 create table t1 (c1 char(10) KEY, c2 char(10), key sec_idx(c2)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3"; select * from t1; c1 c2 c3 r1c1 r1c2 def_c3 insert into t1 values ("r2c1", "r2c2", "r3c3"); drop table t1; # Scenario 11 CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DROP INDEX idx1; DROP TABLE t1; # Scenario 12 CREATE TABLE t1(c2 char(100)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD FULLTEXT INDEX `ft_idx` (c2); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID DROP TABLE t1; # Scenario 13 CREATE TABLE t1(a INT) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(1); ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT ); ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace; DROP TABLE t1; # Scenario 14 CREATE TABLE t1 (col1 int(11) DEFAULT NULL, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, col6 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, col7 int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, col8 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, col9 text, extra int(11) DEFAULT NULL) ROW_FORMAT=REDUNDANT; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. ALTER TABLE t1 DROP COLUMN col7; ALTER TABLE t1 DROP COLUMN col6, DROP COLUMN col9; DROP TABLE t1; # Scenario 15 create table t1(a text, b text, fulltext(a,b)); insert into t1 values('text1', 'text2'); insert into t1 values('test1', 'test2'); alter table t1 add fulltext(b); drop table t1; # Scenario 16 CREATE TABLE t1 (a serial key, b text) ROW_FORMAT=REDUNDANT; INSERT INTO t1(b) VALUES(repeat("a short string - ",5)); INSERT INTO t1(b) VALUES(repeat("a long string - ",50)); INSERT INTO t1(b) SELECT b FROM t1; ALTER TABLE t1 ADD COLUMN (c int), algorithm=INSTANT; ALTER TABLE t1 ADD INDEX c (c), algorithm=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD INDEX c (c), algorithm=INPLACE; DROP TABLE t1; # Scenario 17 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20), c4 char(20)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE test.t1 ADD c6 char(20), DROP COLUMN c3; DROP TABLE t1; # Scenario 18 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE; Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID Warning 1814 InnoDB: Tablespace has been discarded for table 't1' ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't1' DROP TABLE t1; # Scenario 19 CREATE TABLE t1 (a INT, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 FIRST; ALTER TABLE t1 CHANGE aa e INT; INSERT INTO t1 VALUES (1,1,1,1,1); ALTER TABLE t1 ADD COLUMN a INT DEFAULT 1 FIRST, DROP COLUMN a, CHANGE b b INT; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 AFTER a, DROP COLUMN b, CHANGE c c INT; ALTER TABLE t1 CHANGE aa b INT; DROP TABLE t1; # Scenario 20 CREATE TABLE t1 (a varchar(512) NOT NULL, b varchar(512) NOT NULL, FULLTEXT KEY fts_idx (a)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DROP KEY fts_idx; ALTER TABLE t1 ADD COLUMN b_hash char(64) AS(b) VIRTUAL NOT NULL; DROP TABLE t1; # Scenario 21 CREATE PROCEDURE test.fill_table (IN start INT) BEGIN SET @idx =start; WHILE (@idx > 0) DO INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SET @idx = @idx - 1; END WHILE; END| CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; call fill_table(400); DROP PROCEDURE test.fill_table; DROP TABLE t1; # Scenario 22 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; SET GLOBAL innodb_limit_optimistic_insert_debug=2; INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SELECT * FROM t1; id v 1 aaaaaaaaaaaaa 2 aaaaaaaaaaaaa 3 aaaaaaaaaaaaa SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP TABLE t1; # Scenario 23 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 add column c3 char(10) default "newnewnew", drop column c3; select * from t1; c1 c2 c3 r1c1 r1c2 newnewnew r2c1 r2c2 newnewnew DROP TABLE t1; # Scenario 24 CREATE TABLE t1 (c1 char(10)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; INSERT INTO t1 (c1) VALUES("aaaa"); select * from t1; c1 aaaa check table t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; # Scenario 25 CREATE TABLE t1 (c1 int, c2 char(10)) ROW_FORMAT=REDUNDANT PARTITION BY HASH (c1) PARTITIONS 2; alter table t1 drop column c2; DROP TABLE t1; # Scenario 26 CREATE TABLE t1 (c1 char(10) KEY, c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 values ("row1_c1", "row1_c2"); ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); ALTER TABLE t1 force; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c5 INT after c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; DROP TABLE t1; # Scenario 27 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN c INT NOT NULL; ALTER TABLE t1 ADD COLUMN d VARCHAR(100); INSERT INTO t1 VALUES(0, 5, 20, 'Hello world'); DROP TABLE t1; # Scenario 28 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); SELECT * from t1; a b 1 1 2 2 3 3 4 4 5 5 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world'; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 100 204 Hello world 5 5 100 205 Hello world UPDATE t1 SET c = 200 WHERE a > 3; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 200 404 Hello world 5 5 200 405 Hello world DROP TABLE t1; # Scenario 29 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 VALUES(0, 1); SELECT * from t1; c1 c2 1 1 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 1 1 NULL ALTER TABLE t1 ADD COLUMN c4 CHAR(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 1 1 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 1 1 2 NULL DROP TABLE t1; # Scenario 30 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * from t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 r1c1 r1c2 NULL ALTER TABLE t1 ADD COLUMN c4 char(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 2 NULL DROP TABLE t1; # Scenario 31 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 char(10) default "def_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 def_c3 UPDATE t1 SET c3 = "new_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new_c3 UPDATE t1 SET c3 = "new2_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new2_c3 DROP TABLE t1; # Scenario 32 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4); ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 5 1 2 5 2 3 5 3 4 5 UPDATE t1 SET c = b WHERE b <= 2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 1 1 2 2 2 3 5 3 4 5 DROP TABLE t1; # Scenario 33 CREATE TABLE t1(c1 longtext) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES (repeat('x', 8098)); SELECT SUBSTRING(c1, 1000, 32) FROM t1; SUBSTRING(c1, 1000, 32) xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL BEGIN; UPDATE t1 SET c2 = 0; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0 ROLLBACK; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL DROP TABLE t1; # Scenario 34 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 INSERT INTO t1 VALUES("r3c1", "r3c2", "r3c3"); START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 DROP TABLE t1; # Scenario 35 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10, algorithm=INSTANT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK START TRANSACTION; INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); START TRANSACTION; UPDATE t1 SET b = 10; SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 DROP TABLE t1; # Scenario 36 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 DROP TABLE t1; # Scenario 37 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; UPDATE t1 SET c3 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp DROP TABLE t1; # Scenario 38 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 4 0 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(col3); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 39 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 6 0 3 3 3 test/t1#p#p1 6 0 3 3 3 test/t1#p#p2 6 0 3 3 3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(c3); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 40 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 41 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ADD KEY idx3(col3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 42 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INSTANT, DROP COLUMN c2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 DROP TABLE t1; # Scenario 43 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8); SELECT * FROM t1; a b 3 3 6 6 1 1 4 4 7 7 2 2 5 5 8 8 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 3 3 5 6 6 5 1 1 5 4 4 5 7 7 5 2 2 5 5 5 5 8 8 5 ALTER TABLE t1 ADD PARTITION PARTITIONS 10; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 ALTER TABLE t1 ADD KEY(b); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 DROP TABLE t1; # Scenario 44 CREATE TABLE t1 (a BLOB, b BLOB, c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, h VARCHAR(10) DEFAULT NULL) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk"); CREATE INDEX idx ON t1(c(100)); SELECT length(c) FROM t1; length(c) 32000 START TRANSACTION; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); ROLLBACK; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); START TRANSACTION; UPDATE t1 SET a = REPEAT('m', 16000) WHERE a like "aaa%"; ROLLBACK; DROP TABLE t1; # Scenario 45 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); INSERT INTO t1 VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); INSERT INTO t1 VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); ALTER TABLE t1 DROP COLUMN c, algorithm=inplace; ALTER TABLE t1 DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace; DROP TABLE t1; # Scenario 46 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6); ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10; SELECT * FROM t1; a b c d 1 1 1 10 2 2 2 10 3 3 3 10 6 6 6 10 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done'; ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;; SET DEBUG_SYNC = 'now WAIT_FOR altered'; INSERT INTO t1(a, b, c) VALUES(7, 7, 7); UPDATE t1 SET a = 10 WHERE a = 1; DELETE FROM t1 WHERE a = 2; UPDATE t1 SET c = 10 WHERE a = 3; UPDATE t1 SET d = 20 WHERE a = 5; UPDATE t1 SET a = 8, d = 20 WHERE a = 6; SET DEBUG_SYNC = 'now SIGNAL dmls_done'; SELECT * FROM t1; a b d 3 3 10 7 7 10 8 6 20 10 1 10 DROP TABLE t1; # Scenario 47 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (PARTITION p21 VALUES LESS THAN(15), PARTITION p22 VALUES LESS THAN(20)); SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 DROP TABLE t1; # Scenario 48 CREATE TABLE t1 (c1 integer PRIMARY KEY AUTO_INCREMENT, c2 integer NULL UNIQUE) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN c3 int; ALTER TABLE t1 DROP COLUMN c3; ALTER TABLE t1 ADD COLUMN c4 int DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4 c4_new int NULL DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4_new c4_new int NULL DEFAULT 1; DROP TABLE t1; # Scenario 49 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=REDUNDANT; INSERT INTO t1 values ("row1_c1", "a"); SELECT * from t1; c1 c2 row1_c1 a ALTER TABLE t1 ADD COLUMN c3 enum('x', 'y', 'z'), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 row1_c1 a NULL INSERT INTO t1 values ("row2_c2", "b", "y"); SELECT * from t1; c1 c2 c3 row1_c1 a NULL row2_c2 b y ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * from t1; c1 c3 row1_c1 NULL row2_c2 y DROP TABLE t1; # Scenario 50 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DROP COLUMN c2; truncate table t1; truncate table t1; truncate table t1; DROP TABLE t1; # Scenario 51 CREATE TABLE t1 (c1 int KEY, c2 int) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30)); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; # Scenario 52 CREATE TABLE t1(c1 int, c2 int, c3 int, c4 DOUBLE GENERATED ALWAYS AS (c1+c2) STORED, c5 DOUBLE GENERATED ALWAYS AS (c1-c2) VIRTUAL) ROW_FORMAT=REDUNDANT;; INSERT INTO t1(c1,c2,c3) VALUES (1,1,1); SELECT * FROM t1; c1 c2 c3 c4 c5 1 1 1 2 0 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ERROR HY000: Column 'c2' has a generated column dependency. ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c4 c5 1 1 2 0 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 1 1 0 ALTER TABLE t1 ADD COLUMN c6 DOUBLE GENERATED ALWAYS AS (2*c1+c2) VIRTUAL, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c7 DOUBLE GENERATED ALWAYS AS (c1+2*c2) STORED, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c8 DOUBLE GENERATED ALWAYS AS (c1+2*c2) VIRTUAL FIRST, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 1 1 NULL 0 3 ALTER TABLE t1 DROP column c3, add column c10 int as (c1+c2) virtual, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 c10 1 1 0 3 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 c10 1 1 NULL 0 3 2 ALTER TABLE t1 DROP column c3, drop column c6, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c10 1 1 0 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 ALTER TABLE t1 DROP column c3, add column c11 int as (c1+c2) virtual first, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 DROP TABLE t1; # Scenario 53 # Table with functional index. CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT KEY, c1 varchar(10) DEFAULT NULL, c2 varchar(10) DEFAULT NULL, INDEX idx((concat(c1, "c1")))) ROW_FORMAT=REDUNDANT;; INSERT INTO t1(c1, c2) values("abc", "def"); SELECT * FROM t1; id c1 c2 1 abc def ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual after id, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; id c1 1 abc DROP TABLE t1; # Scenarion 54 CREATE TABLE t1 (c1 TEXT, c2 INT, PRIMARY KEY (c1(1))) ROW_FORMAT=REDUNDANT;; SELECT * FROM t1; c1 c2 REPLACE INTO t1(c1) VALUES (''); REPLACE INTO t1(c1) VALUES (''); SELECT * From t1; c1 c2 NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; REPLACE INTO t1(c1) VALUES (''); DROP TABLE t1; # Scenario 55 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (60)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p5 INTO ( PARTITION p51 VALUES LESS THAN (50), PARTITION p52 VALUES LESS THAN (60) ); DROP TABLE t1; # Scenario 56 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, PRIMARY KEY(c1, c4(10))) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 VALUES (1, 2, 3, "abcdefghijklmnop"); SELECT * FROM t1; c1 c2 c3 c4 1 2 3 abcdefghijklmnop ALTER TABLE t1 ADD COLUMN c5 INT, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c4 c5 1 2 3 abcdefghijklmnop NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c3 c4 c5 1 3 abcdefghijklmnop NULL DROP TABLE t1; # Scenario 57 CREATE TABLE t1 (c1 CHAR(20), c2 CHAR(20), PRIMARY KEY PK(c1(4))) ROW_FORMAT=REDUNDANT;; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 DROP TABLE t1; # Scenario 58 # Validating record in table with INSTANT ADD/DROP columns CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r3c2"); ALTER TABLE t1 ADD COLUMN c4 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r4c2"); SELECT * From t1; c2 c3 c4 r1c2 NULL NULL r2c2 r2c3 NULL r3c2 NULL NULL r4c2 NULL NULL DROP TABLE t1; CREATE TABLE t1 (id INT KEY, c2 CHAR(10)) ROW_FORMAT=REDUNDANT PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (40), PARTITION P1 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(1,"r1c2"); INSERT INTO t1 VALUES(2,"r2c2"); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 CHAR(10), ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P0, P1 INTO (PARTITION P1 VALUES LESS THAN (50)); SELECT * FROM t1; id c2 1 NULL 2 NULL DROP TABLE t1; # Scenario 59 # A scenario when #fields were not updated correctly in REDUNDANT record for not-inplace update. CREATE TABLE t1 (c1 VARCHAR(20), c2 CHAR(20)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1="r1c11111111111"; SELECT * FROM t1; c1 r1c11111111111 DROP TABLE t1; # Scenario 60 # When a new partition is added in a table with instant columns and virtual column, # during inheriting INSTANT metadat for new partitions, virtual columns were not skipped. CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300)); ALTER TABLE t1 ADD COLUMN col5 INT, ADD COLUMN col6 INT GENERATED ALWAYS AS (col5 + 1) VIRTUAL, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN col2, algorithm=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p2 INTO(PARTITION p21 VALUES LESS THAN (250), PARTITION p22 VALUES LESS THAN (300)); DROP TABLE t1; # Scenario 61 # When a table is truncated, it's instant metadata is reset. It shall # also reset instant metadata of columns. CREATE TABLE t1 (c1 VARCHAR(1008)) ROW_FORMAT=REDUNDANT; ALTER TABLE t1 ADD COLUMN c2 INT; TRUNCATE TABLE t1; INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),1); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),2); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),3); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),4); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),5); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),6); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),7); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),8); ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500); UPDATE t1 SET c3 = CONCAT(REPEAT('c', 500)); DROP TABLE t1; # Scenario 62 # While updating lob columns on a rebuilt table which had INSTANT cols CREATE TABLE t1 (c1 CHAR(10)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 VALUES ("r1c1"); SELECT * FROM t1; c1 r1c1 ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; ALTER TABLE t1 ADD COLUMN c3 TEXT; UPDATE t1 SET c3 = CONCAT(REPEAT('z', 8204)); ALTER TABLE t1 ADD PRIMARY KEY (c3(10)), ADD FULLTEXT KEY idx (c3); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT * FROM t1; c1 c3 r1c1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz DROP TABLE t1; # Scenario 63 # Truncate of a partition/partition table CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 INSERT INTO t1 values (5, 50), (15, 150); SELECT * FROM t1 PARTITION (p1); a b 5 50 SELECT * FROM t1 PARTITION (p2); a b 15 150 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c 3 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(30)); INSERT INTO t1 (a, b) values (25, 250); SELECT * FROM t1 PARTITION (p1); a b c 5 50 100 SELECT * FROM t1 PARTITION (p2); a b c 15 150 100 SELECT * FROM t1 PARTITION (p3); a b c 25 250 100 alter table t1 drop column b, algorithm=instant; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 5 100 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 TRUNCATE TABLE t1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c TRUNCATE TABLE t1; ALTER TABLE t1 TRUNCATE PARTITION p1; ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c DROP TABLE t1; # Scenario 64 # When nullbitmap size on physical record changes after INSTANT ADD columns. CREATE TABLE t1 (c0 char(9) KEY, c1 char (10), c2 char(20), c3 char(30), c4 char(40), c5 char(50), c6 char(60), c7 char(70), c8 char(80)) ROW_FORMAT=REDUNDANT; SET GLOBAL innodb_limit_optimistic_insert_debug=2; Insert into t1 values( REPEAT("a", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Insert into t1 values( REPEAT("b", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Alter table t1 add column c9 char(90); Insert into t1 values( REPEAT("c", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79), REPEAT("9", 89)); SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP table t1; # Scenario 65 # Truncate all partition shall reset INSTANT Metadata CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (90, 1, 1, "1"), (190, 1, 1, "2"), (290, 1, 1, "3"), (390, 1, 1, "3"); Alter table t1 add column c5 int, algorithm=INSTANT; Alter table t1 drop column c3, algorithm=INSTANT; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 8 MYSQL_TYPE_LONG 0 SE physical_pos=5;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 ALTER TABLE t1 TRUNCATE partition p0, p1, p2, p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 DROP TABLE t1; # Scenario 66 # Purge crash create table t1 (c0 INT KEY, c1 varchar(10), c2 varchar(10)) ROW_FORMAT=REDUNDANT; alter table t1 add column c4 char(10), algorithm=instant; alter table t1 add index idx (c4); insert into t1 values (1, "r1c1", "r1c2", "c4"); SET GLOBAL innodb_purge_stop_now=ON; Update t1 set c2="r122222", c4="c44"; alter table t1 drop column c2, algorithm=instant; SET GLOBAL innodb_purge_run_now=ON; DROP TABLE t1; # Scenario 66 # Inplace update is failing # INSTANT ADD and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 # Disable flush to make sure REDO is applied after restart Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 DROP TABLE t1; # INSTANT DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 drop column c1, algorithm=instant; Insert into t1 values ("r2c2", "r2c3"); Select * from t1; c2 c3 r1c2 r1c3 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c2 c3 r112 r1c3 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c2 c3 r112 r1c3 r212 r2c3 DROP TABLE t1; # INSTANT ADD and DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=REDUNDANT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 Alter table t1 drop column c1, algorithm=instant; Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c2", "r3c3"); Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 r3c4 r3c2 r3c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Update t1 set c2="r312" where c2="r3c2"; Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 # Kill the running server # Kill the server # Start the server Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 DROP TABLE t1; ########### # CLEANUP # ########### ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ SET SESSION debug= '+d,show_dropped_column'; # Scenario 1 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 drop table t1; # Scenario 2 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 3 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1, algorithm=INSTANT; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 drop column c2, algorithm=INSTANT; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 4 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 5 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 char(10) default "def_c3" after c1; select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 insert into t1 value ("r2c1", "r2c3", "r2c2"); alter table t1 drop column c2; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 6 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c2 char(10) default "def_c3", drop column c2; select * from t1; c1 c2 r1c1 def_c3 insert into t1 value ("r2c1", "r2c2"); select * from t1; c1 c2 r1c1 def_c3 r2c1 r2c2 drop table t1; # Scenario 7 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 int after c1; select * from t1; c1 c3 c2 r1c1 NULL r1c2 insert into t1 value ("r2c1", 3, "r2c2"); select * from t1; c1 c3 c2 r1c1 NULL r1c2 r2c1 3 r2c2 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 NULL r2c1 3 drop table t1; # Scenario 8 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", NULL, "r1c3"); select * from t1; c1 c2 c3 r1c1 NULL r1c3 alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 9 CREATE TABLE t1 (C1 char(10), C2 char(10), C3 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c1; select * from t1; C2 C3 r1c2 r1c3 alter table t1 drop column c3; select * from t1; C2 r1c2 drop table t1; # Scenario 10 create table t1 (c1 char(10) KEY, c2 char(10), key sec_idx(c2)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3"; select * from t1; c1 c2 c3 r1c1 r1c2 def_c3 insert into t1 values ("r2c1", "r2c2", "r3c3"); drop table t1; # Scenario 11 CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DROP INDEX idx1; DROP TABLE t1; # Scenario 12 CREATE TABLE t1(c2 char(100)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD FULLTEXT INDEX `ft_idx` (c2); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID DROP TABLE t1; # Scenario 13 CREATE TABLE t1(a INT) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(1); ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT ); ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace; DROP TABLE t1; # Scenario 14 CREATE TABLE t1 (col1 int(11) DEFAULT NULL, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, col6 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, col7 int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, col8 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, col9 text, extra int(11) DEFAULT NULL) ROW_FORMAT=DYNAMIC; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. ALTER TABLE t1 DROP COLUMN col7; ALTER TABLE t1 DROP COLUMN col6, DROP COLUMN col9; DROP TABLE t1; # Scenario 15 create table t1(a text, b text, fulltext(a,b)); insert into t1 values('text1', 'text2'); insert into t1 values('test1', 'test2'); alter table t1 add fulltext(b); drop table t1; # Scenario 16 CREATE TABLE t1 (a serial key, b text) ROW_FORMAT=DYNAMIC; INSERT INTO t1(b) VALUES(repeat("a short string - ",5)); INSERT INTO t1(b) VALUES(repeat("a long string - ",50)); INSERT INTO t1(b) SELECT b FROM t1; ALTER TABLE t1 ADD COLUMN (c int), algorithm=INSTANT; ALTER TABLE t1 ADD INDEX c (c), algorithm=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD INDEX c (c), algorithm=INPLACE; DROP TABLE t1; # Scenario 17 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20), c4 char(20)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE test.t1 ADD c6 char(20), DROP COLUMN c3; DROP TABLE t1; # Scenario 18 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE; Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID Warning 1814 InnoDB: Tablespace has been discarded for table 't1' ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't1' DROP TABLE t1; # Scenario 19 CREATE TABLE t1 (a INT, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 FIRST; ALTER TABLE t1 CHANGE aa e INT; INSERT INTO t1 VALUES (1,1,1,1,1); ALTER TABLE t1 ADD COLUMN a INT DEFAULT 1 FIRST, DROP COLUMN a, CHANGE b b INT; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 AFTER a, DROP COLUMN b, CHANGE c c INT; ALTER TABLE t1 CHANGE aa b INT; DROP TABLE t1; # Scenario 20 CREATE TABLE t1 (a varchar(512) NOT NULL, b varchar(512) NOT NULL, FULLTEXT KEY fts_idx (a)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DROP KEY fts_idx; ALTER TABLE t1 ADD COLUMN b_hash char(64) AS(b) VIRTUAL NOT NULL; DROP TABLE t1; # Scenario 21 CREATE PROCEDURE test.fill_table (IN start INT) BEGIN SET @idx =start; WHILE (@idx > 0) DO INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SET @idx = @idx - 1; END WHILE; END| CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; call fill_table(400); DROP PROCEDURE test.fill_table; DROP TABLE t1; # Scenario 22 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; SET GLOBAL innodb_limit_optimistic_insert_debug=2; INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SELECT * FROM t1; id v 1 aaaaaaaaaaaaa 2 aaaaaaaaaaaaa 3 aaaaaaaaaaaaa SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP TABLE t1; # Scenario 23 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 add column c3 char(10) default "newnewnew", drop column c3; select * from t1; c1 c2 c3 r1c1 r1c2 newnewnew r2c1 r2c2 newnewnew DROP TABLE t1; # Scenario 24 CREATE TABLE t1 (c1 char(10)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; INSERT INTO t1 (c1) VALUES("aaaa"); select * from t1; c1 aaaa check table t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; # Scenario 25 CREATE TABLE t1 (c1 int, c2 char(10)) ROW_FORMAT=DYNAMIC PARTITION BY HASH (c1) PARTITIONS 2; alter table t1 drop column c2; DROP TABLE t1; # Scenario 26 CREATE TABLE t1 (c1 char(10) KEY, c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 values ("row1_c1", "row1_c2"); ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); ALTER TABLE t1 force; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c5 INT after c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; DROP TABLE t1; # Scenario 27 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN c INT NOT NULL; ALTER TABLE t1 ADD COLUMN d VARCHAR(100); INSERT INTO t1 VALUES(0, 5, 20, 'Hello world'); DROP TABLE t1; # Scenario 28 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); SELECT * from t1; a b 1 1 2 2 3 3 4 4 5 5 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world'; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 100 204 Hello world 5 5 100 205 Hello world UPDATE t1 SET c = 200 WHERE a > 3; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 200 404 Hello world 5 5 200 405 Hello world DROP TABLE t1; # Scenario 29 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 VALUES(0, 1); SELECT * from t1; c1 c2 1 1 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 1 1 NULL ALTER TABLE t1 ADD COLUMN c4 CHAR(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 1 1 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 1 1 2 NULL DROP TABLE t1; # Scenario 30 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * from t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 r1c1 r1c2 NULL ALTER TABLE t1 ADD COLUMN c4 char(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 2 NULL DROP TABLE t1; # Scenario 31 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 char(10) default "def_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 def_c3 UPDATE t1 SET c3 = "new_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new_c3 UPDATE t1 SET c3 = "new2_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new2_c3 DROP TABLE t1; # Scenario 32 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4); ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 5 1 2 5 2 3 5 3 4 5 UPDATE t1 SET c = b WHERE b <= 2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 1 1 2 2 2 3 5 3 4 5 DROP TABLE t1; # Scenario 33 CREATE TABLE t1(c1 longtext) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES (repeat('x', 8098)); SELECT SUBSTRING(c1, 1000, 32) FROM t1; SUBSTRING(c1, 1000, 32) xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL BEGIN; UPDATE t1 SET c2 = 0; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0 ROLLBACK; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL DROP TABLE t1; # Scenario 34 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 INSERT INTO t1 VALUES("r3c1", "r3c2", "r3c3"); START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 DROP TABLE t1; # Scenario 35 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10, algorithm=INSTANT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK START TRANSACTION; INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); START TRANSACTION; UPDATE t1 SET b = 10; SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 DROP TABLE t1; # Scenario 36 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 DROP TABLE t1; # Scenario 37 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; UPDATE t1 SET c3 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp DROP TABLE t1; # Scenario 38 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 4 0 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(col3); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 39 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 6 0 3 3 3 test/t1#p#p1 6 0 3 3 3 test/t1#p#p2 6 0 3 3 3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(c3); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 40 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 41 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ADD KEY idx3(col3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 42 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INSTANT, DROP COLUMN c2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 DROP TABLE t1; # Scenario 43 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8); SELECT * FROM t1; a b 3 3 6 6 1 1 4 4 7 7 2 2 5 5 8 8 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 3 3 5 6 6 5 1 1 5 4 4 5 7 7 5 2 2 5 5 5 5 8 8 5 ALTER TABLE t1 ADD PARTITION PARTITIONS 10; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 ALTER TABLE t1 ADD KEY(b); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 DROP TABLE t1; # Scenario 44 CREATE TABLE t1 (a BLOB, b BLOB, c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, h VARCHAR(10) DEFAULT NULL) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk"); CREATE INDEX idx ON t1(c(100)); SELECT length(c) FROM t1; length(c) 32000 START TRANSACTION; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); ROLLBACK; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); START TRANSACTION; UPDATE t1 SET a = REPEAT('m', 16000) WHERE a like "aaa%"; ROLLBACK; DROP TABLE t1; # Scenario 45 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); INSERT INTO t1 VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); INSERT INTO t1 VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); ALTER TABLE t1 DROP COLUMN c, algorithm=inplace; ALTER TABLE t1 DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace; DROP TABLE t1; # Scenario 46 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6); ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10; SELECT * FROM t1; a b c d 1 1 1 10 2 2 2 10 3 3 3 10 6 6 6 10 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done'; ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;; SET DEBUG_SYNC = 'now WAIT_FOR altered'; INSERT INTO t1(a, b, c) VALUES(7, 7, 7); UPDATE t1 SET a = 10 WHERE a = 1; DELETE FROM t1 WHERE a = 2; UPDATE t1 SET c = 10 WHERE a = 3; UPDATE t1 SET d = 20 WHERE a = 5; UPDATE t1 SET a = 8, d = 20 WHERE a = 6; SET DEBUG_SYNC = 'now SIGNAL dmls_done'; SELECT * FROM t1; a b d 3 3 10 7 7 10 8 6 20 10 1 10 DROP TABLE t1; # Scenario 47 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (PARTITION p21 VALUES LESS THAN(15), PARTITION p22 VALUES LESS THAN(20)); SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 DROP TABLE t1; # Scenario 48 CREATE TABLE t1 (c1 integer PRIMARY KEY AUTO_INCREMENT, c2 integer NULL UNIQUE) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN c3 int; ALTER TABLE t1 DROP COLUMN c3; ALTER TABLE t1 ADD COLUMN c4 int DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4 c4_new int NULL DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4_new c4_new int NULL DEFAULT 1; DROP TABLE t1; # Scenario 49 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=DYNAMIC; INSERT INTO t1 values ("row1_c1", "a"); SELECT * from t1; c1 c2 row1_c1 a ALTER TABLE t1 ADD COLUMN c3 enum('x', 'y', 'z'), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 row1_c1 a NULL INSERT INTO t1 values ("row2_c2", "b", "y"); SELECT * from t1; c1 c2 c3 row1_c1 a NULL row2_c2 b y ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * from t1; c1 c3 row1_c1 NULL row2_c2 y DROP TABLE t1; # Scenario 50 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DROP COLUMN c2; truncate table t1; truncate table t1; truncate table t1; DROP TABLE t1; # Scenario 51 CREATE TABLE t1 (c1 int KEY, c2 int) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30)); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; # Scenario 52 CREATE TABLE t1(c1 int, c2 int, c3 int, c4 DOUBLE GENERATED ALWAYS AS (c1+c2) STORED, c5 DOUBLE GENERATED ALWAYS AS (c1-c2) VIRTUAL) ROW_FORMAT=DYNAMIC;; INSERT INTO t1(c1,c2,c3) VALUES (1,1,1); SELECT * FROM t1; c1 c2 c3 c4 c5 1 1 1 2 0 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ERROR HY000: Column 'c2' has a generated column dependency. ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c4 c5 1 1 2 0 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 1 1 0 ALTER TABLE t1 ADD COLUMN c6 DOUBLE GENERATED ALWAYS AS (2*c1+c2) VIRTUAL, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c7 DOUBLE GENERATED ALWAYS AS (c1+2*c2) STORED, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c8 DOUBLE GENERATED ALWAYS AS (c1+2*c2) VIRTUAL FIRST, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 1 1 NULL 0 3 ALTER TABLE t1 DROP column c3, add column c10 int as (c1+c2) virtual, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 c10 1 1 0 3 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 c10 1 1 NULL 0 3 2 ALTER TABLE t1 DROP column c3, drop column c6, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c10 1 1 0 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 ALTER TABLE t1 DROP column c3, add column c11 int as (c1+c2) virtual first, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 DROP TABLE t1; # Scenario 53 # Table with functional index. CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT KEY, c1 varchar(10) DEFAULT NULL, c2 varchar(10) DEFAULT NULL, INDEX idx((concat(c1, "c1")))) ROW_FORMAT=DYNAMIC;; INSERT INTO t1(c1, c2) values("abc", "def"); SELECT * FROM t1; id c1 c2 1 abc def ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual after id, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; id c1 1 abc DROP TABLE t1; # Scenarion 54 CREATE TABLE t1 (c1 TEXT, c2 INT, PRIMARY KEY (c1(1))) ROW_FORMAT=DYNAMIC;; SELECT * FROM t1; c1 c2 REPLACE INTO t1(c1) VALUES (''); REPLACE INTO t1(c1) VALUES (''); SELECT * From t1; c1 c2 NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; REPLACE INTO t1(c1) VALUES (''); DROP TABLE t1; # Scenario 55 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (60)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p5 INTO ( PARTITION p51 VALUES LESS THAN (50), PARTITION p52 VALUES LESS THAN (60) ); DROP TABLE t1; # Scenario 56 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, PRIMARY KEY(c1, c4(10))) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 VALUES (1, 2, 3, "abcdefghijklmnop"); SELECT * FROM t1; c1 c2 c3 c4 1 2 3 abcdefghijklmnop ALTER TABLE t1 ADD COLUMN c5 INT, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c4 c5 1 2 3 abcdefghijklmnop NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c3 c4 c5 1 3 abcdefghijklmnop NULL DROP TABLE t1; # Scenario 57 CREATE TABLE t1 (c1 CHAR(20), c2 CHAR(20), PRIMARY KEY PK(c1(4))) ROW_FORMAT=DYNAMIC;; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 DROP TABLE t1; # Scenario 58 # Validating record in table with INSTANT ADD/DROP columns CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES ("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r3c2"); ALTER TABLE t1 ADD COLUMN c4 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r4c2"); SELECT * From t1; c2 c3 c4 r1c2 NULL NULL r2c2 r2c3 NULL r3c2 NULL NULL r4c2 NULL NULL DROP TABLE t1; CREATE TABLE t1 (id INT KEY, c2 CHAR(10)) ROW_FORMAT=DYNAMIC PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (40), PARTITION P1 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(1,"r1c2"); INSERT INTO t1 VALUES(2,"r2c2"); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 CHAR(10), ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P0, P1 INTO (PARTITION P1 VALUES LESS THAN (50)); SELECT * FROM t1; id c2 1 NULL 2 NULL DROP TABLE t1; # Scenario 59 # A scenario when #fields were not updated correctly in REDUNDANT record for not-inplace update. CREATE TABLE t1 (c1 VARCHAR(20), c2 CHAR(20)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1="r1c11111111111"; SELECT * FROM t1; c1 r1c11111111111 DROP TABLE t1; # Scenario 60 # When a new partition is added in a table with instant columns and virtual column, # during inheriting INSTANT metadat for new partitions, virtual columns were not skipped. CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300)); ALTER TABLE t1 ADD COLUMN col5 INT, ADD COLUMN col6 INT GENERATED ALWAYS AS (col5 + 1) VIRTUAL, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN col2, algorithm=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p2 INTO(PARTITION p21 VALUES LESS THAN (250), PARTITION p22 VALUES LESS THAN (300)); DROP TABLE t1; # Scenario 61 # When a table is truncated, it's instant metadata is reset. It shall # also reset instant metadata of columns. CREATE TABLE t1 (c1 VARCHAR(1008)) ROW_FORMAT=DYNAMIC; ALTER TABLE t1 ADD COLUMN c2 INT; TRUNCATE TABLE t1; INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),1); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),2); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),3); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),4); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),5); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),6); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),7); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),8); ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500); UPDATE t1 SET c3 = CONCAT(REPEAT('c', 500)); DROP TABLE t1; # Scenario 62 # While updating lob columns on a rebuilt table which had INSTANT cols CREATE TABLE t1 (c1 CHAR(10)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 VALUES ("r1c1"); SELECT * FROM t1; c1 r1c1 ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; ALTER TABLE t1 ADD COLUMN c3 TEXT; UPDATE t1 SET c3 = CONCAT(REPEAT('z', 8204)); ALTER TABLE t1 ADD PRIMARY KEY (c3(10)), ADD FULLTEXT KEY idx (c3); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT * FROM t1; c1 c3 r1c1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz DROP TABLE t1; # Scenario 63 # Truncate of a partition/partition table CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 INSERT INTO t1 values (5, 50), (15, 150); SELECT * FROM t1 PARTITION (p1); a b 5 50 SELECT * FROM t1 PARTITION (p2); a b 15 150 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c 3 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(30)); INSERT INTO t1 (a, b) values (25, 250); SELECT * FROM t1 PARTITION (p1); a b c 5 50 100 SELECT * FROM t1 PARTITION (p2); a b c 15 150 100 SELECT * FROM t1 PARTITION (p3); a b c 25 250 100 alter table t1 drop column b, algorithm=instant; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 5 100 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 TRUNCATE TABLE t1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c TRUNCATE TABLE t1; ALTER TABLE t1 TRUNCATE PARTITION p1; ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c DROP TABLE t1; # Scenario 64 # When nullbitmap size on physical record changes after INSTANT ADD columns. CREATE TABLE t1 (c0 char(9) KEY, c1 char (10), c2 char(20), c3 char(30), c4 char(40), c5 char(50), c6 char(60), c7 char(70), c8 char(80)) ROW_FORMAT=DYNAMIC; SET GLOBAL innodb_limit_optimistic_insert_debug=2; Insert into t1 values( REPEAT("a", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Insert into t1 values( REPEAT("b", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Alter table t1 add column c9 char(90); Insert into t1 values( REPEAT("c", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79), REPEAT("9", 89)); SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP table t1; # Scenario 65 # Truncate all partition shall reset INSTANT Metadata CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=DYNAMIC PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (90, 1, 1, "1"), (190, 1, 1, "2"), (290, 1, 1, "3"), (390, 1, 1, "3"); Alter table t1 add column c5 int, algorithm=INSTANT; Alter table t1 drop column c3, algorithm=INSTANT; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 8 MYSQL_TYPE_LONG 0 SE physical_pos=5;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 ALTER TABLE t1 TRUNCATE partition p0, p1, p2, p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 DROP TABLE t1; # Scenario 66 # Purge crash create table t1 (c0 INT KEY, c1 varchar(10), c2 varchar(10)) ROW_FORMAT=DYNAMIC; alter table t1 add column c4 char(10), algorithm=instant; alter table t1 add index idx (c4); insert into t1 values (1, "r1c1", "r1c2", "c4"); SET GLOBAL innodb_purge_stop_now=ON; Update t1 set c2="r122222", c4="c44"; alter table t1 drop column c2, algorithm=instant; SET GLOBAL innodb_purge_run_now=ON; DROP TABLE t1; # Scenario 66 # Inplace update is failing # INSTANT ADD and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 # Disable flush to make sure REDO is applied after restart Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 DROP TABLE t1; # INSTANT DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 drop column c1, algorithm=instant; Insert into t1 values ("r2c2", "r2c3"); Select * from t1; c2 c3 r1c2 r1c3 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c2 c3 r112 r1c3 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c2 c3 r112 r1c3 r212 r2c3 DROP TABLE t1; # INSTANT ADD and DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=DYNAMIC; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 Alter table t1 drop column c1, algorithm=instant; Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c2", "r3c3"); Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 r3c4 r3c2 r3c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Update t1 set c2="r312" where c2="r3c2"; Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 # Kill the running server # Kill the server # Start the server Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 DROP TABLE t1; ########### # CLEANUP # ########### ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ SET SESSION debug= '+d,show_dropped_column'; # Scenario 1 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 drop table t1; # Scenario 2 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 3 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1, algorithm=INSTANT; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 drop column c2, algorithm=INSTANT; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 4 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", "r1c2", "r1c3"); select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 5 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 char(10) default "def_c3" after c1; select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 insert into t1 value ("r2c1", "r2c3", "r2c2"); alter table t1 drop column c2; select * from t1; c1 c3 r1c1 def_c3 r2c1 r2c3 drop table t1; # Scenario 6 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c2 char(10) default "def_c3", drop column c2; select * from t1; c1 c2 r1c1 def_c3 insert into t1 value ("r2c1", "r2c2"); select * from t1; c1 c2 r1c1 def_c3 r2c1 r2c2 drop table t1; # Scenario 7 create table t1 (c1 char(10), c2 char(10)); insert into t1 values ("r1c1", "r1c2"); alter table t1 add column c3 int after c1; select * from t1; c1 c3 c2 r1c1 NULL r1c2 insert into t1 value ("r2c1", 3, "r2c2"); select * from t1; c1 c3 c2 r1c1 NULL r1c2 r2c1 3 r2c2 alter table t1 drop column c2; select * from t1; c1 c3 r1c1 NULL r2c1 3 drop table t1; # Scenario 8 create table t1 (c1 char(10), c2 char(10), c3 char(10)); insert into t1 values ("r1c1", NULL, "r1c3"); select * from t1; c1 c2 c3 r1c1 NULL r1c3 alter table t1 drop column c2; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 1 3 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 0 0 0 !hidden!_dropped_v1_p4_c2 5 13 16711934 0 0 1 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible physical_pos=5;table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v1_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1; select * from t1; c1 c3 r1c1 r1c3 drop table t1; # Scenario 9 CREATE TABLE t1 (C1 char(10), C2 char(10), C3 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 values ("r1c1", "r1c2", "r1c3"); alter table t1 drop column c1; select * from t1; C2 C3 r1c2 r1c3 alter table t1 drop column c3; select * from t1; C2 r1c2 drop table t1; # Scenario 10 create table t1 (c1 char(10) KEY, c2 char(10), key sec_idx(c2)); insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3"; select * from t1; c1 c2 c3 r1c1 r1c2 def_c3 insert into t1 values ("r2c1", "r2c2", "r3c3"); drop table t1; # Scenario 11 CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 DROP INDEX idx1; DROP TABLE t1; # Scenario 12 CREATE TABLE t1(c2 char(100)) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD FULLTEXT INDEX `ft_idx` (c2); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID DROP TABLE t1; # Scenario 13 CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(1); ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT ); ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace; DROP TABLE t1; # Scenario 14 CREATE TABLE t1 (col1 int(11) DEFAULT NULL, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, col6 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, col7 int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, col8 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, col9 text, extra int(11) DEFAULT NULL) ROW_FORMAT=COMPACT; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. ALTER TABLE t1 DROP COLUMN col7; ALTER TABLE t1 DROP COLUMN col6, DROP COLUMN col9; DROP TABLE t1; # Scenario 15 create table t1(a text, b text, fulltext(a,b)); insert into t1 values('text1', 'text2'); insert into t1 values('test1', 'test2'); alter table t1 add fulltext(b); drop table t1; # Scenario 16 CREATE TABLE t1 (a serial key, b text) ROW_FORMAT=COMPACT; INSERT INTO t1(b) VALUES(repeat("a short string - ",5)); INSERT INTO t1(b) VALUES(repeat("a long string - ",50)); INSERT INTO t1(b) SELECT b FROM t1; ALTER TABLE t1 ADD COLUMN (c int), algorithm=INSTANT; ALTER TABLE t1 ADD INDEX c (c), algorithm=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 ADD INDEX c (c), algorithm=INPLACE; DROP TABLE t1; # Scenario 17 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20), c4 char(20)) ROW_FORMAT=COMPACT; ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE test.t1 ADD c6 char(20), DROP COLUMN c3; DROP TABLE t1; # Scenario 18 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) ROW_FORMAT=COMPACT; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE; Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID Warning 1814 InnoDB: Tablespace has been discarded for table 't1' ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't1' DROP TABLE t1; # Scenario 19 CREATE TABLE t1 (a INT, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 FIRST; ALTER TABLE t1 CHANGE aa e INT; INSERT INTO t1 VALUES (1,1,1,1,1); ALTER TABLE t1 ADD COLUMN a INT DEFAULT 1 FIRST, DROP COLUMN a, CHANGE b b INT; ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 AFTER a, DROP COLUMN b, CHANGE c c INT; ALTER TABLE t1 CHANGE aa b INT; DROP TABLE t1; # Scenario 20 CREATE TABLE t1 (a varchar(512) NOT NULL, b varchar(512) NOT NULL, FULLTEXT KEY fts_idx (a)) ROW_FORMAT=COMPACT; ALTER TABLE t1 DROP KEY fts_idx; ALTER TABLE t1 ADD COLUMN b_hash char(64) AS(b) VIRTUAL NOT NULL; DROP TABLE t1; # Scenario 21 CREATE PROCEDURE test.fill_table (IN start INT) BEGIN SET @idx =start; WHILE (@idx > 0) DO INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SET @idx = @idx - 1; END WHILE; END| CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; call fill_table(400); DROP PROCEDURE test.fill_table; DROP TABLE t1; # Scenario 22 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; SET GLOBAL innodb_limit_optimistic_insert_debug=2; INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SELECT * FROM t1; id v 1 aaaaaaaaaaaaa 2 aaaaaaaaaaaaa 3 aaaaaaaaaaaaa SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP TABLE t1; # Scenario 23 create table t1 (c1 char(10), c2 char(10)); # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c2 1 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_ROW_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; insert into t1 values ("r1c1", "r1c2"); select * from t1; c1 c2 r1c1 r1c2 alter table t1 add column c3 char(10) default "def_c3" after c1; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 13 16711934 0 0 0 c3 1 13 16711934 1 1 0 c2 2 13 16711934 0 0 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 2 MYSQL_TYPE_STRING 0 Visible default=6465665f633320202020;physical_pos=5;table_id=TABLE_ID;version_added=1; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; insert into t1 values ("r2c1", "r2c3", "r2c2"); select * from t1; c1 c3 c2 r1c1 def_c3 r1c2 r2c1 r2c3 r2c2 alter table t1 add column c3 char(10) default "newnewnew", drop column c3; select * from t1; c1 c2 c3 r1c1 r1c2 newnewnew r2c1 r2c2 newnewnew DROP TABLE t1; # Scenario 24 CREATE TABLE t1 (c1 char(10)) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; INSERT INTO t1 (c1) VALUES("aaaa"); select * from t1; c1 aaaa check table t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; # Scenario 25 CREATE TABLE t1 (c1 int, c2 char(10)) ROW_FORMAT=COMPACT PARTITION BY HASH (c1) PARTITIONS 2; alter table t1 drop column c2; DROP TABLE t1; # Scenario 26 CREATE TABLE t1 (c1 char(10) KEY, c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 values ("row1_c1", "row1_c2"); ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT; INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT; INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3"); ALTER TABLE t1 force; SELECT * FROM t1; c1 c4 c2 c3 row1_c1 NULL row1_c2 default_c3 row2_c1 NULL row2_c2 row2_c3 row3_c1 row3_c4 row3_c2 row3_c3 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c5 INT after c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; DROP TABLE t1; # Scenario 27 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN c INT NOT NULL; ALTER TABLE t1 ADD COLUMN d VARCHAR(100); INSERT INTO t1 VALUES(0, 5, 20, 'Hello world'); DROP TABLE t1; # Scenario 28 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); SELECT * from t1; a b 1 1 2 2 3 3 4 4 5 5 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world'; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 100 204 Hello world 5 5 100 205 Hello world UPDATE t1 SET c = 200 WHERE a > 3; SELECT * from t1; a b c d e 1 1 100 201 Hello world 2 2 100 202 Hello world 3 3 100 203 Hello world 4 4 200 404 Hello world 5 5 200 405 Hello world DROP TABLE t1; # Scenario 29 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ROW_FORMAT=COMPACT;; INSERT INTO t1 VALUES(0, 1); SELECT * from t1; c1 c2 1 1 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 1 1 NULL ALTER TABLE t1 ADD COLUMN c4 CHAR(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 1 1 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 1 1 2 NULL DROP TABLE t1; # Scenario 30 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * from t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 r1c1 r1c2 NULL ALTER TABLE t1 ADD COLUMN c4 char(100), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 NULL NULL UPDATE t1 SET c3 = 2; SELECT * from t1; c1 c2 c3 c4 r1c1 r1c2 2 NULL DROP TABLE t1; # Scenario 31 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 char(10) default "def_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 def_c3 UPDATE t1 SET c3 = "new_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new_c3 UPDATE t1 SET c3 = "new2_c3"; SELECT * from t1; c1 c2 c3 r1c1 r1c2 new2_c3 DROP TABLE t1; # Scenario 32 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4); ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 5 1 2 5 2 3 5 3 4 5 UPDATE t1 SET c = b WHERE b <= 2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 0 1 1 1 2 2 2 3 5 3 4 5 DROP TABLE t1; # Scenario 33 CREATE TABLE t1(c1 longtext) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES (repeat('x', 8098)); SELECT SUBSTRING(c1, 1000, 32) FROM t1; SUBSTRING(c1, 1000, 32) xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL BEGIN; UPDATE t1 SET c2 = 0; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0 ROLLBACK; SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1; SUBSTRING(c1, 1000, 32) c2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx NULL DROP TABLE t1; # Scenario 34 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 INSERT INTO t1 VALUES("r3c1", "r3c2", "r3c3"); START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 r3c1 temp r3c3 DROP TABLE t1; # Scenario 35 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5); ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10, algorithm=INSTANT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK START TRANSACTION; INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 1 10 2 2 10 3 3 10 4 4 10 5 5 10 6 6 20 7 7 20 INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20); START TRANSACTION; UPDATE t1 SET b = 10; SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c1 1 10 10 2 10 10 3 10 10 4 10 10 5 10 10 6 10 20 7 10 20 8 10 20 9 10 20 DROP TABLE t1; # Scenario 36 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 START TRANSACTION; UPDATE t1 SET c2 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 temp c3_def r2c1 temp r2c3 DROP TABLE t1; # Scenario 37 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; START TRANSACTION; UPDATE t1 SET c3 = "temp"; SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp SET SESSION DEBUG="+d,crash_commit_after"; COMMIT; ERROR HY000: Lost connection to MySQL server during query # restart CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 temp r2c1 r2c2 temp DROP TABLE t1; # Scenario 38 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=COMPACT PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 4 0 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(col3); SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0; Expect 3 0 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 39 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 6 0 3 3 3 test/t1#p#p1 6 0 3 3 3 test/t1#p#p2 6 0 3 3 3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 ADD KEY idx3(c3); SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%"; NAME N_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0_a 7 1 3 4 4 test/t1#p#p0_b 7 1 3 4 4 test/t1#p#p1 7 1 3 4 4 test/t1#p#p2 7 1 3 4 4 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 40 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 DROP TABLE t1; # Scenario 41 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=COMPACT PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg'); ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL ALTER TABLE t1 ADD KEY idx3(col3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; col1 col2 col3 col4 col5 col6 1 2 3 abcdefg def NULL 100 200 300 qwerty def NULL 200 300 400 asdfg def NULL DROP TABLE t1; # Scenario 42 CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);; INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3"); SELECT * FROM t1; c1 c2 c3 1 r1c2 r1c3 100 r2c2 r2c3 200 r3c2 r3c3 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4"; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 c3 col4 1 r1c2 r1c3 def_4 100 r2c2 r2c3 def_4 200 r3c2 r3c3 def_4 ALTER TABLE t1 ALGORITHM INSTANT, DROP COLUMN c2; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100)); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table); ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 ALTER TABLE t1 ADD KEY idx3(c3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c3 col4 1 r1c3 def_4 100 r2c3 def_4 200 r3c3 def_4 DROP TABLE t1; # Scenario 43 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;; INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8); SELECT * FROM t1; a b 3 3 6 6 1 1 4 4 7 7 2 2 5 5 8 8 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 3 3 5 6 6 5 1 1 5 4 4 5 7 7 5 2 2 5 5 5 5 8 8 5 ALTER TABLE t1 ADD PARTITION PARTITIONS 10; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 ALTER TABLE t1 ADD KEY(b); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b c 1 1 5 2 2 5 3 3 5 4 4 5 5 5 5 6 6 5 7 7 5 8 8 5 DROP TABLE t1; # Scenario 44 CREATE TABLE t1 (a BLOB, b BLOB, c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, h VARCHAR(10) DEFAULT NULL) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk"); CREATE INDEX idx ON t1(c(100)); SELECT length(c) FROM t1; length(c) 32000 START TRANSACTION; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); ROLLBACK; INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); START TRANSACTION; UPDATE t1 SET a = REPEAT('m', 16000) WHERE a like "aaa%"; ROLLBACK; DROP TABLE t1; # Scenario 45 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); INSERT INTO t1 VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); INSERT INTO t1 VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); ALTER TABLE t1 DROP COLUMN c, algorithm=inplace; ALTER TABLE t1 DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace; DROP TABLE t1; # Scenario 46 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6); ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10; SELECT * FROM t1; a b c d 1 1 1 10 2 2 2 10 3 3 3 10 6 6 6 10 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done'; ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;; SET DEBUG_SYNC = 'now WAIT_FOR altered'; INSERT INTO t1(a, b, c) VALUES(7, 7, 7); UPDATE t1 SET a = 10 WHERE a = 1; DELETE FROM t1 WHERE a = 2; UPDATE t1 SET c = 10 WHERE a = 3; UPDATE t1 SET d = 20 WHERE a = 5; UPDATE t1 SET a = 8, d = 20 WHERE a = 6; SET DEBUG_SYNC = 'now SIGNAL dmls_done'; SELECT * FROM t1; a b d 3 3 10 7 7 10 8 6 20 10 1 10 DROP TABLE t1; # Scenario 47 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (PARTITION p21 VALUES LESS THAN(15), PARTITION p22 VALUES LESS THAN(20)); SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1; NAME POS MTYPE PRTYPE LEN HAS_DEFAULT DEFAULT_VALUE VERSION_ADDED VERSION_DROPPED PHYSICAL_POS c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 c 2 6 1027 4 1 80000064 1 0 4 DROP TABLE t1; # Scenario 48 CREATE TABLE t1 (c1 integer PRIMARY KEY AUTO_INCREMENT, c2 integer NULL UNIQUE) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN c3 int; ALTER TABLE t1 DROP COLUMN c3; ALTER TABLE t1 ADD COLUMN c4 int DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4 c4_new int NULL DEFAULT 1; ALTER TABLE t1 CHANGE COLUMN c4_new c4_new int NULL DEFAULT 1; DROP TABLE t1; # Scenario 49 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=COMPACT; INSERT INTO t1 values ("row1_c1", "a"); SELECT * from t1; c1 c2 row1_c1 a ALTER TABLE t1 ADD COLUMN c3 enum('x', 'y', 'z'), ALGORITHM=INSTANT; SELECT * from t1; c1 c2 c3 row1_c1 a NULL INSERT INTO t1 values ("row2_c2", "b", "y"); SELECT * from t1; c1 c2 c3 row1_c1 a NULL row2_c2 b y ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * from t1; c1 c3 row1_c1 NULL row2_c2 y DROP TABLE t1; # Scenario 50 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT; ALTER TABLE t1 DROP COLUMN c2; truncate table t1; truncate table t1; truncate table t1; DROP TABLE t1; # Scenario 51 CREATE TABLE t1 (c1 int KEY, c2 int) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30)); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; # Scenario 52 CREATE TABLE t1(c1 int, c2 int, c3 int, c4 DOUBLE GENERATED ALWAYS AS (c1+c2) STORED, c5 DOUBLE GENERATED ALWAYS AS (c1-c2) VIRTUAL) ROW_FORMAT=COMPACT;; INSERT INTO t1(c1,c2,c3) VALUES (1,1,1); SELECT * FROM t1; c1 c2 c3 c4 c5 1 1 1 2 0 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ERROR HY000: Column 'c2' has a generated column dependency. ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c4 c5 1 1 2 0 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 1 1 0 ALTER TABLE t1 ADD COLUMN c6 DOUBLE GENERATED ALWAYS AS (2*c1+c2) VIRTUAL, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c7 DOUBLE GENERATED ALWAYS AS (c1+2*c2) STORED, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 ADD COLUMN c8 DOUBLE GENERATED ALWAYS AS (c1+2*c2) VIRTUAL FIRST, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c5 c6 1 1 0 3 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 1 1 NULL 0 3 ALTER TABLE t1 DROP column c3, add column c10 int as (c1+c2) virtual, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c6 c10 1 1 0 3 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c6 c10 1 1 NULL 0 3 2 ALTER TABLE t1 DROP column c3, drop column c6, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c5 c10 1 1 0 2 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 ALTER TABLE t1 DROP column c3, add column c11 int as (c1+c2) virtual first, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. SELECT * FROM t1; c1 c2 c3 c5 c10 1 1 NULL 0 2 DROP TABLE t1; # Scenario 53 # Table with functional index. CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT KEY, c1 varchar(10) DEFAULT NULL, c2 varchar(10) DEFAULT NULL, INDEX idx((concat(c1, "c1")))) ROW_FORMAT=COMPACT;; INSERT INTO t1(c1, c2) values("abc", "def"); SELECT * FROM t1; id c1 c2 1 abc def ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual after id, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE. ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; id c1 1 abc DROP TABLE t1; # Scenarion 54 CREATE TABLE t1 (c1 TEXT, c2 INT, PRIMARY KEY (c1(1))) ROW_FORMAT=COMPACT;; SELECT * FROM t1; c1 c2 REPLACE INTO t1(c1) VALUES (''); REPLACE INTO t1(c1) VALUES (''); SELECT * From t1; c1 c2 NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; REPLACE INTO t1(c1) VALUES (''); DROP TABLE t1; # Scenario 55 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (60)); ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p5 INTO ( PARTITION p51 VALUES LESS THAN (50), PARTITION p52 VALUES LESS THAN (60) ); DROP TABLE t1; # Scenario 56 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, PRIMARY KEY(c1, c4(10))) ROW_FORMAT=COMPACT;; INSERT INTO t1 VALUES (1, 2, 3, "abcdefghijklmnop"); SELECT * FROM t1; c1 c2 c3 c4 1 2 3 abcdefghijklmnop ALTER TABLE t1 ADD COLUMN c5 INT, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c2 c3 c4 c5 1 2 3 abcdefghijklmnop NULL ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; SELECT * FROM t1; c1 c3 c4 c5 1 3 abcdefghijklmnop NULL DROP TABLE t1; # Scenario 57 CREATE TABLE t1 (c1 CHAR(20), c2 CHAR(20), PRIMARY KEY PK(c1(4))) ROW_FORMAT=COMPACT;; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "c3_def", ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); SELECT * FROM t1; c1 c2 c3 r1c1 r1c2 c3_def r2c1 r2c2 r2c3 DROP TABLE t1; # Scenario 58 # Validating record in table with INSTANT ADD/DROP columns CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); ALTER TABLE t1 ADD COLUMN c3 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3"); ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r3c2"); ALTER TABLE t1 ADD COLUMN c4 CHAR(10), ALGORITHM=INSTANT; INSERT INTO t1 (c2) VALUES ("r4c2"); SELECT * From t1; c2 c3 c4 r1c2 NULL NULL r2c2 r2c3 NULL r3c2 NULL NULL r4c2 NULL NULL DROP TABLE t1; CREATE TABLE t1 (id INT KEY, c2 CHAR(10)) ROW_FORMAT=COMPACT PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (40), PARTITION P1 VALUES LESS THAN (50)); INSERT INTO t1 VALUES(1,"r1c2"); INSERT INTO t1 VALUES(2,"r2c2"); ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; ALTER TABLE t1 ADD COLUMN c2 CHAR(10), ALGORITHM=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION P0, P1 INTO (PARTITION P1 VALUES LESS THAN (50)); SELECT * FROM t1; id c2 1 NULL 2 NULL DROP TABLE t1; # Scenario 59 # A scenario when #fields were not updated correctly in REDUNDANT record for not-inplace update. CREATE TABLE t1 (c1 VARCHAR(20), c2 CHAR(20)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES ("r1c1", "r1c2"); SELECT * FROM t1; c1 c2 r1c1 r1c2 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; UPDATE t1 SET c1="r1c11111111111"; SELECT * FROM t1; c1 r1c11111111111 DROP TABLE t1; # Scenario 60 # When a new partition is added in a table with instant columns and virtual column, # during inheriting INSTANT metadat for new partitions, virtual columns were not skipped. CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=COMPACT PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300)); ALTER TABLE t1 ADD COLUMN col5 INT, ADD COLUMN col6 INT GENERATED ALWAYS AS (col5 + 1) VIRTUAL, ALGORITHM=INSTANT; ALTER TABLE t1 DROP COLUMN col2, algorithm=INSTANT; ALTER TABLE t1 REORGANIZE PARTITION p2 INTO(PARTITION p21 VALUES LESS THAN (250), PARTITION p22 VALUES LESS THAN (300)); DROP TABLE t1; # Scenario 61 # When a table is truncated, it's instant metadata is reset. It shall # also reset instant metadata of columns. CREATE TABLE t1 (c1 VARCHAR(1008)) ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD COLUMN c2 INT; TRUNCATE TABLE t1; INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),1); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),2); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),3); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),4); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),5); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),6); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),7); INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),8); ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500); UPDATE t1 SET c3 = CONCAT(REPEAT('c', 500)); DROP TABLE t1; # Scenario 62 # While updating lob columns on a rebuilt table which had INSTANT cols CREATE TABLE t1 (c1 CHAR(10)) ROW_FORMAT=COMPACT; INSERT INTO t1 VALUES ("r1c1"); SELECT * FROM t1; c1 r1c1 ALTER TABLE t1 ADD COLUMN c2 INT; ALTER TABLE t1 DROP COLUMN c2; ALTER TABLE t1 ADD COLUMN c3 TEXT; UPDATE t1 SET c3 = CONCAT(REPEAT('z', 8204)); ALTER TABLE t1 ADD PRIMARY KEY (c3(10)), ADD FULLTEXT KEY idx (c3); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT * FROM t1; c1 c3 r1c1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz DROP TABLE t1; # Scenario 63 # Truncate of a partition/partition table CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 INSERT INTO t1 values (5, 50), (15, 150); SELECT * FROM t1 PARTITION (p1); a b 5 50 SELECT * FROM t1 PARTITION (p2); a b 15 150 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; b 2 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c 3 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 6 0 1 2 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 b 1 6 1027 0 0 0 c 2 6 1027 1 1 0 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(30)); INSERT INTO t1 (a, b) values (25, 250); SELECT * FROM t1 PARTITION (p1); a b c 5 50 100 SELECT * FROM t1 PARTITION (p2); a b c 15 150 100 SELECT * FROM t1 PARTITION (p3); a b c 25 250 100 alter table t1 drop column b, algorithm=instant; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 5 100 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 25 100 ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible default=80000064;physical_pos=4;table_id=TABLE_ID;version_added=1; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p3_b 5 MYSQL_TYPE_LONG 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 2 2 2 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 1 1 0 !hidden!_dropped_v2_p3_b 5 6 1027 0 0 2 SELECT * FROM t1; a c 15 100 TRUNCATE TABLE t1; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c TRUNCATE TABLE t1; ALTER TABLE t1 TRUNCATE PARTITION p1; ALTER TABLE t1 TRUNCATE PARTITION p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA a 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_TRX_ID 3 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 4 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p1 10 NULL p2 20 NULL p3 30 NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 5 0 0 2 2 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED a 0 6 1283 0 0 0 c 1 6 1027 0 0 0 SELECT * FROM t1; a c DROP TABLE t1; # Scenario 64 # When nullbitmap size on physical record changes after INSTANT ADD columns. CREATE TABLE t1 (c0 char(9) KEY, c1 char (10), c2 char(20), c3 char(30), c4 char(40), c5 char(50), c6 char(60), c7 char(70), c8 char(80)) ROW_FORMAT=COMPACT; SET GLOBAL innodb_limit_optimistic_insert_debug=2; Insert into t1 values( REPEAT("a", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Insert into t1 values( REPEAT("b", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79)); Alter table t1 add column c9 char(90); Insert into t1 values( REPEAT("c", 8), REPEAT("1", 9), REPEAT("2", 19), REPEAT("3", 29), REPEAT("4", 39), REPEAT("5", 49), REPEAT("6", 59), REPEAT("7", 69), REPEAT("8", 79), REPEAT("9", 89)); SET GLOBAL innodb_limit_optimistic_insert_debug=0; DROP table t1; # Scenario 65 # Truncate all partition shall reset INSTANT Metadata CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (90, 1, 1, "1"), (190, 1, 1, "2"), (290, 1, 1, "3"), (390, 1, 1, "3"); Alter table t1 add column c5 int, algorithm=INSTANT; Alter table t1 drop column c3, algorithm=INSTANT; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=7;table_id=TABLE_ID;version_added=1; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 8 MYSQL_TYPE_LONG 0 SE physical_pos=5;version_dropped=2; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 1 1 0 !hidden!_dropped_v2_p5_c3 7 6 1027 0 0 2 ALTER TABLE t1 TRUNCATE partition p0, p1, p2, p3; # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; c4 3 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; c5 4 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; # DD Metadata of partitions in table NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA p0 100 NULL p1 200 NULL p2 300 NULL p3 MAXVALUE NULL # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p0 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p2 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1#p#p3 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED c1 0 6 1027 0 0 0 c2 1 6 1027 0 0 0 c4 2 5 16711932 0 0 0 c5 3 6 1027 0 0 0 DROP TABLE t1; # Scenario 66 # Purge crash create table t1 (c0 INT KEY, c1 varchar(10), c2 varchar(10)) ROW_FORMAT=COMPACT; alter table t1 add column c4 char(10), algorithm=instant; alter table t1 add index idx (c4); insert into t1 values (1, "r1c1", "r1c2", "c4"); SET GLOBAL innodb_purge_stop_now=ON; Update t1 set c2="r122222", c4="c44"; alter table t1 drop column c2, algorithm=instant; SET GLOBAL innodb_purge_run_now=ON; DROP TABLE t1; # Scenario 66 # Inplace update is failing # INSTANT ADD and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 # Disable flush to make sure REDO is applied after restart Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c1 c4 c2 c3 r1c1 NULL r112 r1c3 r2c1 r2c4 r212 r2c3 DROP TABLE t1; # INSTANT DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 drop column c1, algorithm=instant; Insert into t1 values ("r2c2", "r2c3"); Select * from t1; c2 c3 r1c2 r1c3 r2c2 r2c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Select * from t1; c2 c3 r112 r1c3 r212 r2c3 # Kill the running server # Kill the server # Start the server Select * from t1; c2 c3 r112 r1c3 r212 r2c3 DROP TABLE t1; # INSTANT ADD and DROP and INPLACE UPDATE CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=COMPACT; Insert into t1 values ("r1c1", "r1c2", "r1c3"); Select * from t1; c1 c2 c3 r1c1 r1c2 r1c3 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT; Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3"); Select * from t1; c1 c4 c2 c3 r1c1 NULL r1c2 r1c3 r2c1 r2c4 r2c2 r2c3 Alter table t1 drop column c1, algorithm=instant; Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 Insert into t1 values ("r3c4", "r3c2", "r3c3"); Select * from t1; c4 c2 c3 NULL r1c2 r1c3 r2c4 r2c2 r2c3 r3c4 r3c2 r3c3 SET GLOBAL innodb_log_checkpoint_now = ON; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; Update t1 set c2="r112" where c2="r1c2"; Update t1 set c2="r212" where c2="r2c2"; Update t1 set c2="r312" where c2="r3c2"; Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 # Kill the running server # Kill the server # Start the server Select * from t1; c4 c2 c3 NULL r112 r1c3 r2c4 r212 r2c3 r3c4 r312 r3c3 DROP TABLE t1; ########### # CLEANUP # ###########