diff options
Diffstat (limited to 'mysql-test')
30 files changed, 2113 insertions, 155 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 7d4c712eee3..a2e08eacebc 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -1,4 +1,5 @@ -drop table if exists t1,t2,t3; +drop table if exists t0,t1,t2,t3; +drop database if exists test1; set @exit_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=on,derived_with_keys=on'; set @save_optimizer_switch=@@optimizer_switch; @@ -353,30 +354,152 @@ pk pk 80 80 drop table t1, t2, t3, t4; # +# MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled +# +create database test1; +use test1; +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; +CREATE TABLE t0 ( +f1 bigint(20) DEFAULT NULL, +f2 char(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two'); +CREATE TABLE t1 ( +f1 decimal(64,30) DEFAULT NULL, +f2 varchar(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(NULL,'numeric column is NULL'), +(0.000000000000000000000000000000,NULL), +(5.000000000000000000000000000000,'five'), +(1.000000000000000000000000000000,'one'), +(3.000000000000000000000000000000,'three'); +CREATE TABLE t2 ( +f1 double DEFAULT NULL, +f2 varbinary(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three'); +create VIEW v0 AS select f1,f2 from t1 ; +create VIEW v1 AS select tab1_v1.f1,tab1_v1.f2 from t1 tab1_v1 join v0 tab2 on tab1_v1.f1 = tab2.f1 and tab1_v1.f2 = tab2.f2; +create VIEW v2 AS select tab1_v2.f1,tab1_v2.f2 from t2 tab1_v2 join v1 tab2 on tab1_v2.f1 = tab2.f1 and tab1_v2.f2 = tab2.f2; +create VIEW v3 AS select tab1_v3.f1,tab1_v3.f2 from t0 tab1_v3 join v2 tab2 on tab1_v3.f1 = tab2.f1 and tab1_v3.f2 = tab2.f2; +create VIEW v4 AS select tab1_v4.f1,tab1_v4.f2 from t1 tab1_v4 join v3 tab2 on tab1_v4.f1 = tab2.f1 and tab1_v4.f2 = tab2.f2; +create VIEW v5 AS select tab1_v5.f1,tab1_v5.f2 from t2 tab1_v5 join v4 tab2 on tab1_v5.f1 = tab2.f1 and tab1_v5.f2 = tab2.f2; +create VIEW v6 AS select tab1_v6.f1,tab1_v6.f2 from t0 tab1_v6 join v5 tab2 on tab1_v6.f1 = tab2.f1 and tab1_v6.f2 = tab2.f2; +create VIEW v7 AS select tab1_v7.f1,tab1_v7.f2 from t1 tab1_v7 join v6 tab2 on tab1_v7.f1 = tab2.f1 and tab1_v7.f2 = tab2.f2; +create VIEW v8 AS select tab1_v8.f1,tab1_v8.f2 from t2 tab1_v8 join v7 tab2 on tab1_v8.f1 = tab2.f1 and tab1_v8.f2 = tab2.f2; +create VIEW v9 AS select tab1_v9.f1,tab1_v9.f2 from t0 tab1_v9 join v8 tab2 on tab1_v9.f1 = tab2.f1 and tab1_v9.f2 = tab2.f2; +create VIEW v10 AS select tab1_v10.f1,tab1_v10.f2 from t1 tab1_v10 join v9 tab2 on tab1_v10.f1 = tab2.f1 and tab1_v10.f2 = tab2.f2; +create VIEW v11 AS select tab1_v11.f1,tab1_v11.f2 from t2 tab1_v11 join v10 tab2 on tab1_v11.f1 = tab2.f1 and tab1_v11.f2 = tab2.f2; +create VIEW v12 AS select tab1_v12.f1,tab1_v12.f2 from t0 tab1_v12 join v11 tab2 on tab1_v12.f1 = tab2.f1 and tab1_v12.f2 = tab2.f2; +create VIEW v13 AS select tab1_v13.f1,tab1_v13.f2 from t1 tab1_v13 join v12 tab2 on tab1_v13.f1 = tab2.f1 and tab1_v13.f2 = tab2.f2; +create VIEW v14 AS select tab1_v14.f1,tab1_v14.f2 from t2 tab1_v14 join v13 tab2 on tab1_v14.f1 = tab2.f1 and tab1_v14.f2 = tab2.f2; +create VIEW v15 AS select tab1_v15.f1,tab1_v15.f2 from t0 tab1_v15 join v14 tab2 on tab1_v15.f1 = tab2.f1 and tab1_v15.f2 = tab2.f2; +create VIEW v16 AS select tab1_v16.f1,tab1_v16.f2 from t1 tab1_v16 join v15 tab2 on tab1_v16.f1 = tab2.f1 and tab1_v16.f2 = tab2.f2; +create VIEW v17 AS select tab1_v17.f1,tab1_v17.f2 from t2 tab1_v17 join v16 tab2 on tab1_v17.f1 = tab2.f1 and tab1_v17.f2 = tab2.f2; +create VIEW v18 AS select tab1_v18.f1,tab1_v18.f2 from t0 tab1_v18 join v17 tab2 on tab1_v18.f1 = tab2.f1 and tab1_v18.f2 = tab2.f2; +create VIEW v19 AS select tab1_v19.f1,tab1_v19.f2 from t1 tab1_v19 join v18 tab2 on tab1_v19.f1 = tab2.f1 and tab1_v19.f2 = tab2.f2; +create VIEW v20 AS select tab1_v20.f1,tab1_v20.f2 from t2 tab1_v20 join v19 tab2 on tab1_v20.f1 = tab2.f1 and tab1_v20.f2 = tab2.f2; +create VIEW v21 AS select tab1_v21.f1,tab1_v21.f2 from t0 tab1_v21 join v20 tab2 on tab1_v21.f1 = tab2.f1 and tab1_v21.f2 = tab2.f2; +create VIEW v22 AS select tab1_v22.f1,tab1_v22.f2 from t1 tab1_v22 join v21 tab2 on tab1_v22.f1 = tab2.f1 and tab1_v22.f2 = tab2.f2; +create VIEW v23 AS select tab1_v23.f1,tab1_v23.f2 from t2 tab1_v23 join v22 tab2 on tab1_v23.f1 = tab2.f1 and tab1_v23.f2 = tab2.f2; +create VIEW v24 AS select tab1_v24.f1,tab1_v24.f2 from t0 tab1_v24 join v23 tab2 on tab1_v24.f1 = tab2.f1 and tab1_v24.f2 = tab2.f2; +create VIEW v25 AS select tab1_v25.f1,tab1_v25.f2 from t1 tab1_v25 join v24 tab2 on tab1_v25.f1 = tab2.f1 and tab1_v25.f2 = tab2.f2; +create VIEW v26 AS select tab1_v26.f1,tab1_v26.f2 from t2 tab1_v26 join v25 tab2 on tab1_v26.f1 = tab2.f1 and tab1_v26.f2 = tab2.f2; +create VIEW v27 AS select tab1_v27.f1,tab1_v27.f2 from t0 tab1_v27 join v26 tab2 on tab1_v27.f1 = tab2.f1 and tab1_v27.f2 = tab2.f2; +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tab1_v27 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE tab1_v26 hash_ALL NULL #hash#$hj 63 test1.tab1_v27.f1,test1.tab1_v27.f2 5 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE tab1_v25 hash_ALL NULL #hash#$hj 31 test1.tab1_v26.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v24 hash_ALL NULL #hash#$hj 60 test1.tab1_v25.f1,test1.tab1_v25.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v23 hash_ALL NULL #hash#$hj 63 test1.tab1_v24.f1,test1.tab1_v24.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v22 hash_ALL NULL #hash#$hj 31 test1.tab1_v23.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v21 hash_ALL NULL #hash#$hj 60 test1.tab1_v22.f1,test1.tab1_v22.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v20 hash_ALL NULL #hash#$hj 63 test1.tab1_v21.f1,test1.tab1_v21.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v19 hash_ALL NULL #hash#$hj 31 test1.tab1_v20.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v18 hash_ALL NULL #hash#$hj 60 test1.tab1_v19.f1,test1.tab1_v19.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v17 hash_ALL NULL #hash#$hj 63 test1.tab1_v18.f1,test1.tab1_v18.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v16 hash_ALL NULL #hash#$hj 31 test1.tab1_v17.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v15 hash_ALL NULL #hash#$hj 60 test1.tab1_v16.f1,test1.tab1_v16.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v14 hash_ALL NULL #hash#$hj 63 test1.tab1_v15.f1,test1.tab1_v15.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v13 hash_ALL NULL #hash#$hj 31 test1.tab1_v14.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v12 hash_ALL NULL #hash#$hj 60 test1.tab1_v13.f1,test1.tab1_v13.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v11 hash_ALL NULL #hash#$hj 63 test1.tab1_v12.f1,test1.tab1_v12.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v10 hash_ALL NULL #hash#$hj 31 test1.tab1_v11.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v9 hash_ALL NULL #hash#$hj 60 test1.tab1_v10.f1,test1.tab1_v10.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v8 hash_ALL NULL #hash#$hj 63 test1.tab1_v9.f1,test1.tab1_v9.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v7 hash_ALL NULL #hash#$hj 31 test1.tab1_v8.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v6 hash_ALL NULL #hash#$hj 60 test1.tab1_v7.f1,test1.tab1_v7.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v5 hash_ALL NULL #hash#$hj 63 test1.tab1_v6.f1,test1.tab1_v6.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v4 hash_ALL NULL #hash#$hj 31 test1.tab1_v5.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v3 hash_ALL NULL #hash#$hj 60 test1.tab1_v4.f1,test1.tab1_v4.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v2 hash_ALL NULL #hash#$hj 63 test1.tab1_v3.f1,test1.tab1_v3.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v1 hash_ALL NULL #hash#$hj 31 test1.tab1_v2.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 85 test1.tab1_v1.f1,test1.tab1_v1.f2 5 Using where; Using join buffer (incremental, BNLH join) +# This used to hang forever: +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tab1_v27 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE tab1_v26 hash_ALL NULL #hash#$hj 63 test1.tab1_v27.f1,test1.tab1_v27.f2 5 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE tab1_v25 hash_ALL NULL #hash#$hj 31 test1.tab1_v26.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v24 hash_ALL NULL #hash#$hj 60 test1.tab1_v25.f1,test1.tab1_v25.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v23 hash_ALL NULL #hash#$hj 63 test1.tab1_v24.f1,test1.tab1_v24.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v22 hash_ALL NULL #hash#$hj 31 test1.tab1_v23.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v21 hash_ALL NULL #hash#$hj 60 test1.tab1_v22.f1,test1.tab1_v22.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v20 hash_ALL NULL #hash#$hj 63 test1.tab1_v21.f1,test1.tab1_v21.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v19 hash_ALL NULL #hash#$hj 31 test1.tab1_v20.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v18 hash_ALL NULL #hash#$hj 60 test1.tab1_v19.f1,test1.tab1_v19.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v17 hash_ALL NULL #hash#$hj 63 test1.tab1_v18.f1,test1.tab1_v18.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v16 hash_ALL NULL #hash#$hj 31 test1.tab1_v17.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v15 hash_ALL NULL #hash#$hj 60 test1.tab1_v16.f1,test1.tab1_v16.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v14 hash_ALL NULL #hash#$hj 63 test1.tab1_v15.f1,test1.tab1_v15.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v13 hash_ALL NULL #hash#$hj 31 test1.tab1_v14.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v12 hash_ALL NULL #hash#$hj 60 test1.tab1_v13.f1,test1.tab1_v13.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v11 hash_ALL NULL #hash#$hj 63 test1.tab1_v12.f1,test1.tab1_v12.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v10 hash_ALL NULL #hash#$hj 31 test1.tab1_v11.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v9 hash_ALL NULL #hash#$hj 60 test1.tab1_v10.f1,test1.tab1_v10.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v8 hash_ALL NULL #hash#$hj 63 test1.tab1_v9.f1,test1.tab1_v9.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v7 hash_ALL NULL #hash#$hj 31 test1.tab1_v8.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v6 hash_ALL NULL #hash#$hj 60 test1.tab1_v7.f1,test1.tab1_v7.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v5 hash_ALL NULL #hash#$hj 63 test1.tab1_v6.f1,test1.tab1_v6.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v4 hash_ALL NULL #hash#$hj 31 test1.tab1_v5.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v3 hash_ALL NULL #hash#$hj 60 test1.tab1_v4.f1,test1.tab1_v4.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v2 hash_ALL NULL #hash#$hj 63 test1.tab1_v3.f1,test1.tab1_v3.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v1 hash_ALL NULL #hash#$hj 31 test1.tab1_v2.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 85 test1.tab1_v1.f1,test1.tab1_v1.f2 5 Using where; Using join buffer (incremental, BNLH join) +use test; +drop database test1; +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; +# # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, c text); -insert into t2 -select +insert into t2 +select A.a + B.a* 10, A.a + B.a* 10, -'blob-data' +'blob-data' from t1 A, t1 B; set @tmp_jcl= @@join_cache_level; set @tmp_os= @@optimizer_switch; set join_cache_level=6; set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on'; -explain -select * from +explain +select * from t1 join -(select * from t2 order by a limit 1000) as D1 -where +(select * from t2 order by a limit 1000) as D1 +where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 +1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 2c1c416472f..97ef61047a5 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -320,3 +320,20 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not((`test`.`t1`.`a` + 0))) drop table t1; +# +# Start of 10.0 tests +# +# +# MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) +# +SELECT NOT NOT strcmp('a','b'); +NOT NOT strcmp('a','b') +1 +EXPLAIN EXTENDED SELECT NOT NOT strcmp('a','b'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select (strcmp('a','b') <> 0) AS `NOT NOT strcmp('a','b')` +# +# End of 10.0 tests +# diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index d63a9977169..99ed73a3e83 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -145,11 +145,47 @@ select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; a b c filler a b set optimizer_switch='index_condition_pushdown=on'; drop table t1,t2; -set @@join_cache_level= @save_join_cache_level; -set storage_engine=@save_storage_engine; -set optimizer_switch=@innodb_mrr_cpk_tmp; drop table t0; # +# MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk varchar(32) character set utf8 primary key, +kp1 char(32) not null, +col1 varchar(32), +key (kp1) +) engine=innodb; +insert into t1 +select +concat('pk-', 1000 +A.a), +concat('kp1-', 1000 +A.a), +concat('val-', 1000 +A.a) +from test.t0 A ; +create table t2 as select kp1 as a from t1; +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; +explain +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 ref kp1 kp1 32 test.t2.a 1 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; +a pk kp1 col1 +kp1-1000 pk-1000 kp1-1000 val-1000 +kp1-1001 pk-1001 kp1-1001 val-1001 +kp1-1002 pk-1002 kp1-1002 val-1002 +kp1-1003 pk-1003 kp1-1003 val-1003 +kp1-1004 pk-1004 kp1-1004 val-1004 +kp1-1005 pk-1005 kp1-1005 val-1005 +kp1-1006 pk-1006 kp1-1006 val-1006 +kp1-1007 pk-1007 kp1-1007 val-1007 +kp1-1008 pk-1008 kp1-1008 val-1008 +kp1-1009 pk-1009 kp1-1009 val-1009 +drop table t0,t1,t2; +# +# # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions # set @tmp_mdev3817=@@optimizer_switch; @@ -195,40 +231,8 @@ id select_type table type possible_keys key key_len ref rows Extra set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; # -# MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() +# This must be at the end: # -create table t0(a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 ( -pk varchar(32) character set utf8 primary key, -kp1 char(32) not null, -col1 varchar(32), -key (kp1) -) engine=innodb; -insert into t1 -select -concat('pk-', 1000 +A.a), -concat('kp1-', 1000 +A.a), -concat('val-', 1000 +A.a) -from test.t0 A ; -create table t2 as select kp1 as a from t1; -set join_cache_level=8; -set optimizer_switch='mrr=on,mrr_sort_keys=on'; -explain -select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 -1 SIMPLE t1 ref kp1 kp1 32 test.t2.a 1 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; -a pk kp1 col1 -kp1-1000 pk-1000 kp1-1000 val-1000 -kp1-1001 pk-1001 kp1-1001 val-1001 -kp1-1002 pk-1002 kp1-1002 val-1002 -kp1-1003 pk-1003 kp1-1003 val-1003 -kp1-1004 pk-1004 kp1-1004 val-1004 -kp1-1005 pk-1005 kp1-1005 val-1005 -kp1-1006 pk-1006 kp1-1006 val-1006 -kp1-1007 pk-1007 kp1-1007 val-1007 -kp1-1008 pk-1008 kp1-1008 val-1008 -kp1-1009 pk-1009 kp1-1009 val-1009 -drop table t0,t1,t2; +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +set optimizer_switch=@innodb_mrr_cpk_tmp; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 253fb61dc27..456e17a91f7 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -498,8 +498,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) 1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (flat, BNL join) +1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -576,8 +576,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) -1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (flat, BNL join) +1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (incremental, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -654,8 +654,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -732,8 +732,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index e303c288552..40abc197a36 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2222,4 +2222,27 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) DROP TABLE t1,t2,t3; +# +# Bug mdev-6705: wrong on expression after constant row substitution +# that triggers a simplification of WHERE condition +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`d` = 10)) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8)) +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +a b c d +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index beea0daa1fa..81395612269 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2233,6 +2233,29 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) DROP TABLE t1,t2,t3; +# +# Bug mdev-6705: wrong on expression after constant row substitution +# that triggers a simplification of WHERE condition +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`d` = 10)) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8)) +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +a b c d +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index ce233e0db23..b5219333ef1 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -382,3 +382,15 @@ CREATE TABLE t2 (d DATE) ENGINE=MyISAM; SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; a b c d DROP TABLE t1,t2; +# +# Start of 10.0 tests +# +# +# MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) +# +SELECT NOT NOT NULLIF(2,3); +NOT NOT NULLIF(2,3) +1 +# +# End of 10.0 tests +# diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 9899b894ff6..d2383f24f2d 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1346,4 +1346,67 @@ foo foo 1 foo foo 2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; +# +# Bug mdev-6325: wrong selectivity of a column with ref access +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int, key(a)); +insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; +# +# Bug mdev-6843: col IS NULL in where condition when col is always NULL +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int); +insert into t2 select NULL, a from t1; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended +select * from t2 A straight_join t2 B where A.a is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE A ALL NULL NULL NULL NULL 1000 100.00 Using where +1 SIMPLE B ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t2` `A` straight_join `test`.`t2` `B` where isnull(`test`.`A`.`a`) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 013fb1d876c..c4c398a9ff6 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1356,6 +1356,69 @@ foo foo 1 foo foo 2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; +# +# Bug mdev-6325: wrong selectivity of a column with ref access +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int, key(a)); +insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; +# +# Bug mdev-6843: col IS NULL in where condition when col is always NULL +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int); +insert into t2 select NULL, a from t1; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended +select * from t2 A straight_join t2 B where A.a is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE A ALL NULL NULL NULL NULL 1000 100.00 Using where +1 SIMPLE B ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t2` `A` straight_join `test`.`t2` `B` where isnull(`test`.`A`.`a`) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/suite/innodb/r/innodb_monitor.result b/mysql-test/suite/innodb/r/innodb_monitor.result new file mode 100644 index 00000000000..f8d24f4e6f5 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_monitor.result @@ -0,0 +1,585 @@ +set global innodb_monitor_disable = All; +select name, status from information_schema.innodb_metrics; +name status +metadata_table_handles_opened disabled +metadata_table_handles_closed disabled +metadata_table_reference_count disabled +metadata_mem_pool_size disabled +lock_deadlocks disabled +lock_timeouts disabled +lock_rec_lock_waits disabled +lock_table_lock_waits disabled +lock_rec_lock_requests disabled +lock_rec_lock_created disabled +lock_rec_lock_removed disabled +lock_rec_locks disabled +lock_table_lock_created disabled +lock_table_lock_removed disabled +lock_table_locks disabled +lock_row_lock_current_waits disabled +lock_row_lock_time disabled +lock_row_lock_time_max disabled +lock_row_lock_waits disabled +lock_row_lock_time_avg disabled +buffer_pool_size disabled +buffer_pool_reads disabled +buffer_pool_read_requests disabled +buffer_pool_write_requests disabled +buffer_pool_wait_free disabled +buffer_pool_read_ahead disabled +buffer_pool_read_ahead_evicted disabled +buffer_pool_pages_total disabled +buffer_pool_pages_misc disabled +buffer_pool_pages_data disabled +buffer_pool_bytes_data disabled +buffer_pool_pages_dirty disabled +buffer_pool_bytes_dirty disabled +buffer_pool_pages_free disabled +buffer_pages_created disabled +buffer_pages_written disabled +buffer_pages_read disabled +buffer_data_reads disabled +buffer_data_written disabled +buffer_flush_batch_scanned disabled +buffer_flush_batch_num_scan disabled +buffer_flush_batch_scanned_per_call disabled +buffer_flush_batch_rescan disabled +buffer_flush_batch_total_pages disabled +buffer_flush_batches disabled +buffer_flush_batch_pages disabled +buffer_flush_neighbor_total_pages disabled +buffer_flush_neighbor disabled +buffer_flush_neighbor_pages disabled +buffer_flush_n_to_flush_requested disabled +buffer_flush_avg_page_rate disabled +buffer_flush_lsn_avg_rate disabled +buffer_flush_pct_for_dirty disabled +buffer_flush_pct_for_lsn disabled +buffer_flush_sync_waits disabled +buffer_flush_adaptive_total_pages disabled +buffer_flush_adaptive disabled +buffer_flush_adaptive_pages disabled +buffer_flush_sync_total_pages disabled +buffer_flush_sync disabled +buffer_flush_sync_pages disabled +buffer_flush_background_total_pages disabled +buffer_flush_background disabled +buffer_flush_background_pages disabled +buffer_LRU_batch_scanned disabled +buffer_LRU_batch_num_scan disabled +buffer_LRU_batch_scanned_per_call disabled +buffer_LRU_batch_total_pages disabled +buffer_LRU_batches disabled +buffer_LRU_batch_pages disabled +buffer_LRU_single_flush_scanned disabled +buffer_LRU_single_flush_num_scan disabled +buffer_LRU_single_flush_scanned_per_call disabled +buffer_LRU_single_flush_failure_count disabled +buffer_LRU_get_free_search disabled +buffer_LRU_search_scanned disabled +buffer_LRU_search_num_scan disabled +buffer_LRU_search_scanned_per_call disabled +buffer_LRU_unzip_search_scanned disabled +buffer_LRU_unzip_search_num_scan disabled +buffer_LRU_unzip_search_scanned_per_call disabled +buffer_page_read_index_leaf disabled +buffer_page_read_index_non_leaf disabled +buffer_page_read_index_ibuf_leaf disabled +buffer_page_read_index_ibuf_non_leaf disabled +buffer_page_read_undo_log disabled +buffer_page_read_index_inode disabled +buffer_page_read_ibuf_free_list disabled +buffer_page_read_ibuf_bitmap disabled +buffer_page_read_system_page disabled +buffer_page_read_trx_system disabled +buffer_page_read_fsp_hdr disabled +buffer_page_read_xdes disabled +buffer_page_read_blob disabled +buffer_page_read_zblob disabled +buffer_page_read_zblob2 disabled +buffer_page_read_other disabled +buffer_page_written_index_leaf disabled +buffer_page_written_index_non_leaf disabled +buffer_page_written_index_ibuf_leaf disabled +buffer_page_written_index_ibuf_non_leaf disabled +buffer_page_written_undo_log disabled +buffer_page_written_index_inode disabled +buffer_page_written_ibuf_free_list disabled +buffer_page_written_ibuf_bitmap disabled +buffer_page_written_system_page disabled +buffer_page_written_trx_system disabled +buffer_page_written_fsp_hdr disabled +buffer_page_written_xdes disabled +buffer_page_written_blob disabled +buffer_page_written_zblob disabled +buffer_page_written_zblob2 disabled +buffer_page_written_other disabled +os_data_reads disabled +os_data_writes disabled +os_data_fsyncs disabled +os_pending_reads disabled +os_pending_writes disabled +os_log_bytes_written disabled +os_log_fsyncs disabled +os_log_pending_fsyncs disabled +os_log_pending_writes disabled +trx_rw_commits disabled +trx_ro_commits disabled +trx_nl_ro_commits disabled +trx_commits_insert_update disabled +trx_rollbacks disabled +trx_rollbacks_savepoint disabled +trx_rollback_active disabled +trx_active_transactions disabled +trx_rseg_history_len disabled +trx_undo_slots_used disabled +trx_undo_slots_cached disabled +trx_rseg_current_size disabled +purge_del_mark_records disabled +purge_upd_exist_or_extern_records disabled +purge_invoked disabled +purge_undo_log_pages disabled +purge_dml_delay_usec disabled +purge_stop_count disabled +purge_resume_count disabled +log_checkpoints disabled +log_lsn_last_flush disabled +log_lsn_last_checkpoint disabled +log_lsn_current disabled +log_lsn_checkpoint_age disabled +log_lsn_buf_pool_oldest disabled +log_max_modified_age_async disabled +log_max_modified_age_sync disabled +log_pending_log_writes disabled +log_pending_checkpoint_writes disabled +log_num_log_io disabled +log_waits disabled +log_write_requests disabled +log_writes disabled +compress_pages_compressed disabled +compress_pages_decompressed disabled +compression_pad_increments disabled +compression_pad_decrements disabled +index_page_splits disabled +index_page_merge_attempts disabled +index_page_merge_successful disabled +index_page_reorg_attempts disabled +index_page_reorg_successful disabled +index_page_discards disabled +adaptive_hash_searches disabled +adaptive_hash_searches_btree disabled +adaptive_hash_pages_added disabled +adaptive_hash_pages_removed disabled +adaptive_hash_rows_added disabled +adaptive_hash_rows_removed disabled +adaptive_hash_rows_deleted_no_hash_entry disabled +adaptive_hash_rows_updated disabled +file_num_open_files disabled +ibuf_merges_insert disabled +ibuf_merges_delete_mark disabled +ibuf_merges_delete disabled +ibuf_merges_discard_insert disabled +ibuf_merges_discard_delete_mark disabled +ibuf_merges_discard_delete disabled +ibuf_merges disabled +ibuf_size disabled +innodb_master_thread_sleeps disabled +innodb_activity_count disabled +innodb_master_active_loops disabled +innodb_master_idle_loops disabled +innodb_background_drop_table_usec disabled +innodb_ibuf_merge_usec disabled +innodb_log_flush_usec disabled +innodb_mem_validate_usec disabled +innodb_master_purge_usec disabled +innodb_dict_lru_usec disabled +innodb_checkpoint_usec disabled +innodb_dblwr_writes disabled +innodb_dblwr_pages_written disabled +innodb_page_size disabled +innodb_rwlock_s_spin_waits disabled +innodb_rwlock_x_spin_waits disabled +innodb_rwlock_s_spin_rounds disabled +innodb_rwlock_x_spin_rounds disabled +innodb_rwlock_s_os_waits disabled +innodb_rwlock_x_os_waits disabled +dml_reads disabled +dml_inserts disabled +dml_deletes disabled +dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled +ddl_background_drop_indexes disabled +ddl_background_drop_tables disabled +ddl_online_create_index disabled +ddl_pending_alter_table disabled +icp_attempts disabled +icp_no_match disabled +icp_out_of_range disabled +icp_match disabled +set global innodb_monitor_enable = all; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_enable = aaa; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of 'aaa' +set global innodb_monitor_disable = All; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_reset_all = all; +select name from information_schema.innodb_metrics where count!=0; +name +set global innodb_monitor_enable = "%lock%"; +select name from information_schema.innodb_metrics +where status != IF(name like "%lock%", 'enabled', 'disabled'); +name +set global innodb_monitor_disable = "%lock%"; +select name, status from information_schema.innodb_metrics +where name like "%lock%"; +name status +lock_deadlocks disabled +lock_timeouts disabled +lock_rec_lock_waits disabled +lock_table_lock_waits disabled +lock_rec_lock_requests disabled +lock_rec_lock_created disabled +lock_rec_lock_removed disabled +lock_rec_locks disabled +lock_table_lock_created disabled +lock_table_lock_removed disabled +lock_table_locks disabled +lock_row_lock_current_waits disabled +lock_row_lock_time disabled +lock_row_lock_time_max disabled +lock_row_lock_waits disabled +lock_row_lock_time_avg disabled +innodb_rwlock_s_spin_waits disabled +innodb_rwlock_x_spin_waits disabled +innodb_rwlock_s_spin_rounds disabled +innodb_rwlock_x_spin_rounds disabled +innodb_rwlock_s_os_waits disabled +innodb_rwlock_x_os_waits disabled +set global innodb_monitor_enable = "%lock*"; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '%lock*' +set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_disable="%%%%%"; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_enable="%"; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_disable="%_%"; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_enable="log%%%%"; +select name from information_schema.innodb_metrics +where status != IF(name like "log%", 'enabled', 'disabled'); +name +set global innodb_monitor_enable="os_%a_fs_ncs"; +set global innodb_monitor_enable="os%pending%"; +select name, status from information_schema.innodb_metrics +where name like "os%"; +name status +os_data_reads disabled +os_data_writes disabled +os_data_fsyncs enabled +os_pending_reads enabled +os_pending_writes enabled +os_log_bytes_written disabled +os_log_fsyncs disabled +os_log_pending_fsyncs enabled +os_log_pending_writes enabled +set global innodb_monitor_enable=""; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' +set global innodb_monitor_enable="_"; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_' +set global innodb_monitor_disable = module_metadata; +set global innodb_monitor_reset_all = module_metadata; +set global innodb_monitor_enable = metadata_table_handles_opened; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled +set global innodb_monitor_reset = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 NULL NULL 0 enabled +drop table monitor_test; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled +set global innodb_monitor_reset_all = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled +set global innodb_monitor_disable = metadata_table_handles_opened; +set global innodb_monitor_reset = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +drop table monitor_test; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +set global innodb_monitor_reset_all = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = metadata_table_handles_opened; +drop table monitor_test; +create table monitor_test(col int) engine = innodb stats_persistent=0; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled +set global innodb_monitor_enable = metadata_table_handles_closed; +create index idx on monitor_test(col); +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_closed"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 2 NULL 2 enabled +metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_disable = module_metadata; +set global innodb_monitor_reset = module_metadata; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +metadata_table_handles_closed 1 NULL 1 NULL NULL 0 disabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_reset_all = module_metadata; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled +metadata_table_handles_closed NULL NULL 0 NULL NULL 0 disabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = module_trx; +begin; +insert into monitor_test values(9); +commit; +begin; +insert into monitor_test values(9); +rollback; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "trx_rollbacks" or name like "trx_active_transactions"; +name max_count min_count count max_count_reset min_count_reset count_reset status +trx_rollbacks 1 NULL 1 1 NULL 1 enabled +trx_active_transactions 1 0 0 1 0 0 enabled +set global innodb_monitor_disable = module_trx; +set global innodb_monitor_enable = module_dml; +insert into monitor_test values(9); +update monitor_test set col = 10 where col = 9; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 4 NULL 4 4 NULL 4 enabled +dml_inserts 1 NULL 1 1 NULL 1 enabled +dml_deletes 0 NULL 0 0 NULL 0 enabled +dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 6 NULL 6 6 NULL 6 enabled +dml_inserts 1 NULL 1 1 NULL 1 enabled +dml_deletes 2 NULL 2 2 NULL 2 enabled +dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_reset = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 6 NULL 6 0 NULL 0 enabled +dml_inserts 1 NULL 1 0 NULL 0 enabled +dml_deletes 2 NULL 2 0 NULL 0 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +insert into monitor_test values(9); +insert into monitor_test values(1); +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 enabled +dml_inserts 3 NULL 3 2 NULL 2 enabled +dml_deletes 4 NULL 4 2 NULL 2 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_reset_all = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 enabled +dml_inserts 3 NULL 3 2 NULL 2 enabled +dml_deletes 4 NULL 4 2 NULL 2 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_disable = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 disabled +dml_inserts 3 NULL 3 2 NULL 2 disabled +dml_deletes 4 NULL 4 2 NULL 2 disabled +dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled +set global innodb_monitor_reset_all = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads NULL NULL 0 NULL NULL 0 disabled +dml_inserts NULL NULL 0 NULL NULL 0 disabled +dml_deletes NULL NULL 0 NULL NULL 0 disabled +dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = dml_inserts; +insert into monitor_test values(9); +insert into monitor_test values(1); +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads NULL NULL 0 NULL NULL 0 disabled +dml_inserts 2 NULL 2 2 NULL 2 enabled +dml_deletes NULL NULL 0 NULL NULL 0 disabled +dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_disable = module_dml; +drop table monitor_test; +set global innodb_monitor_enable = file_num_open_files; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "file_num_open_files"; +name max_count min_count count max_count_reset min_count_reset count_reset status +file_num_open_files # # # # # # enabled +set global innodb_monitor_disable = file_num_open_files; +set global innodb_monitor_enable = "icp%"; +create table monitor_test(a char(3), b int, c char(2), +primary key (a(1), c(1)), key(b)) engine = innodb; +insert into monitor_test values("13", 2, "aa"); +select a from monitor_test where b < 1 for update; +a +select name, count from information_schema.innodb_metrics +where name like "icp%"; +name count +icp_attempts 1 +icp_no_match 0 +icp_out_of_range 1 +icp_match 0 +select a from monitor_test where b < 3 for update; +a +13 +select name, count from information_schema.innodb_metrics +where name like "icp%"; +name count +icp_attempts 2 +icp_no_match 0 +icp_out_of_range 1 +icp_match 1 +drop table monitor_test; +set global innodb_monitor_disable = all; +set global innodb_monitor_reset_all = all; +select 1 from `information_schema`.`INNODB_METRICS` +where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; +1 +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; diff --git a/mysql-test/suite/innodb/t/innodb_monitor.test b/mysql-test/suite/innodb/t/innodb_monitor.test new file mode 100644 index 00000000000..864e0cae862 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_monitor.test @@ -0,0 +1,387 @@ +# This is the test for Metrics Monitor Table feature. +# Test the metrics monitor system's control system +# and counter accuracy. + +--source include/have_innodb.inc + +set global innodb_monitor_disable = All; + +# Test turn on/off the monitor counter with "all" option +# By default, they will be off +select name, status from information_schema.innodb_metrics; + +# Turn on all monitor counters +set global innodb_monitor_enable = all; + +# status should all change to "enabled" +select name from information_schema.innodb_metrics where status!='enabled'; + +# Test wrong argument to the global configure option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = aaa; + +# We require a valid monitor counter/module name. There is no default +# counter name or module. A warning will be printed asking user to +# specify a valid counter name. +#--disable_warnings +#set global innodb_monitor_enable = default; +#--enable_warnings + +# Turn off all monitor counters, option name should be case +# insensitive +set global innodb_monitor_disable = All; + +# status should all change to "disabled" +select name from information_schema.innodb_metrics where status!='disabled'; + +# Reset all counter values +set global innodb_monitor_reset_all = all; + +# count should all change to 0 +select name from information_schema.innodb_metrics where count!=0; + +# Test wildcard match, turn on all counters contain string "lock" +set global innodb_monitor_enable = "%lock%"; + +# All lock related counter should be enabled +select name from information_schema.innodb_metrics +where status != IF(name like "%lock%", 'enabled', 'disabled'); + +# Disable them +set global innodb_monitor_disable = "%lock%"; + +# All lock related counter should be disabled +select name, status from information_schema.innodb_metrics +where name like "%lock%"; + +# No match for "%lock*" +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = "%lock*"; + +# All counters will be turned on with wildcard match string with all "%" +set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all counters +set global innodb_monitor_disable="%%%%%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# One more round testing. All counters will be turned on with +# single wildcard character "%" +set global innodb_monitor_enable="%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all the counters with "%_%" +set global innodb_monitor_disable="%_%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# Turn on all counters start with "log" +set global innodb_monitor_enable="log%%%%"; + +select name from information_schema.innodb_metrics +where status != IF(name like "log%", 'enabled', 'disabled'); + +# Turn on counters "os_data_fsync" with wildcard match "os_%a_fs_ncs", "_" +# is single character wildcard match word +set global innodb_monitor_enable="os_%a_fs_ncs"; + +# Turn on counters whose name contains "os" and "pending" with +# wildcard match "os%pending%" +set global innodb_monitor_enable="os%pending%"; + +select name, status from information_schema.innodb_metrics +where name like "os%"; + +# Empty string is an invalid option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable=""; + +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable="_"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset_all = module_metadata; + +# Only turn on "table_open" counter +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# This will open the monitor_test table +select * from monitor_test; + +# "metadata_table_handles_opened" should increment by 1 +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset the counter value while counter is still on (started) +# This will reset value "count_reset" but not +# "count" +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again to increment "metadata_table_handles_opened" again +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Cannot reset all monitor value while the counter is on +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn off the counter "metadata_table_handles_opened" +set global innodb_monitor_disable = metadata_table_handles_opened; + +# Reset the counter value while counter is off (disabled) +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again. Since monitor is off, "metadata_table_handles_opened" +# should not be incremented +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# "metadata_table_handles_opened" should increment +select * from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset all the counters, include those counter *_since_start +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn on "table_open" counter again +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Test metadata_table_handles_opened again to see if it is working correctly +# after above round of turning on/off/reset +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb stats_persistent=0; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Test counter "metadata_table_handles_closed", +# create index will close the old handle +set global innodb_monitor_enable = metadata_table_handles_closed; + +create index idx on monitor_test(col); + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_closed"; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +set global innodb_monitor_reset_all = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Test Transaction Module +set global innodb_monitor_enable = module_trx; + +begin; +insert into monitor_test values(9); +commit; + +begin; +insert into monitor_test values(9); +rollback; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "trx_rollbacks" or name like "trx_active_transactions"; + +set global innodb_monitor_disable = module_trx; + +# Test DML Module +set global innodb_monitor_enable = module_dml; + +insert into monitor_test values(9); + +update monitor_test set col = 10 where col = 9; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status + from information_schema.innodb_metrics + where name like "dml%"; + +# test reset counter while the counter is on +set global innodb_monitor_reset = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# insert/delete some rows after the reset +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# We do not allow reset_all while the counter is on, nothing +# should be reset here +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Turn off the counter +set global innodb_monitor_disable = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Reset all counter values +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Open individual counter "dml_inserts" +set global innodb_monitor_enable = dml_inserts; + +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +# Only counter "dml_inserts" should be updated +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +set global innodb_monitor_disable = module_dml; + +drop table monitor_test; + +set global innodb_monitor_enable = file_num_open_files; + +# Counters are unpredictable when innodb-file-per-table is on +--replace_column 2 # 3 # 4 # 5 # 6 # 7 # +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "file_num_open_files"; + +set global innodb_monitor_disable = file_num_open_files; + +# Test ICP module counters +set global innodb_monitor_enable = "icp%"; + +create table monitor_test(a char(3), b int, c char(2), +primary key (a(1), c(1)), key(b)) engine = innodb; + +insert into monitor_test values("13", 2, "aa"); + +select a from monitor_test where b < 1 for update; + +# should have icp_attempts = 1 and icp_out_of_range = 1 +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +# should have icp_attempts = 2 and icp_match = 1 +select a from monitor_test where b < 3 for update; + +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +drop table monitor_test; + +set global innodb_monitor_disable = all; +set global innodb_monitor_reset_all = all; + +# Test for bug #13966091 +select 1 from `information_schema`.`INNODB_METRICS` +where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; + +-- disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +-- enable_warnings diff --git a/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc b/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc new file mode 100644 index 00000000000..b624853cd37 --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_innodb_rows_counters.inc @@ -0,0 +1,50 @@ +######################################### +# Author: Benjamin Renard benj@fb.com +# Date: 11/15/2013 +# Purpose: Showing the difference between current innodb rows stats and the ones recorded at the beginning of the test +# Requirements: Having @[master|slave]_[system_]rows_[read|inserted|deleted|updated] counters already created +######################################### + +--connection master +--echo ==========MASTER========== + +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; + +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read; +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated; +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted; +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted; + +--sync_slave_with_master +--echo ==========SLAVE=========== + +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; + +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read; +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated; +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted; +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted; + +--connection master diff --git a/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result b/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result new file mode 100644 index 00000000000..1cbcd03a113 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_innodb_bug68220.result @@ -0,0 +1,227 @@ +include/master-slave.inc +[connection master] +DROP TABLE IF EXISTS testdb.t1; +DROP DATABASE IF EXISTS testdb; +select variable_value into @master_rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @master_rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @master_rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @master_rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @master_system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @master_system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @master_system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @master_system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select variable_value into @slave_rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @slave_rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @slave_rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @slave_rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @slave_system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @slave_system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @slave_system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @slave_system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +CREATE DATABASE testdb; +USE testdb; +CREATE TABLE testdb.t1 (i int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO testdb.t1 VALUES (1); +==========MASTER========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +0 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +0 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +0 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read; +@system_rows_read - @master_system_rows_read +0 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated; +@system_rows_updated - @master_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted; +@system_rows_deleted - @master_system_rows_deleted +0 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted; +@system_rows_inserted - @master_system_rows_inserted +0 +==========SLAVE=========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +0 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +0 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +0 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read; +@system_rows_read - @slave_system_rows_read +3 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated; +@system_rows_updated - @slave_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted; +@system_rows_deleted - @slave_system_rows_deleted +3 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted; +@system_rows_inserted - @slave_system_rows_inserted +3 +UPDATE t1 SET i=2 WHERE i=1; +==========MASTER========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +1 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +1 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +0 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read; +@system_rows_read - @master_system_rows_read +0 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated; +@system_rows_updated - @master_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted; +@system_rows_deleted - @master_system_rows_deleted +0 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted; +@system_rows_inserted - @master_system_rows_inserted +0 +==========SLAVE=========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +1 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +1 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +0 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read; +@system_rows_read - @slave_system_rows_read +4 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated; +@system_rows_updated - @slave_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted; +@system_rows_deleted - @slave_system_rows_deleted +4 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted; +@system_rows_inserted - @slave_system_rows_inserted +4 +DELETE FROM t1 WHERE i=2; +==========MASTER========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @master_rows_read; +@rows_read - @master_rows_read +2 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @master_rows_updated; +@rows_updated - @master_rows_updated +1 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @master_rows_deleted; +@rows_deleted - @master_rows_deleted +1 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @master_rows_inserted; +@rows_inserted - @master_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @master_system_rows_read; +@system_rows_read - @master_system_rows_read +0 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @master_system_rows_updated; +@system_rows_updated - @master_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @master_system_rows_deleted; +@system_rows_deleted - @master_system_rows_deleted +0 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @master_system_rows_inserted; +@system_rows_inserted - @master_system_rows_inserted +0 +==========SLAVE=========== +select variable_value into @rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select @rows_read - @slave_rows_read; +@rows_read - @slave_rows_read +2 +select variable_value into @rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select @rows_updated - @slave_rows_updated; +@rows_updated - @slave_rows_updated +1 +select variable_value into @rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select @rows_deleted - @slave_rows_deleted; +@rows_deleted - @slave_rows_deleted +1 +select variable_value into @rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select @rows_inserted - @slave_rows_inserted; +@rows_inserted - @slave_rows_inserted +1 +select variable_value into @system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select @system_rows_read - @slave_system_rows_read; +@system_rows_read - @slave_system_rows_read +5 +select variable_value into @system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select @system_rows_updated - @slave_system_rows_updated; +@system_rows_updated - @slave_system_rows_updated +0 +select variable_value into @system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select @system_rows_deleted - @slave_system_rows_deleted; +@system_rows_deleted - @slave_system_rows_deleted +5 +select variable_value into @system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; +select @system_rows_inserted - @slave_system_rows_inserted; +@system_rows_inserted - @slave_system_rows_inserted +5 +DROP TABLE t1; +DROP DATABASE testdb; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test b/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test new file mode 100644 index 00000000000..37d7d106dca --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug68220.test @@ -0,0 +1,59 @@ +--source include/have_innodb.inc +--source include/master-slave.inc + +# +# Bug#68220: innodb_rows_updated is misleading on slave when *info_repository=TABLE +# + +# clean previous tests +--connection master +--disable_warnings +DROP TABLE IF EXISTS testdb.t1; +DROP DATABASE IF EXISTS testdb; +--enable_warnings +--sync_slave_with_master + +# created all the base variables at the beginning at the test +--connection master +select variable_value into @master_rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @master_rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @master_rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @master_rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @master_system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @master_system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @master_system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @master_system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; + +--connection slave +select variable_value into @slave_rows_read from information_schema.global_status where variable_name = 'innodb_rows_read'; +select variable_value into @slave_rows_updated from information_schema.global_status where variable_name = 'innodb_rows_updated'; +select variable_value into @slave_rows_deleted from information_schema.global_status where variable_name = 'innodb_rows_deleted'; +select variable_value into @slave_rows_inserted from information_schema.global_status where variable_name = 'innodb_rows_inserted'; +select variable_value into @slave_system_rows_read from information_schema.global_status where variable_name = 'innodb_system_rows_read'; +select variable_value into @slave_system_rows_updated from information_schema.global_status where variable_name = 'innodb_system_rows_updated'; +select variable_value into @slave_system_rows_deleted from information_schema.global_status where variable_name = 'innodb_system_rows_deleted'; +select variable_value into @slave_system_rows_inserted from information_schema.global_status where variable_name = 'innodb_system_rows_inserted'; + +--connection master +CREATE DATABASE testdb; +USE testdb; +CREATE TABLE testdb.t1 (i int NOT NULL PRIMARY KEY) ENGINE=InnoDB; + +# insert a row and show counters on master and slave +INSERT INTO testdb.t1 VALUES (1); +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# update the row and show counters on master and slave +UPDATE t1 SET i=2 WHERE i=1; +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# delete the row and show counters on master and slave +DELETE FROM t1 WHERE i=2; +--source suite/rpl/include/rpl_innodb_rows_counters.inc + +# clean the test +DROP TABLE t1; +DROP DATABASE testdb; +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/innodb_adaptive_flushing_lwm_basic.result b/mysql-test/suite/sys_vars/r/innodb_adaptive_flushing_lwm_basic.result index 1797845def2..bfd59cfd9cc 100644 --- a/mysql-test/suite/sys_vars/r/innodb_adaptive_flushing_lwm_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_adaptive_flushing_lwm_basic.result @@ -7,55 +7,55 @@ SET @@global.innodb_adaptive_flushing_lwm = 1; SET @@global.innodb_adaptive_flushing_lwm = DEFAULT; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -10 +10.000000 '#---------------------FN_DYNVARS_046_02-------------------------#' SET innodb_adaptive_flushing_lwm = 1; ERROR HY000: Variable 'innodb_adaptive_flushing_lwm' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@innodb_adaptive_flushing_lwm; @@innodb_adaptive_flushing_lwm -10 +10.000000 SELECT local.innodb_adaptive_flushing_lwm; ERROR 42S02: Unknown table 'local' in field list SET global innodb_adaptive_flushing_lwm = 1; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -1 +1.000000 '#--------------------FN_DYNVARS_046_03------------------------#' SET @@global.innodb_adaptive_flushing_lwm = 1; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -1 +1.000000 SET @@global.innodb_adaptive_flushing_lwm = 60; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -60 +60.000000 SET @@global.innodb_adaptive_flushing_lwm = 70; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -70 +70.000000 '#--------------------FN_DYNVARS_046_04-------------------------#' SET @@global.innodb_adaptive_flushing_lwm = -1; Warnings: Warning 1292 Truncated incorrect innodb_adaptive_flushing_lwm value: '-1' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -0 +0.000000 SET @@global.innodb_adaptive_flushing_lwm = "T"; ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_flushing_lwm' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -0 +0.000000 SET @@global.innodb_adaptive_flushing_lwm = "Y"; ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_flushing_lwm' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -0 +0.000000 SET @@global.innodb_adaptive_flushing_lwm = 71; Warnings: Warning 1292 Truncated incorrect innodb_adaptive_flushing_lwm value: '71' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -70 +70.000000 '#----------------------FN_DYNVARS_046_05------------------------#' SELECT @@global.innodb_adaptive_flushing_lwm = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -65,32 +65,32 @@ VARIABLE_VALUE 1 SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -70 +70.000000 SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_adaptive_flushing_lwm'; VARIABLE_VALUE -70 +70.000000 '#---------------------FN_DYNVARS_046_06-------------------------#' SET @@global.innodb_adaptive_flushing_lwm = OFF; ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_flushing_lwm' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -70 +70.000000 SET @@global.innodb_adaptive_flushing_lwm = ON; ERROR 42000: Incorrect argument type to variable 'innodb_adaptive_flushing_lwm' SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -70 +70.000000 '#---------------------FN_DYNVARS_046_07----------------------#' SET @@global.innodb_adaptive_flushing_lwm = TRUE; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -1 +1.000000 SET @@global.innodb_adaptive_flushing_lwm = FALSE; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -0 +0.000000 SET @@global.innodb_adaptive_flushing_lwm = @global_start_value; SELECT @@global.innodb_adaptive_flushing_lwm; @@global.innodb_adaptive_flushing_lwm -10 +10.000000 diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result index eff72613102..d705624eb53 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result @@ -4,58 +4,64 @@ SELECT @global_start_value; 75 '#--------------------FN_DYNVARS_046_01------------------------#' SET @@global.innodb_max_dirty_pages_pct = 0; -SET @@global.innodb_max_dirty_pages_pct = DEFAULT; +Warnings: +Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '0' +SET @@global.innodb_max_dirty_pages_pct = @global_start_value; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -75 +75.000000 '#---------------------FN_DYNVARS_046_02-------------------------#' SET innodb_max_dirty_pages_pct = 1; ERROR HY000: Variable 'innodb_max_dirty_pages_pct' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@innodb_max_dirty_pages_pct; @@innodb_max_dirty_pages_pct -75 +75.000000 SELECT local.innodb_max_dirty_pages_pct; ERROR 42S02: Unknown table 'local' in field list SET global innodb_max_dirty_pages_pct = 0; +Warnings: +Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '0' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 '#--------------------FN_DYNVARS_046_03------------------------#' SET @@global.innodb_max_dirty_pages_pct = 0; +Warnings: +Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '0' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 SET @@global.innodb_max_dirty_pages_pct = 1; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -1 +1.000000 SET @@global.innodb_max_dirty_pages_pct = 99; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -99 +99.000000 '#--------------------FN_DYNVARS_046_04-------------------------#' SET @@global.innodb_max_dirty_pages_pct = -1; Warnings: Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '-1' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 SET @@global.innodb_max_dirty_pages_pct = "T"; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 SET @@global.innodb_max_dirty_pages_pct = "Y"; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 SET @@global.innodb_max_dirty_pages_pct = 1001; Warnings: Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '1001' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -99 +99.999000 '#----------------------FN_DYNVARS_046_05------------------------#' SELECT @@global.innodb_max_dirty_pages_pct = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -65,32 +71,34 @@ VARIABLE_VALUE 1 SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -99 +99.999000 SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct'; VARIABLE_VALUE -99 +99.999000 '#---------------------FN_DYNVARS_046_06-------------------------#' SET @@global.innodb_max_dirty_pages_pct = OFF; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -99 +99.999000 SET @@global.innodb_max_dirty_pages_pct = ON; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -99 +99.999000 '#---------------------FN_DYNVARS_046_07----------------------#' SET @@global.innodb_max_dirty_pages_pct = TRUE; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -1 +1.000000 SET @@global.innodb_max_dirty_pages_pct = FALSE; +Warnings: +Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct value: '0' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -0 +0.001000 SET @@global.innodb_max_dirty_pages_pct = @global_start_value; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -75 +75.000000 diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result index 55de5adbc33..05aa3e5fd89 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result @@ -5,13 +5,13 @@ SET @@global.innodb_max_dirty_pages_pct = 80; 'connection con1' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -80 +80.000000 SET @@global.innodb_max_dirty_pages_pct = 70; 'connect (con2,localhost,root,,,,)' 'connection con2' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -70 +70.000000 'connection default' 'disconnect con2' 'disconnect con1' diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result index 82388cebc82..676ec103664 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result @@ -1,70 +1,70 @@ SET @pct_lwm_start_value = @@global.innodb_max_dirty_pages_pct_lwm; SELECT @pct_lwm_start_value; @pct_lwm_start_value -0 +0.001 SET @pct_start_value = @@global.innodb_max_dirty_pages_pct; SELECT @pct_start_value; @pct_start_value 75 '#--------------------FN_DYNVARS_046_01------------------------#' SET @@global.innodb_max_dirty_pages_pct_lwm = 0; -SET @@global.innodb_max_dirty_pages_pct_lwm = DEFAULT; +SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_lwm_start_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.001000 '#---------------------FN_DYNVARS_046_02-------------------------#' SET innodb_max_dirty_pages_pct_lwm = 1; ERROR HY000: Variable 'innodb_max_dirty_pages_pct_lwm' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@innodb_max_dirty_pages_pct_lwm; @@innodb_max_dirty_pages_pct_lwm -0 +0.001000 SELECT local.innodb_max_dirty_pages_pct_lwm; ERROR 42S02: Unknown table 'local' in field list SET global innodb_max_dirty_pages_pct_lwm = 0; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 '#--------------------FN_DYNVARS_046_03------------------------#' SET @@global.innodb_max_dirty_pages_pct_lwm = 0; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_start_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 '#--------------------FN_DYNVARS_046_04-------------------------#' SET @@global.innodb_max_dirty_pages_pct_lwm = -1; Warnings: Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct_lwm value: '-1' SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = "T"; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct_lwm' SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = "Y"; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct_lwm' SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_start_value + 1; Warnings: Warning 1210 innodb_max_dirty_pages_pct_lwm cannot be set higher than innodb_max_dirty_pages_pct. -Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 75 +Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 75.000000 SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = 100; Warnings: Warning 1292 Truncated incorrect innodb_max_dirty_pages_pct_lwm value: '100' Warning 1210 innodb_max_dirty_pages_pct_lwm cannot be set higher than innodb_max_dirty_pages_pct. -Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 75 +Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 75.000000 SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 '#----------------------FN_DYNVARS_046_05------------------------#' SELECT @@global.innodb_max_dirty_pages_pct_lwm = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -74,36 +74,36 @@ VARIABLE_VALUE 1 SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct_lwm'; VARIABLE_VALUE -75 +75.000000 '#---------------------FN_DYNVARS_046_06-------------------------#' SET @@global.innodb_max_dirty_pages_pct_lwm = OFF; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct_lwm' SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = ON; ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct_lwm' SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -75 +75.000000 '#---------------------FN_DYNVARS_046_07----------------------#' SET @@global.innodb_max_dirty_pages_pct_lwm = TRUE; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -1 +1.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = FALSE; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.000000 SET @@global.innodb_max_dirty_pages_pct = @pct_start_value; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -75 +75.000000 SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_lwm_start_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0 +0.001000 diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result index 6f1c4c21d17..8c0af874228 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_disable_basic.result @@ -207,6 +207,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_indexes disabled ddl_background_drop_tables disabled ddl_online_create_index disabled @@ -429,6 +433,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -439,6 +447,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -449,6 +461,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -461,6 +477,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -471,6 +491,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -481,6 +505,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -491,6 +519,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -504,6 +536,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result index 6f1c4c21d17..8c0af874228 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_enable_basic.result @@ -207,6 +207,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_indexes disabled ddl_background_drop_tables disabled ddl_online_create_index disabled @@ -429,6 +433,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -439,6 +447,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -449,6 +461,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -461,6 +477,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -471,6 +491,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -481,6 +505,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -491,6 +519,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -504,6 +536,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result index 6f1c4c21d17..8c0af874228 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_all_basic.result @@ -207,6 +207,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_indexes disabled ddl_background_drop_tables disabled ddl_online_create_index disabled @@ -429,6 +433,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -439,6 +447,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -449,6 +461,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -461,6 +477,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -471,6 +491,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -481,6 +505,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -491,6 +519,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -504,6 +536,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result index 6f1c4c21d17..8c0af874228 100644 --- a/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_monitor_reset_basic.result @@ -207,6 +207,10 @@ dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled ddl_background_drop_indexes disabled ddl_background_drop_tables disabled ddl_online_create_index disabled @@ -429,6 +433,10 @@ dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -439,6 +447,10 @@ dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -449,6 +461,10 @@ dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; @@ -461,6 +477,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -471,6 +491,10 @@ dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -481,6 +505,10 @@ dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status @@ -491,6 +519,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); @@ -504,6 +536,10 @@ dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; diff --git a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_basic.test b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_basic.test index 7e70ed11351..5b4eaa41598 100644 --- a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_basic.test +++ b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_basic.test @@ -44,7 +44,7 @@ SELECT @global_start_value; ######################################################################## SET @@global.innodb_max_dirty_pages_pct = 0; -SET @@global.innodb_max_dirty_pages_pct = DEFAULT; +SET @@global.innodb_max_dirty_pages_pct = @global_start_value; SELECT @@global.innodb_max_dirty_pages_pct; --echo '#---------------------FN_DYNVARS_046_02-------------------------#' diff --git a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_lwm_basic.test b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_lwm_basic.test index 7a6da2e6a08..d81b6cc725b 100644 --- a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_lwm_basic.test +++ b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_lwm_basic.test @@ -47,7 +47,7 @@ SELECT @pct_start_value; ######################################################################## SET @@global.innodb_max_dirty_pages_pct_lwm = 0; -SET @@global.innodb_max_dirty_pages_pct_lwm = DEFAULT; +SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_lwm_start_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; --echo '#---------------------FN_DYNVARS_046_02-------------------------#' diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index e3b76015b65..7f19553e4e5 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -1,6 +1,7 @@ # Initialize --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t0,t1,t2,t3; +drop database if exists test1; --enable_warnings set @exit_optimizer_switch=@@optimizer_switch; @@ -273,28 +274,89 @@ limit 10; drop table t1, t2, t3, t4; --echo # +--echo # MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled +--echo # +create database test1; +use test1; + +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; + +CREATE TABLE t0 ( + f1 bigint(20) DEFAULT NULL, + f2 char(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two'); + +CREATE TABLE t1 ( + f1 decimal(64,30) DEFAULT NULL, + f2 varchar(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES +(NULL,'numeric column is NULL'), +(0.000000000000000000000000000000,NULL), +(5.000000000000000000000000000000,'five'), +(1.000000000000000000000000000000,'one'), +(3.000000000000000000000000000000,'three'); + +CREATE TABLE t2 ( + f1 double DEFAULT NULL, + f2 varbinary(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three'); + +create VIEW v0 AS select f1,f2 from t1 ; + +let $cnt= 27; +while ($cnt) +{ +# i runs from 1 to 27 + let $i= `select 28 - $cnt`; + let $prev=`select $i - 1`; + +# rem = i mod 3 + let $rem= `select MOD($i, 3)`; +# view uses $i, $prev and $rem: + eval create VIEW v$i AS select tab1_v$i.f1,tab1_v$i.f2 from t$rem tab1_v$i join v$prev tab2 on tab1_v$i.f1 = tab2.f1 and tab1_v$i.f2 = tab2.f2; + dec $cnt; +} + +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +--echo # This used to hang forever: +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; + +use test; +drop database test1; +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; + + +--echo # --echo # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() --echo # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, c text); -insert into t2 -select +insert into t2 +select A.a + B.a* 10, A.a + B.a* 10, - 'blob-data' + 'blob-data' from t1 A, t1 B; set @tmp_jcl= @@join_cache_level; set @tmp_os= @@optimizer_switch; set join_cache_level=6; set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on'; -explain -select * from +explain +select * from t1 join - (select * from t2 order by a limit 1000) as D1 -where + (select * from t2 order by a limit 1000) as D1 +where D1.a= t1.a; set join_cache_level=@tmp_jcl; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 6a99b975e81..d3703de26c5 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -180,3 +180,17 @@ select * from t1 where not (a+0); explain extended select * from t1 where not (a+0); drop table t1; + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) +--echo # +SELECT NOT NOT strcmp('a','b'); +EXPLAIN EXTENDED SELECT NOT NOT strcmp('a','b'); + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/innodb_mrr_cpk.test b/mysql-test/t/innodb_mrr_cpk.test index 16702104a57..cb79c238f2b 100644 --- a/mysql-test/t/innodb_mrr_cpk.test +++ b/mysql-test/t/innodb_mrr_cpk.test @@ -134,12 +134,40 @@ set optimizer_switch='index_condition_pushdown=on'; drop table t1,t2; -set @@join_cache_level= @save_join_cache_level; -set storage_engine=@save_storage_engine; -set optimizer_switch=@innodb_mrr_cpk_tmp; drop table t0; --echo # +--echo # MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk varchar(32) character set utf8 primary key, + kp1 char(32) not null, + col1 varchar(32), + key (kp1) +) engine=innodb; + +insert into t1 +select + concat('pk-', 1000 +A.a), + concat('kp1-', 1000 +A.a), + concat('val-', 1000 +A.a) +from test.t0 A ; + +create table t2 as select kp1 as a from t1; + +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; +explain +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; + +drop table t0,t1,t2; + +--echo # +--echo # --echo # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions --echo # @@ -190,37 +218,12 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; set join_cache_level= @tmp_mdev5037; - drop table t0,t1,t2; --echo # ---echo # MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() +--echo # This must be at the end: --echo # -create table t0(a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 ( - pk varchar(32) character set utf8 primary key, - kp1 char(32) not null, - col1 varchar(32), - key (kp1) -) engine=innodb; - -insert into t1 -select - concat('pk-', 1000 +A.a), - concat('kp1-', 1000 +A.a), - concat('val-', 1000 +A.a) -from test.t0 A ; - -create table t2 as select kp1 as a from t1; - -set join_cache_level=8; -set optimizer_switch='mrr=on,mrr_sort_keys=on'; -explain -select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; -select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; - -drop table t0,t1,t2; - - +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +set optimizer_switch=@innodb_mrr_cpk_tmp; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 7a70c413e8d..19c5f64de78 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1777,4 +1777,28 @@ SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 DROP TABLE t1,t2,t3; +--echo # +--echo # Bug mdev-6705: wrong on expression after constant row substitution +--echo # that triggers a simplification of WHERE condition +--echo # + +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); + +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); + +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); + +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a + WHERE b IN (1,2,3) OR b = d; + +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a + WHERE b IN (1,2,3) OR b = d; + +DROP TABLE t1,t2,t3; + + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 81951a9ce68..40f66946822 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -295,3 +295,16 @@ CREATE TABLE t2 (d DATE) ENGINE=MyISAM; SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; DROP TABLE t1,t2; + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) +--echo # +SELECT NOT NOT NULLIF(2,3); + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 8cb2620550e..5db6f3b9d6a 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -889,4 +889,58 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit DROP TABLE t1,t2; +--echo # +--echo # Bug mdev-6325: wrong selectivity of a column with ref access +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int, key(a)); +insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; + +set use_stat_tables='preferably'; +set histogram_size=100; + +set optimizer_use_condition_selectivity=4; +analyze table t1 persistent for all; +analyze table t2 persistent for all; + +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; + +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +drop table t0,t1,t2; + +--echo # +--echo # Bug mdev-6843: col IS NULL in where condition when col is always NULL +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int); +insert into t2 select NULL, a from t1; + +set use_stat_tables='preferably'; +set histogram_size=100; + +set optimizer_use_condition_selectivity=4; +analyze table t2 persistent for all; + +explain extended +select * from t2 A straight_join t2 B where A.a is null; + +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +drop table t0,t1,t2; + set use_stat_tables=@save_use_stat_tables; + |