############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=REDUNDANT;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES (1, "r1c1", "r1c2"); SELECT * FROM t1; id c1 c2 1 r1c1 r1c2 # INSTANT ADD a column Alter table t1 add column c3 char(10), algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES (2, "r2c1", "r2c2", "r2c3"); SELECT * FROM t1; id c1 c2 c3 1 r1c1 r1c2 NULL 2 r2c1 r2c2 r2c3 # INSTANT DROP a column Alter table t1 drop column c2 , algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; INSERT INTO t1 VALUES (3, "r3c1", "r3c3"); SELECT * FROM t1; id c1 c3 1 r1c1 NULL 2 r2c1 r2c3 3 r3c1 r3c3 # Make sure nothing gets flushed on disk 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; # Make inplace update UPDATE t1 SET c1="r0c0"; # Make not-inplace update UPDATE t1 SET c3="r1c3" WHERE id=1; # Insert a new record INSERT INTO t1 VALUES (4, "r4c1", "r4c3"); # Delete a record DELETE FROM t1 WHERE id=2; # Kill and restart # 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 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1; id c1 c3 1 r0c0 r1c3 3 r0c0 r3c3 4 r4c1 r4c3 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t1 (`c` text, `b` blob NOT NULL, `nc03242` longblob, `d` int DEFAULT NULL, UNIQUE KEY `b` (`b`(99))) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; alter table t1 change c c text after d, add column nc05984 bool, algorithm=instant; 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; INSERT INTO t1 SET b='ulccclraaacaucrouorouoooolrlo', d=6; # Kill and restart SELECT * FROM t1; b nc03242 d c nc05984 ulccclraaacaucrouorouoooolrlo NULL 6 NULL NULL DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=DYNAMIC;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES (1, "r1c1", "r1c2"); SELECT * FROM t1; id c1 c2 1 r1c1 r1c2 # INSTANT ADD a column Alter table t1 add column c3 char(10), algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES (2, "r2c1", "r2c2", "r2c3"); SELECT * FROM t1; id c1 c2 c3 1 r1c1 r1c2 NULL 2 r2c1 r2c2 r2c3 # INSTANT DROP a column Alter table t1 drop column c2 , algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; INSERT INTO t1 VALUES (3, "r3c1", "r3c3"); SELECT * FROM t1; id c1 c3 1 r1c1 NULL 2 r2c1 r2c3 3 r3c1 r3c3 # Make sure nothing gets flushed on disk 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; # Make inplace update UPDATE t1 SET c1="r0c0"; # Make not-inplace update UPDATE t1 SET c3="r1c3" WHERE id=1; # Insert a new record INSERT INTO t1 VALUES (4, "r4c1", "r4c3"); # Delete a record DELETE FROM t1 WHERE id=2; # Kill and restart # 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 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1; id c1 c3 1 r0c0 r1c3 3 r0c0 r3c3 4 r4c1 r4c3 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t1 (`c` text, `b` blob NOT NULL, `nc03242` longblob, `d` int DEFAULT NULL, UNIQUE KEY `b` (`b`(99))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; alter table t1 change c c text after d, add column nc05984 bool, algorithm=instant; 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; INSERT INTO t1 SET b='ulccclraaacaucrouorouoooolrlo', d=6; # Kill and restart SELECT * FROM t1; b nc03242 d c nc05984 ulccclraaacaucrouorouoooolrlo NULL 6 NULL NULL DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ # ------------------------------------------------------------ # Create a table with 3 columns. [id, c1, c2] # ------------------------------------------------------------ CREATE TABLE t1(id INT PRIMARY KEY, c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=COMPACT;; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 0 3 3 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 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 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; INSERT INTO t1 VALUES (1, "r1c1", "r1c2"); SELECT * FROM t1; id c1 c2 1 r1c1 r1c2 # INSTANT ADD a column Alter table t1 add column c3 char(10), algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 7 0 1 3 4 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED id 0 6 1283 0 0 0 c1 1 13 16711934 0 0 0 c2 2 13 16711934 0 0 0 c3 3 13 16711934 1 1 0 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c2 3 MYSQL_TYPE_STRING 0 Visible physical_pos=4;table_id=TABLE_ID; c3 4 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=1; DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; INSERT INTO t1 VALUES (2, "r2c1", "r2c2", "r2c3"); SELECT * FROM t1; id c1 c2 c3 1 r1c1 r1c2 NULL 2 r2c1 r2c2 r2c3 # INSTANT DROP a column Alter table t1 drop column c2 , algorithm=instant; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS test/t1 6 0 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; INSERT INTO t1 VALUES (3, "r3c1", "r3c3"); SELECT * FROM t1; id c1 c3 1 r1c1 NULL 2 r2c1 r2c3 3 r3c1 r3c3 # Make sure nothing gets flushed on disk 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; # Make inplace update UPDATE t1 SET c1="r0c0"; # Make not-inplace update UPDATE t1 SET c3="r1c3" WHERE id=1; # Insert a new record INSERT INTO t1 VALUES (4, "r4c1", "r4c3"); # Delete a record DELETE FROM t1 WHERE id=2; # Kill and restart # 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 2 3 3 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 13 16711934 0 0 0 c3 2 13 16711934 1 1 0 !hidden!_dropped_v2_p4_c2 6 13 16711934 0 0 2 # DD Metadata of table NAME SE_PRIVATE_DATA t1 NULL # DD Metadata of columns in table NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID; c1 2 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID; c3 3 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;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; !hidden!_dropped_v2_p4_c2 6 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=2; SELECT * FROM t1; id c1 c3 1 r0c0 r1c3 3 r0c0 r3c3 4 r4c1 r4c3 ########### # CLEANUP # ########### DROP TABLE t1; CREATE TABLE t1 (`c` text, `b` blob NOT NULL, `nc03242` longblob, `d` int DEFAULT NULL, UNIQUE KEY `b` (`b`(99))) ENGINE=InnoDB ROW_FORMAT=COMPACT; alter table t1 change c c text after d, add column nc05984 bool, algorithm=instant; 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; INSERT INTO t1 SET b='ulccclraaacaucrouorouoooolrlo', d=6; # Kill and restart SELECT * FROM t1; b nc03242 d c nc05984 ulccclraaacaucrouorouoooolrlo NULL 6 NULL NULL DROP TABLE t1;