------------------------------------------------------------ revno: 3219 committer: kevin.lewis@oracle.com branch nick: mysql-5.5-innodb timestamp: Tue 2010-11-02 16:54:23 -0500 message: In this patch, existing tests innodb_bug54679.test and innodb_bug56632.test are removed and replaced by the comprehensive innodb-create-options.test. It uses the rules listed in the comments at the top of that test. This patch introduces these differences from previous behavior; 1) KEY_BLOCK_SIZE=0 is allowed by Innodb in both strict and non-strict mode with no errors or warnings. It was previously used by the server to set KEY_BLOCK_SIZE to undefined. (Bug#56628) 2) An explicit valid non-DEFAULT ROW_FORMAT always takes priority over a valid KEY_BLOCK_SIZE. (bug#56632) 3) Automatic use of COMPRESSED row format is only done if the ROW_FORMAT is DEFAULT or unspecified. 4) ROW_FORMAT=FIXED is prevented in strict mode. This patch also includes various formatting changes for consistency with InnoDB coding standards. Related Bugs Bug#54679: ALTER TABLE causes compressed row_format to revert to compact Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED diff: === added file 'mysql-test/suite/innodb/r/innodb-create-options.result' --- mysql-test/suite/innodb/r/innodb-create-options.result 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/innodb/r/innodb-create-options.result revid:kevin.lewis@oracle.com-20101102215423-drpeyxl5q7xrjfgp @@ -0,0 +1,854 @@ +SET storage_engine=InnoDB; +SET GLOBAL innodb_file_format=`Barracuda`; +SET GLOBAL innodb_file_per_table=ON; +SET SESSION innodb_strict_mode = ON; +# Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +# 'FIXED' is sent to InnoDB since it is used by MyISAM. +# But it is an invalid mode in InnoDB +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +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=COMPACT +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +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=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +# Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +# KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +# Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +# Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) 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=COMPACT +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +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 KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=1 +# Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE +# ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=COMPACT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +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=COMPACT +# Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Error 1005 Can't create table 'test.t1' (errno: 1478) +# Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +# and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope +# and that they can be set to default values during strict mode. +SET GLOBAL innodb_file_format=Antelope; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +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: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +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=COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +SET GLOBAL innodb_file_format=Barracuda; +DROP TABLE IF EXISTS 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; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SET GLOBAL innodb_file_format=Barracuda; +# Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +# and a valid non-zero KEY_BLOCK_SIZE are rejected with +# innodb_file_per_table=OFF and that they can be set to default +# values during strict mode. +SET GLOBAL innodb_file_per_table=OFF; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +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: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +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=COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +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=COMPACT +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +SET GLOBAL innodb_file_per_table=ON; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SET GLOBAL innodb_file_per_table=ON; +################################################## +SET SESSION innodb_strict_mode = OFF; +# Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +# 'FIXED' is sent to InnoDB since it is used by MyISAM. +# It is an invalid mode in InnoDB, use COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +Warnings: +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +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=FIXED +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +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=COMPACT +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +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=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +Warnings: +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +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=FIXED +# Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +# KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +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=COMPACT KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +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 KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +# Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +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=FIXED KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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=COMPACT KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +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 KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +# Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) 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=COMPACT +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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=COMPACT KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=8 +# Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE +# ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPACT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +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=COMPACT KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +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 KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +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=COMPACT +# Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact KEY_BLOCK_SIZE=15 +# Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT +is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +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 WARNINGS; +Level Code Message +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. +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=COMPRESSED KEY_BLOCK_SIZE=1 +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +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 +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 +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +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 +# Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF +and then used again when innodb_file_per_table=ON. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +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=COMPRESSED KEY_BLOCK_SIZE=2 +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +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 +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +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 +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +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 +# Cleanup +DROP TABLE IF EXISTS t1; === modified file 'mysql-test/suite/innodb/r/innodb-zip.result' --- mysql-test/suite/innodb/r/innodb-zip.result revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/r/innodb-zip.result revid:kevin.lewis@oracle.com-20101102215423-drpeyxl5q7xrjfgp @@ -84,8 +84,6 @@ test t9 Compact drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14; alter table t1 key_block_size=0; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0. alter table t1 row_format=dynamic; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; @@ -191,16 +189,9 @@ set global innodb_file_format = `1`; set innodb_strict_mode = off; create table t1 (id int primary key) engine = innodb key_block_size = 0; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=0. drop table t1; set innodb_strict_mode = on; create table t1 (id int primary key) engine = innodb key_block_size = 0; -ERROR HY000: Can't create table 'test.t1' (errno: 1478) -show warnings; -Level Code Message -Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16] -Error 1005 Can't create table 'test.t1' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 9; ERROR HY000: Can't create table 'test.t2' (errno: 1478) show warnings; @@ -219,6 +210,7 @@ SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; table_schema table_name row_format +test t1 Compact test t10 Compact test t11 Redundant test t3 Compressed @@ -228,7 +220,7 @@ test t7 Compressed test t8 Compressed test t9 Dynamic -drop table t3, t4, t5, t6, t7, t8, t9, t10, t11; +drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; create table t1 (id int primary key) engine = innodb key_block_size = 8 row_format = compressed; create table t2 (id int primary key) engine = innodb @@ -254,16 +246,12 @@ Error 1005 Can't create table 'test.t4' (errno: 1478) create table t5 (id int primary key) engine = innodb key_block_size = 8 row_format = default; -ERROR HY000: Can't create table 'test.t5' (errno: 1478) -show warnings; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1005 Can't create table 'test.t5' (errno: 1478) SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; table_schema table_name row_format test t1 Compressed -drop table t1; +test t5 Compressed +drop table t1, t5; create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; ERROR HY000: Can't create table 'test.t1' (errno: 1478) === removed file 'mysql-test/suite/innodb/r/innodb_bug54679.result' --- mysql-test/suite/innodb/r/innodb_bug54679.result revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/r/innodb_bug54679.result 1970-01-01 00:00:00 +0000 @@ -1,88 +0,0 @@ -SET GLOBAL innodb_file_format='Barracuda'; -SET GLOBAL innodb_file_per_table=ON; -SET innodb_strict_mode=ON; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Compressed row_format=COMPRESSED -ALTER TABLE bug54679 ADD COLUMN b INT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Compressed row_format=COMPRESSED -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Compact -ALTER TABLE bug54679 KEY_BLOCK_SIZE=1; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Compressed KEY_BLOCK_SIZE=1 -ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT; -ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 1478) -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Redundant row_format=REDUNDANT -ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug54679 Compressed row_format=REDUNDANT KEY_BLOCK_SIZE=2 -SET GLOBAL innodb_file_format=Antelope; -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; -ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Error 1005 Can't create table '#sql-temporary' (errno: 1478) -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 1478) -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table 'test.bug54679' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1005 Can't create table 'test.bug54679' (errno: 1478) -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; -SET GLOBAL innodb_file_format=Barracuda; -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; -ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 1478) -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 1478) -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table 'test.bug54679' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table 'test.bug54679' (errno: 1478) -SET GLOBAL innodb_file_per_table=ON; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -DROP TABLE bug54679; === removed file 'mysql-test/suite/innodb/r/innodb_bug56632.result' --- mysql-test/suite/innodb/r/innodb_bug56632.result revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/r/innodb_bug56632.result 1970-01-01 00:00:00 +0000 @@ -1,294 +0,0 @@ -SET storage_engine=InnoDB; -SET GLOBAL innodb_file_format=`Barracuda`; -SET GLOBAL innodb_file_per_table=ON; -SET SESSION innodb_strict_mode = ON; -# Test 1) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither -DROP TABLE IF EXISTS bug56632; -Warnings: -Note 1051 Unknown table 'bug56632' -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -ERROR HY000: Can't create table 'test.bug56632' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1005 Can't create table 'test.bug56632' (errno: 1478) -# Test 2) CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -Warnings: -Note 1051 Unknown table 'bug56632' -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 3) CREATE with KEY_BLOCK_SIZE, ALTER with ROW_FORMAT -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed KEY_BLOCK_SIZE=1 -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed KEY_BLOCK_SIZE=1 -# Test 4) CREATE with neither, ALTER with ROW_FORMAT & KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ); -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact -ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact -# Test 5) CREATE with KEY_BLOCK_SIZE=3 (invalid). -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -ERROR HY000: Can't create table 'test.bug56632' (errno: 1478) -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 3. Valid values are [1, 2, 4, 8, 16] -Error 1005 Can't create table 'test.bug56632' (errno: 1478) -SET SESSION innodb_strict_mode = OFF; -# Test 6) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither -DROP TABLE IF EXISTS bug56632; -Warnings: -Note 1051 Unknown table 'bug56632' -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=1 -ALTER TABLE bug56632 ADD COLUMN f1 INT; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL, - `f1` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 7) CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 8) CREATE with KEY_BLOCK_SIZE, ALTER with ROW_FORMAT -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed KEY_BLOCK_SIZE=1 -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 9) CREATE with neither, ALTER with ROW_FORMAT & KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ); -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact -ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 10) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with neither. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=3 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact KEY_BLOCK_SIZE=3 -ALTER TABLE bug56632 ADD COLUMN f1 INT; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL, - `f1` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=3 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact KEY_BLOCK_SIZE=3 -# Test 11) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with ROW_FORMAT=COMPACT. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=3 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact KEY_BLOCK_SIZE=3 -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=3 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=3 -# Test 12) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with KEY_BLOCK_SIZE=1. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=3 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compact KEY_BLOCK_SIZE=3 -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SHOW CREATE TABLE bug56632; -Table Create Table -bug56632 CREATE TABLE `bug56632` ( - `i` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -bug56632 Compressed KEY_BLOCK_SIZE=1 -# Cleanup -DROP TABLE IF EXISTS bug56632; === added file 'mysql-test/suite/innodb/t/innodb-create-options.test' --- mysql-test/suite/innodb/t/innodb-create-options.test 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/innodb/t/innodb-create-options.test revid:kevin.lewis@oracle.com-20101102215423-drpeyxl5q7xrjfgp @@ -0,0 +1,575 @@ +# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE +# Related bugs; +# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact +# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error +# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED +# Rules for interpreting CREATE_OPTIONS +# 1) Create options on an ALTER are added to the options on the +# previous CREATE or ALTER statements. +# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value. +# If the current ROW_FORMAT has explicitly been set to COMPRESSED, +# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE +# will not be used. +# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT. +# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to +# unset or erase the values persisted in the MySQL dictionary and +# by SHOW CTREATE TABLE. +# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are +# both explicitly given, the ROW_FORMAT is always used in non-strict +# mode. +# 6) InnoDB will automatically convert a table to COMPRESSED only if a +# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT +# or has not been used on a previous CREATE TABLE or ALTER TABLE. +# 7) InnoDB strict mode is designed to prevent incompatible create +# options from being used together. +# 8) The non-strict behavior is intended to permit you to import a +# mysqldump file into a database that does not support compressed +# tables, even if the source database contained compressed tables. +# All invalid values and/or incompatible combinations of ROW_FORMAT +# and KEY_BLOCK_SIZE are automatically corrected +# +# *** innodb_strict_mode=ON *** +# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC +# & REDUNDANT. All others are rejected. +# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected. +# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. +# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC & +# REDUNDANT. +# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and +# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope +# and innodb_file_per_table=OFF +# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED +# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified +# as COMPACT, DYNAMIC or REDUNDANT. +# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the +# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified. +# +# *** innodb_strict_mode=OFF *** +# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8. +# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT. +# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid +# ROW_FORMAT is specified. +# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF +# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will +# ignore all non-zero KEY_BLOCK_SIZEs. +# +# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" + +-- source include/have_innodb.inc +SET storage_engine=InnoDB; + +--disable_query_log +# These values can change during the test +LET $innodb_file_format_orig=`select @@innodb_file_format`; +LET $innodb_file_format_max_orig=`select @@innodb_file_format_max`; +LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; +LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; +--enable_query_log + +SET GLOBAL innodb_file_format=`Barracuda`; +SET GLOBAL innodb_file_per_table=ON; + +# The first half of these tests are with strict mode ON. +SET SESSION innodb_strict_mode = ON; + +--echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +DROP TABLE IF EXISTS t1; +--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. +--echo # But it is an invalid mode in InnoDB +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + + +--echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +--echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE +--echo # ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW CREATE TABLE t1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPACT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +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'; + +--echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; +SHOW WARNINGS; + +--echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope +--echo # and that they can be set to default values during strict mode. +SET GLOBAL innodb_file_format=Antelope; +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +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'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +SET GLOBAL innodb_file_format=Barracuda; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_format=Antelope; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD COLUMN f1 INT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SET GLOBAL innodb_file_format=Barracuda; + +--echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with +--echo # innodb_file_per_table=OFF and that they can be set to default +--echo # values during strict mode. +SET GLOBAL innodb_file_per_table=OFF; +DROP TABLE IF EXISTS t1; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +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'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_per_table=OFF; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD COLUMN f1 INT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SET GLOBAL innodb_file_per_table=ON; + +--echo ################################################## +SET SESSION innodb_strict_mode = OFF; + +--echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. +--echo # It is an invalid mode in InnoDB, use COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +--echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +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=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE +--echo # ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +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=COMPRESSED; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +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'; + +--echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +--echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT +--echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + +--echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +--echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF +--echo and then used again when innodb_file_per_table=ON. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; + + +--echo # Cleanup +DROP TABLE IF EXISTS t1; + +--disable_query_log +EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; +EVAL SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; +EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; +EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; +--enable_query_log + === modified file 'mysql-test/suite/innodb/t/innodb-zip.test' --- mysql-test/suite/innodb/t/innodb-zip.test revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/t/innodb-zip.test revid:kevin.lewis@oracle.com-20101102215423-drpeyxl5q7xrjfgp @@ -176,9 +176,7 @@ #Test different values of KEY_BLOCK_SIZE ---error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 0; -show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9; @@ -199,7 +197,7 @@ SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; -drop table t3, t4, t5, t6, t7, t8, t9, t10, t11; +drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; #test different values of ROW_FORMAT with KEY_BLOCK_SIZE create table t1 (id int primary key) engine = innodb @@ -220,14 +218,12 @@ key_block_size = 8 row_format = dynamic; show warnings; ---error ER_CANT_CREATE_TABLE create table t5 (id int primary key) engine = innodb key_block_size = 8 row_format = default; -show warnings; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; -drop table t1; +drop table t1, t5; #test multiple errors --error ER_CANT_CREATE_TABLE === removed file 'mysql-test/suite/innodb/t/innodb_bug54679.test' --- mysql-test/suite/innodb/t/innodb_bug54679.test revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/t/innodb_bug54679.test 1970-01-01 00:00:00 +0000 @@ -1,101 +0,0 @@ -# Test Bug #54679 alter table causes compressed row_format to revert to compact - ---source include/have_innodb.inc - -let $file_format=`select @@innodb_file_format`; -let $file_format_max=`select @@innodb_file_format_max`; -let $file_per_table=`select @@innodb_file_per_table`; -SET GLOBAL innodb_file_format='Barracuda'; -SET GLOBAL innodb_file_per_table=ON; -SET innodb_strict_mode=ON; - -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# The ROW_FORMAT of the table should be preserved when it is not specified -# in ALTER TABLE. -ALTER TABLE bug54679 ADD COLUMN b INT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -DROP TABLE bug54679; - -# Check that the ROW_FORMAT conversion to/from COMPRESSED works. - -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# KEY_BLOCK_SIZE implies COMPRESSED. -ALTER TABLE bug54679 KEY_BLOCK_SIZE=1; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables -WHERE TABLE_NAME='bug54679'; - -# This prevents other than REDUNDANT or COMPACT ROW_FORMAT for new tables. -SET GLOBAL innodb_file_format=Antelope; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; - -SET GLOBAL innodb_file_format=Barracuda; -# This will prevent ROW_FORMAT=COMPRESSED, because the system tablespace -# cannot be compressed. -SET GLOBAL innodb_file_per_table=OFF; - ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -DROP TABLE bug54679; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ -SHOW WARNINGS; -SET GLOBAL innodb_file_per_table=ON; -CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -DROP TABLE bug54679; - -# restore original values, quietly so the test does not fail if those -# defaults are changed --- disable_query_log -EVAL SET GLOBAL innodb_file_format=$file_format; -EVAL SET GLOBAL innodb_file_format_max=$file_format_max; -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; --- enable_query_log === removed file 'mysql-test/suite/innodb/t/innodb_bug56632.test' --- mysql-test/suite/innodb/t/innodb_bug56632.test revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ mysql-test/suite/innodb/t/innodb_bug56632.test 1970-01-01 00:00:00 +0000 @@ -1,216 +0,0 @@ -# -# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED -# http://bugs.mysql.com/56632 -# -# Innodb automatically uses compressed mode when the KEY_BLOCK_SIZE -# parameter is used, except if the ROW_FORMAT is also specified, in -# which case the KEY_BLOCK_SIZE is ignored and a warning is shown. -# But Innodb was getting confused when neither of those parameters -# was used on the ALTER statement after they were both used on the -# CREATE. -# -# This will test the results of all 4 combinations of these two -# parameters of the CREATE and ALTER. -# -# Tests 1-5 use INNODB_STRICT_MODE=1 which returns an error -# if there is anything wrong with the statement. -# -# 1) CREATE with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1, ALTER with neither. -# Result; CREATE; fails with error ER_CANT_CREATE_TABLE -# 2) CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=1 -# Result; CREATE succeeds, -# ALTER quietly converts ROW_FORMAT to compressed. -# 3) CREATE with KEY_BLOCK_SIZE=1, ALTER with ROW_FORMAT=COMPACT -# Result; CREATE quietly converts ROW_FORMAT to compressed, -# ALTER fails with error ER_CANT_CREATE_TABLE. -# 4) CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1 -# Result; CREATE succeeds, -# ALTER; fails with error ER_CANT_CREATE_TABLE -# 5) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with neither. -# Result; CREATE; fails with error ER_CANT_CREATE_TABLE -# -# Tests 6-11 use INNODB_STRICT_MODE=0 which automatically makes -# adjustments if the prameters are incompatible. -# -# 6) CREATE with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1, ALTER with neither. -# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE is ignored. -# ALTER succeeds, no warnings. -# 7) CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=1 -# Result; CREATE succeeds, -# ALTER quietly converts ROW_FORMAT to compressed. -# 8) CREATE with KEY_BLOCK_SIZE=1, ALTER with ROW_FORMAT=COMPACT -# Result; CREATE quietly converts ROW_FORMAT to compressed, -# ALTER succeeds, warns that KEY_BLOCK_SIZE is ignored. -# 9) CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1 -# Result; CREATE succeeds, -# ALTER succeeds, warns that KEY_BLOCK_SIZE is ignored. -# 10) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with neither. -# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE=3 is ignored. -# ALTER succeeds, warns that KEY_BLOCK_SIZE=3 is ignored. -# 11) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with ROW_FORMAT=COMPACT. -# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE=3 is ignored. -# ALTER succeeds, warns that KEY_BLOCK_SIZE=3 is ignored. -# 12) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with KEY_BLOCK_SIZE=1. -# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE=3 is ignored. -# ALTER succeeds, quietly converts ROW_FORMAT to compressed. - --- source include/have_innodb.inc - -SET storage_engine=InnoDB; - ---disable_query_log -# These values can change during the test -LET $innodb_file_format_orig=`select @@innodb_file_format`; -LET $innodb_file_format_max_orig=`select @@innodb_file_format_max`; -LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; ---enable_query_log - -SET GLOBAL innodb_file_format=`Barracuda`; -SET GLOBAL innodb_file_per_table=ON; - -# Innodb strict mode will cause an error on the CREATE or ALTER when; -# 1. both ROW_FORMAT=COMPACT and KEY_BLOCK_SIZE=1, -# 2. KEY_BLOCK_SIZE is not a valid number (0,1,2,4,8,16). -# With innodb_strict_mode = OFF, These errors are corrected -# and just a warning is returned. -SET SESSION innodb_strict_mode = ON; - ---echo # Test 1) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither -DROP TABLE IF EXISTS bug56632; ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; - ---echo # Test 2) CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 3) CREATE with KEY_BLOCK_SIZE, ALTER with ROW_FORMAT -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; ---disable_result_log ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; ---enable_result_log -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 4) CREATE with neither, ALTER with ROW_FORMAT & KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ); -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; ---disable_result_log ---error ER_CANT_CREATE_TABLE -ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; ---enable_result_log -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 5) CREATE with KEY_BLOCK_SIZE=3 (invalid). -DROP TABLE IF EXISTS bug56632; ---error ER_CANT_CREATE_TABLE -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -SHOW WARNINGS; - -SET SESSION innodb_strict_mode = OFF; - ---echo # Test 6) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 ADD COLUMN f1 INT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 7) CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 8) CREATE with KEY_BLOCK_SIZE, ALTER with ROW_FORMAT -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 9) CREATE with neither, ALTER with ROW_FORMAT & KEY_BLOCK_SIZE -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ); -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 10) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with neither. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 ADD COLUMN f1 INT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 11) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with ROW_FORMAT=COMPACT. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 ROW_FORMAT=COMPACT; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Test 12) CREATE with KEY_BLOCK_SIZE=3 (invalid), ALTER with KEY_BLOCK_SIZE=1. -DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; -ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SHOW CREATE TABLE bug56632; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; - ---echo # Cleanup -DROP TABLE IF EXISTS bug56632; - ---disable_query_log -EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -EVAL SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; -EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; -EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; ---enable_query_log - === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc revid:kevin.lewis@oracle.com-20101102150416-47y1ojzyspdjzkqf +++ storage/innobase/handler/ha_innodb.cc revid:kevin.lewis@oracle.com-20101102215423-drpeyxl5q7xrjfgp @@ -6494,6 +6494,33 @@ } /*****************************************************************//** +Return a display name for the row format +@return row format name */ + +const char *get_row_format_name( +/*============================*/ +enum row_type row_format) /*!< in: Row Format */ +{ + switch (row_format) { + case ROW_TYPE_COMPACT: + return("COMPACT"); + case ROW_TYPE_COMPRESSED: + return("COMPRESSED"); + case ROW_TYPE_DYNAMIC: + return("DYNAMIC"); + case ROW_TYPE_REDUNDANT: + return("REDUNDANT"); + case ROW_TYPE_DEFAULT: + return("DEFAULT"); + case ROW_TYPE_FIXED: + return("FIXED"); + default: + break; + } + return("NOT USED"); +} + +/*****************************************************************//** Validates the create options. We may build on this function in future. For now, it checks two specifiers: KEY_BLOCK_SIZE and ROW_FORMAT @@ -6508,9 +6535,9 @@ columns and indexes */ HA_CREATE_INFO* create_info) /*!< in: create info. */ { - ibool kbs_specified = FALSE; + ibool kbs_specified = FALSE; ibool ret = TRUE; - + enum row_type row_type = form->s->row_type; ut_ad(thd != NULL); @@ -6519,13 +6546,28 @@ return(TRUE); } + /* Check for a valid Innodb ROW_FORMAT specifier. For example, + ROW_TYPE_FIXED can be sent to Innodb */ + switch (row_type) { + case ROW_TYPE_COMPACT: + case ROW_TYPE_COMPRESSED: + case ROW_TYPE_DYNAMIC: + case ROW_TYPE_REDUNDANT: + case ROW_TYPE_DEFAULT: + break; + default: + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: invalid ROW_FORMAT specifier."); + ret = FALSE; + } + ut_ad(form != NULL); ut_ad(create_info != NULL); - /* First check if KEY_BLOCK_SIZE was specified. */ - if (create_info->key_block_size - || (create_info->used_fields & HA_CREATE_USED_KEY_BLOCK_SIZE)) { - + /* First check if a non-zero KEY_BLOCK_SIZE was specified. */ + if (create_info->key_block_size) { kbs_specified = TRUE; switch (create_info->key_block_size) { case 1: @@ -6536,13 +6578,12 @@ /* Valid value. */ break; default: - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: invalid" - " KEY_BLOCK_SIZE = %lu." - " Valid values are" - " [1, 2, 4, 8, 16]", - create_info->key_block_size); + push_warning_printf( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: invalid KEY_BLOCK_SIZE = %lu." + " Valid values are [1, 2, 4, 8, 16]", + create_info->key_block_size); ret = FALSE; } } @@ -6550,109 +6591,67 @@ /* If KEY_BLOCK_SIZE was specified, check for its dependencies. */ if (kbs_specified && !srv_file_per_table) { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: KEY_BLOCK_SIZE" - " requires innodb_file_per_table."); + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: KEY_BLOCK_SIZE requires" + " innodb_file_per_table."); ret = FALSE; } if (kbs_specified && srv_file_format < DICT_TF_FORMAT_ZIP) { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: KEY_BLOCK_SIZE" - " requires innodb_file_format >" - " Antelope."); + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: KEY_BLOCK_SIZE requires" + " innodb_file_format > Antelope."); ret = FALSE; } - /* Now check for ROW_FORMAT specifier. */ - if (create_info->used_fields & HA_CREATE_USED_ROW_FORMAT) { - switch (form->s->row_type) { - const char* row_format_name; - case ROW_TYPE_COMPRESSED: - case ROW_TYPE_DYNAMIC: - row_format_name - = form->s->row_type == ROW_TYPE_COMPRESSED - ? "COMPRESSED" - : "DYNAMIC"; - - /* These two ROW_FORMATs require srv_file_per_table - and srv_file_format > Antelope */ - if (!srv_file_per_table) { - push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ROW_FORMAT=%s" - " requires innodb_file_per_table.", - row_format_name); - ret = FALSE; - } - - if (srv_file_format < DICT_TF_FORMAT_ZIP) { - push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ROW_FORMAT=%s" - " requires innodb_file_format >" - " Antelope.", - row_format_name); - ret = FALSE; - } - - /* Cannot specify KEY_BLOCK_SIZE with - ROW_FORMAT = DYNAMIC. - However, we do allow COMPRESSED to be - specified with KEY_BLOCK_SIZE. */ - if (kbs_specified - && form->s->row_type == ROW_TYPE_DYNAMIC) { - push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: cannot specify" - " ROW_FORMAT = DYNAMIC with" - " KEY_BLOCK_SIZE."); - ret = FALSE; - } - - break; - - case ROW_TYPE_REDUNDANT: - case ROW_TYPE_COMPACT: - case ROW_TYPE_DEFAULT: - /* Default is COMPACT. */ - row_format_name - = form->s->row_type == ROW_TYPE_REDUNDANT - ? "REDUNDANT" - : "COMPACT"; - - /* Cannot specify KEY_BLOCK_SIZE with these - format specifiers. */ - if (kbs_specified) { - push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: cannot specify" - " ROW_FORMAT = %s with" - " KEY_BLOCK_SIZE.", - row_format_name); - ret = FALSE; - } - - break; - - default: - push_warning(thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: invalid ROW_FORMAT specifier."); - ret = FALSE; - - } + switch (row_type) { + case ROW_TYPE_COMPRESSED: + case ROW_TYPE_DYNAMIC: + /* These two ROW_FORMATs require srv_file_per_table + and srv_file_format > Antelope */ + if (!srv_file_per_table) { + push_warning_printf( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: ROW_FORMAT=%s requires" + " innodb_file_per_table.", + get_row_format_name(row_type)); + ret = FALSE; + } + + if (srv_file_format < DICT_TF_FORMAT_ZIP) { + push_warning_printf( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: ROW_FORMAT=%s requires" + " innodb_file_format > Antelope.", + get_row_format_name(row_type)); + ret = FALSE; + } + default: + break; + } + + switch (row_type) { + case ROW_TYPE_REDUNDANT: + case ROW_TYPE_COMPACT: + case ROW_TYPE_DYNAMIC: + /* KEY_BLOCK_SIZE is only allowed with Compressed or Default */ + if (kbs_specified) { + push_warning_printf( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: cannot specify ROW_FORMAT = %s" + " with KEY_BLOCK_SIZE.", + get_row_format_name(row_type)); + ret = FALSE; + } + default: + break; } return(ret); @@ -6778,8 +6777,7 @@ goto cleanup; } - if (create_info->key_block_size - || (create_info->used_fields & HA_CREATE_USED_KEY_BLOCK_SIZE)) { + if (create_info->key_block_size) { /* Determine the page_zip.ssize corresponding to the requested page size (key_block_size) in kilobytes. */ @@ -6800,38 +6798,39 @@ } if (!srv_file_per_table) { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: KEY_BLOCK_SIZE" - " requires innodb_file_per_table."); + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: KEY_BLOCK_SIZE requires" + " innodb_file_per_table."); flags = 0; } if (file_format < DICT_TF_FORMAT_ZIP) { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: KEY_BLOCK_SIZE" - " requires innodb_file_format >" - " Antelope."); + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: KEY_BLOCK_SIZE requires" + " innodb_file_format > Antelope."); flags = 0; } if (!flags) { - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ignoring" - " KEY_BLOCK_SIZE=%lu.", - create_info->key_block_size); + push_warning_printf( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: ignoring" + " KEY_BLOCK_SIZE=%lu.", + create_info->key_block_size); } } row_type = form->s->row_type; if (flags) { - /* if KEY_BLOCK_SIZE was specified on this statement and - ROW_FORMAT was not, automatically change ROW_FORMAT to COMPRESSED.*/ - if ( (create_info->used_fields & HA_CREATE_USED_KEY_BLOCK_SIZE) - && !(create_info->used_fields & HA_CREATE_USED_ROW_FORMAT)) { + /* if ROW_FORMAT is set to default, + automatically change it to COMPRESSED.*/ + if (row_type == ROW_TYPE_DEFAULT) { row_type = ROW_TYPE_COMPRESSED; } else if (row_type != ROW_TYPE_COMPRESSED) { /* ROW_FORMAT other than COMPRESSED @@ -6841,8 +6840,7 @@ such combinations can be obtained with ALTER TABLE anyway. */ push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, + thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_ILLEGAL_HA_CREATE_OPTION, "InnoDB: ignoring KEY_BLOCK_SIZE=%lu" " unless ROW_FORMAT=COMPRESSED.", @@ -6867,33 +6865,24 @@ } switch (row_type) { - const char* row_format_name; case ROW_TYPE_REDUNDANT: break; case ROW_TYPE_COMPRESSED: case ROW_TYPE_DYNAMIC: - row_format_name - = row_type == ROW_TYPE_COMPRESSED - ? "COMPRESSED" - : "DYNAMIC"; - if (!srv_file_per_table) { push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, + thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ROW_FORMAT=%s" - " requires innodb_file_per_table.", - row_format_name); + "InnoDB: ROW_FORMAT=%s requires" + " innodb_file_per_table.", + get_row_format_name(row_type)); } else if (file_format < DICT_TF_FORMAT_ZIP) { push_warning_printf( - thd, - MYSQL_ERROR::WARN_LEVEL_WARN, + thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ROW_FORMAT=%s" - " requires innodb_file_format >" - " Antelope.", - row_format_name); + "InnoDB: ROW_FORMAT=%s requires" + " innodb_file_format > Antelope.", + get_row_format_name(row_type)); } else { flags |= DICT_TF_COMPACT | (DICT_TF_FORMAT_ZIP @@ -6905,10 +6894,10 @@ case ROW_TYPE_NOT_USED: case ROW_TYPE_FIXED: default: - push_warning(thd, - MYSQL_ERROR::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: assuming ROW_FORMAT=COMPACT."); + push_warning( + thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA_CREATE_OPTION, + "InnoDB: assuming ROW_FORMAT=COMPACT."); case ROW_TYPE_DEFAULT: case ROW_TYPE_COMPACT: flags = DICT_TF_COMPACT;