EVOLUTION-MANAGER

Edit File: default_row_format_alter.result

SET @row_format = @@GLOBAL.innodb_default_row_format; #################################### # Check if table rebuilding alter isn't affect if table is created # with explicit row_format CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB; INSERT INTO t1 VALUES (1, 'abc'); SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL row_format=COMPACT 0 N SET GLOBAL innodb_default_row_format=DYNAMIC; ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY; # Here we expect COMPACT because it was explicitly specified at CREATE SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL row_format=COMPACT 0 N DROP TABLE t1; #################################### # Check if table rebuilding alter is affected when there is no # row_format specified at CREATE TABLE. SET GLOBAL innodb_default_row_format = COMPACT; CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB; INSERT INTO t1 VALUES (1, 'abc'); SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N SET GLOBAL innodb_default_row_format = DYNAMIC; ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY; # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the # default_row_format is changed to DYNAMIC just before ALTER SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Dynamic # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N DROP TABLE t1; #################################### # Check the row_format effect on ALTER, ALGORITHM=COPY SET GLOBAL innodb_default_row_format = REDUNDANT; CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB; INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Redundant # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N SET GLOBAL innoDB_default_row_format = COMPACT; ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY; # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't # created with explicit row_format, so we expect ROW_FORMAT=COMPACT SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N DROP TABLE t1; ################################### # Check the row_format effect on ALTER, ALGORITH=COPY on # create table with explicit row_format CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB; INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Redundant # # # # # # NULL # # NULL latin1_swedish_ci NULL row_format=REDUNDANT 0 N SET GLOBAL innoDB_default_row_format = COMPACT; ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY; # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is # created with explicit row_format, so we expect original # ROW_FORMAT=REDUNDANT SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Redundant # # # # # # NULL # # NULL latin1_swedish_ci NULL row_format=REDUNDANT 0 N DROP TABLE t1; ################################## # Check row_format on ALTER ALGORITHM=INPLACE SET GLOBAL innodb_default_row_format=COMPACT; CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB; INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N SET GLOBAL innodb_default_row_format=DYNAMIC; ALTER TABLE t1 DROP INDEX k1; # Because it is in-place operation, there is no rebuild, so the # original format has to be retained. SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary t1 InnoDB # Compact # # # # # # NULL # # NULL latin1_swedish_ci NULL 0 N DROP TABLE t1; SET GLOBAL innodb_default_row_format = @row_format;