diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-create-options.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-create-options.test | 134 |
1 files changed, 39 insertions, 95 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test index 0f8e6f8dda2..c09d707ab11 100644 --- a/mysql-test/suite/innodb/t/innodb-create-options.test +++ b/mysql-test/suite/innodb/t/innodb-create-options.test @@ -55,8 +55,12 @@ # 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. -SET storage_engine=InnoDB; +-- source include/have_innodb.inc +SET default_storage_engine=InnoDB; --disable_query_log # These values can change during the test @@ -76,10 +80,8 @@ SET SESSION innodb_strict_mode = ON; 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; @@ -96,10 +98,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE 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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; @@ -108,29 +108,23 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --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=8; +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=16; +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; @@ -141,27 +135,19 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ); ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ +--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=16; +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; @@ -173,31 +159,25 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE 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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=2; ---replace_regex /#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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=4; ---replace_regex /#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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ +--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=16; +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; @@ -213,20 +193,14 @@ 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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPACT; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=REDUNDANT; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; @@ -240,10 +214,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --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 @@ -251,20 +223,14 @@ SHOW WARNINGS; --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; @@ -276,29 +242,21 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 KEY_BLOCK_SIZE=2; SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /#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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ +SHOW CREATE TABLE t1; SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -312,20 +270,14 @@ SET GLOBAL innodb_file_format=Barracuda; --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=16; ---replace_regex / - .*[0-9]*)/)/ +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; 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; @@ -337,20 +289,14 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=1; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=COMPACT; SHOW WARNINGS; @@ -365,10 +311,7 @@ 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 /#sql-[0-9a-f_]+/#sql-temporary/ ---error ER_CANT_CREATE_TABLE ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /#sql-[0-9a-f_]+/#sql-temporary/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -422,14 +365,14 @@ 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; +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=16; +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; @@ -455,12 +398,12 @@ 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; +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=16; +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; @@ -489,14 +432,14 @@ 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; +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=16; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; SHOW WARNINGS; SHOW CREATE TABLE t1; ALTER TABLE t1 ADD COLUMN f1 INT; @@ -521,7 +464,8 @@ 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 +--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; |