diff options
Diffstat (limited to 'mysql-test/suite/innodb_zip/r/page_size,4k.rdiff')
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/page_size,4k.rdiff | 389 |
1 files changed, 64 insertions, 325 deletions
diff --git a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff index c12527fd8a3..ad3bf70aedd 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff @@ -1,10 +1,10 @@ ---- page_size.result -+++ page_size,4k.result +--- /home/kevg/work/m/bb-10.2-MDEV-20194-CHECK-row-size/mysql-test/suite/innodb_zip/r/page_size.result 2019-09-15 19:33:42.676109532 +0300 ++++ /home/kevg/work/m/bb-10.2-MDEV-20194-CHECK-row-size/mysql-test/suite/innodb_zip/r/page_size.reject 2019-09-15 19:40:40.306732147 +0300 @@ -3,7 +3,7 @@ SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; variable_value --16384 +-8192 +4096 # Test 3) Query some information_shema tables that are dependent upon # the page size. @@ -13,79 +13,25 @@ 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/t3 5 39 PRIMARY 3 3 1 50 +test/t3 5 37 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 Formats_Permitted Path test/t1 Single DEFAULT DEFAULT Compact or Redundant MYSQLD_DATADIR/test/t1.ibd test/t2 Single DEFAULT DEFAULT Compact or Redundant MYSQLD_DATADIR/test/t2.ibd --test/t3 Single DEFAULT 8192 Compressed MYSQLD_DATADIR/test/t3.ibd +-test/t3 Single DEFAULT 4096 Compressed MYSQLD_DATADIR/test/t3.ibd +test/t3 Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t3.ibd test/t4 Single DEFAULT DEFAULT Dynamic MYSQLD_DATADIR/test/t4.ibd DROP TABLE t1, t2, t3, t4; # Test 4) The maximum row size is dependent upon the page size. -@@ -47,141 +47,90 @@ +@@ -47,63 +47,60 @@ 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(127) - ) 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(128) - ) 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. -+ERROR 42000: Row size too large (> max_row_size). 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(155) - ) 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(156) - ) 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. -+ERROR 42000: Row size too large (> max_row_size). 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) +-c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(103) +c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(76) ) ROW_FORMAT=compressed; DROP TABLE t1; @@ -93,112 +39,72 @@ 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(79) +-c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(110) ++c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(85) ) 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. -+ERROR 42000: Row size too large (> max_row_size). 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(155) - ) 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) -+c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(156) - ) 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)) -+ERROR 42000: Row size too large (> max_row_size). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +-ERROR 42000: Row size is greater than 3930. 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(128) character set utf8, +-b varchar(128) character set utf8, +-c varchar(128) character set utf8, +-d varchar(128) character set utf8, ++ERROR 42000: Row size is greater than 1902. 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(98) character set utf8, +b varchar(98) character set utf8, +c varchar(98) character set utf8, +d varchar(97) character set utf8, -+PRIMARY KEY (a,b,c,d)) + PRIMARY KEY (a,b,c,d)) 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)) +-CREATE TABLE t1 (a varchar(128) character set utf8, +-b varchar(128) character set utf8, +-c varchar(128) character set utf8, +-d varchar(129) character set utf8, +CREATE TABLE t1 (a varchar(98) character set utf8, +b varchar(98) character set utf8, +c varchar(98) character set utf8, +d varchar(98) character set utf8, -+PRIMARY KEY (a,b,c,d)) + PRIMARY KEY (a,b,c,d)) 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)) +-ERROR 42000: Specified key was too long; max key length is 1536 bytes +-CREATE TABLE t1 (a varchar(128) character set utf8, +-b varchar(128) character set utf8, +-c varchar(128) character set utf8, +-d varchar(128) character set utf8, +-e varchar(128) character set utf8, +ERROR 42000: Specified key was too long; max key length is 1173 bytes +CREATE TABLE t1 (a varchar(98) character set utf8, +b varchar(98) character set utf8, +c varchar(98) character set utf8, +d varchar(98) character set utf8, +e varchar(97) character set utf8, -+PRIMARY KEY (a), KEY (b,c,d,e)) + PRIMARY KEY (a), KEY (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(255) character set utf8, --f varchar(5) character set utf8, --PRIMARY KEY (a), KEY (b,c,d,e,f)) +-CREATE TABLE t1 (a varchar(128) character set utf8, +-b varchar(128) character set utf8, +-c varchar(128) character set utf8, +-d varchar(128) character set utf8, +-e varchar(129) character set utf8, +CREATE TABLE t1 (a varchar(98) character set utf8, +b varchar(98) character set utf8, +c varchar(98) character set utf8, +d varchar(98) character set utf8, +e varchar(98) character set utf8, -+PRIMARY KEY (a), KEY (b,c,d,e)) + PRIMARY KEY (a), KEY (b,c,d,e)) ENGINE=innodb; --ERROR 42000: Specified key was too long; max key length is 3072 bytes +-ERROR 42000: Specified key was too long; max key length is 1536 bytes +ERROR 42000: Specified key was too long; max key length is 1173 bytes # Test 5) KEY_BLOCK_SIZE validation - CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; -+ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") +-CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; ++CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; + ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") 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; +-Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 8. +Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 4. -+Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -+Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -+CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; + Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") + Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB + CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; +ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -213,17 +119,7 @@ ALTER TABLE t1 KEY_BLOCK_SIZE=4; SHOW WARNINGS; Level Code Message -@@ -213,15 +162,21 @@ - DROP TABLE t1; - SET SESSION innodb_strict_mode = OFF; - CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; -+Warnings: -+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16. - SHOW WARNINGS; - Level Code Message -+Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=16. - SELECT table_name, row_format, create_options - FROM information_schema.tables WHERE table_name = 't1'; +@@ -145,8 +142,11 @@ table_name row_format create_options t1 Compressed row_format=COMPRESSED key_block_size=16 ALTER TABLE t1 KEY_BLOCK_SIZE=8; @@ -235,7 +131,7 @@ SELECT table_name, row_format, create_options FROM information_schema.tables WHERE table_name = 't1'; table_name row_format create_options -@@ -265,6 +220,7 @@ +@@ -190,6 +190,7 @@ ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -243,15 +139,16 @@ 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 -@@ -272,6 +228,7 @@ +@@ -197,7 +198,7 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message +-Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 8. +Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 4. 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 -@@ -283,6 +240,7 @@ +@@ -209,6 +210,7 @@ ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -259,156 +156,16 @@ Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. 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 -@@ -290,107 +248,13 @@ +@@ -216,7 +218,7 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message +-Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 8. +Warning 1478 InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 4. Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. 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_format = `Barracuda`; - Warnings: - Warning 131 Using innodb_file_format is deprecated and the parameter may be removed in future releases. See https://mariadb.com/kb/en/library/xtradbinnodb-file-format/ --# 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 12 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, -@@ -405,10 +269,6 @@ - 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)); -@@ -423,37 +283,15 @@ - 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; +@@ -256,8 +258,10 @@ UPDATE t1 SET t=@e; ROLLBACK; CREATE INDEX t1u ON t1 (u(767)); @@ -421,28 +178,8 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( -@@ -479,28 +317,12 @@ - `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)), +@@ -287,8 +291,8 @@ 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)), @@ -452,7 +189,16 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DROP TABLE t1; # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -@@ -581,27 +403,25 @@ +@@ -350,6 +354,8 @@ + 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; ++Warnings: ++Warning 139 Row size is greater than 1981. Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. + 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); +@@ -369,21 +375,19 @@ DROP TABLE t1; CREATE TABLE t1( c text NOT NULL, d text NOT NULL, @@ -460,8 +206,8 @@ +PRIMARY KEY (c(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. -+Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +-Warning 139 Row size is greater than 892. Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. ++Warning 139 Row size is greater than 894. 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, @@ -469,7 +215,7 @@ +PRIMARY KEY (c(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. +-Warning 139 Row size is greater than 1916. 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, @@ -478,10 +224,3 @@ 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. -+Warning 139 Row size too large (> 1982). 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; |