diff options
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/main/join_outer_innodb.result | 24 | ||||
-rw-r--r-- | mysql-test/main/selectivity.result | 57 | ||||
-rw-r--r-- | mysql-test/main/selectivity.test | 29 | ||||
-rw-r--r-- | mysql-test/main/selectivity_innodb.result | 57 | ||||
-rw-r--r-- | mysql-test/main/stat_tables.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/ibuf_not_empty.result | 7 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/ibuf_not_empty.combinations | 5 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/ibuf_not_empty.test | 45 | ||||
-rw-r--r-- | mysql-test/suite/innodb_fts/r/innodb_fts_misc.result | 21 | ||||
-rw-r--r-- | mysql-test/suite/innodb_fts/t/innodb_fts_misc.test | 23 | ||||
-rw-r--r-- | mysql-test/suite/perfschema/r/misc.result | 16 | ||||
-rw-r--r-- | mysql-test/suite/perfschema/t/misc.test | 48 |
12 files changed, 291 insertions, 45 deletions
diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index ccd73ec955f..a0358094baa 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -434,46 +434,46 @@ where t1.a10 = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index -1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 +1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where +1 SIMPLE t14 ALL PRIMARY NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index -1 SIMPLE t5 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index -1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where 1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index -1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 +1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where +1 SIMPLE t14 ALL PRIMARY NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index -1 SIMPLE t5 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index -1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where 1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index drop view v1; drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16; # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index f11fee4b985..c548748b242 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1753,5 +1753,62 @@ a 1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; +# +# MDEV-20424: New default value for optimizer_use_condition-selectivity +# leads to bad plan +# +create table t1(a int, b int, c int, d int, key(a,b)); +insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; +insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; +create table t2(a int, b int, c int, primary key(a)); +insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; +create table t3(a int, b int, c int, primary key(a)); +insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; +set optimizer_use_condition_selectivity=1; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 9 100.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity=2; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 9 9.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +drop table t1,t2,t3; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index a9588ea205d..3cf4f320994 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1206,6 +1206,35 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit DROP TABLE t1; +--echo # +--echo # MDEV-20424: New default value for optimizer_use_condition-selectivity +--echo # leads to bad plan +--echo # + +create table t1(a int, b int, c int, d int, key(a,b)); +insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; +insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; + +create table t2(a int, b int, c int, primary key(a)); +insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; + +create table t3(a int, b int, c int, primary key(a)); +insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; + +let $query= select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; + +set optimizer_use_condition_selectivity=1; +eval explain extended $query; +eval $query; + +set optimizer_use_condition_selectivity=2; +eval explain extended $query; +eval $query; +set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; + +drop table t1,t2,t3; + --echo # End of 10.1 tests # diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 80295473e9b..b293f6adb5f 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1763,6 +1763,63 @@ a 1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; +# +# MDEV-20424: New default value for optimizer_use_condition-selectivity +# leads to bad plan +# +create table t1(a int, b int, c int, d int, key(a,b)); +insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; +insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; +create table t2(a int, b int, c int, primary key(a)); +insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; +create table t3(a int, b int, c int, primary key(a)); +insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; +set optimizer_use_condition_selectivity=1; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 11 100.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity=2; +explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 10 NULL 11 11.00 Using index condition; Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 +select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; +b a a b +0 0 1 1 +1 1 2 2 +2 2 3 3 +3 3 4 4 +4 4 5 5 +5 5 6 6 +6 6 7 7 +7 7 8 8 +8 8 9 9 +9 9 10 10 +set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +drop table t1,t2,t3; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 35a21377689..3ff10cc2448 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -218,8 +218,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where -1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 -1 SIMPLE n1 ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 Using where +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / sum(volume) as mkt_share diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result index 5147521b19f..dc852dfb8e5 100644 --- a/mysql-test/suite/innodb/r/ibuf_not_empty.result +++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result @@ -21,6 +21,11 @@ INSERT INTO t1 SELECT 0,b,c FROM t1; # restart: --innodb-force-recovery=6 --innodb-change-buffer-dump check table t1; Table Op Msg_type Msg_text -test.t1 check status OK +test.t1 check Warning InnoDB: Index 'b' contains #### entries, should be 4096. +test.t1 check error Corrupt +# restart +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; +InnoDB 0 transactions not purged +SET GLOBAL innodb_fast_shutdown=0; # restart DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.combinations b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index 470d375c661..14d8728cdf8 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -42,9 +42,44 @@ INSERT INTO t1 SELECT 0,b,c FROM t1; INSERT INTO t1 SELECT 0,b,c FROM t1; INSERT INTO t1 SELECT 0,b,c FROM t1; INSERT INTO t1 SELECT 0,b,c FROM t1; +let MYSQLD_DATADIR=`select @@datadir`; +let PAGE_SIZE=`select @@innodb_page_size`; + +--source include/shutdown_mysqld.inc + +# Corrupt the change buffer bitmap, to claim that pages are clean +perl; +do "$ENV{MTR_SUITE_DIR}/include/crc32.pl"; +my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +my $ps= $ENV{PAGE_SIZE}; +my $page; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +# Clean the change buffer bitmap. +substr($page,38,$ps - 38 - 8) = chr(0) x ($ps - 38 - 8); +my $polynomial = 0x82f63b78; # CRC-32C +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ + mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} +sysseek(FILE, $ps, 0) || die "Unable to rewind $file\n"; +syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; +close(FILE) || die "Unable to close $file"; +EOF --let $restart_parameters= --innodb-force-recovery=6 --innodb-change-buffer-dump ---source include/restart_mysqld.inc +--source include/start_mysqld.inc --replace_regex /contains \d+ entries/contains #### entries/ check table t1; @@ -52,5 +87,13 @@ check table t1; --let $restart_parameters= --source include/restart_mysqld.inc +# Ensure that the slow shutdown will not time out due to running purge. +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; +--source include/wait_all_purged.inc +# The change buffer merge for the injected corruption must complete +# without exceeding the 60-second shutdown_server timeout. +SET GLOBAL innodb_fast_shutdown=0; +--source include/restart_mysqld.inc + # Cleanup DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result index 0bcea9165b1..91f875377e2 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result @@ -1,4 +1,3 @@ -drop table if exists t1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), @@ -409,7 +408,6 @@ AGAINST ('"following database"@10' IN BOOLEAN MODE); id 105 DROP TABLE t1; -drop table if exists t50; set names utf8; "----------Test1---------" create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb; @@ -648,9 +646,6 @@ s1 ŁŁŁŁ LLLL ŁŁŁŁ ŁŁŁŁ -DROP TABLE if EXISTS t2; -Warnings: -Note 1051 Unknown table 'test.t2' CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB; CREATE FULLTEXT INDEX i ON t2 ( s1); INSERT INTO t2 VALUES @@ -702,9 +697,14 @@ DROP TABLE t1; CREATE TABLE t1 (id INT,char_column VARCHAR(60)); CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB; ALTER TABLE t2 DROP a; -SET @@autocommit=0; CREATE FULLTEXT INDEX i ON t1 (char_column); INSERT INTO t1 values (1,'aaa'); +CREATE TABLE mdev20987_1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB; +CREATE TABLE mdev20987_2(f1 INT NOT NULL, f2 CHAR(100), +FULLTEXT(f2), +FOREIGN KEY(f1) REFERENCES mdev20987_1(f1))ENGINE=InnoDB; +INSERT INTO mdev20987_1 VALUES(1); +INSERT INTO mdev20987_2 VALUES(1, 'mariadb'); # restart SHOW CREATE TABLE t2; Table Create Table @@ -713,12 +713,8 @@ t2 CREATE TABLE `t2` ( PRIMARY KEY (`FTS_DOC_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb'); -SET @@autocommit=1; -DROP TABLE t1, t2; +DROP TABLE t1, t2, mdev20987_2, mdev20987_1; "----------Test28---------" -drop table if exists `fts_test`; -Warnings: -Note 1051 Unknown table 'test.fts_test' create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb; set session autocommit=0; insert into `fts_test` values (''); @@ -908,9 +904,6 @@ id title body 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... DROP TABLE articles; -drop table if exists t1; -Warnings: -Note 1051 Unknown table 'test.t1' create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key, title varchar(200),body text,fulltext(title,body)) engine=innodb; insert into t1 set body='test'; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test index 4563d6dde77..e1f4874a1d4 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test @@ -7,10 +7,6 @@ let collation=UTF8_UNICODE_CI; --source include/have_collation.inc ---disable_warnings -drop table if exists t1; ---enable_warnings - # Create FTS table CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -403,10 +399,6 @@ DROP TABLE t1; #------------------------------------------------------------------------------ # More FTS test from peter's testing #------------------------------------------------------------------------------ ---disable_warnings -drop table if exists t50; ---enable_warnings - set names utf8; @@ -608,7 +600,6 @@ CREATE FULLTEXT INDEX i ON t1 (s1); INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL'); SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI); -DROP TABLE if EXISTS t2; CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB; CREATE FULLTEXT INDEX i ON t2 ( s1); INSERT INTO t2 VALUES @@ -672,17 +663,21 @@ DROP TABLE t1; CREATE TABLE t1 (id INT,char_column VARCHAR(60)); CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB; ALTER TABLE t2 DROP a; -SET @@autocommit=0; CREATE FULLTEXT INDEX i ON t1 (char_column); INSERT INTO t1 values (1,'aaa'); + +CREATE TABLE mdev20987_1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB; +CREATE TABLE mdev20987_2(f1 INT NOT NULL, f2 CHAR(100), + FULLTEXT(f2), + FOREIGN KEY(f1) REFERENCES mdev20987_1(f1))ENGINE=InnoDB; +INSERT INTO mdev20987_1 VALUES(1); +INSERT INTO mdev20987_2 VALUES(1, 'mariadb'); --source include/restart_mysqld.inc SHOW CREATE TABLE t2; DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb'); -SET @@autocommit=1; -DROP TABLE t1, t2; +DROP TABLE t1, t2, mdev20987_2, mdev20987_1; --echo "----------Test28---------" -drop table if exists `fts_test`; create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb; set session autocommit=0; insert into `fts_test` values (''); @@ -871,8 +866,6 @@ DROP TABLE articles; # Test for Bug 13940669 - 64901: INNODB: ASSERTION FAILURE IN # THREAD 34387022112 IN FILE REM0CMP.CC LINE 5 -drop table if exists t1; - create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key, title varchar(200),body text,fulltext(title,body)) engine=innodb; diff --git a/mysql-test/suite/perfschema/r/misc.result b/mysql-test/suite/perfschema/r/misc.result index 4c8fdca8e68..2e4d21d625a 100644 --- a/mysql-test/suite/perfschema/r/misc.result +++ b/mysql-test/suite/perfschema/r/misc.result @@ -134,3 +134,19 @@ truncate performance_schema.events_statements_history 0 select * from t1 3 insert into t1 select RAND()*10000 from t1 6 drop table t1; +# +# MDEV-17896 Assertion `pfs->get_refcount() > 0' failed +# in release_table_share +# +SELECT COUNT(*)<@@performance_schema_max_table_instances FROM +performance_schema.objects_summary_global_by_type WHERE OBJECT_TYPE='TABLE'; +COUNT(*)<@@performance_schema_max_table_instances +1 +CREATE TABLE t0(a INT); +SELECT * FROM t0; +a +DROP TEMPORARY TABLE IF EXISTS t0; +Warnings: +Note 1051 Unknown table 'test.t0' +FLUSH TABLE t0; +DROP TABLE t0; diff --git a/mysql-test/suite/perfschema/t/misc.test b/mysql-test/suite/perfschema/t/misc.test index c9f7dc6bfc0..80ce64f0302 100644 --- a/mysql-test/suite/perfschema/t/misc.test +++ b/mysql-test/suite/perfschema/t/misc.test @@ -222,3 +222,51 @@ insert into t1 select RAND()*10000 from t1; select sql_text, rows_examined from performance_schema.events_statements_history; drop table t1; +--echo # +--echo # MDEV-17896 Assertion `pfs->get_refcount() > 0' failed +--echo # in release_table_share +--echo # + +# There must be at least one available slot in PFS table_share_array for +# this test to be meaningful. If there are no free slots we must +# restart mysqld, it is the only way to reset PFS table_share_array +let $query= SELECT COUNT(*)<@@performance_schema_max_table_instances FROM + performance_schema.objects_summary_global_by_type WHERE OBJECT_TYPE='TABLE'; + +let $free_slots_available= `$query`; + +if (!$free_slots_available) +{ + source include/restart_mysqld.inc; +} +eval $query; + +CREATE TABLE t0(a INT); + +# TABLE_SHARE must be cached in the table definition cache. +SELECT * FROM t0; + +# Dropping t0 using DROP TEMPORARY frees up a slot in table_share_array, +# but the persistent table is not correctly dropped, i.e. TABLE_SHARE::m_psi +# still points to that slot in table_share_array. +DROP TEMPORARY TABLE IF EXISTS t0; + +# Try re-using each and every slot in PFS table_share_array. If bug is +# there, we re-use t0 slot. +# The newly created table that re-uses the t0 slot ends up +# resetting the PFS_table_share refcount. +let $i= `SELECT @@performance_schema_max_table_instances`; +disable_query_log; +while ($i) +{ + # Memory engine is here to reduce disk IO + eval CREATE TABLE t$i(a INT) ENGINE=MEMORY; + eval DROP TABLE t$i; + dec $i; +} +enable_query_log; + +# FLUSH TABLE crashes the server when PFS_table_share is found with +# an unexpected refcount. +FLUSH TABLE t0; +DROP TABLE t0; |