diff options
| -rw-r--r-- | mysql-test/r/selectivity.result | 56 | ||||
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 56 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_semi_sync_gtid_reconnect.result | 28 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_semi_sync_gtid_reconnect.test | 75 | ||||
| -rw-r--r-- | mysql-test/t/selectivity.test | 32 | ||||
| -rw-r--r-- | scripts/wsrep_sst_mariabackup.sh | 7 | ||||
| -rw-r--r-- | sql/sql_repl.cc | 6 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 | ||||
| -rw-r--r-- | sql/table.cc | 21 | ||||
| -rw-r--r-- | sql/table.h | 1 |
10 files changed, 281 insertions, 5 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 60fac392fa8..d16bd41b2cb 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1815,5 +1815,61 @@ b a a b 9 9 10 10 set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; drop table t1,t2,t3; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 0e629ab38c3..f2435179a85 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1825,6 +1825,62 @@ b a a b 9 9 10 10 set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; drop table t1,t2,t3; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 Using index +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; # End of 10.1 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_gtid_reconnect.result b/mysql-test/suite/rpl/r/rpl_semi_sync_gtid_reconnect.result new file mode 100644 index 00000000000..3c720b94fde --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_gtid_reconnect.result @@ -0,0 +1,28 @@ +include/master-slave.inc +[connection master] +RESET MASTER; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +include/stop_slave.inc +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; +include/start_slave.inc +CREATE TABLE t1 (a INT); +INSERT INTO t1 SET a = 1; +include/save_master_gtid.inc +FLUSH LOGS; +INSERT INTO t1 SET a = 2; +include/stop_slave_sql.inc +INSERT INTO t1 SET a = 3; +include/sync_slave_io_with_master.inc +include/stop_slave_io.inc +RESET MASTER; +SET @@global.gtid_binlog_state = '0-1-2'; +CHANGE MASTER TO MASTER_USE_GTID = slave_pos; +SET @@global.gtid_slave_pos = '0-1-2'; +include/start_slave.inc +INSERT INTO t1 SET a = 4; +DROP TABLE t1; +SET @@GLOBAL. rpl_semi_sync_master_enabled = 0; +include/stop_slave.inc +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 0; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_gtid_reconnect.test b/mysql-test/suite/rpl/t/rpl_semi_sync_gtid_reconnect.test new file mode 100644 index 00000000000..c524fcff3a4 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_gtid_reconnect.test @@ -0,0 +1,75 @@ +source include/not_embedded.inc; +source include/have_binlog_format_mixed.inc; +source include/have_semisync.inc; +source include/master-slave.inc; + +# +# Semisync initialization +# +--connection master +RESET MASTER; +--let $sav_enabled_master=`SELECT @@GLOBAL.rpl_semi_sync_master_enabled` +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; + +--connection slave +source include/stop_slave.inc; +--let $sav_enabled_slave=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled` +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; +source include/start_slave.inc; + +# Prove fixes to +# MDEV-19376 Assert (!m_active_tranxs->is_tranx_end_pos(trx_wait_binlog_name...) +# +# +# Run few queries to replicate/execute on slave. +# Stop the slave applier. +# Replicate/not-executed few more. +# Restart the slave. +# +--connection master +CREATE TABLE t1 (a INT); +INSERT INTO t1 SET a = 1; +--source include/save_master_gtid.inc +--let $resume_gtid = $master_pos +FLUSH LOGS; +INSERT INTO t1 SET a = 2; + +--sync_slave_with_master +--connection slave +--source include/stop_slave_sql.inc + +--connection master +INSERT INTO t1 SET a = 3; + +# the sync connection is 'slave' by default +--source include/sync_slave_io_with_master.inc +--connection slave +--source include/stop_slave_io.inc + +--connection master +RESET MASTER; +--eval SET @@global.gtid_binlog_state = '$resume_gtid' + +# The resume gtid is set up to point to the very first binlog file +--connection slave +CHANGE MASTER TO MASTER_USE_GTID = slave_pos; +--eval SET @@global.gtid_slave_pos = '$resume_gtid' +# Yet the slave io first submits the last received binlog file name:pos. +--source include/start_slave.inc + +# Here goes the cracker. +--connection master +INSERT INTO t1 SET a = 4; + +# +# Clean up +# +--connection master +DROP TABLE t1; +--eval SET @@GLOBAL. rpl_semi_sync_master_enabled = $sav_enabled_master + +--sync_slave_with_master +source include/stop_slave.inc; +--eval SET @@GLOBAL. rpl_semi_sync_slave_enabled = $sav_enabled_slave +source include/start_slave.inc; +--source include/rpl_end.inc diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index b145cd08d67..c9e1bea1f12 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1240,6 +1240,38 @@ set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; drop table t1,t2,t3; + +--echo # +--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +--echo # + + + +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; + +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; + +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; + +let $query= SELECT * FROM t1 + WHERE + EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id + WHERE A.a=t1.a AND t2.b < 20); + +eval $query; +eval explain $query; + +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; + +eval explain $query; + +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; + --echo # End of 10.1 tests # diff --git a/scripts/wsrep_sst_mariabackup.sh b/scripts/wsrep_sst_mariabackup.sh index 9dff0f1aa9c..55111b00ff9 100644 --- a/scripts/wsrep_sst_mariabackup.sh +++ b/scripts/wsrep_sst_mariabackup.sh @@ -83,7 +83,14 @@ fi pcmd="pv $pvopts" declare -a RC +set +e INNOBACKUPEX_BIN=$(which mariabackup) +if test -z $INNOBACKUPEX_BIN +then + wsrep_log_error 'mariabackup binary not found in $PATH' + exit 42 +fi +set -e XBSTREAM_BIN=mbstream XBCRYPT_BIN=xbcrypt # Not available in MariaBackup diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 8a6bea34fb4..294dcc61713 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -2742,7 +2742,11 @@ void mysql_binlog_send(THD* thd, char* log_ident, my_off_t pos, run hook first when all check has been made that slave seems to be requesting a reasonable position. i.e when transmit actually starts */ - if (RUN_HOOK(binlog_transmit, transmit_start, (thd, flags, log_ident, pos))) + + DBUG_ASSERT(pos == linfo.pos); + + if (RUN_HOOK(binlog_transmit, transmit_start, + (thd, flags, linfo.log_file_name, linfo.pos))) { info->errmsg= "Failed to run hook 'transmit_start'"; info->error= ER_UNKNOWN_ERROR; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c450572c9bd..b8f23cd135b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8003,7 +8003,6 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, something went wrong. */ sel /= (double)table->quick_rows[key] / (double) table->stat_records(); - DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); used_range_selectivity= true; } @@ -8052,7 +8051,6 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (table->field[fldno]->cond_selectivity > 0) { sel /= table->field[fldno]->cond_selectivity; - DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); } /* @@ -8110,7 +8108,6 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (field->cond_selectivity > 0) { sel/= field->cond_selectivity; - DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); } break; @@ -8122,7 +8119,6 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables, keyparts, ref_keyuse_steps); - DBUG_ASSERT(0.0 < sel && sel <= 1.0); return sel; } diff --git a/sql/table.cc b/sql/table.cc index 57abf6c4c7b..bb6c35fecd2 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4571,6 +4571,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL; + quick_condition_rows=0; + initialize_quick_structures(); #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; @@ -8556,3 +8558,22 @@ bool fk_modifies_child(enum_fk_option opt) static bool can_write[]= { false, false, true, true, false, true }; return can_write[opt]; } + + +/* + @brief + Initialize all the quick structures that are used to stored the + estimates when the range optimizer is run. + @details + This is specifically needed when we read the TABLE structure from the + table cache. There can be some garbage data from previous queries + that need to be reset here. +*/ + +void TABLE::initialize_quick_structures() +{ + bzero(quick_rows, sizeof(quick_rows)); + bzero(quick_key_parts, sizeof(quick_key_parts)); + bzero(quick_costs, sizeof(quick_costs)); + bzero(quick_n_ranges, sizeof(quick_n_ranges)); +} diff --git a/sql/table.h b/sql/table.h index f10e00562dc..e0541ef3f2e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1441,6 +1441,7 @@ public: bool is_filled_at_execution(); bool update_const_key_parts(COND *conds); + void initialize_quick_structures(); my_ptrdiff_t default_values_offset() const { return (my_ptrdiff_t) (s->default_values - record[0]); } |