diff options
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/create_options.result | 24 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/innodb-create-options.result | 870 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/innodb-zip.result | 9 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_zip/t/create_options.test | 18 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_zip/t/innodb-create-options.test | 561 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_zip/t/innodb-zip.test | 17 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 9 |
7 files changed, 37 insertions, 1471 deletions
diff --git a/mysql-test/suite/innodb_zip/r/create_options.result b/mysql-test/suite/innodb_zip/r/create_options.result index 2d80894c8cd..1c152229b3c 100644 --- a/mysql-test/suite/innodb_zip/r/create_options.result +++ b/mysql-test/suite/innodb_zip/r/create_options.result @@ -1,4 +1,7 @@ SET default_storage_engine=InnoDB; +SET GLOBAL innodb_file_format=`Barracuda`; +Warnings: +Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html 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 @@ -309,6 +312,10 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelop Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SET GLOBAL innodb_file_format=Barracuda; Warnings: Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html @@ -340,7 +347,7 @@ Level Code Message SET GLOBAL innodb_file_format=Barracuda; Warnings: Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -# Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +# Test 8) StrictMode=ON, Make sure ROW_FORMAT=COMPRESSED # and a valid non-zero KEY_BLOCK_SIZE are rejected with # innodb_file_per_table=OFF and that they can be set to default # values during strict mode. @@ -361,7 +368,12 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; Level Code Message @@ -392,7 +404,11 @@ Level Code Message Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' +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 +SHOW WARNINGS; +Level Code Message ALTER TABLE t1 ROW_FORMAT=COMPACT; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; TABLE_NAME ROW_FORMAT CREATE_OPTIONS @@ -837,3 +853,5 @@ TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Dynamic row_format=DYNAMIC # Cleanup DROP TABLE t1; +Warnings: +Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html diff --git a/mysql-test/suite/innodb_zip/r/innodb-create-options.result b/mysql-test/suite/innodb_zip/r/innodb-create-options.result deleted file mode 100644 index 1b92eb71fba..00000000000 --- a/mysql-test/suite/innodb_zip/r/innodb-create-options.result +++ /dev/null @@ -1,870 +0,0 @@ -SET default_storage_engine=InnoDB; -SET GLOBAL innodb_file_format=`Barracuda`; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -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 'test.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: 140 "Wrong create options") -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: invalid ROW_FORMAT specifier. -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=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 Dynamic -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: invalid ROW_FORMAT specifier. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Dynamic -# Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE -# KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -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 -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=2 -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) 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 -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=1 -# Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: invalid ROW_FORMAT specifier. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -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 -ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Compressed row_format=COMPRESSED key_block_size=1 -# Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Compact row_format=COMPACT -ALTER TABLE t1 KEY_BLOCK_SIZE=2; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -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: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -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=2; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -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=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 -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: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -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 Dynamic -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: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -# Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and -# and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope -# and that they can be set to default values during strict mode. -SET GLOBAL innodb_file_format=Antelope; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -DROP TABLE IF EXISTS t1; -Warnings: -Note 1051 Unknown table 'test.t1' -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -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=2; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -SET GLOBAL innodb_file_format=Barracuda; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; -SET GLOBAL innodb_file_format=Antelope; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -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=4. -Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. -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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 -SHOW WARNINGS; -Level Code Message -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; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -# Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and -# and a valid non-zero KEY_BLOCK_SIZE are rejected with -# innodb_file_per_table=OFF and that they can be set to default -# values during strict mode. -SET GLOBAL innodb_file_per_table=OFF; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -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: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -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: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' -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 Dynamic -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; -SHOW WARNINGS; -Level Code Message -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=DYNAMIC. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. -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=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 Dynamic -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; -Warnings: -Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. -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=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=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 -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=2 -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) 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 -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=1 -# 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=DYNAMIC. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. -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=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=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 ); -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 -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=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 key_block_size=2 -# 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=1; -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=1 -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=1 -ALTER TABLE t1 ROW_FORMAT=COMPACT; -Warnings: -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -SHOW WARNINGS; -Level Code Message -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -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 -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -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 Redundant row_format=REDUNDANT key_block_size=1 -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -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 Dynamic row_format=DYNAMIC key_block_size=1 -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=1 -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 Dynamic -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 half of the page size. -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 Dynamic key_block_size=15 -# Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a -valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT -is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -Level Code Message -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Compressed row_format=COMPRESSED key_block_size=1 -SET GLOBAL innodb_file_format=Antelope; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -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=DYNAMIC. -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=DYNAMIC. -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=COMPRESSED key_block_size=1 -SET GLOBAL innodb_file_format=Barracuda; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -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; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -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 Dynamic row_format=DYNAMIC -SET GLOBAL innodb_file_format=Barracuda; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -Level Code Message -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Dynamic row_format=DYNAMIC -# Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a -valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF -and then used again when innodb_file_per_table=ON. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -Level Code Message -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -TABLE_NAME ROW_FORMAT CREATE_OPTIONS -t1 Compressed row_format=COMPRESSED key_block_size=2 -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE t1 ADD COLUMN f1 INT; -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=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; -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=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; -Warnings: -Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html diff --git a/mysql-test/suite/innodb_zip/r/innodb-zip.result b/mysql-test/suite/innodb_zip/r/innodb-zip.result index 47215a1e077..c715f77b9ba 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-zip.result +++ b/mysql-test/suite/innodb_zip/r/innodb-zip.result @@ -1,4 +1,3 @@ -DROP DATABASE IF EXISTS mysqltest_innodb_zip; CREATE DATABASE mysqltest_innodb_zip; USE mysqltest_innodb_zip; SELECT table_name, row_format, data_length, index_length @@ -316,19 +315,16 @@ Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table `mysqltest_innodb_zip`.`t6` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t7 (id int primary key) engine = innodb row_format = dynamic; -ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table `mysqltest_innodb_zip`.`t7` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; table_schema table_name row_format data_length index_length +mysqltest_innodb_zip t7 Dynamic {valid} 0 mysqltest_innodb_zip t8 Compact {valid} 0 mysqltest_innodb_zip t9 Redundant {valid} 0 -drop table t8, t9; +drop table t7, t8, t9; set global innodb_file_per_table = on; set global innodb_file_format = `0`; Warnings: @@ -409,5 +405,4 @@ select @@innodb_file_format_max; @@innodb_file_format_max Barracuda drop table normal_table, zip_table; -USE test; DROP DATABASE mysqltest_innodb_zip; diff --git a/mysql-test/suite/innodb_zip/t/create_options.test b/mysql-test/suite/innodb_zip/t/create_options.test index 1a3dbdff90a..86ef141cae8 100644 --- a/mysql-test/suite/innodb_zip/t/create_options.test +++ b/mysql-test/suite/innodb_zip/t/create_options.test @@ -63,10 +63,11 @@ SET default_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_per_table_orig=`select @@innodb_file_per_table`; -LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; --enable_query_log +SET GLOBAL innodb_file_format=`Barracuda`; SET GLOBAL innodb_file_per_table=ON; # The first half of these tests are with strict mode ON. @@ -242,8 +243,9 @@ SHOW WARNINGS; --error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; ---error 1478 +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; SET GLOBAL innodb_file_format=Barracuda; DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; @@ -257,7 +259,7 @@ ALTER TABLE t1 ADD COLUMN f2 INT; SHOW WARNINGS; SET GLOBAL innodb_file_format=Barracuda; ---echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +--echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT=COMPRESSED --echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with --echo # innodb_file_per_table=OFF and that they can be set to default --echo # values during strict mode. @@ -269,8 +271,10 @@ SHOW WARNINGS; --error ER_ILLEGAL_HA,1005 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; SHOW WARNINGS; ---error 1005 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'; +DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; @@ -287,8 +291,9 @@ SHOW WARNINGS; --error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; ---error 1478 ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=COMPACT; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ALTER TABLE t1 ROW_FORMAT=REDUNDANT; @@ -522,7 +527,6 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE DROP TABLE t1; --disable_query_log +EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; --enable_query_log - diff --git a/mysql-test/suite/innodb_zip/t/innodb-create-options.test b/mysql-test/suite/innodb_zip/t/innodb-create-options.test deleted file mode 100644 index aeb22514bf6..00000000000 --- a/mysql-test/suite/innodb_zip/t/innodb-create-options.test +++ /dev/null @@ -1,561 +0,0 @@ ---source include/have_innodb.inc -# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE -# Related bugs; -# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact -# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error -# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED -# Rules for interpreting CREATE_OPTIONS -# 1) Create options on an ALTER are added to the options on the -# previous CREATE or ALTER statements. -# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value. -# If the current ROW_FORMAT has explicitly been set to COMPRESSED, -# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE -# will not be used. -# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT. -# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to -# unset or erase the values persisted in the MySQL dictionary and -# by SHOW CTREATE TABLE. -# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are -# both explicitly given, the ROW_FORMAT is always used in non-strict -# mode. -# 6) InnoDB will automatically convert a table to COMPRESSED only if a -# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT -# or has not been used on a previous CREATE TABLE or ALTER TABLE. -# 7) InnoDB strict mode is designed to prevent incompatible create -# options from being used together. -# 8) The non-strict behavior is intended to permit you to import a -# mysqldump file into a database that does not support compressed -# tables, even if the source database contained compressed tables. -# All invalid values and/or incompatible combinations of ROW_FORMAT -# and KEY_BLOCK_SIZE are automatically corrected -# -# *** innodb_strict_mode=ON *** -# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC -# & REDUNDANT. All others are rejected. -# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected. -# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. -# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC & -# REDUNDANT. -# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and -# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope -# and innodb_file_per_table=OFF -# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED -# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified -# as COMPACT, DYNAMIC or REDUNDANT. -# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the -# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified. -# -# *** innodb_strict_mode=OFF *** -# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8. -# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT. -# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid -# ROW_FORMAT is specified. -# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF -# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will -# ignore all non-zero KEY_BLOCK_SIZEs. -# -# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" -# This test case does not try to create tables with KEY_BLOCK_SIZE > 4 -# since they are rejected for InnoDB page sizes of 8k and 16k. -# See innodb_16k and innodb_8k for those tests. - --- source include/have_innodb.inc -SET default_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_per_table_orig=`select @@innodb_file_per_table`; -LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; ---enable_query_log - -SET GLOBAL innodb_file_format=`Barracuda`; -SET GLOBAL innodb_file_per_table=ON; - -# The first half of these tests are with strict mode ON. -SET SESSION innodb_strict_mode = ON; - ---echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 ---echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' -DROP TABLE IF EXISTS t1; ---echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. ---echo # But it is an invalid mode in InnoDB ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; ---replace_regex / - .*[0-9]*[)]/)/ -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'; ---error ER_ILLEGAL_HA_CREATE_OPTION -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 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE ---echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC -DROP TABLE IF EXISTS t1; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; -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'; -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=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ); ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -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'; -ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 KEY_BLOCK_SIZE=2; -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'; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 KEY_BLOCK_SIZE=4; -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'; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 KEY_BLOCK_SIZE=2; -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=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=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; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=COMPACT; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=REDUNDANT; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -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; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; - ---echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and ---echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope ---echo # and that they can be set to default values during strict mode. -SET GLOBAL innodb_file_format=Antelope; -DROP TABLE IF EXISTS t1; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; -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; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 KEY_BLOCK_SIZE=2; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SET GLOBAL innodb_file_format=Barracuda; -DROP TABLE IF EXISTS t1; -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; -SHOW CREATE TABLE t1; -SHOW WARNINGS; -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; -SHOW WARNINGS; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SET GLOBAL innodb_file_format=Barracuda; - ---echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and ---echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with ---echo # innodb_file_per_table=OFF and that they can be set to default ---echo # values during strict mode. -SET GLOBAL innodb_file_per_table=OFF; -DROP TABLE IF EXISTS t1; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; ---replace_regex / - .*[0-9]*[)]/)/ -SHOW WARNINGS; -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; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 KEY_BLOCK_SIZE=1; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -SHOW WARNINGS; ---error ER_ILLEGAL_HA_CREATE_OPTION -ALTER TABLE t1 ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -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; -ALTER TABLE t1 ADD COLUMN f1 INT; -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=2; -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=1; -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=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=DEFAULT KEY_BLOCK_SIZE=1; -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=2; -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=1; -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, ---echo # it defaults to half of the page size. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a ---echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT ---echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Antelope; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Barracuda; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Antelope; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_format=Barracuda; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - ---echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a ---echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF ---echo and then used again when innodb_file_per_table=ON. -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=ON; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=OFF; -ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; -SET GLOBAL innodb_file_per_table=ON; -ALTER TABLE t1 ADD COLUMN f2 INT; -SHOW WARNINGS; -SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; - - ---echo # Cleanup -DROP TABLE IF EXISTS t1; - ---disable_query_log -EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; -EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; ---enable_query_log - diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test index 1c7c7c8c419..8a201cb37ae 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-zip.test +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -1,10 +1,5 @@ -- source include/have_innodb.inc - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest_innodb_zip; ---enable_warnings - CREATE DATABASE mysqltest_innodb_zip; USE mysqltest_innodb_zip; SELECT table_name, row_format, data_length, index_length @@ -18,14 +13,13 @@ let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; - --let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' set session innodb_strict_mode=0; set global innodb_file_per_table=off; set global innodb_file_format=`0`; SET @@global.innodb_stats_on_metadata=ON; - + create table t0(a int primary key) engine=innodb row_format=compressed; create table t00(a int primary key) engine=innodb key_block_size=4 row_format=compressed; @@ -280,21 +274,18 @@ show warnings; create table t6 (id int primary key) engine = innodb row_format = compressed; --replace_regex / - .*[0-9]*[)]/)/ show warnings; ---replace_regex / - .*[0-9]*[)]/)/ ---error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; ---replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; --replace_result 16384 {valid} 8192 {valid} 4096 {valid} --eval $query_i_s -drop table t8, t9; +drop table t7, t8, t9; #test valid values with innodb_file_format unset set global innodb_file_per_table = on; -set global innodb_file_format = `0`; +set global innodb_file_format = `0`; --replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE @@ -369,6 +360,4 @@ eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; --enable_query_log -USE test; DROP DATABASE mysqltest_innodb_zip; - diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index db1d90c06fe..93f5c936915 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -12368,15 +12368,6 @@ create_table_info_t::create_options_are_invalid() } break; case ROW_TYPE_DYNAMIC: - if (!m_allow_file_per_table && !is_temp) { - push_warning_printf( - m_thd, Sql_condition::WARN_LEVEL_WARN, - ER_ILLEGAL_HA_CREATE_OPTION, - "InnoDB: ROW_FORMAT=%s requires" - " innodb_file_per_table.", - get_row_format_name(row_format)); - ret = "ROW_FORMAT"; - } if (!is_temp && srv_file_format < UNIV_FORMAT_B) { push_warning_printf( m_thd, Sql_condition::WARN_LEVEL_WARN, |