commit fcb63d1d520597ebcf465d71971d0bd62c4d84e5 Author: Laurynas Biveinis Date: Mon Jan 16 12:47:55 2017 +0200 Fix bug 84366 / 1625151 (InnoDB index dives do not detect concurrent tree changes, return bogus estimates) In btr_estimate_n_rows_in_range_low, consider that the tree structure has changed, if the two dive paths have crossed, and the same node in the two paths appears to contain a different number of records. If the paths have crossed without a detected tree structure change, return zero estimate immediately. Add waits for purge to complete to stabilize main.index_merge_innodb test. diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index f4919fd7b51..9e7d4dd7d3d 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -455,6 +455,10 @@ alter table t3 add keyB int not null, add index iB(keyB); alter table t3 add keyC int not null, add index iC(keyC); update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; +# Force complete purge +SET @@GLOBAL.innodb_fast_shutdown = 0; +--source include/restart_mysqld.inc + -- disable_query_log -- disable_result_log analyze table t3; diff --git a/mysql-test/include/index_merge2.inc b/mysql-test/include/index_merge2.inc index a825815566b..bc5875b05ad 100644 --- a/mysql-test/include/index_merge2.inc +++ b/mysql-test/include/index_merge2.inc @@ -372,6 +372,10 @@ alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; +# Force complete purge +SET @@GLOBAL.innodb_fast_shutdown = 0; +--source include/restart_mysqld.inc + -- disable_query_log -- disable_result_log analyze table t1; diff --git a/mysql-test/include/index_merge_ror.inc b/mysql-test/include/index_merge_ror.inc index 3824b6f739c..6a772a2fb3f 100644 --- a/mysql-test/include/index_merge_ror.inc +++ b/mysql-test/include/index_merge_ror.inc @@ -184,6 +184,10 @@ select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=1 delete from t1 where key3=100 and key4=100; +# Force complete purge +SET @@GLOBAL.innodb_fast_shutdown = 0; +--source include/restart_mysqld.inc + -- disable_query_log -- disable_result_log analyze table t1; diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index cf94975e675..73a2a7df109 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -420,6 +420,8 @@ alter table t3 add keyA int not null, add index iA(keyA); alter table t3 add keyB int not null, add index iB(keyB); alter table t3 add keyC int not null, add index iC(keyC); update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart explain select * from t3 where key1=1 or key2=2 or key3=3 or key4=4 or key5=5 or key6=6 or key7=7 or key8=8 or @@ -917,6 +919,8 @@ select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=1 key1 key2 key3 key4 filler1 -1 -1 100 100 key4-key3 delete from t1 where key3=100 and key4=100; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart # ROR-union with all ROR-intersections giving empty results explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra @@ -1422,6 +1426,8 @@ set @d=@d*2; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge i2,i3 i3,i2 4,4 NULL # 100.00 Using sort_union(i3,i2); Using where diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 395badc7e5a..eb711239ca6 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -428,6 +428,8 @@ alter table t3 add keyA int not null, add index iA(keyA); alter table t3 add keyB int not null, add index iB(keyB); alter table t3 add keyC int not null, add index iC(keyC); update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart explain select * from t3 where key1=1 or key2=2 or key3=3 or key4=4 or key5=5 or key6=6 or key7=7 or key8=8 or @@ -959,6 +961,8 @@ select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=1 key1 key2 key3 key4 filler1 -1 -1 100 100 key4-key3 delete from t1 where key3=100 and key4=100; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart # ROR-union with all ROR-intersections giving empty results explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra @@ -1479,6 +1483,8 @@ set @d=@d*2; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; +SET @@GLOBAL.innodb_fast_shutdown = 0; +# restart explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge i2,i3 i3,i2 4,4 NULL 11 100.00 Using sort_union(i3,i2); Using where diff --git a/mysql-test/suite/innodb/r/bug84366.result b/mysql-test/suite/innodb/r/bug84366.result new file mode 100644 index 00000000000..bed2913e9d9 --- /dev/null +++ b/mysql-test/suite/innodb/r/bug84366.result @@ -0,0 +1,32 @@ +# +# Bug 84366: InnoDB index dives do not detect concurrent tree changes, return +# bogus estimates +# +CREATE TABLE t1 (key1 INT NOT NULL, key2 INT NOT NULL, +INDEX i1(key1), INDEX i2(key2)) ENGINE = InnoDB; +SET @@GLOBAL.innodb_purge_stop_now = TRUE; +ALTER TABLE t1 ADD keyC INT NOT NULL, ADD INDEX iC(keyC); +UPDATE t1 SET keyC = key1; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge i1,i2,iC i1,i2,iC 4,4,4 NULL 3 100.00 Using union(i1,i2,iC); Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12)) +SET DEBUG_SYNC = "btr_estimate_n_rows_in_range_between_dives SIGNAL estimate_ready WAIT_FOR estimate_finish"; +EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12; +SET DEBUG_SYNC = "now WAIT_FOR estimate_ready"; +SET @@GLOBAL.innodb_purge_run_now = TRUE; +SET DEBUG_SYNC = "now SIGNAL estimate_finish"; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge i1,i2,iC i1,i2,iC 4,4,4 NULL 3 100.00 Using union(i1,i2,iC); Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12)) +EXPLAIN SELECT * FROM t1 WHERE key1=1 or key2=2 or keyC=12; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge i1,i2,iC i1,i2,iC 4,4,4 NULL 3 100.00 Using union(i1,i2,iC); Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12)) +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/bug84366.test b/mysql-test/suite/innodb/t/bug84366.test new file mode 100644 index 00000000000..7d1471e9765 --- /dev/null +++ b/mysql-test/suite/innodb/t/bug84366.test @@ -0,0 +1,66 @@ +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_innodb.inc + +--echo # +--echo # Bug 84366: InnoDB index dives do not detect concurrent tree changes, return +--echo # bogus estimates +--echo # + +--source include/count_sessions.inc + +CREATE TABLE t1 (key1 INT NOT NULL, key2 INT NOT NULL, + INDEX i1(key1), INDEX i2(key2)) ENGINE = InnoDB; + +--disable_query_log +INSERT INTO t1 VALUES (1, 1), (2, 2); + +let $1=9; +SET @d=2; +while ($1) +{ + INSERT INTO t1 SELECT key1 + @d, key2 + @d FROM t1; + SET @d=@d*2; + dec $1; +} +--enable_query_log + +SET @@GLOBAL.innodb_purge_stop_now = TRUE; + +ALTER TABLE t1 ADD keyC INT NOT NULL, ADD INDEX iC(keyC); +UPDATE t1 SET keyC = key1; + +ANALYZE TABLE t1; + +--connect(con1,localhost,root,,) + +EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12; + +SET DEBUG_SYNC = "btr_estimate_n_rows_in_range_between_dives SIGNAL estimate_ready WAIT_FOR estimate_finish"; + +send EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12; + +connection default; +SET DEBUG_SYNC = "now WAIT_FOR estimate_ready"; + +SET @@GLOBAL.innodb_purge_run_now = TRUE; + +# wait_innodb_all_purged.inc is not used directly as we wait for the trx age +# to become two, not zero, due to open transactions +--let $status_var= innodb_purge_trx_id_age +--let $status_var_value= 2 +--source include/wait_for_status_var.inc + +SET DEBUG_SYNC = "now SIGNAL estimate_finish"; + +connection con1; +reap; + +EXPLAIN SELECT * FROM t1 WHERE key1=1 or key2=2 or keyC=12; + +disconnect con1; +connection default; + +DROP TABLE t1; + +--source include/wait_until_count_sessions.inc diff --git a/storage/innobase/btr/btr0cur.cc b/storage/innobase/btr/btr0cur.cc index b6f8fe1b7c9..8358ed23be3 100644 --- a/storage/innobase/btr/btr0cur.cc +++ b/storage/innobase/btr/btr0cur.cc @@ -5650,6 +5650,12 @@ btr_estimate_n_rows_in_range_low( mtr_commit(&mtr); +#ifdef UNIV_DEBUG + if (!strcmp(index->name, "iC")) { + DEBUG_SYNC_C("btr_estimate_n_rows_in_range_between_dives"); + } +#endif + mtr_start(&mtr); cursor.path_arr = path2; @@ -5826,12 +5832,16 @@ btr_estimate_n_rows_in_range_low( if (!diverged && slot1->nth_rec != slot2->nth_rec) { - /* If both slots do not point to the same page, + /* If both slots do not point to the same page or if + the paths have crossed and the same page on both + apparently contains a different number of records, this means that the tree must have changed between the dive for slot1 and the dive for slot2 at the beginning of this function. */ if (slot1->page_no != slot2->page_no - || slot1->page_level != slot2->page_level) { + || slot1->page_level != slot2->page_level + || (slot1->nth_rec >= slot2->nth_rec + && slot1->n_recs != slot2->n_recs)) { /* If the tree keeps changing even after a few attempts, then just return some arbitrary @@ -5873,9 +5883,7 @@ btr_estimate_n_rows_in_range_low( in this case slot1->nth_rec will point to the supr record and slot2->nth_rec will point to 6. */ - n_rows = 0; - should_count_the_left_border = false; - should_count_the_right_border = false; + return(0); } } else if (diverged && !diverged_lot) {