diff options
Diffstat (limited to 'mysql-test')
22 files changed, 802 insertions, 62 deletions
diff --git a/mysql-test/include/innodb_row_format.combinations b/mysql-test/include/innodb_row_format.combinations new file mode 100644 index 00000000000..fb94d61dd42 --- /dev/null +++ b/mysql-test/include/innodb_row_format.combinations @@ -0,0 +1,8 @@ +[redundant] +innodb_default_row_format=redundant + +[compact] +innodb_default_row_format=compact + +[dynamic] +innodb_default_row_format=dynamic diff --git a/mysql-test/include/innodb_row_format.inc b/mysql-test/include/innodb_row_format.inc new file mode 100644 index 00000000000..8c6357e57d6 --- /dev/null +++ b/mysql-test/include/innodb_row_format.inc @@ -0,0 +1,4 @@ +# The goal of including this file is to enable innodb_default_row_format +# combinations (see include/innodb_row_format.combinations) + +--source include/have_innodb.inc diff --git a/mysql-test/suite/innodb/r/create-index-debug.result b/mysql-test/suite/innodb/r/create-index-debug.result index 99f6efe9bfe..9d266c68af3 100644 --- a/mysql-test/suite/innodb/r/create-index-debug.result +++ b/mysql-test/suite/innodb/r/create-index-debug.result @@ -10,9 +10,6 @@ CHAR(255) NOT NULL, f13 CHAR(255) NOT NULL, f14 CHAR(255) NOT NULL,f15 CHAR(255) NOT NULL, f16 CHAR(255) NOT NULL, f17 CHAR(255) NOT NULL,f18 CHAR(255) NOT NULL) ENGINE=INNODB ROW_FORMAT=DYNAMIC; -Warnings: -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. INSERT INTO t1 VALUES('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r'); INSERT INTO t1 SELECT * FROM t1; diff --git a/mysql-test/suite/innodb/r/default_row_format_alter.result b/mysql-test/suite/innodb/r/default_row_format_alter.result new file mode 100644 index 00000000000..1f4f78ee670 --- /dev/null +++ b/mysql-test/suite/innodb/r/default_row_format_alter.result @@ -0,0 +1,85 @@ +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=COMPACT +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=COMPACT +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +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 +t1 InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +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 +t1 InnoDB # Redundant # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +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 +t1 InnoDB # Redundant # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=REDUNDANT +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 +t1 InnoDB # Redundant # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=REDUNDANT +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +DROP TABLE t1; +SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/r/default_row_format_compatibility.result b/mysql-test/suite/innodb/r/default_row_format_compatibility.result new file mode 100644 index 00000000000..741241ddba0 --- /dev/null +++ b/mysql-test/suite/innodb/r/default_row_format_compatibility.result @@ -0,0 +1,100 @@ +SET @row_format = @@GLOBAL.innodb_default_row_format; +SET @large_prefix = @@GLOBAL.innodb_large_prefix; +SET @file_format = @@GLOBAL.innodb_file_format; +SET GLOBAL innodb_file_format = barracuda; +# ########################################################### +# Check with Import/Export tablespace with Default_row_format +SET GLOBAL innodb_default_row_format=Compact; +SELECT @@innodb_default_row_format; +@@innodb_default_row_format +compact +SELECT @@innodb_file_per_table; +@@innodb_file_per_table +1 +CREATE TABLE tab(a INT) ENGINE=InnoDB; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +INSERT INTO tab VALUES(1); +INSERT INTO tab VALUES(2); +SELECT * FROM tab; +a +1 +2 +FLUSH TABLE tab FOR EXPORT; +UNLOCK TABLES; +DROP TABLE tab; +SET GLOBAL innodb_default_row_format=Dynamic; +CREATE TABLE tab(a INT) ENGINE=InnoDB; +ALTER TABLE tab DISCARD TABLESPACE; +ALTER TABLE tab IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) +DROP TABLE tab; +SET GLOBAL innodb_default_row_format=Compact; +SELECT @@innodb_default_row_format; +@@innodb_default_row_format +compact +CREATE TABLE tab(a INT) ENGINE=InnoDB; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +ALTER TABLE tab DISCARD TABLESPACE; +ALTER TABLE tab IMPORT TABLESPACE; +SELECT * FROM tab; +a +1 +2 +DROP TABLE tab; +# ########################################################### +SET GLOBAL innodb_default_row_format=Dynamic; +SET GLOBAL innodb_large_prefix=ON; +SELECT @@innodb_default_row_format; +@@innodb_default_row_format +dynamic +CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(3070))) ENGINE= InnoDB; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +INSERT INTO tab(a,b) VALUES(1,'Check with max column size'); +SELECT * FROM tab; +a b +1 Check with max column size +SET GLOBAL innodb_default_row_format=COMPACT; +ALTER TABLE tab ROW_FORMAT=COMPACT; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +DROP TABLE tab; +SET GLOBAL innodb_default_row_format=Default; +SELECT @@innodb_default_row_format; +@@innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format=Dynamic; +SELECT @@innodb_default_row_format; +@@innodb_default_row_format +dynamic +CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(767))) ENGINE= InnoDB; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +INSERT INTO tab(a,b) VALUES(1,'Check with max column size'); +SELECT * FROM tab; +a b +1 Check with max column size +ALTER TABLE tab ROW_FORMAT=COMPACT; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=COMPACT +SELECT * FROM tab; +a b +1 Check with max column size +ALTER TABLE tab ROW_FORMAT=COMPRESSED; +SELECT * FROM tab; +a b +1 Check with max column size +ALTER TABLE tab ROW_FORMAT=Dynamic; +SHOW TABLE STATUS LIKE 'tab'; +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 +tab InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=DYNAMIC +DROP TABLE tab; +SET GLOBAL innodb_default_row_format = @row_format; +SET GLOBAL innodb_large_prefix = @large_prefix; +SET GLOBAL innodb_file_format = @file_format; diff --git a/mysql-test/suite/innodb/r/default_row_format_create,dynamic.rdiff b/mysql-test/suite/innodb/r/default_row_format_create,dynamic.rdiff new file mode 100644 index 00000000000..bb23b78213b --- /dev/null +++ b/mysql-test/suite/innodb/r/default_row_format_create,dynamic.rdiff @@ -0,0 +1,11 @@ +--- default_row_format_create.result ++++ default_row_format_create,dynamic.result +@@ -1,7 +1,7 @@ + CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB; + 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 +-t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL ++t1 InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL + DROP TABLE t1; + CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; + SHOW TABLE STATUS LIKE 't1'; diff --git a/mysql-test/suite/innodb/r/default_row_format_create,redundant.rdiff b/mysql-test/suite/innodb/r/default_row_format_create,redundant.rdiff new file mode 100644 index 00000000000..35aa402edd9 --- /dev/null +++ b/mysql-test/suite/innodb/r/default_row_format_create,redundant.rdiff @@ -0,0 +1,11 @@ +--- default_row_format_create.result ++++ default_row_format_create,redundant.result +@@ -1,7 +1,7 @@ + CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB; + 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 +-t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL ++t1 InnoDB # Redundant # # # # # # NULL # NULL NULL latin1_swedish_ci NULL + DROP TABLE t1; + CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; + SHOW TABLE STATUS LIKE 't1'; diff --git a/mysql-test/suite/innodb/r/default_row_format_create.result b/mysql-test/suite/innodb/r/default_row_format_create.result new file mode 100644 index 00000000000..4e90922d70d --- /dev/null +++ b/mysql-test/suite/innodb/r/default_row_format_create.result @@ -0,0 +1,31 @@ +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB; +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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL +DROP TABLE t1; +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +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 +t1 InnoDB # Dynamic # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=DYNAMIC +DROP TABLE t1; +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB 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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=COMPACT +DROP TABLE t1; +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB 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 +t1 InnoDB # Redundant # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=REDUNDANT +DROP TABLE t1; +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +Warnings: +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming 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 +t1 InnoDB # Compact # # # # # # NULL # NULL NULL latin1_swedish_ci NULL row_format=COMPRESSED key_block_size=1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/file_format_defaults.result b/mysql-test/suite/innodb/r/file_format_defaults.result new file mode 100644 index 00000000000..981188f00f7 --- /dev/null +++ b/mysql-test/suite/innodb/r/file_format_defaults.result @@ -0,0 +1,56 @@ +SELECT @@innodb_strict_mode; +@@innodb_strict_mode +0 +SELECT @@innodb_file_per_table; +@@innodb_file_per_table +1 +SET @file_format = @@GLOBAL.innodb_file_format; +SET GLOBAL innodb_large_prefix=ON; +SET SQL_MODE=strict_all_tables; +CREATE TABLE tab0 (c1 VARCHAR(65530), KEY(c1(3073))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +SHOW CREATE TABLE tab0; +Table Create Table +tab0 CREATE TABLE `tab0` ( + `c1` varchar(65530) DEFAULT NULL, + KEY `c1` (`c1`(3072)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED +DROP TABLE tab0; +CREATE TABLE tab0 (c1 VARCHAR(65530), KEY(c1(3073))) ENGINE=InnoDB KEY_BLOCK_SIZE=2; +ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +SET GLOBAL innodb_file_format=Antelope; +CREATE TABLE tab0(c1 INT,c2 LONGBLOB ) ENGINE=InnoDB ROW_FORMAT=Dynamic; +DROP TABLE tab0; +SET GLOBAL innodb_file_format=Default; +SELECT @@innodb_file_format; +@@innodb_file_format +Antelope +SET GLOBAL innodb_strict_mode=OFF; +SET GLOBAL innodb_strict_mode=Default; +SELECT @@innodb_strict_mode; +@@innodb_strict_mode +0 +SET GLOBAL innodb_large_prefix=OFF; +SELECT @@innodb_large_prefix; +@@innodb_large_prefix +0 +SET GLOBAL innodb_large_prefix=Default; +SELECT @@innodb_large_prefix; +@@innodb_large_prefix +0 +SET GLOBAL innodb_file_format_max=Default; +SELECT @@innodb_file_format_max; +@@innodb_file_format_max +Antelope +CREATE TABLE tab1(c1 int ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT @@innodb_file_format_max; +@@innodb_file_format_max +Antelope +SET GLOBAL innodb_file_format_max=Default; +SET GLOBAL innodb_large_prefix=off; +SET GLOBAL innodb_file_format = @file_format; +DROP TABLE tab1; diff --git a/mysql-test/suite/innodb/t/default_row_format_alter.test b/mysql-test/suite/innodb/t/default_row_format_alter.test new file mode 100644 index 00000000000..03e6ec38060 --- /dev/null +++ b/mysql-test/suite/innodb/t/default_row_format_alter.test @@ -0,0 +1,98 @@ +--source include/have_innodb.inc + +SET @row_format = @@GLOBAL.innodb_default_row_format; + +--echo #################################### +--echo # Check if table rebuilding alter isn't affect if table is created +--echo # with explicit row_format +eval CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB; +INSERT INTO t1 VALUES (1, 'abc'); +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; + +SET GLOBAL innodb_default_row_format=DYNAMIC; +ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY; + +--echo # Here we expect COMPACT because it was explicitly specified at CREATE +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +--echo #################################### +--echo # Check if table rebuilding alter is affected when there is no +--echo # row_format specified at CREATE TABLE. +SET GLOBAL innodb_default_row_format = COMPACT; +eval CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB; +INSERT INTO t1 VALUES (1, 'abc'); + +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; + +SET GLOBAL innodb_default_row_format = DYNAMIC; +ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY; + +--echo # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the +--echo # default_row_format is changed to DYNAMIC just before ALTER +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +--echo #################################### +--echo # Check the row_format effect on ALTER, ALGORITHM=COPY +SET GLOBAL innodb_default_row_format = REDUNDANT; +eval CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB; +INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); + +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; + +SET GLOBAL innoDB_default_row_format = COMPACT; +ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY; + +--echo # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't +--echo # created with explicit row_format, so we expect ROW_FORMAT=COMPACT +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +--echo +--echo ################################### +--echo # Check the row_format effect on ALTER, ALGORITH=COPY on +--echo # create table with explicit row_format +eval CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB; +INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); + +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; + +SET GLOBAL innoDB_default_row_format = COMPACT; +ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY; + +--echo # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is +--echo # created with explicit row_format, so we expect original +--echo # ROW_FORMAT=REDUNDANT +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +--echo +--echo ################################## +--echo # Check row_format on ALTER ALGORITHM=INPLACE +SET GLOBAL innodb_default_row_format=COMPACT; + +eval CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB; +INSERT INTO t1 VALUES (1, REPEAT('abc',1000)); + +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; + +SET GLOBAL innodb_default_row_format=DYNAMIC; +ALTER TABLE t1 DROP INDEX k1; + +--echo # Because it is in-place operation, there is no rebuild, so the +--echo # original format has to be retained. +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/t/default_row_format_compatibility.test b/mysql-test/suite/innodb/t/default_row_format_compatibility.test new file mode 100644 index 00000000000..17ab21ca06b --- /dev/null +++ b/mysql-test/suite/innodb/t/default_row_format_compatibility.test @@ -0,0 +1,167 @@ +--source include/have_innodb.inc + +SET @row_format = @@GLOBAL.innodb_default_row_format; +SET @large_prefix = @@GLOBAL.innodb_large_prefix; +SET @file_format = @@GLOBAL.innodb_file_format; +SET GLOBAL innodb_file_format = barracuda; + +# set the variables +let $MYSQLD_DATADIR = `SELECT @@datadir`; + +--echo # ########################################################### +--echo # Check with Import/Export tablespace with Default_row_format + +# Set row_format=Compact +SET GLOBAL innodb_default_row_format=Compact; + +# Check row_format=Compact +SELECT @@innodb_default_row_format; + +# Check file_per_table=1 +SELECT @@innodb_file_per_table; + +CREATE TABLE tab(a INT) ENGINE=InnoDB; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +INSERT INTO tab VALUES(1); +INSERT INTO tab VALUES(2); + +# Check the rows +SELECT * FROM tab; + +FLUSH TABLE tab FOR EXPORT; + +# Take the backup of the ibd and cfg files +--copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_DATADIR/tab.cfg +--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/tab.ibd + +UNLOCK TABLES; + +# Cleanup +DROP TABLE tab; + +# Set the default_row_format=Dynamic +SET GLOBAL innodb_default_row_format=Dynamic; + +CREATE TABLE tab(a INT) ENGINE=InnoDB; + +# Remove the *.ibd file +ALTER TABLE tab DISCARD TABLESPACE; + +# Move the *.ibd,*.cfg file into orginal location +--move_file $MYSQLD_DATADIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg +--move_file $MYSQLD_DATADIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd + +--error ER_TABLE_SCHEMA_MISMATCH +ALTER TABLE tab IMPORT TABLESPACE; + +# Take the backup of the ibd and cfg files +--copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_DATADIR/tab.cfg +--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/tab.ibd + +# Cleanup +DROP TABLE tab; + +# Remove orphan files +--remove_file $MYSQLD_DATADIR/test/tab.cfg +--remove_file $MYSQLD_DATADIR/test/tab.ibd + +# Set the default_row_format=Compact +SET GLOBAL innodb_default_row_format=Compact; + +# Check row_format=Compact +SELECT @@innodb_default_row_format; + +CREATE TABLE tab(a INT) ENGINE=InnoDB; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +# Remove the *.ibd file +ALTER TABLE tab DISCARD TABLESPACE; + +# Move the *ibd,*.cfg file into orginal location +--move_file $MYSQLD_DATADIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg +--move_file $MYSQLD_DATADIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd + +# Check import is successful (because same row_format) +ALTER TABLE tab IMPORT TABLESPACE; + +# Check the rows +SELECT * FROM tab; + +# Cleanup +DROP TABLE tab; + +--echo # ########################################################### +# Check when Index Column size (3070 bytes) is too long, Change row_format +# Check when Index Column size (767 bytes), Change row_format +# Dynamic to Compact to Dynamic + +# Set the default_row_format=Dynamic +SET GLOBAL innodb_default_row_format=Dynamic; +SET GLOBAL innodb_large_prefix=ON; + +SELECT @@innodb_default_row_format; + +CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(3070))) ENGINE= InnoDB; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +INSERT INTO tab(a,b) VALUES(1,'Check with max column size'); + +# Check by SELECT, no errors +SELECT * FROM tab; + +# Change row_format to Compact +SET GLOBAL innodb_default_row_format=COMPACT; + +# Check error ERROR 1709 (HY000): Index column size too large +-- error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE tab ROW_FORMAT=COMPACT; + +# Cleanup +DROP TABLE tab; + +# Change the default_row_format to default +SET GLOBAL innodb_default_row_format=Default; +SELECT @@innodb_default_row_format; +SET GLOBAL innodb_default_row_format=Dynamic; + +# Change row_format to Dynamic +SELECT @@innodb_default_row_format; + +CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(767))) ENGINE= InnoDB; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +INSERT INTO tab(a,b) VALUES(1,'Check with max column size'); + +# Check by SELECT, no errors +SELECT * FROM tab; + +# Check no errors because Compact allows 767 bytes +ALTER TABLE tab ROW_FORMAT=COMPACT; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +# Check by SELECT, no errors +SELECT * FROM tab; + +# Check no errors +ALTER TABLE tab ROW_FORMAT=COMPRESSED; + +# Check by SELECT, no errors +SELECT * FROM tab; + +# Check no errors +ALTER TABLE tab ROW_FORMAT=Dynamic; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 'tab'; + +# Cleanup +DROP TABLE tab; + +SET GLOBAL innodb_default_row_format = @row_format; +SET GLOBAL innodb_large_prefix = @large_prefix; +SET GLOBAL innodb_file_format = @file_format; diff --git a/mysql-test/suite/innodb/t/default_row_format_create.test b/mysql-test/suite/innodb/t/default_row_format_create.test new file mode 100644 index 00000000000..e0981abf7eb --- /dev/null +++ b/mysql-test/suite/innodb/t/default_row_format_create.test @@ -0,0 +1,28 @@ +--source include/have_innodb.inc +--source include/innodb_row_format.inc + +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; + +CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # +SHOW TABLE STATUS LIKE 't1'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/file_format_defaults-master.opt b/mysql-test/suite/innodb/t/file_format_defaults-master.opt new file mode 100644 index 00000000000..202ecee6d48 --- /dev/null +++ b/mysql-test/suite/innodb/t/file_format_defaults-master.opt @@ -0,0 +1 @@ +--innodb_large_prefix=OFF --innodb_file_format=barracuda --innodb-file-format-check=off --innodb-file-format-max=antelope diff --git a/mysql-test/suite/innodb/t/file_format_defaults.test b/mysql-test/suite/innodb/t/file_format_defaults.test new file mode 100644 index 00000000000..c16ca068e46 --- /dev/null +++ b/mysql-test/suite/innodb/t/file_format_defaults.test @@ -0,0 +1,73 @@ +#*********************************************************** +# WL#7703: +# Check the max key length 3072 when innodb_large_prefix=ON +# Check boundary value of max key length 3073 +# When innodb_file_format=Antelope, compress DDLs fails +# Check file_format_max becomes Barracuda on DDL operation +# on compression table. +#*********************************************************** +-- source include/have_innodb.inc +-- source include/have_innodb_16k.inc + +# Check some default settings +SELECT @@innodb_strict_mode; + +SELECT @@innodb_file_per_table; + +SET @file_format = @@GLOBAL.innodb_file_format; + +SET GLOBAL innodb_large_prefix=ON; +SET SQL_MODE=strict_all_tables; + +CREATE TABLE tab0 (c1 VARCHAR(65530), KEY(c1(3073))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; +SHOW CREATE TABLE tab0; +DROP TABLE tab0; + +--error ER_TOO_BIG_ROWSIZE +CREATE TABLE tab0 (c1 VARCHAR(65530), KEY(c1(3073))) ENGINE=InnoDB KEY_BLOCK_SIZE=2; + +SET GLOBAL innodb_file_format=Antelope; + +# WL#8307 Make ROW_FORMAT=DYNAMIC the default +# will allow ROW_FORMAT=DYNAMIC even if innodb_file_format=Antelope. +CREATE TABLE tab0(c1 INT,c2 LONGBLOB ) ENGINE=InnoDB ROW_FORMAT=Dynamic; +DROP TABLE tab0; + +SET GLOBAL innodb_file_format=Default; + +SELECT @@innodb_file_format; + +SET GLOBAL innodb_strict_mode=OFF; + +# Check with default value +SET GLOBAL innodb_strict_mode=Default; + +SELECT @@innodb_strict_mode; + +SET GLOBAL innodb_large_prefix=OFF; + +SELECT @@innodb_large_prefix; + +SET GLOBAL innodb_large_prefix=Default; + +# Check with default value +SELECT @@innodb_large_prefix; + +SET GLOBAL innodb_file_format_max=Default; + +# Check with default value +SELECT @@innodb_file_format_max; + +CREATE TABLE tab1(c1 int ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + +# Check file format changed to Barracuda, on DDL operation +SELECT @@innodb_file_format_max; + +SET GLOBAL innodb_file_format_max=Default; + +# Restore to the value that we explicitly used at startup. +SET GLOBAL innodb_large_prefix=off; + +SET GLOBAL innodb_file_format = @file_format; + +DROP TABLE tab1; diff --git a/mysql-test/suite/innodb_zip/r/innodb-create-options.result b/mysql-test/suite/innodb_zip/r/innodb-create-options.result index fe3d799229d..e961d0ca7d0 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-create-options.result +++ b/mysql-test/suite/innodb_zip/r/innodb-create-options.result @@ -282,12 +282,12 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelop Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; Level Code Message @@ -318,13 +318,10 @@ Level Code Message Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SET GLOBAL innodb_file_format=Barracuda; -DROP TABLE IF EXISTS t1; +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; SET GLOBAL innodb_file_format=Antelope; ALTER TABLE t1 ADD COLUMN f1 INT; @@ -369,12 +366,12 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; Level Code Message @@ -405,11 +402,11 @@ Level Code Message Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC ALTER TABLE t1 ROW_FORMAT=COMPACT; SHOW WARNINGS; Level Code Message @@ -789,16 +786,11 @@ TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Dynamic row_format=DYNAMIC SET GLOBAL innodb_file_format=Antelope; ALTER TABLE t1 ADD COLUMN f1 INT; -Warnings: -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Compact row_format=DYNAMIC +t1 Dynamic row_format=DYNAMIC SET GLOBAL innodb_file_format=Barracuda; ALTER TABLE t1 ADD COLUMN f2 INT; SHOW WARNINGS; diff --git a/mysql-test/suite/innodb_zip/r/innodb-zip.result b/mysql-test/suite/innodb_zip/r/innodb-zip.result index 584a8a5d647..2695df64187 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-zip.result +++ b/mysql-test/suite/innodb_zip/r/innodb-zip.result @@ -22,9 +22,6 @@ Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t1(a int primary key) engine=innodb row_format=dynamic; -Warnings: -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t2(a int primary key) engine=innodb row_format=redundant; create table t3(a int primary key) engine=innodb row_format=compact; create table t4(a int primary key) engine=innodb key_block_size=9; @@ -75,7 +72,7 @@ SELECT table_schema, table_name, row_format, data_length, index_length FROM info table_schema table_name row_format data_length index_length mysqltest_innodb_zip t0 Compact {valid} 0 mysqltest_innodb_zip t00 Compact {valid} 0 -mysqltest_innodb_zip t1 Compact {valid} 0 +mysqltest_innodb_zip t1 Dynamic {valid} 0 mysqltest_innodb_zip t10 Dynamic {valid} 0 mysqltest_innodb_zip t11 Compressed 1024 0 mysqltest_innodb_zip t12 Compressed 1024 0 @@ -295,19 +292,16 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table `mysqltest_innodb_zip`.`t6` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t7 (id int primary key) engine = innodb row_format = dynamic; -ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; table_schema table_name row_format data_length index_length +mysqltest_innodb_zip t7 Dynamic {valid} 0 mysqltest_innodb_zip t8 Compact {valid} 0 mysqltest_innodb_zip t9 Redundant {valid} 0 -drop table t8, t9; +drop table t7, t8, t9; set global innodb_file_per_table = on; set global innodb_file_format = `0`; create table t1 (id int primary key) engine = innodb key_block_size = 1; @@ -339,19 +333,16 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelop Error 1005 Can't create table `mysqltest_innodb_zip`.`t6` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t7 (id int primary key) engine = innodb row_format = dynamic; -ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1005 Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; table_schema table_name row_format data_length index_length +mysqltest_innodb_zip t7 Dynamic {valid} 0 mysqltest_innodb_zip t8 Compact {valid} 0 mysqltest_innodb_zip t9 Redundant {valid} 0 -drop table t8, t9; +drop table t7, t8, t9; set global innodb_file_per_table=1; set global innodb_file_format=Antelope; set global innodb_file_per_table=on; diff --git a/mysql-test/suite/innodb_zip/t/innodb-create-options.test b/mysql-test/suite/innodb_zip/t/innodb-create-options.test index 53d739feccb..282b588c841 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-create-options.test +++ b/mysql-test/suite/innodb_zip/t/innodb-create-options.test @@ -244,10 +244,10 @@ CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; --replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; --replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; ---replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; @@ -264,11 +264,10 @@ SHOW WARNINGS; --error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; SHOW WARNINGS; SET GLOBAL innodb_file_format=Barracuda; -DROP TABLE IF EXISTS t1; +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; SET GLOBAL innodb_file_format=Antelope; ALTER TABLE t1 ADD COLUMN f1 INT; @@ -296,11 +295,10 @@ SHOW WARNINGS; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; --replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; ---replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; @@ -317,9 +315,9 @@ SHOW WARNINGS; --error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ALTER TABLE t1 ROW_FORMAT=COMPACT; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test index e8ba98f14ce..e9bac78f99e 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-zip.test +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -275,16 +275,14 @@ create table t6 (id int primary key) engine = innodb row_format = compressed; --replace_regex / - .*[0-9]*[)]/)/ show warnings; --replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; ---replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; --replace_result 16384 {valid} 8192 {valid} 4096 {valid} --eval $query_i_s -drop table t8, t9; +drop table t7, t8, t9; #test valid values with innodb_file_format unset set global innodb_file_per_table = on; @@ -312,17 +310,14 @@ show warnings; create table t6 (id int primary key) engine = innodb row_format = compressed; --replace_regex / - .*[0-9]*[)]/)/ show warnings; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; ---replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; --replace_result 16384 {valid} 8192 {valid} 4096 {valid} --eval $query_i_s -drop table t8, t9; +drop table t7, t8, t9; eval set global innodb_file_per_table=$per_table; eval set global innodb_file_format=$format; diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result index 6424ba28297..5a223cf65fc 100644 --- a/mysql-test/suite/parts/r/longname.result +++ b/mysql-test/suite/parts/r/longname.result @@ -12,15 +12,6 @@ SUBPARTITION BY HASH ( id2 ) SUBPARTITIONS 2 ( PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); -Warnings: -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; database_name table_name length(table_name) CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( diff --git a/mysql-test/suite/sys_vars/r/innodb_default_row_format_basic.result b/mysql-test/suite/sys_vars/r/innodb_default_row_format_basic.result new file mode 100644 index 00000000000..971b8b9d243 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_default_row_format_basic.result @@ -0,0 +1,48 @@ +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format = 'redundant'; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +redundant +SET GLOBAL innodb_default_row_format = 'dynamic'; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +dynamic +SET GLOBAL innodb_default_row_format = 'compact'; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format = 'compressed'; +ERROR 42000: Variable 'innodb_default_row_format' can't be set to the value of 'compressed' +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format = 'foobar'; +ERROR 42000: Variable 'innodb_default_row_format' can't be set to the value of 'foobar' +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format = 0; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +redundant +SET GLOBAL innodb_default_row_format = 1; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +compact +SET GLOBAL innodb_default_row_format = 2; +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +dynamic +SET GLOBAL innodb_default_row_format = 3; +ERROR 42000: Variable 'innodb_default_row_format' can't be set to the value of '3' +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +dynamic +SET GLOBAL innodb_default_row_format = 123; +ERROR 42000: Variable 'innodb_default_row_format' can't be set to the value of '123' +SELECT @@global.innodb_default_row_format; +@@global.innodb_default_row_format +dynamic +SET GLOBAL innodb_default_row_format = default; diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 161f740dbfb..fd33338cbc8 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -635,6 +635,20 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME INNODB_DEFAULT_ROW_FORMAT +SESSION_VALUE NULL +GLOBAL_VALUE compact +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE compact +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE ENUM +VARIABLE_COMMENT The default ROW FORMAT for all innodb tables created without explicit ROW_FORMAT. Possible values are REDUNDANT, COMPACT, and DYNAMIC. The ROW_FORMAT value COMPRESSED is not allowed +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST redundant,compact,dynamic +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_DEFRAGMENT SESSION_VALUE NULL GLOBAL_VALUE OFF diff --git a/mysql-test/suite/sys_vars/t/innodb_default_row_format_basic.test b/mysql-test/suite/sys_vars/t/innodb_default_row_format_basic.test new file mode 100644 index 00000000000..f9aabf49ba4 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_default_row_format_basic.test @@ -0,0 +1,41 @@ +--source include/have_innodb.inc + +# Check the default value +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 'redundant'; +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 'dynamic'; +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 'compact'; +SELECT @@global.innodb_default_row_format; + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_default_row_format = 'compressed'; +SELECT @@global.innodb_default_row_format; + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_default_row_format = 'foobar'; +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 0; +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 1; +SELECT @@global.innodb_default_row_format; + +SET GLOBAL innodb_default_row_format = 2; +SELECT @@global.innodb_default_row_format; + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_default_row_format = 3; +SELECT @@global.innodb_default_row_format; + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_default_row_format = 123; +SELECT @@global.innodb_default_row_format; + + +SET GLOBAL innodb_default_row_format = default; |