diff options
Diffstat (limited to 'mysql-test/suite/innodb_zip/r/page_size.result')
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/page_size.result | 593 |
1 files changed, 593 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result new file mode 100644 index 00000000000..6355b5e76b6 --- /dev/null +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -0,0 +1,593 @@ +SET default_storage_engine=InnoDB; +# Test 1) Show the page size from Information Schema +SELECT variable_value FROM information_schema.global_status +WHERE LOWER(variable_name) = 'innodb_page_size'; +variable_value +16384 +# Test 3) Query some information_shema tables that are dependent upon +# the page size. +SELECT t.name table_name, t.n_cols, t.flag table_flags, +i.name index_name, i.page_no root_page, i.type, +i.n_fields, i.merge_threshold +FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +WHERE t.table_id = i.table_id +AND t.name LIKE 'mysql%' + ORDER BY t.name, i.index_id; +table_name n_cols table_flags index_name root_page type n_fields merge_threshold +mysql/innodb_index_stats 11 33 PRIMARY 3 3 4 50 +mysql/innodb_table_stats 9 33 PRIMARY 3 3 2 50 +mysql/transaction_registry 8 33 PRIMARY 3 3 1 50 +mysql/transaction_registry 8 33 commit_id 4 2 1 50 +mysql/transaction_registry 8 33 begin_timestamp 5 0 1 50 +mysql/transaction_registry 8 33 commit_timestamp 6 0 2 50 +CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb; +CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb; +CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb; +CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb; +SELECT t.name table_name, t.n_cols, t.flag table_flags, +i.name index_name, i.page_no root_page, i.type, +i.n_fields, i.merge_threshold +FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +WHERE t.table_id = i.table_id +AND t.name LIKE 'test%' + ORDER BY t.name, i.name; +table_name n_cols table_flags index_name root_page type n_fields merge_threshold +test/t1 5 0 PRIMARY 3 3 1 50 +test/t2 5 1 PRIMARY 3 3 1 50 +test/t3 5 41 PRIMARY 3 3 1 50 +test/t4 5 33 PRIMARY 3 3 1 50 +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Space_Type Page_Size Zip_Size Path +test/t1 Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t1.ibd +test/t2 Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t2.ibd +test/t3 Single DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd +test/t4 Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd +DROP TABLE t1, t2, t3, t4; +# Test 4) The maximum row size is dependent upon the page size. +# Each row format has its own amount of overhead that +# varies depending on number of fields and other overhead. +SET SESSION innodb_strict_mode = ON; +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(211) +) ROW_FORMAT=redundant; +DROP TABLE t1; +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(212) +) ROW_FORMAT=redundant; +ERROR 42000: Row size too large (> 8123). 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. +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246) +) ROW_FORMAT=compact; +DROP TABLE t1; +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247) +) ROW_FORMAT=compact; +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. +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157) +) ROW_FORMAT=compressed; +DROP TABLE t1; +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160) +) ROW_FORMAT=compressed; +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 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246) +) ROW_FORMAT=dynamic; +DROP TABLE t1; +CREATE TABLE t1 ( +c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), +c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), +c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200), +c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200), +c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200), +c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200), +c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247) +) ROW_FORMAT=dynamic; +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 (a varchar(255) character set utf8, +b varchar(255) character set utf8, +c varchar(255) character set utf8, +d varchar(255) character set utf8, +e varchar(4) character set utf8, +PRIMARY KEY (a,b,c,d,e)) +ENGINE=innodb; +DROP TABLE t1; +CREATE TABLE t1 (a varchar(255) character set utf8, +b varchar(255) character set utf8, +c varchar(255) character set utf8, +d varchar(255) character set utf8, +e varchar(5) character set utf8, +PRIMARY KEY (a,b,c,d,e)) +ENGINE=innodb; +ERROR 42000: Specified key was too long; max key length is 3072 bytes +CREATE TABLE t1 (a varchar(255) character set utf8, +b varchar(255) character set utf8, +c varchar(255) character set utf8, +d varchar(255) character set utf8, +e varchar(255) character set utf8, +f varchar(4) character set utf8, +PRIMARY KEY (a), KEY (b,c,d,e,f)) +ENGINE=innodb; +DROP TABLE t1; +CREATE TABLE t1 (a varchar(255) character set utf8, +b varchar(255) character set utf8, +c varchar(255) character set utf8, +d varchar(255) character set utf8, +e varchar(255) character set utf8, +f varchar(5) character set utf8, +PRIMARY KEY (a), KEY (b,c,d,e,f)) +ENGINE=innodb; +ERROR 42000: Specified key was too long; max key length is 3072 bytes +# Test 5) KEY_BLOCK_SIZE validation +CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=16 +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=8 +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=4 +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=2 +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=1 +ALTER TABLE t1 KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED +DROP TABLE t1; +SET SESSION innodb_strict_mode = OFF; +CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=16 +ALTER TABLE t1 KEY_BLOCK_SIZE=8; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=8 +ALTER TABLE t1 KEY_BLOCK_SIZE=4; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=4 +ALTER TABLE t1 KEY_BLOCK_SIZE=2; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=2 +ALTER TABLE t1 KEY_BLOCK_SIZE=1; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED key_block_size=1 +ALTER TABLE t1 KEY_BLOCK_SIZE=0; +SHOW WARNINGS; +Level Code Message +SELECT table_name, row_format, create_options +FROM information_schema.tables WHERE table_name = 't1'; +table_name row_format create_options +t1 Compressed row_format=COMPRESSED +DROP TABLE t1; +# Test 6) KEY_BLOCK_SIZE with innodb_file_per_table=OFF +SET SESSION innodb_strict_mode = ON; +SET GLOBAL innodb_file_per_table = OFF; +SHOW VARIABLES LIKE 'innodb_file_per_table'; +Variable_name Value +innodb_file_per_table OFF +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 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table `test`.`t4` (errno: 140 "Wrong create options") +Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +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 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table `test`.`t5` (errno: 140 "Wrong create options") +Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +SET GLOBAL innodb_file_per_table = ON; +# Test 7) This series of tests were moved from innodb-index to here +# because the second alter table t1 assumes a 16k page size. +# Moving the test allows the rest of innodb-index to be run on all +# page sizes. The previously disabled portions of this test were +# moved as well. +CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8; +CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb; +INSERT INTO t3 VALUES (22),(44),(33),(55),(66); +INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'), +('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik'); +CREATE TABLE t1(a int, b blob, c text, d text NOT NULL) +ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0; +INSERT INTO t1 +SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d; +DROP TABLE t2, t3; +SELECT COUNT(*) FROM t1 WHERE a=44; +COUNT(*) +5 +SELECT a, +LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1 +ORDER BY 1, 2, 3, 4, 5, 6; +a LENGTH(b) b=LEFT(REPEAT(d,100*a),65535) LENGTH(c) c=REPEAT(d,20*a) d +22 22000 1 4400 1 adfd72nh9k +22 22000 1 4400 1 jejdkrun87 +22 26400 1 5280 1 adfdpplkeock +22 28600 1 5720 1 adfdijnmnb78k +22 35200 1 7040 1 adfdijn0loKNHJik +33 33000 1 6600 1 adfd72nh9k +33 33000 1 6600 1 jejdkrun87 +33 39600 1 7920 1 adfdpplkeock +33 42900 1 8580 1 adfdijnmnb78k +33 52800 1 10560 1 adfdijn0loKNHJik +44 44000 1 8800 1 adfd72nh9k +44 44000 1 8800 1 jejdkrun87 +44 52800 1 10560 1 adfdpplkeock +44 57200 1 11440 1 adfdijnmnb78k +44 65535 1 14080 1 adfdijn0loKNHJik +55 55000 1 11000 1 adfd72nh9k +55 55000 1 11000 1 jejdkrun87 +55 65535 1 13200 1 adfdpplkeock +55 65535 1 14300 1 adfdijnmnb78k +55 65535 1 17600 1 adfdijn0loKNHJik +66 65535 1 13200 1 adfd72nh9k +66 65535 1 13200 1 jejdkrun87 +66 65535 1 15840 1 adfdpplkeock +66 65535 1 17160 1 adfdijnmnb78k +66 65535 1 21120 1 adfdijn0loKNHJik +ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20)); +ERROR 23000: Duplicate entry '22' for key 'PRIMARY' +DELETE FROM t1 WHERE d='null'; +ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20)); +ERROR 23000: Duplicate entry '22' for key 'PRIMARY' +DELETE FROM t1 WHERE a%2; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767)); +SELECT COUNT(*) FROM t1 WHERE a=44; +COUNT(*) +5 +SELECT a, +LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1; +a LENGTH(b) b=LEFT(REPEAT(d,100*a), 65535) LENGTH(c) c=REPEAT(d,20*a) d +22 22000 1 4400 1 adfd72nh9k +22 35200 1 7040 1 adfdijn0loKNHJik +22 28600 1 5720 1 adfdijnmnb78k +22 26400 1 5280 1 adfdpplkeock +22 22000 1 4400 1 jejdkrun87 +44 44000 1 8800 1 adfd72nh9k +44 65535 1 14080 1 adfdijn0loKNHJik +44 57200 1 11440 1 adfdijnmnb78k +44 52800 1 10560 1 adfdpplkeock +44 44000 1 8800 1 jejdkrun87 +66 65535 1 13200 1 adfd72nh9k +66 65535 1 21120 1 adfdijn0loKNHJik +66 65535 1 17160 1 adfdijnmnb78k +66 65535 1 15840 1 adfdpplkeock +66 65535 1 13200 1 jejdkrun87 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` blob NOT NULL, + `c` text NOT NULL, + `d` text NOT NULL, + PRIMARY KEY (`a`,`b`(255),`c`(255)), + KEY `b` (`b`(767)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 769 NULL # Using where +DROP TABLE t1; +# Test 8) Test creating a table that could lead to undo log overflow. +CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob, +h blob,i blob,j blob,k blob,l blob,m blob,n blob, +o blob,p blob,q blob,r blob,s blob,t blob,u blob) +ENGINE=InnoDB ROW_FORMAT=dynamic; +SET @a = repeat('a', 767); +SET @b = repeat('b', 767); +SET @c = repeat('c', 767); +SET @d = repeat('d', 767); +SET @e = repeat('e', 767); +INSERT INTO t1 VALUES (@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a); +UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b, +k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b; +CREATE INDEX t1a ON t1 (a(767)); +CREATE INDEX t1b ON t1 (b(767)); +CREATE INDEX t1c ON t1 (c(767)); +CREATE INDEX t1d ON t1 (d(767)); +CREATE INDEX t1e ON t1 (e(767)); +UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c, +k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c; +CREATE INDEX t1f ON t1 (f(767)); +BEGIN; +UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d, +k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d; +ROLLBACK; +BEGIN; +UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d; +UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d, +n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d; +COMMIT; +CREATE INDEX t1g ON t1 (g(767)); +UPDATE t1 SET g=@e; +CREATE INDEX t1h ON t1 (h(767)); +UPDATE t1 SET h=@e; +CREATE INDEX t1i ON t1 (i(767)); +UPDATE t1 SET i=@e; +CREATE INDEX t1j ON t1 (j(767)); +UPDATE t1 SET j=@e; +CREATE INDEX t1k ON t1 (k(767)); +UPDATE t1 SET k=@e; +CREATE INDEX t1l ON t1 (l(767)); +UPDATE t1 SET l=@e; +CREATE INDEX t1m ON t1 (m(767)); +UPDATE t1 SET m=@e; +CREATE INDEX t1n ON t1 (n(767)); +UPDATE t1 SET n=@e; +CREATE INDEX t1o ON t1 (o(767)); +UPDATE t1 SET o=@e; +CREATE INDEX t1p ON t1 (p(767)); +UPDATE t1 SET p=@e; +CREATE INDEX t1q ON t1 (q(767)); +UPDATE t1 SET q=@e; +CREATE INDEX t1r ON t1 (r(767)); +UPDATE t1 SET r=@e; +CREATE INDEX t1s ON t1 (s(767)); +UPDATE t1 SET s=@e; +CREATE INDEX t1t ON t1 (t(767)); +BEGIN; +UPDATE t1 SET t=@e; +ROLLBACK; +CREATE INDEX t1u ON t1 (u(767)); +CREATE INDEX t1ut ON t1 (u(767), t(767)); +CREATE INDEX t1st ON t1 (s(767), t(767)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob DEFAULT NULL, + `b` blob DEFAULT NULL, + `c` blob DEFAULT NULL, + `d` blob DEFAULT NULL, + `e` blob DEFAULT NULL, + `f` blob DEFAULT NULL, + `g` blob DEFAULT NULL, + `h` blob DEFAULT NULL, + `i` blob DEFAULT NULL, + `j` blob DEFAULT NULL, + `k` blob DEFAULT NULL, + `l` blob DEFAULT NULL, + `m` blob DEFAULT NULL, + `n` blob DEFAULT NULL, + `o` blob DEFAULT NULL, + `p` blob DEFAULT NULL, + `q` blob DEFAULT NULL, + `r` blob DEFAULT NULL, + `s` blob DEFAULT NULL, + `t` blob DEFAULT NULL, + `u` blob DEFAULT NULL, + KEY `t1a` (`a`(767)), + KEY `t1b` (`b`(767)), + KEY `t1c` (`c`(767)), + KEY `t1d` (`d`(767)), + KEY `t1e` (`e`(767)), + KEY `t1f` (`f`(767)), + KEY `t1g` (`g`(767)), + KEY `t1h` (`h`(767)), + KEY `t1i` (`i`(767)), + KEY `t1j` (`j`(767)), + KEY `t1k` (`k`(767)), + KEY `t1l` (`l`(767)), + KEY `t1m` (`m`(767)), + KEY `t1n` (`n`(767)), + KEY `t1o` (`o`(767)), + KEY `t1p` (`p`(767)), + KEY `t1q` (`q`(767)), + KEY `t1r` (`r`(767)), + KEY `t1s` (`s`(767)), + KEY `t1t` (`t`(767)), + KEY `t1u` (`u`(767)), + KEY `t1ut` (`u`(767),`t`(767)), + KEY `t1st` (`s`(767),`t`(767)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +DROP TABLE t1; +# Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE +CREATE TABLE bug12547647( +a int NOT NULL, b blob NOT NULL, c text, +PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767)) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751)); +COMMIT; +BEGIN; +UPDATE bug12547647 SET c = REPEAT('b',16928); +SHOW WARNINGS; +Level Code Message +ROLLBACK; +DROP TABLE bug12547647; +SET SESSION innodb_strict_mode = OFF; +CREATE TABLE t1( +pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48), +pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48), +pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48), +pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48), +sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48), +sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48), +sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48), +sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48), +PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08, +pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16), +KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08, +sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16)) +ROW_FORMAT=Redundant ENGINE=InnoDB; +SET @r = repeat('a', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('b', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('c', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('d', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('e', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +DELETE from t1; +DROP TABLE t1; +CREATE TABLE t1( +pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48), +pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48), +pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48), +pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48), +sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48), +sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48), +sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48), +sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48), +PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08, +pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16), +KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08, +sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16)) +ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 ENGINE=InnoDB; +SET @r = repeat('a', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('b', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('c', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('d', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +SET @r = repeat('e', 48); +INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, +@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +DELETE from t1; +DROP TABLE t1; +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; +Warnings: +Warning 139 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. +DROP TABLE t1; +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; +Warnings: +Warning 139 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. +DROP TABLE t1; +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; +Warnings: +Warning 139 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. +DROP TABLE t1; +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; |