| Server IP : 172.67.216.182 / Your IP : 172.71.81.30 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb_zip/r/ |
Upload File : |
SET default_storage_engine=InnoDB; 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' # '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: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: invalid ROW_FORMAT specifier. Error 1031 Table storage engine for 't1' doesn't have this option 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 t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1031 Table storage engine for 't1' doesn't have this option 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 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 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 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 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 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 t1; CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] Error 1031 Table storage engine for 't1' doesn't have this option # 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 CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; DROP TABLE t1; 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 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 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; 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 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 t1; CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1031 Table storage engine for 't1' doesn't have this option CREATE TABLE t1 ( i INT ) 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=DYNAMIC DROP TABLE t1; 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 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 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; 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 t1;