------------------------------------------------------------ revno: 3198 committer: kevin.lewis@oracle.com branch nick: mysql-5.5-innodb timestamp: Thu 2010-10-21 13:37:24 -0500 message: Bug#56628 and Bug#56632 This code change modifies several behaviors that were previously not expected or expected differently or just did not work right. The following rules for create options are now enforced and tested for. # 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 default or 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 CREATE 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) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. # 2) A valid KEY_BLOCK_SIZE is incompatible with COMPACT, DYNAMIC & # REDUNDANT. # 3) KEY_BLOCK_SIZE=1,2,4,8, or 16 as well as ROW_FORMAT=COMPRESSED and # ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope # and innodb_file_per_table=OFF # 4) 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. # 5) 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 KEY_BLOCK_SIZEs. # # See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" In this patch, existing tests innodb_bug54679.test and innodb_bug56632.test are modified to use these rules and a new exaustive test is created to replace them. A future patch can delete these tests. The differences from previous behavior are; 1) KEY_BLOCK_SIZE=0 is allowed in both strict and non-strict mode with no errors or warnings. (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. 3) ROW_FORMAT=FIXED is prevented in strict mode. 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-20101021183724-x6jrypd426cd7gdz @@ -0,0 +1,850 @@ +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 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 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 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 and KEY_BLOCK_SIZE +are rejected with Antelope +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 and 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 +valid KEY_BLOCK_SIZE are remembered but not used with Antelope +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 and KEY_BLOCK_SIZE +# are rejected with innodb_file_per_table=OFF +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:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/r/innodb-zip.result revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -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) === modified file 'mysql-test/suite/innodb/r/innodb_bug54679.result' --- mysql-test/suite/innodb/r/innodb_bug54679.result revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/r/innodb_bug54679.result revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -35,24 +35,28 @@ TABLE_NAME ROW_FORMAT CREATE_OPTIONS bug54679 Redundant row_format=REDUNDANT ALTER TABLE bug54679 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 = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) 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 +bug54679 Redundant row_format=REDUNDANT 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. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. 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; === modified file 'mysql-test/suite/innodb/r/innodb_bug56632.result' --- mysql-test/suite/innodb/r/innodb_bug56632.result revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/r/innodb_bug56632.result revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -2,7 +2,7 @@ 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 +# Test 1) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1, ALTER with neither DROP TABLE IF EXISTS bug56632; Warnings: Note 1051 Unknown table 'bug56632' @@ -12,7 +12,7 @@ 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 +# Test 2) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=2 DROP TABLE IF EXISTS bug56632; Warnings: Note 1051 Unknown table 'bug56632' @@ -27,40 +27,38 @@ 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 +ALTER TABLE bug56632 KEY_BLOCK_SIZE=2; 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 +) 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 Compressed row_format=COMPACT KEY_BLOCK_SIZE=1 -# Test 3) CREATE with KEY_BLOCK_SIZE, ALTER with ROW_FORMAT +bug56632 Compact row_format=COMPACT +# Test 3) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=4, ALTER with ROW_FORMAT=COMPACT DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=4; 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 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 +bug56632 Compressed KEY_BLOCK_SIZE=4 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 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 +bug56632 Compressed KEY_BLOCK_SIZE=4 +# Test 4) StrictMode=ON, CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 DROP TABLE IF EXISTS bug56632; CREATE TABLE bug56632 ( i INT ); SHOW WARNINGS; @@ -73,7 +71,7 @@ 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; +ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8; SHOW CREATE TABLE bug56632; Table Create Table bug56632 CREATE TABLE `bug56632` ( @@ -82,16 +80,62 @@ 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). +# Test 5) StrictMode=ON, CREATE with ROW_FORMAT=COMPRESSED & KEY_BLOCK_SIZE=16, ALTER with neither +DROP TABLE IF EXISTS bug56632 ; +CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +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=COMPRESSED KEY_BLOCK_SIZE=16 +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=COMPRESSED KEY_BLOCK_SIZE=16 +ALTER TABLE bug56632 ADD COLUMN f1 INT; +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=COMPRESSED KEY_BLOCK_SIZE=16 +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=COMPRESSED KEY_BLOCK_SIZE=16 +# Test 6) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=8, ALTER with neither +DROP TABLE IF EXISTS bug56632 ; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=8; +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=8 +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=8 +ALTER TABLE bug56632 ADD COLUMN f1 INT; +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=8 +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=8 +# Test 7) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=9 (invalid). DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=9; 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] +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. 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 +# Test 8) StrictMode=OFF, CREATE with ROW_FORMAT & KEY_BLOCK_SIZE=1, ALTER with neither DROP TABLE IF EXISTS bug56632; Warnings: Note 1051 Unknown table 'bug56632' @@ -124,7 +168,7 @@ 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 +# Test 9) StrictMode=OFF, CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE=2 DROP TABLE IF EXISTS bug56632; CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT; SHOW WARNINGS; @@ -137,45 +181,48 @@ 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; +ALTER TABLE bug56632 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. 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2 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 +bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=2 +# Test 10) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=4, ALTER with ROW_FORMAT=4 DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=4; 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 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 +bug56632 Compressed KEY_BLOCK_SIZE=4 ALTER TABLE bug56632 ROW_FORMAT=COMPACT; Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4 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 +bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=4 +# Test 11) StrictMode=OFF, CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 DROP TABLE IF EXISTS bug56632; CREATE TABLE bug56632 ( i INT ); SHOW WARNINGS; @@ -188,97 +235,147 @@ 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 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. +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=8 +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=8 +# Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMRESSED & KEY_BLOCK_SIZE=16, ALTER with neither +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +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=COMPRESSED KEY_BLOCK_SIZE=16 +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=COMPRESSED KEY_BLOCK_SIZE=16 +ALTER TABLE bug56632 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +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=COMPRESSED KEY_BLOCK_SIZE=16 +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=COMPRESSED KEY_BLOCK_SIZE=16 +# Test 8) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=8, ALTER with neither +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=8; +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=8 +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=8 +ALTER TABLE bug56632 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +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=8 +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=8 +# Test 12) StrictMode=OFF, 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 13) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=5 (invalid), ALTER with ROW_FORMAT=COMPACT. +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=5; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=5. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=5. +SHOW CREATE TABLE bug56632; +Table Create Table +bug56632 CREATE TABLE `bug56632` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=5 +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=5 ALTER TABLE bug56632 ROW_FORMAT=COMPACT; Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=5. SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=5. 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=5 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. +bug56632 Compact row_format=COMPACT KEY_BLOCK_SIZE=5 +# Test 14) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=7 (invalid), ALTER with KEY_BLOCK_SIZE=1. DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=7; Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=7. SHOW WARNINGS; Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=3. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=7. 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=7 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 +bug56632 Compact KEY_BLOCK_SIZE=7 ALTER TABLE bug56632 KEY_BLOCK_SIZE=1; SHOW WARNINGS; Level Code Message === 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-20101021183724-x6jrypd426cd7gdz @@ -0,0 +1,575 @@ +# +# 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 combinations of these two +# parameters on CREATE and ALTER. + +-- 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; + +# 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 default or 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 imcompatible 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) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. +# 2) A valid KEY_BLOCK_SIZE is incompatible with COMPACT, DYNAMIC & +# REDUNDANT. +# 3) KEY_BLOCK_SIZE=1,2,4,8, or 16 as well as ROW_FORMAT=COMPRESSED and +# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope +# and innodb_file_per_table=OFF +# 4) 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. +# 5) 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 KEY_BLOCK_SIZEs. +# +# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" + +# 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 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 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 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 and KEY_BLOCK_SIZE +--echo are rejected with Antelope +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 and KEY_BLOCK_SIZE +--echo # are rejected with innodb_file_per_table=OFF and that +--echo # they can be set to default 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 +--echo valid KEY_BLOCK_SIZE are remembered but not used with Antelope +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 and KEY_BLOCK_SIZE +--echo # are rejected with innodb_file_per_table=OFF +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_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 'mysql-test/suite/innodb/t/innodb-zip.test' --- mysql-test/suite/innodb/t/innodb-zip.test revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/t/innodb-zip.test revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -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 === modified file 'mysql-test/suite/innodb/t/innodb_bug54679.test' --- mysql-test/suite/innodb/t/innodb_bug54679.test revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/t/innodb_bug54679.test revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -37,12 +37,17 @@ 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'; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; +--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='bug54679'; === modified file 'mysql-test/suite/innodb/t/innodb_bug56632.test' --- mysql-test/suite/innodb/t/innodb_bug56632.test revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ mysql-test/suite/innodb/t/innodb_bug56632.test revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -12,46 +12,58 @@ # 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 +# Tests 1-5 use INNODB_STRICT_MODE=ON 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 +# 2) CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=2 # Result; CREATE succeeds, # ALTER quietly converts ROW_FORMAT to compressed. -# 3) CREATE with KEY_BLOCK_SIZE=1, ALTER with ROW_FORMAT=COMPACT +# 3) CREATE with KEY_BLOCK_SIZE=4, 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. +# 4) CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 +# Result; CREATE succeeds, +# ALTER; fails with error ER_CANT_CREATE_TABLE +# 5) CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=16 +# Result; CREATE succeeds, +# ALTER; fails with error ER_CANT_CREATE_TABLE +# 6) CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 +# Result; CREATE succeeds, +# ALTER; fails with error ER_CANT_CREATE_TABLE +# 7) CREATE with KEY_BLOCK_SIZE=9 (invalid), ALTER with neither. # Result; CREATE; fails with error ER_CANT_CREATE_TABLE # -# Tests 6-11 use INNODB_STRICT_MODE=0 which automatically makes +# Tests 6-11 use INNODB_STRICT_MODE=OFF which automatically makes # adjustments if the prameters are incompatible. # -# 6) CREATE with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1, ALTER with neither. +# 8) 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 +# 9) CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=2 # Result; CREATE succeeds, # ALTER quietly converts ROW_FORMAT to compressed. -# 8) CREATE with KEY_BLOCK_SIZE=1, ALTER with ROW_FORMAT=COMPACT +# 10)CREATE with KEY_BLOCK_SIZE=4, 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 +# 11)CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 # 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. +# 12)CREATE with ROW_FORMAT=COMPRESSION & KEY_BLOCK_SIZE=16, ALTER with neither +# Result; CREATE succeeds, +# ALTER succeeds, format stays compressed. +# 13)CREATE with KEY_BLOCK_SIZE=8, ALTER with neither +# Result; CREATE succeeds with ROW_FORMAT=COMPRESSION automatically, +# ALTER succeeds with ROW_FORMAT=COMPRESSION automatically. +# 14) 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. +# 15) CREATE with KEY_BLOCK_SIZE=5 (invalid), ALTER with ROW_FORMAT=COMPACT. +# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE=5 is ignored. +# ALTER succeeds, warns that KEY_BLOCK_SIZE=5 is ignored. +# 16) CREATE with KEY_BLOCK_SIZE=7 (invalid), ALTER with KEY_BLOCK_SIZE=1. +# Result; CREATE succeeds, warns that KEY_BLOCK_SIZE=7 is ignored. # ALTER succeeds, quietly converts ROW_FORMAT to compressed. -- source include/have_innodb.inc @@ -74,28 +86,31 @@ # 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. +# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" SET SESSION innodb_strict_mode = ON; ---echo # Test 1) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither +--echo # Test 1) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=1, 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 +--echo # Test 2) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with KEY_BLOCK_SIZE=2 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; +--disable_result_log +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug56632 KEY_BLOCK_SIZE=2; +--enable_result_log 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 +--echo # Test 3) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=4, ALTER with ROW_FORMAT=COMPACT DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=4; SHOW WARNINGS; SHOW CREATE TABLE bug56632; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; @@ -106,7 +121,7 @@ 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 +--echo # Test 4) StrictMode=ON, CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 DROP TABLE IF EXISTS bug56632; CREATE TABLE bug56632 ( i INT ); SHOW WARNINGS; @@ -114,20 +129,41 @@ 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; +ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8; --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). +--echo # Test 5) StrictMode=ON, CREATE with ROW_FORMAT=COMPRESSED & KEY_BLOCK_SIZE=16, ALTER with neither +DROP TABLE IF EXISTS bug56632 ; +CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +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 CREATE TABLE bug56632; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; + +--echo # Test 6) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=8, ALTER with neither +DROP TABLE IF EXISTS bug56632 ; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=8; +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 CREATE TABLE bug56632; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; + +--echo # Test 7) StrictMode=ON, CREATE with KEY_BLOCK_SIZE=9 (invalid). DROP TABLE IF EXISTS bug56632; --error ER_CANT_CREATE_TABLE -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=9; SHOW WARNINGS; + SET SESSION innodb_strict_mode = OFF; ---echo # Test 6) CREATE with ROW_FORMAT & KEY_BLOCK_SIZE, ALTER with neither +--echo # Test 8) StrictMode=OFF, CREATE with ROW_FORMAT & KEY_BLOCK_SIZE=1, ALTER with neither DROP TABLE IF EXISTS bug56632; CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; SHOW WARNINGS; @@ -138,20 +174,20 @@ 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 +--echo # Test 9) StrictMode=OFF, CREATE with ROW_FORMAT, ALTER with KEY_BLOCK_SIZE=2 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; +ALTER TABLE bug56632 KEY_BLOCK_SIZE=2; 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 +--echo # Test 10) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=4, ALTER with ROW_FORMAT=4 DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=1; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=4; SHOW WARNINGS; SHOW CREATE TABLE bug56632; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; @@ -160,31 +196,53 @@ 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 +--echo # Test 11) StrictMode=OFF, CREATE with neither, ALTER with ROW_FORMAT=COMPACT & KEY_BLOCK_SIZE=8 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; +ALTER TABLE bug56632 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +SHOW CREATE TABLE bug56632; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; + +--echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMRESSED & KEY_BLOCK_SIZE=16, ALTER with neither +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +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 8) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=8, ALTER with neither +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=8; +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 12) StrictMode=OFF, 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 13) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=5 (invalid), ALTER with ROW_FORMAT=COMPACT. +DROP TABLE IF EXISTS bug56632; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=5; SHOW WARNINGS; SHOW CREATE TABLE bug56632; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; @@ -193,9 +251,9 @@ 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. +--echo # Test 14) StrictMode=OFF, CREATE with KEY_BLOCK_SIZE=7 (invalid), ALTER with KEY_BLOCK_SIZE=1. DROP TABLE IF EXISTS bug56632; -CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=3; +CREATE TABLE bug56632 ( i INT ) KEY_BLOCK_SIZE=7; SHOW WARNINGS; SHOW CREATE TABLE bug56632; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 'bug56632'; @@ -204,6 +262,7 @@ 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; === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc revid:sunny.bains@oracle.com-20101021112227-bbr5izx2h3tykqmi +++ storage/innobase/handler/ha_innodb.cc revid:kevin.lewis@oracle.com-20101021183724-x6jrypd426cd7gdz @@ -6512,7 +6512,8 @@ { ibool kbs_specified = FALSE; ibool ret = TRUE; - + enum row_type row_type = form->s->row_type; + const char* row_format_name = "Unknown"; ut_ad(thd != NULL); @@ -6521,13 +6522,37 @@ 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: + row_format_name = "COMPACT"; + break; + case ROW_TYPE_COMPRESSED: + row_format_name = "COMPRESSED"; + break; + case ROW_TYPE_DYNAMIC: + row_format_name = "DYNAMIC"; + break; + case ROW_TYPE_REDUNDANT: + row_format_name = "REDUNDANT"; + break; + case ROW_TYPE_DEFAULT: + row_format_name = "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: @@ -6568,93 +6593,56 @@ 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.", + 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; + } + 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.", + row_format_name); + ret = FALSE; + } + default: + break; } return(ret); @@ -6780,8 +6768,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. */ @@ -6830,10 +6817,9 @@ 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