diff options
25 files changed, 1119 insertions, 876 deletions
diff --git a/mysql-test/suite/archive/archive.result b/mysql-test/suite/archive/archive.result index 5cf1dd3c230..03748728a7a 100644 --- a/mysql-test/suite/archive/archive.result +++ b/mysql-test/suite/archive/archive.result @@ -12862,6 +12862,5 @@ select * from t1; ERROR HY000: Table 't1' is marked as crashed and should be repaired show warnings; Level Code Message -Warning 127 Got error 127 when reading table `test`.`t1` Error 1194 Table 't1' is marked as crashed and should be repaired drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-create-options.result b/mysql-test/suite/innodb/r/innodb-create-options.result index c73baa60d42..d63515e86d8 100644 --- a/mysql-test/suite/innodb/r/innodb-create-options.result +++ b/mysql-test/suite/innodb/r/innodb-create-options.result @@ -1,4 +1,4 @@ -SET storage_engine=InnoDB; +SET default_storage_engine=InnoDB; SET GLOBAL innodb_file_format=`Barracuda`; SET GLOBAL innodb_file_per_table=ON; SET SESSION innodb_strict_mode = ON; @@ -6,14 +6,14 @@ SET SESSION innodb_strict_mode = ON; # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +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 140 InnoDB: invalid ROW_FORMAT specifier. +Warning 1478 InnoDB: invalid ROW_FORMAT specifier. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -46,11 +46,11 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE 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: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: invalid ROW_FORMAT specifier. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 Compact @@ -61,78 +61,78 @@ 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 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +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") 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 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +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") 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 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +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") -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; 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 +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=8 +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=16; +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=16 +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=16 +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: invalid ROW_FORMAT specifier. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; -ERROR HY000: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") -ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=16; +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=16 +t1 Compressed key_block_size=1 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; SHOW WARNINGS; Level Code Message @@ -146,11 +146,11 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE 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: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 @@ -158,35 +158,35 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE 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: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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=8; -ERROR HY000: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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=16; +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=16 +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; @@ -212,23 +212,23 @@ t1 CREATE TABLE `t1` ( `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: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 @@ -253,7 +253,7 @@ 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 140 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope @@ -261,24 +261,24 @@ Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") SET GLOBAL innodb_file_format=Antelope; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +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 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") 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 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") 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 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; @@ -297,35 +297,42 @@ 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: 140 "Wrong create options") +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 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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; 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: 140 "Wrong create options") +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=COMPACT. +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 -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; Level Code Message @@ -339,23 +346,23 @@ SET GLOBAL innodb_file_format=Barracuda; # 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; +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 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") 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 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") 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 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; @@ -375,23 +382,23 @@ 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: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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: Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") +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 @@ -415,12 +422,8 @@ 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: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Error 1005 Can't create table '#sql-temporary' (errno: 140 "Wrong create options") ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; Level Code Message @@ -437,10 +440,10 @@ SET SESSION innodb_strict_mode = OFF; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; Warnings: -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 @@ -477,10 +480,10 @@ TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Compact ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; Warnings: -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 @@ -489,70 +492,70 @@ t1 Compact row_format=FIXED DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +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; +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=8 +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=8 +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=16; +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=16 +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=16 +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 @@ -560,10 +563,10 @@ DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ); ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 @@ -571,32 +574,32 @@ DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ); ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. +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; +ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. +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=8 +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=16; +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=16 +t1 Compressed key_block_size=1 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; SHOW WARNINGS; Level Code Message @@ -611,28 +614,28 @@ TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Compact row_format=COMPACT ALTER TABLE t1 KEY_BLOCK_SIZE=2; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2 unless ROW_FORMAT=COMPRESSED. +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 @@ -650,23 +653,23 @@ 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; +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=8 +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=16; +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=16 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 ALTER TABLE t1 ADD COLUMN f1 INT; SHOW WARNINGS; Level Code Message @@ -675,40 +678,40 @@ 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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1 ALTER TABLE t1 ROW_FORMAT=COMPACT; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +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=16 +t1 Compact row_format=COMPACT key_block_size=1 ALTER TABLE t1 ROW_FORMAT=REDUNDANT; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +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=16 +t1 Redundant row_format=REDUNDANT key_block_size=1 ALTER TABLE t1 ROW_FORMAT=DYNAMIC; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=16 unless ROW_FORMAT=COMPRESSED. +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=16 +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=16 +t1 Compressed row_format=COMPRESSED key_block_size=1 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; Level Code Message @@ -721,14 +724,15 @@ 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 +# 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 140 InnoDB: ignoring KEY_BLOCK_SIZE=15. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=15. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=15. +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 @@ -745,16 +749,16 @@ t1 Compressed row_format=COMPRESSED key_block_size=1 SET GLOBAL innodb_file_format=Antelope; ALTER TABLE t1 ADD COLUMN f1 INT; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 @@ -775,12 +779,12 @@ t1 Dynamic row_format=DYNAMIC SET GLOBAL innodb_file_format=Antelope; ALTER TABLE t1 ADD COLUMN f1 INT; Warnings: -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +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 @@ -803,20 +807,11 @@ 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 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=2. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Warning 140 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 +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; @@ -833,16 +828,11 @@ 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 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Warning 140 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 +t1 Dynamic row_format=DYNAMIC SET GLOBAL innodb_file_per_table=ON; ALTER TABLE t1 ADD COLUMN f2 INT; SHOW WARNINGS; diff --git a/mysql-test/suite/innodb/r/innodb-zip.result b/mysql-test/suite/innodb/r/innodb-zip.result index 8f920b9212e..0f03d59424a 100644 --- a/mysql-test/suite/innodb/r/innodb-zip.result +++ b/mysql-test/suite/innodb/r/innodb-zip.result @@ -1,59 +1,61 @@ +SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; 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; Warnings: -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t00(a int primary key) engine=innodb key_block_size=4 row_format=compressed; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +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_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t1(a int primary key) engine=innodb row_format=dynamic; Warnings: -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t2(a int primary key) engine=innodb row_format=redundant; create table t3(a int primary key) engine=innodb row_format=compact; create table t4(a int primary key) engine=innodb key_block_size=9; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=9. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9. create table t5(a int primary key) engine=innodb key_block_size=1 row_format=redundant; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. set global innodb_file_per_table=on; create table t6(a int primary key) engine=innodb key_block_size=1 row_format=redundant; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. set global innodb_file_format=`1`; create table t7(a int primary key) engine=innodb key_block_size=1 row_format=redundant; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. create table t8(a int primary key) engine=innodb key_block_size=1 row_format=fixed; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. create table t9(a int primary key) engine=innodb key_block_size=1 row_format=compact; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. create table t10(a int primary key) engine=innodb key_block_size=1 row_format=dynamic; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED. create table t11(a int primary key) engine=innodb key_block_size=1 row_format=compressed; create table t12(a int primary key) engine=innodb @@ -62,51 +64,39 @@ create table t13(a int primary key) engine=innodb row_format=compressed; create table t14(a int primary key) engine=innodb key_block_size=9; Warnings: -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=9. -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9. +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t0 Compact 16384 0 -test t00 Compact 16384 0 -test t1 Compact 16384 0 -test t10 Dynamic 16384 0 +test t0 Compact {valid} 0 +test t00 Compact {valid} 0 +test t1 Compact {valid} 0 +test t10 Dynamic {valid} 0 test t11 Compressed 1024 0 test t12 Compressed 1024 0 -test t13 Compressed 8192 0 -test t14 Compact 16384 0 -test t2 Redundant 16384 0 -test t3 Compact 16384 0 -test t4 Compact 16384 0 -test t5 Redundant 16384 0 -test t6 Redundant 16384 0 -test t7 Redundant 16384 0 -test t8 Compact 16384 0 -test t9 Compact 16384 0 +test t13 Compressed {valid} 0 +test t14 Compact {valid} 0 +test t2 Redundant {valid} 0 +test t3 Compact {valid} 0 +test t4 Compact {valid} 0 +test t5 Redundant {valid} 0 +test t6 Redundant {valid} 0 +test t7 Redundant {valid} 0 +test t8 Compact {valid} 0 +test t9 Compact {valid} 0 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14; alter table t1 key_block_size=0; alter table t1 row_format=dynamic; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t1 Dynamic 16384 0 +test t1 Dynamic {valid} 0 alter table t1 row_format=compact; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t1 Compact 16384 0 +test t1 Compact {valid} 0 alter table t1 row_format=redundant; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t1 Redundant 16384 0 +test t1 Redundant {valid} 0 drop table t1; create table t1(a int not null, b text, index(b(10))) engine=innodb key_block_size=1; @@ -123,37 +113,17 @@ rollback; select a,left(b,40) from t1 natural join t2; a left(b,40) 1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 test t1 Compressed 2048 1024 -test t2 Compact 16384 0 +test t2 Compact {valid} 0 drop table t1,t2; -SET SESSION innodb_strict_mode = off; -CREATE TABLE t1( -c TEXT NOT NULL, d TEXT NOT NULL, -PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. -CREATE TABLE t1( -c TEXT NOT NULL, d TEXT NOT NULL, -PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII; -ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. -CREATE TABLE t1( -c TEXT NOT NULL, d TEXT NOT NULL, -PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII; -drop table t1; -CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. -CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); -DROP TABLE t1; create table t1( c1 int not null, c2 blob, c3 blob, c4 blob, primary key(c1, c2(22), c3(22))) engine = innodb row_format = dynamic; @@ -206,147 +176,119 @@ create table t2 (id int primary key) engine = innodb key_block_size = 9; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") create table t3 (id int primary key) engine = innodb key_block_size = 1; create table t4 (id int primary key) engine = innodb key_block_size = 2; create table t5 (id int primary key) engine = innodb key_block_size = 4; -create table t6 (id int primary key) engine = innodb key_block_size = 8; -create table t7 (id int primary key) engine = innodb key_block_size = 16; create table t8 (id int primary key) engine = innodb row_format = compressed; create table t9 (id int primary key) engine = innodb row_format = dynamic; create table t10(id int primary key) engine = innodb row_format = compact; create table t11(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'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t1 Compact 16384 0 -test t10 Compact 16384 0 -test t11 Redundant 16384 0 +test t1 Compact {valid} 0 +test t10 Compact {valid} 0 +test t11 Redundant {valid} 0 test t3 Compressed 1024 0 -test t4 Compressed 2048 0 -test t5 Compressed 4096 0 -test t6 Compressed 8192 0 -test t7 Compressed 16384 0 -test t8 Compressed 8192 0 -test t9 Dynamic 16384 0 -drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; +test t4 Compressed {valid} 0 +test t5 Compressed {valid} 0 +test t8 Compressed {valid} 0 +test t9 Dynamic {valid} 0 +drop table t1, t3, t4, t5, t8, t9, t10, t11; create table t1 (id int primary key) engine = innodb -key_block_size = 8 row_format = compressed; +key_block_size = 4 row_format = compressed; create table t2 (id int primary key) engine = innodb -key_block_size = 8 row_format = redundant; +key_block_size = 4 row_format = redundant; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") create table t3 (id int primary key) engine = innodb -key_block_size = 8 row_format = compact; +key_block_size = 4 row_format = compact; ERROR HY000: Can't create table 'test.t3' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t3' (errno: 140 "Wrong create options") create table t4 (id int primary key) engine = innodb -key_block_size = 8 row_format = dynamic; +key_block_size = 4 row_format = dynamic; ERROR HY000: Can't create table 'test.t4' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t4' (errno: 140 "Wrong create options") create table t5 (id int primary key) engine = innodb -key_block_size = 8 row_format = default; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +key_block_size = 4 row_format = default; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t1 Compressed 8192 0 -test t5 Compressed 8192 0 +test t1 Compressed 4096 0 +test t5 Compressed 4096 0 drop table t1, t5; create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; ERROR HY000: Can't create table 'test.t1' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Warning 140 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +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") create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = compact; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Warning 140 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = dynamic; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Warning 140 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 set global innodb_file_per_table = off; create table t1 (id int primary key) engine = innodb key_block_size = 1; ERROR HY000: Can't create table 'test.t1' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") create table t2 (id int primary key) engine = innodb key_block_size = 2; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") create table t3 (id int primary key) engine = innodb key_block_size = 4; ERROR HY000: Can't create table 'test.t3' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t3' (errno: 140 "Wrong create options") -create table t4 (id int primary key) engine = innodb key_block_size = 8; -ERROR HY000: Can't create table 'test.t4' (errno: 140 "Wrong create options") -show warnings; -Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Error 1005 Can't create table 'test.t4' (errno: 140 "Wrong create options") -create table t5 (id int primary key) engine = innodb key_block_size = 16; -ERROR HY000: Can't create table 'test.t5' (errno: 140 "Wrong create options") -show warnings; -Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Error 1005 Can't create table 'test.t5' (errno: 140 "Wrong create options") create table t6 (id int primary key) engine = innodb row_format = compressed; ERROR HY000: Can't create table 'test.t6' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table 'test.t6' (errno: 140 "Wrong create options") create table t7 (id int primary key) engine = innodb row_format = dynamic; ERROR HY000: Can't create table 'test.t7' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. Error 1005 Can't create table 'test.t7' (errno: 140 "Wrong create options") 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'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t8 Compact 16384 0 -test t9 Redundant 16384 0 +test t8 Compact {valid} 0 +test t9 Redundant {valid} 0 drop table t8, t9; set global innodb_file_per_table = on; set global innodb_file_format = `0`; @@ -354,55 +296,40 @@ create table t1 (id int primary key) engine = innodb key_block_size = 1; ERROR HY000: Can't create table 'test.t1' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t1' (errno: 140 "Wrong create options") create table t2 (id int primary key) engine = innodb key_block_size = 2; ERROR HY000: Can't create table 'test.t2' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t2' (errno: 140 "Wrong create options") create table t3 (id int primary key) engine = innodb key_block_size = 4; ERROR HY000: Can't create table 'test.t3' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t3' (errno: 140 "Wrong create options") -create table t4 (id int primary key) engine = innodb key_block_size = 8; -ERROR HY000: Can't create table 'test.t4' (errno: 140 "Wrong create options") -show warnings; -Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Error 1005 Can't create table 'test.t4' (errno: 140 "Wrong create options") -create table t5 (id int primary key) engine = innodb key_block_size = 16; -ERROR HY000: Can't create table 'test.t5' (errno: 140 "Wrong create options") -show warnings; -Level Code Message -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. -Error 1005 Can't create table 'test.t5' (errno: 140 "Wrong create options") create table t6 (id int primary key) engine = innodb row_format = compressed; ERROR HY000: Can't create table 'test.t6' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t6' (errno: 140 "Wrong create options") create table t7 (id int primary key) engine = innodb row_format = dynamic; ERROR HY000: Can't create table 'test.t7' (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t7' (errno: 140 "Wrong create options") 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'; +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 -mysql innodb_index_stats Compact 16384 0 -mysql innodb_table_stats Compact 16384 0 -test t8 Compact 16384 0 -test t9 Redundant 16384 0 +test t8 Compact {valid} 0 +test t9 Redundant {valid} 0 drop table t8, t9; -set global innodb_file_per_table=0; +set global innodb_file_per_table=1; set global innodb_file_format=Antelope; set global innodb_file_per_table=on; set global innodb_file_format=`Barracuda`; @@ -415,7 +342,7 @@ select @@innodb_file_format_max; Antelope create table zip_table ( c1 int -) engine = innodb key_block_size = 8; +) engine = innodb key_block_size = 4; select @@innodb_file_format_max; @@innodb_file_format_max Barracuda diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 934311adcaf..c33ff12a73f 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -696,8 +696,6 @@ select count(*) from t1 where sca_pic is null; count(*) 2 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); -alter table t1 drop index sca_pic; -alter table t1 add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 @@ -705,9 +703,6 @@ select count(*) from t1 where cat_code='E'; count(*) 0 alter table t1 drop index sca_pic, add index (sca_pic, cat_code); -ERROR 42000: Incorrect index name 'sca_pic' -alter table t1 drop index sca_pic; -alter table t1 add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 @@ -1696,7 +1691,7 @@ variable_value - @innodb_rows_deleted_orig 71 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; variable_value - @innodb_rows_inserted_orig -1071 +1007 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig 866 @@ -2261,7 +2256,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (v varchar(10), c char(10)) row_format=fixed; Warnings: -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2977,8 +2972,11 @@ CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; +set @old_sql_mode = @@sql_mode; +set @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t2 MODIFY a INT NOT NULL; -ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR HY000: Column 'a' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL +set @@sql_mode = @old_sql_mode; DELETE FROM t1; DROP TABLE t2,t1; CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) @@ -3029,7 +3027,7 @@ c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255) ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1( id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB; @@ -3048,8 +3046,8 @@ SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' CREATE TABLE t1 ( a int ) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; SELECT * FROM t2; diff --git a/mysql-test/suite/innodb/r/innodb_bug52745.result b/mysql-test/suite/innodb/r/innodb_bug52745.result index 927ba0e0e53..f4393e8fae0 100644 --- a/mysql-test/suite/innodb/r/innodb_bug52745.result +++ b/mysql-test/suite/innodb/r/innodb_bug52745.result @@ -127,4 +127,4 @@ Warning 1265 Data truncated for column 'col79' at row 1 Warning 1264 Out of range value for column 'col84' at row 1 DROP TABLE bug52745; SET GLOBAL innodb_file_format=Antelope; -SET GLOBAL innodb_file_per_table=0; +SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb/r/innodb_bug53591.result b/mysql-test/suite/innodb/r/innodb_bug53591.result index b0196318801..dbebb9d2d33 100644 --- a/mysql-test/suite/innodb/r/innodb_bug53591.result +++ b/mysql-test/suite/innodb/r/innodb_bug53591.result @@ -4,11 +4,10 @@ set old_alter_table=0; CREATE TABLE bug53591(a text charset utf8 not null) ENGINE=InnoDB KEY_BLOCK_SIZE=1; ALTER TABLE bug53591 ADD PRIMARY KEY(a(220)); -ERROR HY000: Too big row +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is {checked_valid}. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs SHOW WARNINGS; Level Code Message -Error 139 Too big row -Error 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is {checked_valid}. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs DROP TABLE bug53591; SET GLOBAL innodb_file_format=Antelope; -SET GLOBAL innodb_file_per_table=0; +SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb/r/innodb_bug60049.result b/mysql-test/suite/innodb/r/innodb_bug60049.result index a1788a8ab0a..8e3be130e48 100644 --- a/mysql-test/suite/innodb/r/innodb_bug60049.result +++ b/mysql-test/suite/innodb/r/innodb_bug60049.result @@ -1,9 +1,10 @@ -set @@global.innodb_fast_shutdown=0; -CREATE TABLE t(a INT)ENGINE=InnoDB; +call mtr.add_suppression('InnoDB: Error: Table "mysql"."innodb_(table|index)_stats" not found'); +call mtr.add_suppression('InnoDB: Error: Fetch of persistent statistics requested'); +CREATE TABLE t(a INT)ENGINE=InnoDB STATS_PERSISTENT=0; RENAME TABLE t TO u; DROP TABLE u; SELECT @@innodb_fast_shutdown; @@innodb_fast_shutdown 0 Last record of ID_IND root page (9): -1808000018050074000000000000000c5359535f464f524549474e5f434f4c53 +18080000180500c0000000000000000c5359535f464f524549474e5f434f4c53 diff --git a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result index e194f67f5c5..8ec10a86b37 100644 --- a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result +++ b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result @@ -46,7 +46,6 @@ ERROR HY000: Index corrupt_bit_test_ā is corrupted show warnings; Level Code Message Warning 180 InnoDB: Index "idxē" for table "test"."corrupt_bit_test_ā" is marked as corrupted -Warning 180 Got error 180 when reading table `test`.`corrupt_bit_test_ā` Error 1712 Index corrupt_bit_test_ā is corrupted insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001); select * from corrupt_bit_test_ā use index(primary) where a = 10001; diff --git a/mysql-test/suite/innodb/r/innodb_index_large_prefix.result b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result index f966574506a..330ade923b3 100644 --- a/mysql-test/suite/innodb/r/innodb_index_large_prefix.result +++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result @@ -1,11 +1,16 @@ +SET default_storage_engine=InnoDB; set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; set global innodb_large_prefix=1; -create table worklog5743(a TEXT not null, primary key (a(1000))) -ROW_FORMAT=DYNAMIC, engine = innodb; +### Test 1 ### +create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC; +show warnings; +Level Code Message insert into worklog5743 values(repeat("a", 20000)); update worklog5743 set a = (repeat("b", 16000)); create index idx on worklog5743(a(2000)); +show warnings; +Level Code Message begin; update worklog5743 set a = (repeat("x", 17000)); select @@session.tx_isolation; @@ -26,9 +31,13 @@ a = repeat("x", 17000) 1 rollback; drop table worklog5743; -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; +### Test 2 ### +create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; +show warnings; +Level Code Message create index idx on worklog5743(a1, a2(2000)); +show warnings; +Level Code Message insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; @@ -49,8 +58,8 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) rollback; drop table worklog5743; -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; +### Test 3 ### +create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; create index idx on worklog5743(a1, a2(50)); insert into worklog5743 values(9, repeat("a", 10000)); begin; @@ -72,48 +81,328 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) rollback; drop table worklog5743; -create table worklog5743_2(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; -create table worklog5743_4(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; -create index idx1 on worklog5743_2(a1, a2(942)); -ERROR HY000: Too big row -create index idx1 on worklog5743_2(a1, a2(940)); -create index idx1 on worklog5743_4(a1, a2(1966)); -ERROR HY000: Too big row -create index idx1 on worklog5743_4(a1, a2(1964)); +### Test 4 ### +create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1; +create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2; +create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4; +create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8; +create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=16; +set global innodb_large_prefix=0; +create index idx1 on worklog5743_1(a2(4000)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 767 bytes +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +set global innodb_large_prefix=1; +create index idx2 on worklog5743_1(a2(4000)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx3 on worklog5743_1(a2(436)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx4 on worklog5743_1(a2(434)); +show warnings; +Level Code Message +create index idx5 on worklog5743_1(a1, a2(430)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx6 on worklog5743_1(a1, a2(428)); +show warnings; +Level Code Message +set global innodb_large_prefix=0; +create index idx1 on worklog5743_2(a2(4000)); +Warnings: +Warning 1071 Specified key was too long; max key length is 767 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 767 bytes +set global innodb_large_prefix=1; +create index idx2 on worklog5743_2(a2(4000)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx3 on worklog5743_2(a2(948)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx4 on worklog5743_2(a2(946)); +show warnings; +Level Code Message +create index idx5 on worklog5743_2(a1, a2(942)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx6 on worklog5743_2(a1, a2(940)); +show warnings; +Level Code Message +set global innodb_large_prefix=0; +create index idx1 on worklog5743_4(a2(4000)); +Warnings: +Warning 1071 Specified key was too long; max key length is 767 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 767 bytes +set global innodb_large_prefix=1; +create index idx2 on worklog5743_4(a2(4000)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx3 on worklog5743_4(a2(1972)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx4 on worklog5743_4(a2(1970)); +show warnings; +Level Code Message +create index idx5 on worklog5743_4(a1, a2(1966)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +show warnings; +Level Code Message +Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs +create index idx6 on worklog5743_4(a1, a2(1964)); +show warnings; +Level Code Message +set global innodb_large_prefix=0; +create index idx1 on worklog5743_8(a2(1000)); +Warnings: +Warning 1071 Specified key was too long; max key length is 767 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 767 bytes +set global innodb_large_prefix=1; +create index idx2 on worklog5743_8(a2(3073)); +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +create index idx3 on worklog5743_8(a2(3072)); +show warnings; +Level Code Message +create index idx4 on worklog5743_8(a1, a2(3069)); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx5 on worklog5743_8(a1, a2(3068)); +show warnings; +Level Code Message +create index idx6 on worklog5743_8(a1, a2(2000), a3(1069)); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx7 on worklog5743_8(a1, a2(2000), a3(1068)); +show warnings; +Level Code Message +set global innodb_large_prefix=0; +create index idx1 on worklog5743_16(a2(1000)); +Warnings: +Warning 1071 Specified key was too long; max key length is 767 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 767 bytes +set global innodb_large_prefix=1; +create index idx2 on worklog5743_16(a2(3073)); +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +create index idx3 on worklog5743_16(a2(3072)); +show warnings; +Level Code Message +create index idx4 on worklog5743_16(a1, a2(3069)); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx5 on worklog5743_16(a1, a2(3068)); +show warnings; +Level Code Message +create index idx6 on worklog5743_16(a1, a2(2000), a3(1069)); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx7 on worklog5743_16(a1, a2(2000), a3(1068)); +show warnings; +Level Code Message +insert into worklog5743_1 values(9, repeat("a", 10000)); insert into worklog5743_2 values(9, repeat("a", 10000)); insert into worklog5743_4 values(9, repeat("a", 10000)); +insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000)); +insert into worklog5743_16 values(9, repeat("a", 10000), repeat("a", 10000)); +set global innodb_large_prefix=0; +insert into worklog5743_1 values(2, repeat("b", 10000)); +insert into worklog5743_2 values(2, repeat("b", 10000)); +insert into worklog5743_4 values(2, repeat("b", 10000)); +insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000)); +insert into worklog5743_16 values(2, repeat("b", 10000), repeat("b", 10000)); +set global innodb_large_prefix=1; +select a1, left(a2, 20) from worklog5743_1; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_2; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_4; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_8; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_16; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb begin; +update worklog5743_1 set a1 = 1000; update worklog5743_2 set a1 = 1000; update worklog5743_4 set a1 = 1000; +update worklog5743_8 set a1 = 1000; +update worklog5743_16 set a1 = 1000; +select a1, left(a2, 20) from worklog5743_1; +a1 left(a2, 20) +1000 aaaaaaaaaaaaaaaaaaaa +1000 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_2; +a1 left(a2, 20) +1000 aaaaaaaaaaaaaaaaaaaa +1000 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_4; +a1 left(a2, 20) +1000 aaaaaaaaaaaaaaaaaaaa +1000 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_8; +a1 left(a2, 20) +1000 aaaaaaaaaaaaaaaaaaaa +1000 bbbbbbbbbbbbbbbbbbbb +select a1, left(a2, 20) from worklog5743_16; +a1 left(a2, 20) +1000 aaaaaaaaaaaaaaaaaaaa +1000 bbbbbbbbbbbbbbbbbbbb select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ -explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE worklog5743_2 ref idx1 idx1 5 const 1 -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -a1 a2 = repeat("a", 10000) -9 1 -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; -a1 a2 = repeat("a", 10000) -9 1 +1 SIMPLE worklog5743_1 ref idx6 idx6 5 const 1 +explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_2 ref idx6 idx6 5 const 1 +explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_4 ref idx6 idx6 5 const 1 +explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_8 ref idx5,idx7 idx5 5 const 1 +explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_16 ref idx5,idx7 idx5 5 const 1 +select a1, left(a2, 20) from worklog5743_1 where a1 = 9; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa +select a1, left(a2, 20) from worklog5743_16 where a1 = 9; +a1 left(a2, 20) +9 aaaaaaaaaaaaaaaaaaaa SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; @@session.tx_isolation READ-UNCOMMITTED -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -a1 a2 = repeat("a", 10000) -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; -a1 a2 = repeat("a", 10000) +select a1, left(a2, 20) from worklog5743_1 where a1 = 9; +a1 left(a2, 20) +select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +a1 left(a2, 20) +select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +a1 left(a2, 20) +select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +a1 left(a2, 20) +select a1, left(a2, 20) from worklog5743_16 where a1 = 9; +a1 left(a2, 20) rollback; +drop table worklog5743_1; drop table worklog5743_2; drop table worklog5743_4; -create table worklog5743(a1 int, a2 varchar(3000)) -ROW_FORMAT=DYNAMIC, engine = innodb; -create index idx on worklog5743(a1, a2); -insert into worklog5743 values(9, repeat("a", 3000)); +drop table worklog5743_8; +drop table worklog5743_16; +### Test 5 ### +create table worklog5743(a1 int, +a2 varchar(20000), +a3 varchar(3073), +a4 varchar(3072), +a5 varchar(3069), +a6 varchar(3068)) +ROW_FORMAT=DYNAMIC; +create index idx1 on worklog5743(a2); +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +create index idx2 on worklog5743(a3); +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +create index idx3 on worklog5743(a4); +show warnings; +Level Code Message +create index idx4 on worklog5743(a1, a2); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Warning 1071 Specified key was too long; max key length is 3072 bytes +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx5 on worklog5743(a1, a5); +ERROR 42000: Specified key was too long; max key length is 3072 bytes +show warnings; +Level Code Message +Error 1071 Specified key was too long; max key length is 3072 bytes +create index idx6 on worklog5743(a1, a6); +show warnings; +Level Code Message +show create table worklog5743; +Table Create Table +worklog5743 CREATE TABLE `worklog5743` ( + `a1` int(11) DEFAULT NULL, + `a2` varchar(20000) DEFAULT NULL, + `a3` varchar(3073) DEFAULT NULL, + `a4` varchar(3072) DEFAULT NULL, + `a5` varchar(3069) DEFAULT NULL, + `a6` varchar(3068) DEFAULT NULL, + KEY `idx1` (`a2`(3072)), + KEY `idx2` (`a3`(3072)), + KEY `idx3` (`a4`), + KEY `idx6` (`a1`,`a6`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +insert into worklog5743 values(9, +repeat("a", 20000), repeat("a", 3073), +repeat("a", 3072), repeat("a", 3069), +repeat("a", 3068)); begin; update worklog5743 set a1 = 1000; select @@session.tx_isolation; @@ -121,7 +410,7 @@ select @@session.tx_isolation; REPEATABLE-READ explain select a1 from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE worklog5743 ref idx idx 5 const 1 Using index +1 SIMPLE worklog5743 ref idx6 idx6 5 const 1 Using index select a1 from worklog5743 where a1 = 9; a1 9 @@ -133,13 +422,13 @@ select a1 from worklog5743 where a1 = 9; a1 rollback; drop table worklog5743; -create table worklog5743(a TEXT not null, primary key (a(1000))) -engine = innodb; +### Test 6 ### +create table worklog5743(a TEXT not null, primary key (a(1000))); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. -create table worklog5743(a TEXT) engine = innodb; -create index idx on worklog5743(a(1000)); +create table worklog5743(a TEXT); +create index idx on worklog5743(a(768)); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. -create index idx on worklog5743(a(725)); +create index idx on worklog5743(a(767)); insert into worklog5743 values(repeat("a", 20000)); begin; insert into worklog5743 values(repeat("b", 20000)); @@ -160,25 +449,30 @@ a = repeat("x", 25000) 1 rollback; drop table worklog5743; -create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; -create index idx on worklog5743(a(3073)); +### Test 7 ### +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC; +create index idx1 on worklog5743(a(3073)); Warnings: Warning 1071 Specified key was too long; max key length is 3072 bytes -Warning 1071 Specified key was too long; max key length is 3072 bytes create index idx2 on worklog5743(a(3072)); show create table worklog5743; Table Create Table worklog5743 CREATE TABLE `worklog5743` ( `a` text NOT NULL, - KEY `idx` (`a`(3072)), + KEY `idx1` (`a`(3072)), KEY `idx2` (`a`(3072)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC drop table worklog5743; -create table worklog5743(a TEXT not null) engine = innodb; +create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT; +create index idx on worklog5743(a(768)); +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create index idx2 on worklog5743(a(767)); +drop table worklog5743; +create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT; create index idx on worklog5743(a(768)); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. create index idx2 on worklog5743(a(767)); drop table worklog5743; SET GLOBAL innodb_file_format=Antelope; -SET GLOBAL innodb_file_per_table=0; +SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_large_prefix=0; diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result index 0c4413282f7..6f97fdcc0a0 100644 --- a/mysql-test/suite/innodb/r/innodb_information_schema.result +++ b/mysql-test/suite/innodb/r/innodb_information_schema.result @@ -1,18 +1,18 @@ lock_mode lock_type lock_table lock_index lock_rec lock_data -X RECORD `test`.```t'\"_str` `PRIMARY` 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' -X RECORD `test`.```t'\"_str` `PRIMARY` 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' -X RECORD `test`.```t'\"_str` `PRIMARY` 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' -X RECORD `test`.```t'\"_str` `PRIMARY` 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' -X RECORD `test`.```t'\"_str` `PRIMARY` 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' -X RECORD `test`.```t'\"_str` `PRIMARY` 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' -X RECORD `test`.```t'\"_str` `PRIMARY` 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' -X RECORD `test`.```t'\"_str` `PRIMARY` 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' -X RECORD `test`.`t_min` `PRIMARY` 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 -X RECORD `test`.`t_min` `PRIMARY` 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 -X RECORD `test`.`t_max` `PRIMARY` 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 -X RECORD `test`.`t_max` `PRIMARY` 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 -X RECORD `test`.```t'\"_str` `PRIMARY` 1 supremum pseudo-record -X RECORD `test`.```t'\"_str` `PRIMARY` 1 supremum pseudo-record +X RECORD `test`.```t'\"_str` PRIMARY 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' +X RECORD `test`.```t'\"_str` PRIMARY 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' +X RECORD `test`.```t'\"_str` PRIMARY 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' +X RECORD `test`.```t'\"_str` PRIMARY 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' +X RECORD `test`.```t'\"_str` PRIMARY 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' +X RECORD `test`.```t'\"_str` PRIMARY 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' +X RECORD `test`.```t'\"_str` PRIMARY 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' +X RECORD `test`.```t'\"_str` PRIMARY 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' +X RECORD `test`.`t_min` PRIMARY 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 +X RECORD `test`.`t_min` PRIMARY 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 +X RECORD `test`.`t_max` PRIMARY 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 +X RECORD `test`.`t_max` PRIMARY 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 +X RECORD `test`.```t'\"_str` PRIMARY 1 supremum pseudo-record +X RECORD `test`.```t'\"_str` PRIMARY 1 supremum pseudo-record lock_table COUNT(*) `test`.`t_max` 2 `test`.`t_min` 2 diff --git a/mysql-test/suite/innodb/r/innodb_information_schema_buffer.result b/mysql-test/suite/innodb/r/innodb_information_schema_buffer.result index b4a350e77a3..dcdf3082067 100644 --- a/mysql-test/suite/innodb/r/innodb_information_schema_buffer.result +++ b/mysql-test/suite/innodb/r/innodb_information_schema_buffer.result @@ -6,28 +6,28 @@ CREATE TABLE infoschema_buffer_test (col1 INT) ENGINE = INNODB; INSERT INTO infoschema_buffer_test VALUES(9); SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE -test/infoschema_buffer_test GEN_CLUST_INDEX 1 29 FILE_PAGE INDEX +`test`.`infoschema_buffer_test` GEN_CLUST_INDEX 1 29 FILE_PAGE INDEX INSERT INTO infoschema_buffer_test VALUES(19); SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE -test/infoschema_buffer_test GEN_CLUST_INDEX 2 58 FILE_PAGE INDEX +`test`.`infoschema_buffer_test` GEN_CLUST_INDEX 2 58 FILE_PAGE INDEX CREATE INDEX idx ON infoschema_buffer_test(col1); SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and INDEX_NAME = "idx" and PAGE_TYPE="index"; TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE -test/infoschema_buffer_test idx 2 32 FILE_PAGE INDEX +`test`.`infoschema_buffer_test` idx 2 32 FILE_PAGE INDEX DROP TABLE infoschema_buffer_test; SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test"; +WHERE TABLE_NAME like "%infoschema_buffer_test%"; TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE CREATE TABLE infoschema_parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; @@ -38,93 +38,9 @@ ON DELETE CASCADE) ENGINE=INNODB; SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_child" and PAGE_STATE="file_page" +WHERE TABLE_NAME like "%infoschema_child%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; count(*) 2 DROP TABLE infoschema_child; DROP TABLE infoschema_parent; -show create table information_schema.innodb_buffer_page; -Table Create Table -INNODB_BUFFER_PAGE CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` ( - `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0', - `BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT '0', - `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGE_TYPE` varchar(64) DEFAULT NULL, - `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0', - `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0', - `IS_HASHED` varchar(3) DEFAULT NULL, - `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0', - `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0', - `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0', - `TABLE_NAME` varchar(1024) DEFAULT NULL, - `INDEX_NAME` varchar(1024) DEFAULT NULL, - `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0', - `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0', - `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGE_STATE` varchar(64) DEFAULT NULL, - `IO_FIX` varchar(64) DEFAULT NULL, - `IS_OLD` varchar(3) DEFAULT NULL, - `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0' -) ENGINE=MEMORY DEFAULT CHARSET=utf8 -show create table information_schema.innodb_buffer_page_lru; -Table Create Table -INNODB_BUFFER_PAGE_LRU CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE_LRU` ( - `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0', - `LRU_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0', - `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGE_TYPE` varchar(64) DEFAULT NULL, - `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0', - `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0', - `IS_HASHED` varchar(3) DEFAULT NULL, - `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0', - `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0', - `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0', - `TABLE_NAME` varchar(1024) DEFAULT NULL, - `INDEX_NAME` varchar(1024) DEFAULT NULL, - `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0', - `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0', - `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0', - `COMPRESSED` varchar(3) DEFAULT NULL, - `IO_FIX` varchar(64) DEFAULT NULL, - `IS_OLD` varchar(3) DEFAULT NULL, - `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0' -) ENGINE=MEMORY DEFAULT CHARSET=utf8 -show create table information_schema.innodb_buffer_pool_stats; -Table Create Table -INNODB_BUFFER_POOL_STATS CREATE TEMPORARY TABLE `INNODB_BUFFER_POOL_STATS` ( - `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0', - `POOL_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0', - `FREE_BUFFERS` bigint(21) unsigned NOT NULL DEFAULT '0', - `DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0', - `OLD_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0', - `MODIFIED_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0', - `PENDING_DECOMPRESS` bigint(21) unsigned NOT NULL DEFAULT '0', - `PENDING_READS` bigint(21) unsigned NOT NULL DEFAULT '0', - `PENDING_FLUSH_LRU` bigint(21) unsigned NOT NULL DEFAULT '0', - `PENDING_FLUSH_LIST` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGES_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGES_NOT_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGES_MADE_YOUNG_RATE` double NOT NULL DEFAULT '0', - `PAGES_MADE_NOT_YOUNG_RATE` double NOT NULL DEFAULT '0', - `NUMBER_PAGES_READ` bigint(21) unsigned NOT NULL DEFAULT '0', - `NUMBER_PAGES_CREATED` bigint(21) unsigned NOT NULL DEFAULT '0', - `NUMBER_PAGES_WRITTEN` bigint(21) unsigned NOT NULL DEFAULT '0', - `PAGES_READ_RATE` double NOT NULL DEFAULT '0', - `PAGES_CREATE_RATE` double NOT NULL DEFAULT '0', - `PAGES_WRITTEN_RATE` double NOT NULL DEFAULT '0', - `NUMBER_PAGES_GET` bigint(21) unsigned NOT NULL DEFAULT '0', - `HIT_RATE` bigint(21) unsigned NOT NULL DEFAULT '0', - `YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0', - `NOT_YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0', - `NUMBER_PAGES_READ_AHEAD` bigint(21) unsigned NOT NULL DEFAULT '0', - `NUMBER_READ_AHEAD_EVICTED` bigint(21) unsigned NOT NULL DEFAULT '0', - `READ_AHEAD_RATE` double NOT NULL DEFAULT '0', - `READ_AHEAD_EVICTED_RATE` double NOT NULL DEFAULT '0', - `LRU_IO_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0', - `LRU_IO_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0', - `UNCOMPRESS_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0', - `UNCOMPRESS_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0' -) ENGINE=MEMORY DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result b/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result index 0c68c8a6975..746072781bd 100644 --- a/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result +++ b/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result @@ -31,6 +31,11 @@ SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; col_1_varchar = REPEAT("c", 4000) 0 1 +ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +ALTER TABLE worklog5743 ROW_FORMAT=COMPACT; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +ALTER TABLE worklog5743 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; DROP TABLE worklog5743; CREATE TABLE worklog5743 ( col_1_text TEXT (4000) , col_2_text TEXT (4000) , @@ -539,13 +544,15 @@ col_1_blob = REPEAT("c", 4000) DROP TABLE worklog5743_key8; CREATE TABLE worklog5743 ( col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , -col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), +col_3_text TEXT (4000), col_4_blob BLOB (4000), col_5_text TEXT (4000), col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) ) ROW_FORMAT=DYNAMIC, engine = innodb; INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) ); +CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); +CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) @@ -570,6 +577,19 @@ SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; col_1_varbinary = REPEAT("c", 4000) 1 0 +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); +CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); +CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +ERROR HY000: Undo log record is too big. +SHOW WARNINGS; +Level Code Message +Error 1713 Undo log record is too big. DROP TABLE worklog5743; CREATE TABLE worklog5743 ( col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , @@ -607,6 +627,13 @@ REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) ); ROLLBACK; +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varchar = REPEAT("o", 4000); +ERROR HY000: Undo log record is too big. +SHOW WARNINGS; +Level Code Message +Error 1713 Undo log record is too big. SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); col_1_varbinary = REPEAT("c", 4000) @@ -873,6 +900,7 @@ SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 COMMIT; +"Disconnect the connections 1 and 2" DROP TABLE worklog5743; CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , @@ -1172,7 +1200,6 @@ DROP INDEX prefix_idx ON worklog5743; CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (4000)); Warnings: Warning 1071 Specified key was too long; max key length is 3072 bytes -Warning 1071 Specified key was too long; max key length is 3072 bytes INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743; col_1_varbinary = REPEAT("a", 4000) @@ -1230,7 +1257,6 @@ DROP INDEX prefix_idx ON worklog5743; CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000)); Warnings: Warning 1071 Specified key was too long; max key length is 3072 bytes -Warning 1071 Specified key was too long; max key length is 3072 bytes INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; col_1_text = REPEAT("a", 4000) @@ -1288,7 +1314,7 @@ AND col_2_text = REPEAT("o", 4000); col_1_text = REPEAT("c", 4000) ALTER TABLE worklog5743 DROP PRIMARY KEY; ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (950)); -ERROR HY000: Too big row +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; col_1_text = REPEAT("a", 4000) @@ -1343,11 +1369,22 @@ CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , PRIMARY KEY (col_1_varchar(767)) ) engine = innodb; +INSERT INTO worklog5743 VALUES(REPEAT('a',4000),REPEAT('b',4000)); CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1000)); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes -Warning 1071 Specified key was too long; max key length is 767 bytes +ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SHOW CREATE TABLE worklog5743; +Table Create Table +worklog5743 CREATE TABLE `worklog5743` ( + `col_1_varchar` varchar(4000) NOT NULL DEFAULT '', + `col_2_varchar` varchar(4000) DEFAULT NULL, + PRIMARY KEY (`col_1_varchar`(767)), + KEY `prefix_idx` (`col_1_varchar`(767)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT DROP TABLE worklog5743; -SET GLOBAL innodb_file_format=Antelope; -SET GLOBAL innodb_file_per_table=0; -SET GLOBAL innodb_large_prefix=0; +"Disconnect the connection 1" diff --git a/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result b/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result index abd4c243912..e1f6f8608e1 100644 --- a/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result +++ b/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result @@ -86,5 +86,5 @@ col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) 1 1 DROP TABLE worklog5743; SET GLOBAL innodb_file_format=Antelope; -SET GLOBAL innodb_file_per_table=0; +SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_large_prefix=0; diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test index 12c24820031..c699117790a 100644 --- a/mysql-test/suite/innodb/t/innodb-create-options.test +++ b/mysql-test/suite/innodb/t/innodb-create-options.test @@ -1,4 +1,3 @@ ---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 @@ -55,8 +54,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 +79,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 +97,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; @@ -108,29 +107,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 +134,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ +--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 +158,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=2; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=REDUNDANT; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=4; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=DYNAMIC; SHOW WARNINGS; SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ +--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 +192,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPACT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=REDUNDANT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=COMPRESSED; SHOW WARNINGS; @@ -240,10 +213,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 +222,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 +241,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 KEY_BLOCK_SIZE=8; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 KEY_BLOCK_SIZE=2; SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; SET GLOBAL innodb_file_format=Barracuda; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; SET GLOBAL innodb_file_format=Antelope; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ +SHOW CREATE TABLE t1; SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -312,20 +269,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 +288,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 KEY_BLOCK_SIZE=1; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE +--error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ROW_FORMAT=DYNAMIC; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=COMPACT; SHOW WARNINGS; @@ -365,10 +310,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 /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE ALTER TABLE t1 ADD COLUMN f1 INT; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ / - .*[0-9]*)/)/ SHOW WARNINGS; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -422,14 +364,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 +397,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 +431,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 +463,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; diff --git a/mysql-test/suite/innodb/t/innodb-zip.test b/mysql-test/suite/innodb/t/innodb-zip.test index 7e2e2d1d3c9..e4e801bd1f3 100644 --- a/mysql-test/suite/innodb/t/innodb-zip.test +++ b/mysql-test/suite/innodb/t/innodb-zip.test @@ -8,10 +8,15 @@ if (`select plugin_auth_version <= "1.1.8-29.3" from information_schema.plugins let $per_table=`select @@innodb_file_per_table`; let $format=`select @@innodb_file_format`; let $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; +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 @@ -43,20 +48,20 @@ create table t13(a int primary key) engine=innodb row_format=compressed; create table t14(a int primary key) engine=innodb key_block_size=9; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid} +--eval $query_i_s drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14; alter table t1 key_block_size=0; alter table t1 row_format=dynamic; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s alter table t1 row_format=compact; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s alter table t1 row_format=redundant; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s drop table t1; create table t1(a int not null, b text, index(b(10))) engine=innodb @@ -88,35 +93,15 @@ connection default; disconnect a; disconnect b; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +analyze table t1; +analyze table t2; +--replace_result 16384 {valid} 12288 {valid} +--eval $query_i_s drop table t1,t2; -# The following should fail in non-strict mode too. -# (The fix of Bug #50945 only affects REDUNDANT and COMPACT tables.) -SET SESSION innodb_strict_mode = off; ---error ER_TOO_BIG_ROWSIZE -CREATE TABLE t1( - c TEXT NOT NULL, d TEXT NOT NULL, - PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; ---error ER_TOO_BIG_ROWSIZE -CREATE TABLE t1( - c TEXT NOT NULL, d TEXT NOT NULL, - PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII; -CREATE TABLE t1( - c TEXT NOT NULL, d TEXT NOT NULL, - PRIMARY KEY (c(767),d(767))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII; -drop table t1; ---error ER_TOO_BIG_ROWSIZE -CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438))) -ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); -DROP TABLE t1; +# +# Bug #50945 moved to innodb_16k.test, innodb_8k.test, & innodb_4k.test +# # # Test blob column inheritance (mantis issue#36) @@ -182,18 +167,18 @@ set innodb_strict_mode = on; create table t1 (id int primary key) engine = innodb key_block_size = 0; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9; ---replace_regex / - .*[0-9]*)/)/ show warnings; create table t3 (id int primary key) engine = innodb key_block_size = 1; create table t4 (id int primary key) engine = innodb key_block_size = 2; create table t5 (id int primary key) engine = innodb key_block_size = 4; -create table t6 (id int primary key) engine = innodb key_block_size = 8; -create table t7 (id int primary key) engine = innodb key_block_size = 16; +# These tests are now done in innodb_16k, innodb_8k and innodb_4k +# where they get different result depending on page size +# create table t6 (id int primary key) engine = innodb key_block_size = 8; +# create table t7 (id int primary key) engine = innodb key_block_size = 16; #check various ROW_FORMAT values. create table t8 (id int primary key) engine = innodb row_format = compressed; @@ -201,156 +186,110 @@ create table t9 (id int primary key) engine = innodb row_format = dynamic; create table t10(id int primary key) engine = innodb row_format = compact; create table t11(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'; -drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid} +--eval $query_i_s +drop table t1, t3, t4, t5, t8, t9, t10, t11; #test different values of ROW_FORMAT with KEY_BLOCK_SIZE create table t1 (id int primary key) engine = innodb -key_block_size = 8 row_format = compressed; +key_block_size = 4 row_format = compressed; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb -key_block_size = 8 row_format = redundant; ---replace_regex / - .*[0-9]*)/)/ +key_block_size = 4 row_format = redundant; show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb -key_block_size = 8 row_format = compact; ---replace_regex / - .*[0-9]*)/)/ +key_block_size = 4 row_format = compact; show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t4 (id int primary key) engine = innodb -key_block_size = 8 row_format = dynamic; ---replace_regex / - .*[0-9]*)/)/ +key_block_size = 4 row_format = dynamic; show warnings; create table t5 (id int primary key) engine = innodb -key_block_size = 8 row_format = default; +key_block_size = 4 row_format = default; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--eval $query_i_s drop table t1, t5; #test multiple errors ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = compact; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = dynamic; ---replace_regex / - .*[0-9]*)/)/ show warnings; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--eval $query_i_s #test valid values with innodb_file_per_table unset set global innodb_file_per_table = off; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; ---replace_regex / - .*[0-9]*)/)/ -show warnings; ---replace_regex / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -create table t4 (id int primary key) engine = innodb key_block_size = 8; ---replace_regex / - .*[0-9]*)/)/ -show warnings; ---replace_regex / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -create table t5 (id int primary key) engine = innodb key_block_size = 16; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ + +# Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k +# and innodb_4k since they get different warnings with smaller page sizes. + --error ER_CANT_CREATE_TABLE 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; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s drop table t8, t9; #test valid values with innodb_file_format unset set global innodb_file_per_table = on; set global innodb_file_format = `0`; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; ---replace_regex / - .*[0-9]*)/)/ -show warnings; ---replace_regex / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -create table t4 (id int primary key) engine = innodb key_block_size = 8; ---replace_regex / - .*[0-9]*)/)/ show warnings; ---replace_regex / - .*[0-9]*)/)/ ---error ER_CANT_CREATE_TABLE -create table t5 (id int primary key) engine = innodb key_block_size = 16; ---replace_regex / - .*[0-9]*)/)/ -show warnings; ---replace_regex / - .*[0-9]*)/)/ + +# Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k +# and innodb_4k since they get different warnings with smaller page sizes. + --error ER_CANT_CREATE_TABLE 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; -SELECT table_schema, table_name, row_format, data_length, index_length -FROM information_schema.tables WHERE engine='innodb'; +--replace_result 16384 {valid} 8192 {valid} 4096 {valid} +--eval $query_i_s drop table t8, t9; eval set global innodb_file_per_table=$per_table; @@ -368,7 +307,7 @@ create table normal_table ( select @@innodb_file_format_max; create table zip_table ( c1 int -) engine = innodb key_block_size = 8; +) engine = innodb key_block_size = 4; select @@innodb_file_format_max; set global innodb_file_format_max=`Antelope`; select @@innodb_file_format_max; @@ -387,3 +326,4 @@ drop table normal_table, zip_table; eval set global innodb_file_format=$format; eval set global innodb_file_per_table=$per_table; eval set session innodb_strict_mode=$innodb_strict_mode_orig; +SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 4e5d6d7ce90..270a4de5678 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -25,10 +25,11 @@ if (`select plugin_auth_version <= "1.1.8-29.3" from information_schema.plugins ####################################################################### -- source include/have_innodb.inc + let $MYSQLD_DATADIR= `select @@datadir`; + let collation=utf8_unicode_ci; --source include/have_collation.inc - set optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # Save the original values of some variables in order to be able to @@ -450,21 +451,11 @@ INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca select count(*) from t1 where sca_code = 'PD'; select count(*) from t1 where sca_code <= 'PD'; select count(*) from t1 where sca_pic is null; -# this should be fixed by MySQL (see Bug #51451) -# now that http://bugs.mysql.com/49838 is fixed the following ALTER does -# copy the table instead of failing -# --error ER_WRONG_NAME_FOR_INDEX alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); -alter table t1 drop index sca_pic; -alter table t1 add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; select count(*) from t1 where cat_code='E'; -# this should be fixed by MySQL (see Bug #51451) ---error ER_WRONG_NAME_FOR_INDEX alter table t1 drop index sca_pic, add index (sca_pic, cat_code); -alter table t1 drop index sca_pic; -alter table t1 add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; select count(*) from t1 where sca_pic >= 'n'; select sca_pic from t1 where sca_pic is null; @@ -1264,7 +1255,7 @@ drop table t1; # CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB; ---error 1765 +--error 1764 SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE); DROP TABLE t1; @@ -2328,12 +2319,12 @@ CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; -# mysqltest first does replace_regex, then replace_result ---replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ -# Embedded server doesn't chdir to data directory ---replace_result $MYSQLD_DATADIR ./ master-data/ '' ---error 1025 +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +set @old_sql_mode = @@sql_mode; +set @@sql_mode = 'STRICT_TRANS_TABLES'; +--error ER_FK_COLUMN_NOT_NULL ALTER TABLE t2 MODIFY a INT NOT NULL; +set @@sql_mode = @old_sql_mode; DELETE FROM t1; DROP TABLE t2,t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug52745.test b/mysql-test/suite/innodb/t/innodb_bug52745.test index 58bcc264677..3c5d79826f0 100644 --- a/mysql-test/suite/innodb/t/innodb_bug52745.test +++ b/mysql-test/suite/innodb/t/innodb_bug52745.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +-- source include/have_innodb.inc let $file_format=`select @@innodb_file_format`; let $file_per_table=`select @@innodb_file_per_table`; diff --git a/mysql-test/suite/innodb/t/innodb_bug53591.test b/mysql-test/suite/innodb/t/innodb_bug53591.test index f0ed4b93c32..8bc461719b8 100644 --- a/mysql-test/suite/innodb/t/innodb_bug53591.test +++ b/mysql-test/suite/innodb/t/innodb_bug53591.test @@ -1,9 +1,5 @@ ---source include/have_innodb.inc +-- source include/have_innodb.inc -if (`select plugin_auth_version <= "1.1.8-29.3" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB 1.1.8-29.3 or earlier -} let $file_format=`select @@innodb_file_format`; let $file_per_table=`select @@innodb_file_per_table`; @@ -14,8 +10,10 @@ set old_alter_table=0; CREATE TABLE bug53591(a text charset utf8 not null) ENGINE=InnoDB KEY_BLOCK_SIZE=1; --- error 139 +-- replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid} +-- error ER_TOO_BIG_ROWSIZE ALTER TABLE bug53591 ADD PRIMARY KEY(a(220)); +-- replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid} SHOW WARNINGS; DROP TABLE bug53591; diff --git a/mysql-test/suite/innodb/t/innodb_bug60049.test b/mysql-test/suite/innodb/t/innodb_bug60049.test index 70fec8cb959..6760d1a1f02 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60049.test +++ b/mysql-test/suite/innodb/t/innodb_bug60049.test @@ -6,10 +6,8 @@ -- source include/have_innodb.inc -- source include/have_innodb_16k.inc -# -# This test will not work if we don't do full shutdown of innodb -# -set @@global.innodb_fast_shutdown=0; +call mtr.add_suppression('InnoDB: Error: Table "mysql"."innodb_(table|index)_stats" not found'); +call mtr.add_suppression('InnoDB: Error: Fetch of persistent statistics requested'); -- disable_query_log let $create1 = query_get_value(SHOW CREATE TABLE mysql.innodb_table_stats, Create Table, 1); @@ -18,7 +16,7 @@ DROP TABLE mysql.innodb_index_stats; DROP TABLE mysql.innodb_table_stats; -- enable_query_log -CREATE TABLE t(a INT)ENGINE=InnoDB; +CREATE TABLE t(a INT)ENGINE=InnoDB STATS_PERSISTENT=0; RENAME TABLE t TO u; DROP TABLE u; SELECT @@innodb_fast_shutdown; diff --git a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test index b8d19ddfcee..c57e52b65cc 100644 --- a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test +++ b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test @@ -2,6 +2,7 @@ # Test for persistent corrupt bit for corrupted index and table # -- source include/have_innodb.inc +-- source include/have_innodb_16k.inc # Issues with innodb_change_buffering_debug on Windows, so the test scenario # cannot be created on windows @@ -11,6 +12,7 @@ --source include/have_debug.inc -- disable_query_log +call mtr.add_suppression("Flagged corruption of idx.*in CHECK TABLE"); # This test setup is extracted from bug56680.test: # The flag innodb_change_buffering_debug is only available in debug builds. # It instructs InnoDB to try to evict pages from the buffer pool when diff --git a/mysql-test/suite/innodb/t/innodb_file_format.test b/mysql-test/suite/innodb/t/innodb_file_format.test index 941edb728dd..f45083101c0 100644 --- a/mysql-test/suite/innodb/t/innodb_file_format.test +++ b/mysql-test/suite/innodb/t/innodb_file_format.test @@ -1,5 +1,5 @@ ---source include/have_innodb.inc ---source include/restart_and_reinit.inc +-- source include/restart_and_reinit.inc +-- source include/have_innodb.inc let $innodb_file_format_orig=`select @@innodb_file_format`; diff --git a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test index 6873c2a404c..17f82f88fef 100644 --- a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test +++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test @@ -1,6 +1,9 @@ ---source include/have_innodb.inc # Testcase for worklog #5743: Lift the limit of index key prefixes +--source include/have_innodb.inc +--source include/have_innodb_16k.inc +SET default_storage_engine=InnoDB; + let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; @@ -9,10 +12,11 @@ set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; set global innodb_large_prefix=1; +-- echo ### Test 1 ### # Create a table of DYNAMIC format, with a primary index of 1000 bytes in # size -create table worklog5743(a TEXT not null, primary key (a(1000))) -ROW_FORMAT=DYNAMIC, engine = innodb; +create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC; +show warnings; # Do some insertion and update to excercise the external cache # code path @@ -23,13 +27,14 @@ update worklog5743 set a = (repeat("b", 16000)); # Create a secondary index create index idx on worklog5743(a(2000)); +show warnings; # Start a few sessions to do selections on table being updated in default # session, so it would rebuild the previous version from undo log. -# 1) Default session: Initiate an update on the externally stored column -# 2) Session con1: Select from table with repeated read -# 3) Session con2: Select from table with read uncommitted -# 4) Default session: rollback updates +# 1) Default session: Initiate an update on the externally stored column +# 2) Session con1: Select from table with repeated read +# 3) Session con2: Select from table with read uncommitted +# 4) Default session: rollback updates begin; update worklog5743 set a = (repeat("x", 17000)); @@ -55,11 +60,12 @@ rollback; drop table worklog5743; +-- echo ### Test 2 ### # Create a table with only a secondary index has large prefix column -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; - +create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; +show warnings; create index idx on worklog5743(a1, a2(2000)); +show warnings; insert into worklog5743 values(9, repeat("a", 10000)); @@ -85,9 +91,9 @@ rollback; drop table worklog5743; +-- echo ### Test 3 ### # Create a table with a secondary index has small (50 bytes) prefix column -create table worklog5743(a1 int, a2 TEXT not null) -ROW_FORMAT=DYNAMIC, engine = innodb; +create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; create index idx on worklog5743(a1, a2(50)); @@ -115,72 +121,225 @@ rollback; drop table worklog5743; -# Create a table of ROW_FORMAT=COMPRESSED format -create table worklog5743_2(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; - -create table worklog5743_4(a1 int, a2 TEXT not null) -ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; - -# The maximum overall index record (not prefix) length for this table -# is page_zip_empty_size() / 2, which is 960. "Too big row" error ( -# HA_ERR_TO_BIG_ROW) will be printed if this limit is exceeded. -# Considering other fields and their overhead, the maximum length -# for column a2 is 940 or 941 depending on the zlib version used and -# compressBound() value used in page_zip_empty_size() (please refer -# to Bug #47495 for more detail). --- error 139 -create index idx1 on worklog5743_2(a1, a2(942)); - -create index idx1 on worklog5743_2(a1, a2(940)); - -# similarly, the maximum index record length for the table is -# 1984. Considering other fields and their overhead, the -# maximum length for column a2 is 1964 or 1965 (please refer -# to Bug #47495 for more detail). --- error 139 -create index idx1 on worklog5743_4(a1, a2(1966)); - -create index idx1 on worklog5743_4(a1, a2(1964)); - +-- echo ### Test 4 ### +# Create compressed tables with each KEY_BLOCK_SIZE. +create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1; +create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2; +create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4; +create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8; +create table worklog5743_16(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=16; + +# The maximum overall index record (not prefix) length of a +# compressed table is dependent on innodb-page-size (IPS), +# key_block_size (KBS) and the number of fields (NF). +# "Too big row" error (HA_ERR_TO_BIG_ROW) will be returned if this +# limit is exceeded. +# See page_zip_empty_size() and Bug #47495 for more detail. + +# Test edge cases for indexes using key_block_size=1 +set global innodb_large_prefix=0; +-- error ER_TOO_BIG_ROWSIZE +create index idx1 on worklog5743_1(a2(4000)); +show warnings; +set global innodb_large_prefix=1; +-- error ER_TOO_BIG_ROWSIZE +create index idx2 on worklog5743_1(a2(4000)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx3 on worklog5743_1(a2(436)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx4 on worklog5743_1(a2(434)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx5 on worklog5743_1(a1, a2(430)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx6 on worklog5743_1(a1, a2(428)); +show warnings; + +# Test edge cases for indexes using key_block_size=2 +set global innodb_large_prefix=0; +create index idx1 on worklog5743_2(a2(4000)); +show warnings; +set global innodb_large_prefix=1; +-- error ER_TOO_BIG_ROWSIZE +create index idx2 on worklog5743_2(a2(4000)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx3 on worklog5743_2(a2(948)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx4 on worklog5743_2(a2(946)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx5 on worklog5743_2(a1, a2(942)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx6 on worklog5743_2(a1, a2(940)); +show warnings; + +# Test edge cases for indexes using key_block_size=4 +set global innodb_large_prefix=0; +create index idx1 on worklog5743_4(a2(4000)); +show warnings; +set global innodb_large_prefix=1; +-- error ER_TOO_BIG_ROWSIZE +create index idx2 on worklog5743_4(a2(4000)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx3 on worklog5743_4(a2(1972)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx4 on worklog5743_4(a2(1970)); +show warnings; +-- error ER_TOO_BIG_ROWSIZE +create index idx5 on worklog5743_4(a1, a2(1966)); +show warnings; +# Bug#13391353 Limit is one byte less on on 32bit-Linux only +create index idx6 on worklog5743_4(a1, a2(1964)); +show warnings; + +# Test edge cases for indexes using key_block_size=8 +set global innodb_large_prefix=0; +create index idx1 on worklog5743_8(a2(1000)); +show warnings; +set global innodb_large_prefix=1; +create index idx2 on worklog5743_8(a2(3073)); +show warnings; +create index idx3 on worklog5743_8(a2(3072)); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx4 on worklog5743_8(a1, a2(3069)); +show warnings; +create index idx5 on worklog5743_8(a1, a2(3068)); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx6 on worklog5743_8(a1, a2(2000), a3(1069)); +show warnings; +create index idx7 on worklog5743_8(a1, a2(2000), a3(1068)); +show warnings; + +# Test edge cases for indexes using key_block_size=16 +set global innodb_large_prefix=0; +create index idx1 on worklog5743_16(a2(1000)); +show warnings; +set global innodb_large_prefix=1; +create index idx2 on worklog5743_16(a2(3073)); +show warnings; +create index idx3 on worklog5743_16(a2(3072)); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx4 on worklog5743_16(a1, a2(3069)); +show warnings; +create index idx5 on worklog5743_16(a1, a2(3068)); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx6 on worklog5743_16(a1, a2(2000), a3(1069)); +show warnings; +create index idx7 on worklog5743_16(a1, a2(2000), a3(1068)); +show warnings; + +# Insert a large record into each of these tables. +insert into worklog5743_1 values(9, repeat("a", 10000)); insert into worklog5743_2 values(9, repeat("a", 10000)); insert into worklog5743_4 values(9, repeat("a", 10000)); +insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000)); +insert into worklog5743_16 values(9, repeat("a", 10000), repeat("a", 10000)); + +# Now if we change the global innodb_large_prefix back to 767, +# updates to these indexes should still be allowed. +set global innodb_large_prefix=0; +insert into worklog5743_1 values(2, repeat("b", 10000)); +insert into worklog5743_2 values(2, repeat("b", 10000)); +insert into worklog5743_4 values(2, repeat("b", 10000)); +insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000)); +insert into worklog5743_16 values(2, repeat("b", 10000), repeat("b", 10000)); +set global innodb_large_prefix=1; + +select a1, left(a2, 20) from worklog5743_1; +select a1, left(a2, 20) from worklog5743_2; +select a1, left(a2, 20) from worklog5743_4; +select a1, left(a2, 20) from worklog5743_8; +select a1, left(a2, 20) from worklog5743_16; begin; +update worklog5743_1 set a1 = 1000; update worklog5743_2 set a1 = 1000; update worklog5743_4 set a1 = 1000; +update worklog5743_8 set a1 = 1000; +update worklog5743_16 set a1 = 1000; +select a1, left(a2, 20) from worklog5743_1; +select a1, left(a2, 20) from worklog5743_2; +select a1, left(a2, 20) from worklog5743_4; +select a1, left(a2, 20) from worklog5743_8; +select a1, left(a2, 20) from worklog5743_16; + # Do a select from another connection that would use the secondary index --connection con1 select @@session.tx_isolation; -explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +explain select a1, left(a2, 20) from worklog5743_16 where a1 = 9; +select a1, left(a2, 20) from worklog5743_1 where a1 = 9; +select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +select a1, left(a2, 20) from worklog5743_16 where a1 = 9; # Do read uncommitted in another session, it would show there is no # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select @@session.tx_isolation; -select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; -select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; +select a1, left(a2, 20) from worklog5743_1 where a1 = 9; +select a1, left(a2, 20) from worklog5743_2 where a1 = 9; +select a1, left(a2, 20) from worklog5743_4 where a1 = 9; +select a1, left(a2, 20) from worklog5743_8 where a1 = 9; +select a1, left(a2, 20) from worklog5743_16 where a1 = 9; --connection default rollback; +drop table worklog5743_1; drop table worklog5743_2; drop table worklog5743_4; +drop table worklog5743_8; +drop table worklog5743_16; + +-- echo ### Test 5 ### +# Create a table with large varchar columns and create indexes +# directly on these large columns to show that prefix limit is +# automatically applied and to show that limit. +create table worklog5743(a1 int, + a2 varchar(20000), + a3 varchar(3073), + a4 varchar(3072), + a5 varchar(3069), + a6 varchar(3068)) + ROW_FORMAT=DYNAMIC; +create index idx1 on worklog5743(a2); +create index idx2 on worklog5743(a3); +create index idx3 on worklog5743(a4); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx4 on worklog5743(a1, a2); +show warnings; +-- error ER_TOO_LONG_KEY +create index idx5 on worklog5743(a1, a5); +show warnings; +create index idx6 on worklog5743(a1, a6); +show warnings; +show create table worklog5743; -# Create a table with varchar column, and create index directly on this -# large column (without prefix) -create table worklog5743(a1 int, a2 varchar(3000)) -ROW_FORMAT=DYNAMIC, engine = innodb; - -# Create an index with large column without prefix -create index idx on worklog5743(a1, a2); - -insert into worklog5743 values(9, repeat("a", 3000)); +insert into worklog5743 values(9, + repeat("a", 20000), repeat("a", 3073), + repeat("a", 3072), repeat("a", 3069), + repeat("a", 3068)); begin; @@ -203,19 +362,19 @@ rollback; drop table worklog5743; +-- echo ### Test 6 ### # Create a table with old format, and the limit is 768 bytes. -- error ER_INDEX_COLUMN_TOO_LONG -create table worklog5743(a TEXT not null, primary key (a(1000))) -engine = innodb; +create table worklog5743(a TEXT not null, primary key (a(1000))); -create table worklog5743(a TEXT) engine = innodb; +create table worklog5743(a TEXT); # Excercise the column length check in ha_innobase::add_index() -- error ER_INDEX_COLUMN_TOO_LONG -create index idx on worklog5743(a(1000)); +create index idx on worklog5743(a(768)); # This should be successful -create index idx on worklog5743(a(725)); +create index idx on worklog5743(a(767)); # Perform some DMLs insert into worklog5743 values(repeat("a", 20000)); @@ -240,29 +399,40 @@ rollback; drop table worklog5743; -# Some border line test on the column length. +-- echo ### Test 7 ### +# Some border line tests on the column length. # We have a limit of 3072 bytes for Barracuda table -create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; - -# Length exceeds maximum supported key length, will auto-truncated to 3072 -create index idx on worklog5743(a(3073)); +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC; +# Length exceeds maximum supported key length +# It will be auto-truncated to 3072 +create index idx1 on worklog5743(a(3073)); create index idx2 on worklog5743(a(3072)); - show create table worklog5743; - drop table worklog5743; -# We have a limit of 767 bytes for Antelope table -create table worklog5743(a TEXT not null) engine = innodb; - +# We have a limit of 767 bytes for Antelope tables +create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT; -- error ER_INDEX_COLUMN_TOO_LONG create index idx on worklog5743(a(768)); - create index idx2 on worklog5743(a(767)); +drop table worklog5743; +create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT; +-- error ER_INDEX_COLUMN_TOO_LONG +create index idx on worklog5743(a(768)); +create index idx2 on worklog5743(a(767)); drop table worklog5743; + eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc +--connection con2 +--disconnect con2 +--source include/wait_until_disconnected.inc +--connection default + diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_buffer.test b/mysql-test/suite/innodb/t/innodb_information_schema_buffer.test index 751a2bd6b5e..6858b898649 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema_buffer.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema_buffer.test @@ -25,7 +25,7 @@ INSERT INTO infoschema_buffer_test VALUES(9); # right away SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; # The NUMBER_RECORDS and DATA_SIZE should check with each insertion @@ -33,14 +33,14 @@ INSERT INTO infoschema_buffer_test VALUES(19); SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; CREATE INDEX idx ON infoschema_buffer_test(col1); SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test" +WHERE TABLE_NAME like "%infoschema_buffer_test%" and PAGE_STATE="file_page" and INDEX_NAME = "idx" and PAGE_TYPE="index"; @@ -49,7 +49,7 @@ DROP TABLE infoschema_buffer_test; SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_buffer_test"; +WHERE TABLE_NAME like "%infoschema_buffer_test%"; # Do one more test #--replace_regex /'*[0-9]*'/'NUM'/ @@ -64,13 +64,9 @@ ENGINE=INNODB; SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE -WHERE TABLE_NAME like "%infoschema_child" and PAGE_STATE="file_page" +WHERE TABLE_NAME like "%infoschema_child%" and PAGE_STATE="file_page" and PAGE_TYPE="index"; DROP TABLE infoschema_child; DROP TABLE infoschema_parent; -show create table information_schema.innodb_buffer_page; -show create table information_schema.innodb_buffer_page_lru; -show create table information_schema.innodb_buffer_pool_stats; - diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test index 776f5dae517..77f55002da5 100644 --- a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test +++ b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test @@ -1,4 +1,3 @@ ---source include/have_innodb.inc ######## suite/innodb/t/innodb_prefix_index_liftedlimit.test ########## # # # Testcase for worklog WL#5743: Lift the limit of index key prefixes # @@ -14,15 +13,15 @@ # # ###################################################################### -if (`select plugin_auth_version <= "1.1.8-29.3" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB 1.1.8-29.3 or earlier -} +--source include/have_innodb.inc +--source include/have_innodb_16k.inc # Save innodb variables -let $innodb_file_format_orig=`select @@innodb_file_format`; -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; +--disable_query_log +let $innodb_file_format_orig = `select @@innodb_file_format`; +let $innodb_file_per_table_orig = `select @@innodb_file_per_table`; +let $innodb_large_prefix_orig = `select @@innodb_large_prefix`; +--enable_query_log # Set Innodb file format as feature works for Barracuda file format set global innodb_file_format="Barracuda"; @@ -53,6 +52,11 @@ WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT; +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE worklog5743 ROW_FORMAT=COMPACT; +ALTER TABLE worklog5743 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; DROP TABLE worklog5743; @@ -486,10 +490,10 @@ DROP TABLE worklog5743_key8; #------------------------------------------------------------------------------ # Create mutiple prefix index. We can not create prefix index length > 16K # as index is written in undo log page which of 16K size. -# So we can create max 5 prefix index of length 3072 on table +# So we can create max 2 prefix index of length 3072 on table CREATE TABLE worklog5743 ( col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) , -col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000), +col_3_text TEXT (4000), col_4_blob BLOB (4000), col_5_text TEXT (4000), col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) ) ROW_FORMAT=DYNAMIC, engine = innodb; INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), @@ -497,13 +501,10 @@ REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) ); -# Update hangs if we create following 5 indexes. Uncomment them once its fix +# Update reports ER_UNDO_RECORD_TOO_BIG if we create more than 2 indexes. # Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -#CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); -#CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); -#CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); -#CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); -#CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); +CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); +CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000), REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), @@ -515,14 +516,29 @@ UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); + INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) ); DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000); SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743; -DROP TABLE worklog5743; +# Add 3 more indexes. +# Update used to hang but now ER_UNDO_RECORD_TOO_BIG is reported; +# Bug#12547647 - UPDATE LOGGING COULD EXCEED UNDO LOG PAGE SIZE +INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), +REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), +REPEAT("a", 4000) , REPEAT("a", 255) +); +CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); +CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072)); +CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072)); +--error ER_UNDO_RECORD_TOO_BIG +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +SHOW WARNINGS; +DROP TABLE worklog5743; #------------------------------------------------------------------------------ # Create mutiple prefix index. We can not create prefix index length > 16K as @@ -536,7 +552,8 @@ col_6_varchar VARCHAR (4000), col_7_binary BINARY (255) ) ROW_FORMAT=DYNAMIC, engine = innodb; -# Update hangs if we create following 5 indexes. Uncomment them once its fix +# Update used to hang if we create following 5 indexes. Fixed in; +# Bug#12547647 - UPDATE LOGGING COULD EXCEED UNDO LOG PAGE SIZE CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072)); CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072)); CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072)); @@ -566,11 +583,13 @@ REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000), REPEAT("a", 4000) , REPEAT("a", 255) ); ROLLBACK; -# Uncomment Update fater Bug#12547647 is fixed - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE # Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -#UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) -#WHERE col_1_varbinary = REPEAT("a", 4000) -#AND col_2_varchar = REPEAT("o", 4000); +# Instead of this error, it would hang before this fix. +--error ER_UNDO_RECORD_TOO_BIG +UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000) +WHERE col_1_varbinary = REPEAT("a", 4000) +AND col_2_varchar = REPEAT("o", 4000); +SHOW WARNINGS; SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000), @@ -603,14 +622,28 @@ SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743; DROP TABLE worklog5743; # Prefix index with utf8 charset + varchar. -# For varchar we also log the column itself as oppose of TEXT so it error -# with limit 1024 due to overhead. +# utf8 charcter takes 3 bytes in mysql so prefix index limit is 3072/3 = 1024 +# This is a case where dict_index_too_big_for_undo() is too conservative. +# If it did not return error 1118, to commented code would work. +# See bug#12953735. --replace_regex /> [0-9]*/> max_row_size/ --- error 1118 +-- error ER_TOO_BIG_ROWSIZE CREATE TABLE worklog5743 (col_1_varchar VARCHAR (4000) CHARACTER SET 'utf8', col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8' , PRIMARY KEY (col_1_varchar(1024)) ) ROW_FORMAT=DYNAMIC, engine = innodb; +#INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); +#CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1024)); +#INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); +#SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) FROM worklog5743; +#UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000) +#WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000); +#SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743 +#WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000); +#INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); +#DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); +#SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; +#DROP TABLE worklog5743; #------------------------------------------------------------------------------ # prefinx index on utf8 charset with transaction @@ -738,7 +771,7 @@ COMMIT; # Wait for commit let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist WHERE info='COMMIT'; ---source include/wait_condition.inc +--source include/wait_condition.inc --echo "In connection 1" --connection con1 @@ -847,8 +880,12 @@ worklog5743; SELECT COUNT(*) FROM worklog5743; COMMIT; +--echo "Disconnect the connections 1 and 2" --disconnect con1 +--source include/wait_until_disconnected.inc +--connection con2 --disconnect con2 +--source include/wait_until_disconnected.inc --connection default DROP TABLE worklog5743; @@ -993,7 +1030,7 @@ AND col_2_varchar = REPEAT("o", 4000); ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); ---error 1062 +--error ER_DUP_ENTRY INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; @@ -1027,7 +1064,7 @@ ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); ---error 1062 +--error ER_DUP_ENTRY INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000); SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743; @@ -1247,8 +1284,8 @@ AND col_2_text = REPEAT("o", 4000); ALTER TABLE worklog5743 DROP PRIMARY KEY; # Again add index length > 948. Expect error 'to big row ' due to exceed -# in key length. --- error 139 +# in key length. +-- error ER_TOO_BIG_ROWSIZE ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (950)); INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000)); SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743; @@ -1310,7 +1347,7 @@ VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), REPEAT("o", 4000)); INSERT INTO worklog5743 VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000)); ---error 1062 +--error ER_DUP_ENTRY ALTER TABLE worklog5743 ADD PRIMARY KEY `pk_idx` (col_1_varchar(3000)); DROP TABLE worklog5743; @@ -1332,11 +1369,24 @@ CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , PRIMARY KEY (col_1_varchar(767)) ) engine = innodb; -# Prefix index > 767 is truncated with REDUNDANT and COMPACT +INSERT INTO worklog5743 VALUES(REPEAT('a',4000),REPEAT('b',4000)); +# Prefix index > 767 is truncated with REDUNDANT and COMPACT +--enable_info CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1000)); +ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT; +--disable_info +SHOW CREATE TABLE worklog5743; DROP TABLE worklog5743; #------------------------------------------------------------------------------ -eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; +--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 global innodb_large_prefix = $innodb_large_prefix_orig; +--echo "Disconnect the connection 1" +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc +--enable_query_log +--connection default + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2edc24db14d..f865be44ea3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -16631,13 +16631,8 @@ int report_error(TABLE *table, int error) */ if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT && !table->in_use->killed) - { - push_warning_printf(table->in_use, Sql_condition::WARN_LEVEL_WARN, error, - "Got error %d when reading table %`s.%`s", - error, table->s->db.str, table->s->table_name.str); sql_print_error("Got error %d when reading table '%s'", error, table->s->path.str); - } table->file->print_error(error,MYF(0)); return 1; } |