diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-create-options.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-create-options.result | 854 |
1 files changed, 854 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-create-options.result b/mysql-test/suite/innodb/r/innodb-create-options.result new file mode 100644 index 00000000000..aec9d731ce6 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-create-options.result @@ -0,0 +1,854 @@ +SET storage_engine=InnoDB; +SET GLOBAL innodb_file_format=`Barracuda`; +SET GLOBAL innodb_file_per_table=ON; +SET SESSION innodb_strict_mode = ON; +# Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +# 'FIXED' is sent to InnoDB since it is used by MyISAM. +# But it is an invalid mode in InnoDB +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +# Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +# KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +# Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +# Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=1 +# Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE +# ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=COMPACT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +# Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Error 1005 Can't create table 'test.t1' (errno: 1478) +# Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +# and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope +# and that they can be set to default values during strict mode. +SET GLOBAL innodb_file_format=Antelope; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +SET GLOBAL innodb_file_format=Barracuda; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SET GLOBAL innodb_file_format=Barracuda; +# Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and +# and a valid non-zero KEY_BLOCK_SIZE are rejected with +# innodb_file_per_table=OFF and that they can be set to default +# values during strict mode. +SET GLOBAL innodb_file_per_table=OFF; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.t1' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Error 1005 Can't create table 'test.t1' (errno: 1478) +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +SET GLOBAL innodb_file_per_table=ON; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SET GLOBAL innodb_file_per_table=ON; +################################################## +SET SESSION innodb_strict_mode = OFF; +# Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 +# KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' +# 'FIXED' is sent to InnoDB since it is used by MyISAM. +# It is an invalid mode in InnoDB, use COMPACT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +Warnings: +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=FIXED +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; +Warnings: +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=FIXED +# Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +# KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +# Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=FIXED KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ); +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +# Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=2 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed KEY_BLOCK_SIZE=8 +# Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE +# ALTER with each ROW_FORMAT +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPACT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Redundant row_format=REDUNDANT KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16 +ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact +ALTER TABLE t1 ROW_FORMAT=COMPACT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPACT +# Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, it defaults to 8 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; +Warnings: +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact KEY_BLOCK_SIZE=15 +# Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT +is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPRESSED KEY_BLOCK_SIZE=1 +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=DYNAMIC +SET GLOBAL innodb_file_format=Barracuda; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +# Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a +valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF +and then used again when innodb_file_per_table=ON. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=COMPRESSED KEY_BLOCK_SIZE=2 +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE t1 ADD COLUMN f1 INT; +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Compact row_format=DYNAMIC +SET GLOBAL innodb_file_per_table=ON; +ALTER TABLE t1 ADD COLUMN f2 INT; +SHOW WARNINGS; +Level Code Message +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +t1 Dynamic row_format=DYNAMIC +# Cleanup +DROP TABLE IF EXISTS t1; |