############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=REDUNDANT;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1)); SELECT id, length(c1), length(c2), length(c3) FROM t1; id length(c1) length(c2) length(c3) 1 4000 4000 1 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 1 aaaaaaaaaa bbbbbbbbbb c # INSTANT ADD a column ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 8 0 1 4 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 0 0 0 c4 4 12 16716047 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100)); SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 200 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh # INSTANT DROP a column ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c4 3 12 16716047 1 1 0 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 200 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=DYNAMIC;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1)); SELECT id, length(c1), length(c2), length(c3) FROM t1; id length(c1) length(c2) length(c3) 1 4000 4000 1 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 1 aaaaaaaaaa bbbbbbbbbb c # INSTANT ADD a column ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 8 0 1 4 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 0 0 0 c4 4 12 16716047 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100)); SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 200 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh # INSTANT DROP a column ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c4 3 12 16716047 1 1 0 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 200 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2, c3] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=COMPACT;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 0 4 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1)); SELECT id, length(c1), length(c2), length(c3) FROM t1; id length(c1) length(c2) length(c3) 1 4000 4000 1 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 1 aaaaaaaaaa bbbbbbbbbb c # INSTANT ADD a column ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 8 0 1 4 5 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c3 3 12 16715791 0 0 0 c4 4 12 16716047 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID; c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100)); SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 200 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; id length(c1) length(c2) length(c3) length(c4) 1 4000 4000 1 500 2 4000 4000 1 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd 2 eeeeeeeeee ffffffffff g hhhhhhhhhh # INSTANT DROP a column ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 2 4 4 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 12 16715791 0 0 0 c2 2 12 16715791 0 0 0 c4 3 12 16716047 1 1 0 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID; c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh START TRANSACTION; UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 200 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 xxxxxxxxxx bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ROLLBACK; SELECT id, length(c1), length(c2), length(c4) FROM t1; id length(c1) length(c2) length(c4) 1 4000 4000 500 2 4000 4000 100 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10) 1 aaaaaaaaaa bbbbbbbbbb dddddddddd 2 eeeeeeeeee ffffffffff hhhhhhhhhh ########### # CLEANUP # ########### DROP TABLE t1;