- commits - lists.mariadb.org

[Commits] 6c9919baaf1: Extend the join prefix to ensure all tables in the duplicate range either are inside or outside the sort nest
by Varun 16 Dec '19

16 Dec '19

revision-id: 6c9919baaf1e45830cd10f7c4d139c0746c2fc30 (mariadb-10.4.4-377-g6c9919baaf1) parent(s): 4512fe80b9b0cdc6d540f85b3d37934b0edd4bba author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-17 03:09:48 +0530 message: Extend the join prefix to ensure all tables in the duplicate range either are inside or outside the sort nest --- mysql-test/main/sort_nest.result | 73 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/sort_nest.test | 46 +++++++++++++++++++++++++ sql/opt_subselect.cc | 13 +++++++ sql/sql_select.cc | 9 ++--- sql/sql_select.h | 14 ++++++-- sql/sql_sort_nest.cc | 44 ++++++++++++++++++++++-- 6 files changed, 188 insertions(+), 11 deletions(-) diff --git a/mysql-test/main/sort_nest.result b/mysql-test/main/sort_nest.result index 08e5b557a2f..a9de5b8b156 100644 --- a/mysql-test/main/sort_nest.result +++ b/mysql-test/main/sort_nest.result @@ -2919,3 +2919,76 @@ a b a b 1 1 1 1 0 0 0 0 DROP TABLE t0,t1,t2,t3; +# +# Ensure all tables in Duplicate weedout range are either inside +# or outside the sort nest +# +CREATE TABLE t0(a int); +CREATE TABLE t1 (a int, b int, c int, key(b)); +CREATE TABLE t2 (a int, b int, c int); +CREATE TABLE t3 (a int, b int, c int, key(a)); +CREATE TABLE t4 (a int, b int, c int, key(a)); +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t2 SELECT a,a,a FROM t0; +INSERT INTO t2 (a,b,c) values (9,9,9); +INSERT INTO t3 SELECT a,a,a FROM t0; +INSERT INTO t4 SELECT a,a,a FROM t0; +ANALYZE TABLE t0 PERSISTENT FOR ALL; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +set optimizer_switch='cost_based_order_by_limit=on'; +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.c < 10 AND t4.a=t2.b) +ORDER BY t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; Using temporary; Using filesort +1 PRIMARY t1 ref b b 5 test.t3.b 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 ref a a 5 test.t2.b 1 Using index; End temporary +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.c < 10 AND t4.a=t2.b) +ORDER BY t1.b DESC +LIMIT 5; +a a b b +9 9 9 9 +9 9 9 9 +8 8 8 8 +7 7 7 7 +6 6 6 6 +set optimizer_switch='cost_based_order_by_limit=off'; +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.c < 10 AND t4.a=t2.b) +ORDER BY t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; Using temporary; Using filesort +1 PRIMARY t1 ref b b 5 test.t3.b 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 ref a a 5 test.t2.b 1 Using index; End temporary +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.c < 10 AND t4.a=t2.b) +ORDER BY t1.b DESC +LIMIT 5; +a a b b +9 9 9 9 +9 9 9 9 +8 8 8 8 +7 7 7 7 +6 6 6 6 +drop table t0,t1,t2,t3,t4; diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test index ef50167d412..952d0bc8c5a 100644 --- a/mysql-test/main/sort_nest.test +++ b/mysql-test/main/sort_nest.test @@ -1177,3 +1177,49 @@ eval EXPLAIN FORMAT=JSON $query; eval $query; DROP TABLE t0,t1,t2,t3; + +--echo # +--echo # Ensure all tables in Duplicate weedout range are either inside +--echo # or outside the sort nest +--echo # + +CREATE TABLE t0(a int); +CREATE TABLE t1 (a int, b int, c int, key(b)); +CREATE TABLE t2 (a int, b int, c int); +CREATE TABLE t3 (a int, b int, c int, key(a)); +CREATE TABLE t4 (a int, b int, c int, key(a)); + +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t2 SELECT a,a,a FROM t0; +INSERT INTO t2 (a,b,c) values (9,9,9); +INSERT INTO t3 SELECT a,a,a FROM t0; +INSERT INTO t4 SELECT a,a,a FROM t0; + +--disable_result_log +ANALYZE TABLE t0 PERSISTENT FOR ALL; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +--enable_result_log + +let $query= SELECT t1.a, t2.a, t1.b,t2.b + FROM t1, t2 + WHERE t1.a=t2.a AND + t1.b IN (SELECT t3.b FROM t3,t4 + WHERE t3.c < 10 AND t4.a=t2.b) + ORDER BY t1.b DESC + LIMIT 5; + + +set optimizer_switch='cost_based_order_by_limit=on'; +eval EXPLAIN $query; +eval $query; + +set optimizer_switch='cost_based_order_by_limit=off'; +eval EXPLAIN $query; +eval $query; + +drop table t0,t1,t2,t3,t4; + diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 822240dcfac..d7e2760394d 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3513,6 +3513,19 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, } +bool +Duplicate_weedout_picker::sort_nest_allowed_for_sj(table_map prefix_tables) +{ + /* + The check here is to ensure that either all the tables involve in + the duplicate weedout strategy are COMPLETELY outside the join prefix + or are COMPLETELY inside the join prefix + */ + if (!dupsweedout_tables || !(~prefix_tables & dupsweedout_tables)) + return TRUE; + return FALSE; +} + /* Remove the last join tab from from join->cur_sj_inner_tables bitmap we assume remaining_tables doesnt contain @tab. diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b87dc39fc06..2e120067229 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9611,7 +9611,7 @@ best_extension_by_limited_search(JOIN *join, } /* - Join buffering should be not considered in 2 cases + Join buffering should not be considered in 2 cases 1) if join_cache_level = 0 that is join buffering is disabled 2) if the limit was pushed to a prefix of the join that can resolve the ORDER BY clause @@ -9761,15 +9761,10 @@ best_extension_by_limited_search(JOIN *join, s->check_if_index_satisfies_ordering(index_used)) limit_applied_to_nest= TRUE; - /* - TODO varun: - 1) get an optimizer switch to enable or disable the sort - nest instead of a system variable - */ if (!nest_created && (join->prefix_resolves_ordering || join->consider_adding_sort_nest(sort_nest_tables | - real_table_bit))) + real_table_bit, idx))) { // SORT_NEST branch join->positions[idx].sort_nest_operation_here= TRUE; diff --git a/sql/sql_select.h b/sql/sql_select.h index ec86ae346ed..686cf237a40 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -739,6 +739,12 @@ class Semi_join_strategy_picker virtual void mark_used() = 0; + /* + Check if a sort nest can be added to a prefix of the join(which may + include the inner tables of a semi join). + */ + virtual bool sort_nest_allowed_for_sj(table_map prefix_tables) = 0; + virtual ~Semi_join_strategy_picker() {} }; @@ -779,6 +785,7 @@ class Duplicate_weedout_picker : public Semi_join_strategy_picker struct st_position *loose_scan_pos); void mark_used() { is_used= TRUE; } + bool sort_nest_allowed_for_sj(table_map remaining_tables); friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); }; @@ -824,6 +831,7 @@ class Firstmatch_picker : public Semi_join_strategy_picker struct st_position *loose_scan_pos); void mark_used() { is_used= TRUE; } + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return TRUE; } friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); }; @@ -866,7 +874,7 @@ class LooseScan_picker : public Semi_join_strategy_picker sj_strategy_enum *strategy, struct st_position *loose_scan_pos); void mark_used() { is_used= TRUE; } - + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return TRUE; } friend class Loose_scan_opt; friend void best_access_path(JOIN *join, JOIN_TAB *s, @@ -916,6 +924,7 @@ class Sj_materialization_picker : public Semi_join_strategy_picker sj_strategy_enum *strategy, struct st_position *loose_scan_pos); void mark_used() { is_used= TRUE; } + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return TRUE; } friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); }; @@ -1956,7 +1965,8 @@ class JOIN :public Sql_alloc void setup_range_scan(JOIN_TAB *tab, uint idx, double records); bool is_join_buffering_allowed(JOIN_TAB *tab); bool check_join_prefix_resolves_ordering(table_map previous_tables); - bool consider_adding_sort_nest(table_map previous_tables); + bool consider_adding_sort_nest(table_map previous_tables, uint idx); + bool is_duplicate_removal_ensured(table_map prefix_tables, uint idx); void set_fraction_output_for_nest(); double sort_nest_oper_cost(double join_record_count, uint idx, ulong rec_len); diff --git a/sql/sql_sort_nest.cc b/sql/sql_sort_nest.cc index 18d2a596587..847d047126c 100644 --- a/sql/sql_sort_nest.cc +++ b/sql/sql_sort_nest.cc @@ -1446,18 +1446,58 @@ bool JOIN::sort_nest_allowed() FALSE otherwise */ -bool JOIN::consider_adding_sort_nest(table_map prefix_tables) +bool JOIN::consider_adding_sort_nest(table_map prefix_tables, uint idx) { if (!sort_nest_possible || // (1) get_cardinality_estimate || // (2) cur_embedding_map || // (3) - cur_sj_inner_tables) // (4) + cur_sj_inner_tables || // (4) + !is_duplicate_removal_ensured(prefix_tables, idx)) return FALSE; return check_join_prefix_resolves_ordering(prefix_tables); // (5) } +/* + @brief + Check if the join prefix ensures duplicate removal for semi joins + + @details + This function checks for all the semi join strategies, if the join + prefix can ensure duplicate removal if a sort nest is considered + on the join prefix. + + @retval + TRUE Duplicate removal is ensured + FALSE Otherwise +*/ + +bool +JOIN::is_duplicate_removal_ensured(table_map prefix_tables, uint idx) +{ + if (!select_lex->have_merged_subqueries) + return TRUE; + + POSITION *pos= positions + idx; + Semi_join_strategy_picker *pickers[]= + { + &pos->firstmatch_picker, + &pos->loosescan_picker, + &pos->sjmat_picker, + &pos->dups_weedout_picker, + NULL, + }; + Semi_join_strategy_picker **strategy; + for (strategy= pickers; *strategy != NULL; strategy++) + { + if (!(*strategy)->sort_nest_allowed_for_sj(prefix_tables)) + return FALSE; + } + return TRUE; +} + + /* @brief Check if indexes on a table are allowed to resolve the ORDER BY clause

1 0

[Commits] f9cc0d345ae: Range Locking: acquire correct range locks at start/end of index
by psergey 16 Dec '19

16 Dec '19

revision-id: f9cc0d345aeccabcb84ac98ce5d1130b637e024d (fb-prod201903-272-gf9cc0d345ae) parent(s): fbbc63bd33d1bd1ff75bec0f7abf093e2e2de070 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-16 19:06:39 +0300 message: Range Locking: acquire correct range locks at start/end of index Make ha_rocksdb::index_{first,last}_intern() acquire appropriate locks. --- mysql-test/suite/rocksdb/r/range_locking.result | 48 +++++++++++++++++++++- .../suite/rocksdb/r/range_locking_rev_cf.result | 48 +++++++++++++++++++++- mysql-test/suite/rocksdb/t/range_locking.inc | 44 +++++++++++++++++++- storage/rocksdb/ha_rocksdb.cc | 16 ++++++-- 4 files changed, 148 insertions(+), 8 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/range_locking.result b/mysql-test/suite/rocksdb/r/range_locking.result index 5a58ddf148e..85264fbf2fa 100644 --- a/mysql-test/suite/rocksdb/r/range_locking.result +++ b/mysql-test/suite/rocksdb/r/range_locking.result @@ -468,6 +468,52 @@ $cf_id $trx_id 00${indexnr}8000000480000005 - 01${indexnr}8000000480000008 X rollback; connection con1; rollback; -disconnect con1; connection default; drop table t0, t1; +# +# A bug: range locking was not used when scan started at table start or end +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int not null, +a int, +primary key(pk) +) engine=rocksdb; +insert into t1 select a*2,a*2 from t10; +connection con1; +begin; +select * from t1 where pk=500 for update; +pk a +500 500 +connection default; +begin; +select * from t1 where pk<10 order by pk limit 10 for update; +pk a +0 0 +2 2 +4 4 +6 6 +8 8 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr} - 00${indexnr}8000000a X +rollback; +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +pk a +1998 1998 +1996 1996 +1994 1994 +1992 1992 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800007c6 - 01${indexnr+1} X +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t10,t1; diff --git a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result index 811ad88e91b..87522d5ae37 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result +++ b/mysql-test/suite/rocksdb/r/range_locking_rev_cf.result @@ -428,6 +428,52 @@ $cf_id $trx_id 00${indexnr}8000000480000008 - 01${indexnr}8000000480000005 X rollback; connection con1; rollback; -disconnect con1; connection default; drop table t0, t1; +# +# A bug: range locking was not used when scan started at table start or end +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int not null, +a int, +primary key(pk) +) engine=rocksdb; +insert into t1 select a*2,a*2 from t10; +connection con1; +begin; +select * from t1 where pk=500 for update; +pk a +500 500 +connection default; +begin; +select * from t1 where pk<10 order by pk limit 10 for update; +pk a +0 0 +2 2 +4 4 +6 6 +8 8 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr} - 00${indexnr}8000000a X +rollback; +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +pk a +1998 1998 +1996 1996 +1994 1994 +1992 1992 +# select * from information_schema.rocksdb_locks; # With replacements by select_from_is_rowlocks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $trx_id 00${indexnr}800007c6 - 01${indexnr+1} X +rollback; +connection con1; +rollback; +disconnect con1; +connection default; +drop table t0,t10,t1; diff --git a/mysql-test/suite/rocksdb/t/range_locking.inc b/mysql-test/suite/rocksdb/t/range_locking.inc index ecc8e4432bb..fd0f3f4e6c6 100644 --- a/mysql-test/suite/rocksdb/t/range_locking.inc +++ b/mysql-test/suite/rocksdb/t/range_locking.inc @@ -498,7 +498,47 @@ rollback; connection con1; rollback; -disconnect con1; -connection default; +connection default; drop table t0, t1; + +--echo # +--echo # A bug: range locking was not used when scan started at table start or end +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t1 ( + pk int not null, + a int, + primary key(pk) +) engine=rocksdb; + +insert into t1 select a*2,a*2 from t10; + +connection con1; +begin; +select * from t1 where pk=500 for update; +connection default; + +begin; +select * from t1 where pk<10 order by pk limit 10 for update; + +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +begin; +select * from t1 where pk>1990 order by pk desc limit 10 for update; +let $select_from_is_rowlocks_current_trx_only=1; +--source suite/rocksdb/include/select_from_is_rowlocks.inc +rollback; + +connection con1; +rollback; +disconnect con1; + +connection default; +drop table t0,t10,t1; diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index cca9122f9d0..f77da1bc8da 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -9430,13 +9430,17 @@ int ha_rocksdb::index_first_intern(uchar *const buf) { Rdb_transaction *const tx = get_or_create_tx(table->in_use); DBUG_ASSERT(tx != nullptr); + bool use_locking_iter; + if ((rc = set_range_lock(tx, kd, HA_READ_KEY_OR_NEXT, index_key, + end_range, &use_locking_iter))) + DBUG_RETURN(rc); + const bool is_new_snapshot = !tx->has_snapshot(); // Loop as long as we get a deadlock error AND we end up creating the // snapshot here (i.e. it did not exist prior to this) for (;;) { setup_scan_iterator(kd, &index_key, false, key_start_matching_bytes, - (rocksdb_use_range_locking && - m_lock_rows != RDB_LOCK_NONE && !end_range)); + use_locking_iter); m_scan_it->Seek(index_key); m_skip_scan_it_next_call = true; @@ -9522,13 +9526,17 @@ int ha_rocksdb::index_last_intern(uchar *const buf) { Rdb_transaction *const tx = get_or_create_tx(table->in_use); DBUG_ASSERT(tx != nullptr); + bool use_locking_iter; + if ((rc = set_range_lock(tx, kd, HA_READ_PREFIX_LAST_OR_PREV, index_key, + end_range, &use_locking_iter))) + DBUG_RETURN(rc); + bool is_new_snapshot = !tx->has_snapshot(); // Loop as long as we get a deadlock error AND we end up creating the // snapshot here (i.e. it did not exist prior to this) for (;;) { setup_scan_iterator(kd, &index_key, false, key_end_matching_bytes, - (rocksdb_use_range_locking && - m_lock_rows != RDB_LOCK_NONE && !end_range)); + use_locking_iter); m_scan_it->SeekForPrev(index_key); m_skip_scan_it_next_call = false;

1 0

[Commits] fbbc63bd33d: Range Locking, SeekForUpdate: use LockingIter with locking full table scan
by psergey 15 Dec '19

15 Dec '19

revision-id: fbbc63bd33d1bd1ff75bec0f7abf093e2e2de070 (fb-prod201903-271-gfbbc63bd33d) parent(s): 1f738064ee9d3e3d04491ab01b6a6afcd6085136 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-15 23:39:36 +0300 message: Range Locking, SeekForUpdate: use LockingIter with locking full table scan - Make ha_rocksdb::setup_iterator_for_rnd_scan() use LockingIterator (that is, use SeekForUpdate) when doing a locking full table scan. - Adjust testcases that are affected by the change - Correctly handle transaction isolation errors (kLockTimeout, kDeadlock, etc). (Without SeekForUpdate, it was not possible to get such errors from an iterator. Now it's possible, and before this commit, we didn't handle e.g. kDeadlock) values ). --- .../rocksdb/r/range_locking_seek_for_update.result | 32 ++++++++++++++++++++-- mysql-test/suite/rocksdb/r/unique_sec.result | 4 +++ .../suite/rocksdb/r/unique_sec_rev_cf.result | 4 +++ .../rocksdb/t/range_locking_seek_for_update.test | 32 ++++++++++++++++++++++ mysql-test/suite/rocksdb/t/unique_sec.inc | 10 ++++++- storage/rocksdb/ha_rocksdb.cc | 25 ++++++++++------- storage/rocksdb/ha_rocksdb.h | 4 ++- 7 files changed, 96 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result index 2614e8ce66e..cddf039bc4f 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result +++ b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result @@ -173,7 +173,7 @@ begin; delete from t1 where pk=7; set debug_sync='now SIGNAL spoiler_inserted'; connection default; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; @@ -225,7 +225,7 @@ pk a connection default; begin; select * from t1 order by pk desc limit 2 for update; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; @@ -236,10 +236,36 @@ pk a connection default; begin; select * from t1 order by pk desc limit 2 for update; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; disconnect con1; connection default; drop table t0,t1; +# +# A test: full table scan doesn't lock gaps +# +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 values (10,10),(20,20),(30,30); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +begin; +select * from t1 for update; +pk a +10 10 +20 20 +30 30 +connection con2; +insert into t1 values (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +connection con1; +rollback; +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/r/unique_sec.result b/mysql-test/suite/rocksdb/r/unique_sec.result index 64db56ca78e..a9550164e30 100644 --- a/mysql-test/suite/rocksdb/r/unique_sec.result +++ b/mysql-test/suite/rocksdb/r/unique_sec.result @@ -115,6 +115,10 @@ ERROR 23000: Duplicate entry '37' for key 'id5' UPDATE t1 SET id5=34 WHERE id1=38; ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.id5 # NULL values are unique +# (Note: the following UPDATE reads through the whole table without +# finding anything to update. With point locking, this is fine, +# but with range locking it will time out while waiting on a row lock +# that the other transaction is holding) UPDATE t1 SET id5=NULL WHERE value1 > 37; COMMIT; COMMIT; diff --git a/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result b/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result index 0ff55ac8d10..e3f592f6284 100644 --- a/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result +++ b/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result @@ -115,6 +115,10 @@ ERROR 23000: Duplicate entry '37' for key 'id5' UPDATE t1 SET id5=34 WHERE id1=38; ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.id5 # NULL values are unique +# (Note: the following UPDATE reads through the whole table without +# finding anything to update. With point locking, this is fine, +# but with range locking it will time out while waiting on a row lock +# that the other transaction is holding) UPDATE t1 SET id5=NULL WHERE value1 > 37; COMMIT; COMMIT; diff --git a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test index 0230113eeb1..32590af1799 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test +++ b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test @@ -254,3 +254,35 @@ rollback; disconnect con1; connection default; drop table t0,t1; + +--echo # +--echo # A test: full table scan doesn't lock gaps +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 values (10,10),(20,20),(30,30); + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +begin; + +select * from t1 for update; + +connection con2; + +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values (5,5); + +connection con1; +rollback; + +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/t/unique_sec.inc b/mysql-test/suite/rocksdb/t/unique_sec.inc index 2f11cd3b65a..a78a1e1218d 100644 --- a/mysql-test/suite/rocksdb/t/unique_sec.inc +++ b/mysql-test/suite/rocksdb/t/unique_sec.inc @@ -148,8 +148,16 @@ UPDATE t1 SET id5=37 WHERE id1=38; UPDATE t1 SET id5=34 WHERE id1=38; --echo # NULL values are unique +--echo # (Note: the following UPDATE reads through the whole table without +--echo # finding anything to update. With point locking, this is fine, +--echo # but with range locking it will time out while waiting on a row lock +--echo # that the other transaction is holding) +if (`select @@rocksdb_use_range_locking=0`) { UPDATE t1 SET id5=NULL WHERE value1 > 37; - +} +if (`select @@rocksdb_use_range_locking=1`) { +-- echo UPDATE t1 SET id5=NULL WHERE value1 > 37; +} connection con1; COMMIT; diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 2e161dbfb4b..cca9122f9d0 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -8012,14 +8012,18 @@ int ha_rocksdb::read_before_key(const Rdb_key_def &kd, } -inline int iter_status_to_retval(rocksdb::Iterator *it, int not_found_code) { +int ha_rocksdb::iter_status_to_retval(rocksdb::Iterator *it, + const Rdb_key_def &kd, + int not_found_code) { if (it->Valid()) return HA_EXIT_SUCCESS; + rocksdb::Status s= it->status(); - if (s.IsTimedOut()) - return HA_ERR_LOCK_WAIT_TIMEOUT; - //TODO: should we handle other kinds of errors? - return not_found_code; + if (s.ok() || s.IsNotFound()) + return not_found_code; + + Rdb_transaction *&tx = get_tx_from_thd(table->in_use); + return tx->set_status_error(table->in_use, s, kd, m_tbl_def, m_table_handler); } int ha_rocksdb::read_after_key(const Rdb_key_def &kd, @@ -8051,7 +8055,7 @@ int ha_rocksdb::read_after_key(const Rdb_key_def &kd, return is_valid_iterator(m_scan_it) ? HA_EXIT_SUCCESS : - iter_status_to_retval(m_scan_it, HA_ERR_KEY_NOT_FOUND); + iter_status_to_retval(m_scan_it, kd, HA_ERR_KEY_NOT_FOUND); } int ha_rocksdb::position_to_correct_key( @@ -10774,7 +10778,8 @@ void ha_rocksdb::setup_iterator_for_rnd_scan() { rocksdb::Slice table_key((const char *)m_pk_packed_tuple, key_size); - setup_scan_iterator(*m_pk_descr, &table_key, false, key_start_matching_bytes); + setup_scan_iterator(*m_pk_descr, &table_key, false, key_start_matching_bytes, + (m_lock_rows != RDB_LOCK_NONE) && rocksdb_use_range_locking); m_scan_it->Seek(table_key); m_skip_scan_it_next_call = true; } @@ -10864,7 +10869,7 @@ int ha_rocksdb::rnd_next_with_direction(uchar *const buf, bool move_forward) { In this case, we should return EOF. */ rc = HA_ERR_END_OF_FILE; - DBUG_RETURN(m_scan_it ? iter_status_to_retval(m_scan_it, rc) : rc); + DBUG_RETURN(m_scan_it ? iter_status_to_retval(m_scan_it, *m_pk_descr, rc) : rc); } for (;;) { @@ -10885,7 +10890,7 @@ int ha_rocksdb::rnd_next_with_direction(uchar *const buf, bool move_forward) { } if (!is_valid_iterator(m_scan_it)) { - rc = iter_status_to_retval(m_scan_it, HA_ERR_END_OF_FILE); + rc = iter_status_to_retval(m_scan_it, *m_pk_descr, HA_ERR_END_OF_FILE); break; } @@ -14051,7 +14056,7 @@ static void show_rocksdb_stall_vars(THD *thd, SHOW_VAR *var, char *buff) { } // -// psergey: lock tree escalation count status variable. +// Lock Tree Status variables // static longlong rocksdb_locktree_escalation_count=1234; static longlong rocksdb_locktree_current_lock_memory=0; diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h index a285e74e728..272dc9973d4 100644 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@ -321,7 +321,7 @@ class ha_rocksdb : public my_core::handler { rocksdb::Slice *upper_bound_slice); void setup_scan_iterator(const Rdb_key_def &kd, rocksdb::Slice *slice, const bool use_all_keys, const uint eq_cond_len, - bool use_locking_iterator=false) + bool use_locking_iterator) MY_ATTRIBUTE((__nonnull__)); int set_range_lock(Rdb_transaction *tx, @@ -1031,6 +1031,8 @@ class ha_rocksdb : public my_core::handler { bool m_in_rpl_update_rows; bool m_force_skip_unique_check; + + int iter_status_to_retval(rocksdb::Iterator *it, const Rdb_key_def &kd, int not_found_code); }; /*

1 0

[Commits] d4579809cc0: Range Locking, SeekForUpdate: use LockingIter with locking full table scan
by psergey 15 Dec '19

15 Dec '19

revision-id: d4579809cc08bb9de785f60ab4a9b6f8fdeb2a99 (fb-prod201903-271-gd4579809cc0) parent(s): 1f738064ee9d3e3d04491ab01b6a6afcd6085136 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-15 23:38:32 +0300 message: Range Locking, SeekForUpdate: use LockingIter with locking full table scan - Make ha_rocksdb::setup_iterator_for_rnd_scan() use LockingIterator (that is, use SeekForUpdate) when doing a locking full table scan. - Adjust testcases that are affected by the change - Correctly handle transaction isolation errors (kLockTimeout, kDeadlock, etc). (Without SeekForUpdate, it was not possible to get such errors from an iterator. Now it's possible). --- .../rocksdb/r/range_locking_seek_for_update.result | 32 ++++++++++++++++++++-- mysql-test/suite/rocksdb/r/unique_sec.result | 4 +++ .../suite/rocksdb/r/unique_sec_rev_cf.result | 4 +++ .../rocksdb/t/range_locking_seek_for_update.test | 32 ++++++++++++++++++++++ mysql-test/suite/rocksdb/t/unique_sec.inc | 10 ++++++- storage/rocksdb/ha_rocksdb.cc | 25 ++++++++++------- storage/rocksdb/ha_rocksdb.h | 4 ++- 7 files changed, 96 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result index 2614e8ce66e..cddf039bc4f 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result +++ b/mysql-test/suite/rocksdb/r/range_locking_seek_for_update.result @@ -173,7 +173,7 @@ begin; delete from t1 where pk=7; set debug_sync='now SIGNAL spoiler_inserted'; connection default; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; @@ -225,7 +225,7 @@ pk a connection default; begin; select * from t1 order by pk desc limit 2 for update; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; @@ -236,10 +236,36 @@ pk a connection default; begin; select * from t1 order by pk desc limit 2 for update; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY rollback; connection con1; rollback; disconnect con1; connection default; drop table t0,t1; +# +# A test: full table scan doesn't lock gaps +# +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 values (10,10),(20,20),(30,30); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +begin; +select * from t1 for update; +pk a +10 10 +20 20 +30 30 +connection con2; +insert into t1 values (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +connection con1; +rollback; +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/r/unique_sec.result b/mysql-test/suite/rocksdb/r/unique_sec.result index 64db56ca78e..a9550164e30 100644 --- a/mysql-test/suite/rocksdb/r/unique_sec.result +++ b/mysql-test/suite/rocksdb/r/unique_sec.result @@ -115,6 +115,10 @@ ERROR 23000: Duplicate entry '37' for key 'id5' UPDATE t1 SET id5=34 WHERE id1=38; ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.id5 # NULL values are unique +# (Note: the following UPDATE reads through the whole table without +# finding anything to update. With point locking, this is fine, +# but with range locking it will time out while waiting on a row lock +# that the other transaction is holding) UPDATE t1 SET id5=NULL WHERE value1 > 37; COMMIT; COMMIT; diff --git a/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result b/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result index 0ff55ac8d10..e3f592f6284 100644 --- a/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result +++ b/mysql-test/suite/rocksdb/r/unique_sec_rev_cf.result @@ -115,6 +115,10 @@ ERROR 23000: Duplicate entry '37' for key 'id5' UPDATE t1 SET id5=34 WHERE id1=38; ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.id5 # NULL values are unique +# (Note: the following UPDATE reads through the whole table without +# finding anything to update. With point locking, this is fine, +# but with range locking it will time out while waiting on a row lock +# that the other transaction is holding) UPDATE t1 SET id5=NULL WHERE value1 > 37; COMMIT; COMMIT; diff --git a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test index 0230113eeb1..32590af1799 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test +++ b/mysql-test/suite/rocksdb/t/range_locking_seek_for_update.test @@ -254,3 +254,35 @@ rollback; disconnect con1; connection default; drop table t0,t1; + +--echo # +--echo # A test: full table scan doesn't lock gaps +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 values (10,10),(20,20),(30,30); + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +begin; + +select * from t1 for update; + +connection con2; + +--error ER_LOCK_WAIT_TIMEOUT +insert into t1 values (5,5); + +connection con1; +rollback; + +disconnect con1; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/rocksdb/t/unique_sec.inc b/mysql-test/suite/rocksdb/t/unique_sec.inc index 2f11cd3b65a..a78a1e1218d 100644 --- a/mysql-test/suite/rocksdb/t/unique_sec.inc +++ b/mysql-test/suite/rocksdb/t/unique_sec.inc @@ -148,8 +148,16 @@ UPDATE t1 SET id5=37 WHERE id1=38; UPDATE t1 SET id5=34 WHERE id1=38; --echo # NULL values are unique +--echo # (Note: the following UPDATE reads through the whole table without +--echo # finding anything to update. With point locking, this is fine, +--echo # but with range locking it will time out while waiting on a row lock +--echo # that the other transaction is holding) +if (`select @@rocksdb_use_range_locking=0`) { UPDATE t1 SET id5=NULL WHERE value1 > 37; - +} +if (`select @@rocksdb_use_range_locking=1`) { +-- echo UPDATE t1 SET id5=NULL WHERE value1 > 37; +} connection con1; COMMIT; diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 2e161dbfb4b..cca9122f9d0 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -8012,14 +8012,18 @@ int ha_rocksdb::read_before_key(const Rdb_key_def &kd, } -inline int iter_status_to_retval(rocksdb::Iterator *it, int not_found_code) { +int ha_rocksdb::iter_status_to_retval(rocksdb::Iterator *it, + const Rdb_key_def &kd, + int not_found_code) { if (it->Valid()) return HA_EXIT_SUCCESS; + rocksdb::Status s= it->status(); - if (s.IsTimedOut()) - return HA_ERR_LOCK_WAIT_TIMEOUT; - //TODO: should we handle other kinds of errors? - return not_found_code; + if (s.ok() || s.IsNotFound()) + return not_found_code; + + Rdb_transaction *&tx = get_tx_from_thd(table->in_use); + return tx->set_status_error(table->in_use, s, kd, m_tbl_def, m_table_handler); } int ha_rocksdb::read_after_key(const Rdb_key_def &kd, @@ -8051,7 +8055,7 @@ int ha_rocksdb::read_after_key(const Rdb_key_def &kd, return is_valid_iterator(m_scan_it) ? HA_EXIT_SUCCESS : - iter_status_to_retval(m_scan_it, HA_ERR_KEY_NOT_FOUND); + iter_status_to_retval(m_scan_it, kd, HA_ERR_KEY_NOT_FOUND); } int ha_rocksdb::position_to_correct_key( @@ -10774,7 +10778,8 @@ void ha_rocksdb::setup_iterator_for_rnd_scan() { rocksdb::Slice table_key((const char *)m_pk_packed_tuple, key_size); - setup_scan_iterator(*m_pk_descr, &table_key, false, key_start_matching_bytes); + setup_scan_iterator(*m_pk_descr, &table_key, false, key_start_matching_bytes, + (m_lock_rows != RDB_LOCK_NONE) && rocksdb_use_range_locking); m_scan_it->Seek(table_key); m_skip_scan_it_next_call = true; } @@ -10864,7 +10869,7 @@ int ha_rocksdb::rnd_next_with_direction(uchar *const buf, bool move_forward) { In this case, we should return EOF. */ rc = HA_ERR_END_OF_FILE; - DBUG_RETURN(m_scan_it ? iter_status_to_retval(m_scan_it, rc) : rc); + DBUG_RETURN(m_scan_it ? iter_status_to_retval(m_scan_it, *m_pk_descr, rc) : rc); } for (;;) { @@ -10885,7 +10890,7 @@ int ha_rocksdb::rnd_next_with_direction(uchar *const buf, bool move_forward) { } if (!is_valid_iterator(m_scan_it)) { - rc = iter_status_to_retval(m_scan_it, HA_ERR_END_OF_FILE); + rc = iter_status_to_retval(m_scan_it, *m_pk_descr, HA_ERR_END_OF_FILE); break; } @@ -14051,7 +14056,7 @@ static void show_rocksdb_stall_vars(THD *thd, SHOW_VAR *var, char *buff) { } // -// psergey: lock tree escalation count status variable. +// Lock Tree Status variables // static longlong rocksdb_locktree_escalation_count=1234; static longlong rocksdb_locktree_current_lock_memory=0; diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h index a285e74e728..272dc9973d4 100644 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@ -321,7 +321,7 @@ class ha_rocksdb : public my_core::handler { rocksdb::Slice *upper_bound_slice); void setup_scan_iterator(const Rdb_key_def &kd, rocksdb::Slice *slice, const bool use_all_keys, const uint eq_cond_len, - bool use_locking_iterator=false) + bool use_locking_iterator) MY_ATTRIBUTE((__nonnull__)); int set_range_lock(Rdb_transaction *tx, @@ -1031,6 +1031,8 @@ class ha_rocksdb : public my_core::handler { bool m_in_rpl_update_rows; bool m_force_skip_unique_check; + + int iter_status_to_retval(rocksdb::Iterator *it, const Rdb_key_def &kd, int not_found_code); }; /*

1 0

[Commits] c74fdfb: Option to use per-index-cfs, misc fixes
by Sergei Petrunia 14 Dec '19

14 Dec '19

revision-id: c74fdfb005d696209f0def67c15a562d3dbfe803 () parent(s): 8db4a6a5e2b2a3a53b2a4e01f87753a1d7e03c3d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-14 12:59:31 +0300 message: Option to use per-index-cfs, misc fixes - Added an option to use 4 tables and per-index CFs. - Compile script fixes to use the current branch --- make-4-cfs.sql | 48 ++++++++++++++++++++++++++++++++++ run-all.sh | 2 +- run-test.sh | 73 ++++++++++++++++++++++++++++++++++++++-------------- setup-fbmysql-nov.sh | 9 +++++++ 4 files changed, 112 insertions(+), 20 deletions(-) diff --git a/make-4-cfs.sql b/make-4-cfs.sql new file mode 100644 index 0000000..6e22311 --- /dev/null +++ b/make-4-cfs.sql @@ -0,0 +1,48 @@ +use sbtest; +alter table sbtest1 rename sbtest1_orig; +alter table sbtest2 rename sbtest2_orig; +alter table sbtest3 rename sbtest3_orig; +alter table sbtest4 rename sbtest4_orig; + +CREATE TABLE sbtest1 ( + id int(11) NOT NULL AUTO_INCREMENT, + k int(11) NOT NULL DEFAULT '0', + c char(120) NOT NULL DEFAULT '', + pad char(60) NOT NULL DEFAULT '', + PRIMARY KEY (id) COMMENT 'cf1_pk', + KEY k_1 (k) COMMENT 'cf1_sk' +) ENGINE=ROCKSDB CHARSET=latin1; + +CREATE TABLE sbtest2 ( + id int(11) NOT NULL AUTO_INCREMENT, + k int(11) NOT NULL DEFAULT '0', + c char(120) NOT NULL DEFAULT '', + pad char(60) NOT NULL DEFAULT '', + PRIMARY KEY (id) COMMENT 'cf2_pk', + KEY k_1 (k) COMMENT 'cf2_sk' +) ENGINE=ROCKSDB CHARSET=latin1; + +CREATE TABLE sbtest3 ( + id int(11) NOT NULL AUTO_INCREMENT, + k int(11) NOT NULL DEFAULT '0', + c char(120) NOT NULL DEFAULT '', + pad char(60) NOT NULL DEFAULT '', + PRIMARY KEY (id) COMMENT 'cf3_pk', + KEY k_1 (k) COMMENT 'cf3_sk' +) ENGINE=ROCKSDB CHARSET=latin1; + +CREATE TABLE sbtest4 ( + id int(11) NOT NULL AUTO_INCREMENT, + k int(11) NOT NULL DEFAULT '0', + c char(120) NOT NULL DEFAULT '', + pad char(60) NOT NULL DEFAULT '', + PRIMARY KEY (id) COMMENT 'cf4_pk', + KEY k_1 (k) COMMENT 'cf4_sk' +) ENGINE=ROCKSDB CHARSET=latin1; + +set rocksdb_commit_in_the_middle=1; +insert into sbtest1 select * from sbtest1_orig; +insert into sbtest2 select * from sbtest2_orig; +insert into sbtest3 select * from sbtest3_orig; +insert into sbtest4 select * from sbtest4_orig; +set rocksdb_commit_in_the_middle=0; diff --git a/run-all.sh b/run-all.sh index 777b9e2..eb897b5 100755 --- a/run-all.sh +++ b/run-all.sh @@ -1,6 +1,6 @@ #!/bin/bash -./setup.sh +#./setup.sh echo "rocksdb_use_range_locking=1" >> my-fbmysql-range-locking.cnf diff --git a/run-test.sh b/run-test.sh index 78a0644..21411a0 100755 --- a/run-test.sh +++ b/run-test.sh @@ -3,12 +3,20 @@ ### ### Parse options ### -USAGE_STR="Usage: $0 [-p] server_name test_run_name" +USAGE_STR="Usage: $0 [-p] [-m] [-c] server_name test_run_name \ + -p - Use perf for profiling + -m - Put datadir on /dev/shm + -c - Assume sysbench uses 4 tables and move them to different CFs. +" -while getopts ":p" opt; do +while getopts ":pmc" opt; do case ${opt} in p ) USE_PERF=1 ;; + m ) USE_RAMDISK=1 + ;; + c ) USE_4_CFS=1 + ;; \? ) echo $USAGE_STR exit 1 ;; @@ -46,6 +54,10 @@ echo "Starting test "$RUN_NAME" with $SERVER_DIR" if [[ $USE_PERF ]] ; then echo " Collecting perf profile" fi + +if [[ $USE_RAMDISK ]] ; then + echo " Using /dev/shm for data dir" +fi #exit 0 ############################################################################# @@ -55,34 +67,49 @@ sleep 5 DATA_DIR=data-fbmysql-$SERVERNAME rm -rf $DATA_DIR -# cp -r ${DATA_DIR}.clean /dev/shm/$DATA_DIR +if [[ $USE_RAMDISK ]] ; then + rm -rf /dev/shm/$DATA_DIR + cp -r ${DATA_DIR}.clean /dev/shm/$DATA_DIR + ln -s /dev/shm/$DATA_DIR $DATA_DIR +else + cp -r ${DATA_DIR}.clean $DATA_DIR +fi + +#exit 0; ./$SERVER_DIR/sql/mysqld --defaults-file=./my-fbmysql-${SERVERNAME}.cnf & -attempts=0 -while true ; do - ./$SERVER_DIR/client/mysql --defaults-file=./my-fbmysql-${SERVERNAME}.cnf -uroot -e "create database sbtest" +server_attempts=0 - if [ $? -eq 0 ]; then +while true ; do + client_attempts=0 + while true ; do + ./$SERVER_DIR/client/mysql --defaults-file=./my-fbmysql-${SERVERNAME}.cnf -uroot -e "create database sbtest" + + if [ $? -eq 0 ]; then + break + fi + sleep 1 + + client_attempts=$((client_attempts + 1)) + if [ $client_attempts -ge 10 ]; then + break; + fi + done + + MYSQLD_PID=`ps -C mysqld --no-header | awk '{print $1}'` + if [[ "a${MYSQLD_PID}b" != "ab" ]] ; then break fi - sleep 1 - attempts=$((attempts + 1)) - if [ $attempts -ge 10 ]; then - echo "Failed!" + server_attempts=$((server_attempts + 1)) + if [ $server_attempts -ge 4 ]; then + echo "Failed to launch mysqld" exit 1 fi done -MYSQLD_PID=`ps -C mysqld --no-header | awk '{print $1}'` -if [[ "a${MYSQLD_PID}b" == "ab" ]] ; then - echo "Failed to locate mysqld process" - exit 1 -fi - - ############################################################################# ### Prepare the benchmark @@ -93,7 +120,7 @@ mkdir -p $RESULT_DIR SYSBENCH_ARGS=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root \ --mysql-storage-engine=rocksdb \ --time=60 \ - /usr/share/sysbench/oltp_write_only.lua --table-size=1000000" + /usr/share/sysbench/oltp_write_only.lua --table-size=250000 --tables=4" SYSBENCH_TEST="oltp_write_only.lua" cat > $RESULT_DIR/info.txt <<END @@ -104,8 +131,15 @@ SYSBENCH_ARGS=$SYSBENCH_ARGS END +sleep 3 sysbench $SYSBENCH_ARGS prepare | tee $RESULT_DIR/sysbench-prepare.txt +if [[ $USE_4_CFS ]] ; then + echo "Splitting 4 tables into different CFs" + ./$SERVER_DIR/client/mysql --defaults-file=./my-fbmysql-${SERVERNAME}.cnf \ + -uroot < make-4-cfs.sql +fi +sleep 3 ./$SERVER_DIR/client/mysql --defaults-file=./my-fbmysql-${SERVERNAME}.cnf \ -uroot -e "show variables like 'rocksdb%'" > $RESULT_DIR/variables-rocksdb.txt @@ -118,6 +152,7 @@ sysbench $SYSBENCH_ARGS prepare | tee $RESULT_DIR/sysbench-prepare.txt ./$SERVER_DIR/client/mysql --defaults-file=./my-fbmysql-${SERVERNAME}.cnf \ -uroot -e "create table test.rocksdb_vars as select * from information_schema.GLOBAL_STATUS where variable_name like 'ROCKSDB%'" +sleep 3 ############################################################################# ### Start the profiler if [[ $USE_PERF ]] ; then diff --git a/setup-fbmysql-nov.sh b/setup-fbmysql-nov.sh new file mode 100755 index 0000000..b2fcf77 --- /dev/null +++ b/setup-fbmysql-nov.sh @@ -0,0 +1,9 @@ +#!/bin/bash + + +./setup-server/setup-fbmysql-clone.sh range-locking range-locking-fb-mysql-5.6.35-seekforupdate +./setup-server/setup-fbmysql-clone.sh orig range-locking-fb-mysql-5.6.35-seekforupdate-nov-base + +cp -r data-fbmysql-orig data-fbmysql-orig.clean +cp -r data-fbmysql-range-locking data-fbmysql-range-locking.clean +

1 0

[Commits] 97b067babde: MDEV-19901: Wrong window function calculation
by Varun 13 Dec '19

13 Dec '19

revision-id: 97b067babde074e877ae2bf40f3e7c8bcde05813 (mariadb-10.2.29-62-g97b067babde) parent(s): f0aa073f2bf3d8d85b3d028df89cdb4cdfc4002d author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-13 20:07:33 +0530 message: MDEV-19901: Wrong window function calculation The function compare_window_funcs_by_window_specs() does not correctly compare the window functions and this leads to skipping sorting for some window functions as it assumes that the sort order has not changed. --- mysql-test/r/win.result | 17 +++++++++++++++++ mysql-test/t/win.test | 16 ++++++++++++++++ sql/sql_window.cc | 20 ++++++++++++++++++-- 3 files changed, 51 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 805fd2ed3d7..bb834c39cd8 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3643,5 +3643,22 @@ x foo drop table t1; # +# MDEV-19901: Wrong window function calculation +# +create table t1 (a int, b int); +insert into t1 values (1, 1), (1, 2), (2, 3), (2, 4); +SELECT +SUM(b) OVER () AS x, +SUM(b) OVER (PARTITION BY a) AS y, +SUM(b) OVER (ORDER BY b) AS z +FROM t1 +ORDER BY z; +x y z +10 3 1 +10 3 3 +10 7 6 +10 7 10 +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 0f79834567b..96ec9b0b167 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2351,6 +2351,22 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; drop table t1; +--echo # +--echo # MDEV-19901: Wrong window function calculation +--echo # + +create table t1 (a int, b int); +insert into t1 values (1, 1), (1, 2), (2, 3), (2, 4); + +SELECT + SUM(b) OVER () AS x, + SUM(b) OVER (PARTITION BY a) AS y, + SUM(b) OVER (ORDER BY b) AS z +FROM t1 +ORDER BY z; + +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_window.cc b/sql/sql_window.cc index b258b8f56c9..9d8c47a7bb2 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -362,9 +362,9 @@ int compare_order_lists(SQL_I_List<ORDER> *part_list1, return cmp; } if (elem1) - return CMP_GT_C; - if (elem2) return CMP_LT_C; + if (elem2) + return CMP_GT_C; return CMP_EQ; } @@ -546,6 +546,22 @@ typedef int (*Item_window_func_cmp)(Item_window_func *f1, - window frame compatibility. The changes between the groups are marked by setting item_window_func->marker. + + The sorting of the window functions is done in such a way that window + functions that can be computed together are adjacent and the first window + function in this list of compatible functions for sorting + has the LONGEST list for ordering. + + For example lets consider these 3 window functions + sum(a) OVER (PARTITION BY a) + sum(a) OVER (PARTITION BY a, b) + sum(a) OVER (PARTITION BY a, b, c) + + After sorting the order would be + sum(a) OVER (PARTITION BY a, b, c) + sum(a) OVER (PARTITION BY a, b) + sum(a) OVER (PARTITION BY a) + This would only require sorting once(sort criteria a,b,c). */ static

1 0

[Commits] Review draft for: Extend the join prefix to ensure all tables in the duplicate range ...
by Sergey Petrunia 12 Dec '19

12 Dec '19

Hi Varun, Please check out the draft notes below. Any comments/objections? I'm having difficulty reviewing the patch as the naming is counter-intuitive (either that, or I miss something). > commit dcc092af9b8b73587788dcd3e664bb496378ec76 > Author: Varun Gupta <varun.gupta(a)mariadb.com> > Date: Wed Nov 6 13:24:29 2019 +0530 > > Extend the join prefix to ensure all tables in the duplicate range either are inside or outside the sort nest > ... > diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc > index 822240dcfac..543378a85f4 100644 > --- a/sql/opt_subselect.cc > +++ b/sql/opt_subselect.cc > @@ -3513,6 +3513,13 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, > } > It is not clear what this function means. > +bool Duplicate_weedout_picker::sort_nest_allowed_for_sj(table_map prefix_tables) > +{ dupsweedout_tables is defined as: /* Tables that we will need to have in the prefix to do the weedout step (all inner and all outer that the involved semi-joins are correlated with) */ table_map dupsweedout_tables; I don't follow what the condition means? > + if (!dupsweedout_tables || !(~prefix_tables & dupsweedout_tables)) > + return FALSE; > + return TRUE; > +} > + > /* > Remove the last join tab from from join->cur_sj_inner_tables bitmap > we assume remaining_tables doesnt contain @tab. > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 25cf486f27d..e3056be7cc5 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -9594,8 +9594,6 @@ best_extension_by_limited_search(JOIN *join, > JOIN_TAB *s; > double best_record_count= DBL_MAX; > double best_read_time= DBL_MAX; > - bool disable_jbuf= (join->thd->variables.join_cache_level == 0) || > - nest_created; > bool save_prefix_resolves_ordering= join->prefix_resolves_ordering; > > if (nest_created && !limit_applied_to_nest) > @@ -9610,9 +9608,17 @@ best_extension_by_limited_search(JOIN *join, > apply_limit.add("original_record_count", original_record_count); > apply_limit.add("record_count_after_limit_applied", record_count); > } > - > } > > + /* > + Join buffering should be not considered in 2 cases s/should be not/should not be/? > + 1) if join_cache_level = 0 that is join buffering is disabled > + 2) if the limit was pushed to a prefix of the join that can resolve the > + ORDER BY clause > + */ > + bool disable_jbuf= (join->thd->variables.join_cache_level == 0) || > + limit_applied_to_nest; > + > DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time, > "part_plan");); > > @@ -9755,15 +9761,10 @@ best_extension_by_limited_search(JOIN *join, > s->check_if_index_satisfies_ordering(index_used)) > limit_applied_to_nest= TRUE; > > - /* > - TODO varun: > - 1) get an optimizer switch to enable or disable the sort > - nest instead of a system variable > - */ > if (!nest_created && > (join->prefix_resolves_ordering || > join->consider_adding_sort_nest(sort_nest_tables | > - real_table_bit))) > + real_table_bit, idx))) > { > // SORT_NEST branch > join->positions[idx].sort_nest_operation_here= TRUE; > @@ -9789,6 +9790,8 @@ best_extension_by_limited_search(JOIN *join, > sort_nest_tables | real_table_bit, > TRUE, limit_applied_to_nest)) > DBUG_RETURN(TRUE); > + if (!(join->is_index_with_ordering_allowed(idx) && > + s->check_if_index_satisfies_ordering(index_used))) > join->positions[idx].sort_nest_operation_here= FALSE; > trace_rest.end(); > } > @@ -9808,8 +9811,11 @@ best_extension_by_limited_search(JOIN *join, > DBUG_RETURN(TRUE); > trace_rest.end(); > > - if (!idx) > + if (idx == join->const_tables) > + { > limit_applied_to_nest= FALSE; > + join->positions[idx].sort_nest_operation_here= FALSE; > + } > swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); > } > else > diff --git a/sql/sql_select.h b/sql/sql_select.h > index ec86ae346ed..0e0d1de8f15 100644 > --- a/sql/sql_select.h > +++ b/sql/sql_select.h > @@ -738,6 +738,7 @@ class Semi_join_strategy_picker > struct st_position *loose_scan_pos) = 0; > > virtual void mark_used() = 0; > + virtual bool sort_nest_allowed_for_sj(table_map prefix_tables) = 0; > > virtual ~Semi_join_strategy_picker() {} > }; > @@ -779,6 +780,7 @@ class Duplicate_weedout_picker : public Semi_join_strategy_picker > struct st_position *loose_scan_pos); > > void mark_used() { is_used= TRUE; } > + bool sort_nest_allowed_for_sj(table_map remaining_tables); > friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); > }; > > @@ -824,6 +826,7 @@ class Firstmatch_picker : public Semi_join_strategy_picker > struct st_position *loose_scan_pos); > > void mark_used() { is_used= TRUE; } > + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return FALSE; } Please clarify why this is so. > friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); > }; > > @@ -866,7 +869,7 @@ class LooseScan_picker : public Semi_join_strategy_picker > sj_strategy_enum *strategy, > struct st_position *loose_scan_pos); > void mark_used() { is_used= TRUE; } > - > + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return FALSE; } I think this is ok to disable. > friend class Loose_scan_opt; > friend void best_access_path(JOIN *join, > JOIN_TAB *s, > @@ -916,6 +919,7 @@ class Sj_materialization_picker : public Semi_join_strategy_picker > sj_strategy_enum *strategy, > struct st_position *loose_scan_pos); > void mark_used() { is_used= TRUE; } > + bool sort_nest_allowed_for_sj(table_map remaining_tables) { return FALSE; } Seems to be ok. > > friend void fix_semijoin_strategies_for_picked_join_order(JOIN *join); > }; > @@ -1956,7 +1960,8 @@ class JOIN :public Sql_alloc > void setup_range_scan(JOIN_TAB *tab, uint idx, double records); > bool is_join_buffering_allowed(JOIN_TAB *tab); > bool check_join_prefix_resolves_ordering(table_map previous_tables); > - bool consider_adding_sort_nest(table_map previous_tables); > + bool consider_adding_sort_nest(table_map previous_tables, uint idx); > + bool extend_prefix_to_ensure_duplicate_removal(table_map prefix_tables, uint idx); > void set_fraction_output_for_nest(); > double sort_nest_oper_cost(double join_record_count, uint idx, > ulong rec_len); > diff --git a/sql/sql_sort_nest.cc b/sql/sql_sort_nest.cc > index 18d2a596587..5725c1ef0db 100644 > --- a/sql/sql_sort_nest.cc > +++ b/sql/sql_sort_nest.cc > @@ -1446,18 +1446,44 @@ bool JOIN::sort_nest_allowed() > FALSE otherwise > */ > (note to self: this returns FALSE when one CANNOT add a sort nest) > -bool JOIN::consider_adding_sort_nest(table_map prefix_tables) > +bool JOIN::consider_adding_sort_nest(table_map prefix_tables, uint idx) > { > if (!sort_nest_possible || // (1) > get_cardinality_estimate || // (2) > cur_embedding_map || // (3) > - cur_sj_inner_tables) // (4) > + cur_sj_inner_tables || // (4) > + extend_prefix_to_ensure_duplicate_removal(prefix_tables, idx)) (note to self: extend_prefix_to_ensure_duplicate_removal()= TRUE means we CANNOT add a sort nest) > return FALSE; > > return check_join_prefix_resolves_ordering(prefix_tables); // (5) > } > > > +bool > +JOIN::extend_prefix_to_ensure_duplicate_removal(table_map prefix_tables, uint idx) The name is poor. The function is called "extend_..." but it doesn't perform any extension. What it actually does is to check "whether sort nest is *NOT allowed* at this point. @return TRUE - CANNOT add a sort nest FALSE - CAN add a sort nest > +{ > + if (!select_lex->have_merged_subqueries) > + return FALSE; > + > + POSITION *pos= positions + idx; > + Semi_join_strategy_picker *pickers[]= > + { > + &pos->firstmatch_picker, > + &pos->loosescan_picker, > + &pos->sjmat_picker, > + &pos->dups_weedout_picker, > + NULL, > + }; > + Semi_join_strategy_picker **strategy; > + for (strategy= pickers; *strategy != NULL; strategy++) > + { So, the logic is: if any of the semi-join strategies returns true ("disallow") that means the sort nest cannot be placed here and must be moved. > + if ((*strategy)->sort_nest_allowed_for_sj(prefix_tables)) > + return TRUE; > + } FirstMatch and LooseScan pickers always return FALSE. This means they always allow adding a sort nest... Is this correct? > + return FALSE; > +} > + > + > /* > @brief > Check if indexes on a table are allowed to resolve the ORDER BY clause BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Commits] ea9a7ef6ec0: MDEV-21243: Join buffer: condition is checked in wrong place for range access
by psergey 11 Dec '19

11 Dec '19

revision-id: ea9a7ef6ec0e3e24927fb1fc39e00cb526eec89c (mariadb-10.3.10-1-gea9a7ef6ec0) parent(s): bad2f1569da57c4a81cc84ec2f4a79924df9c8d6 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-11 17:56:25 +0300 message: MDEV-21243: Join buffer: condition is checked in wrong place for range access In this scenario: - There is a possible range access for table T - And there is a ref access on the same index which uses fewer key parts - The join optimizer picks the ref access (because it is cheaper) - make_join_select applies this heuristic to switch to range: /* Range uses longer key; Use this instead of ref on key */ Join buffer will be used without having called JOIN_TAB::make_scan_filter(). This means, conditions that should be checked when reading table T will be checked after T is joined with the contents of the join buffer, instead. Fixed this by adding a make_scan_filter() check. (updated patch after backport to 10.3) (Backported to 10.3.10) (rebuild) (Fix testcase on Windows) --- mysql-test/main/join_cache.result | 72 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/join_cache.test | 39 +++++++++++++++++++++ sql/sql_select.cc | 10 ++++++ 3 files changed, 121 insertions(+) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fde6e0fec6b..a2c83088b8a 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6049,4 +6049,76 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size = default; +# +# MDEV-21243: Join buffer: condition is checked in wrong place for range access +# +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; +create table t3 ( +kp1 int, +kp2 int, +col1 int, +col2 int, +key (kp1, kp2) +); +insert into t3 +select +A.a, +B.a, +A.a + 100*B.a, +A.a + 100*B.a +from +t2 A, t2 B; +analyze table t3; +Table Op Msg_type Msg_text +test.t3 analyze status Table is already up to date +# The following must have "B.col1 + 1 < 33333" attached to table B +# and not to the block-nl-join node: +explain format=json +select * +from t1 a, t3 b +where +b.kp1=a.a and +b.kp1 <= 10 and +b.kp2 <= 10 and +b.col1 +1 < 33333; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "a", + "access_type": "index", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "rows": 10, + "filtered": 100, + "attached_condition": "a.a <= 10", + "using_index": true + }, + "block-nl-join": { + "table": { + "table_name": "b", + "access_type": "range", + "possible_keys": ["kp1"], + "key": "kp1", + "key_length": "10", + "used_key_parts": ["kp1", "kp2"], + "rows": 836, + "filtered": 11.962, + "index_condition": "b.kp2 <= 10", + "attached_condition": "b.kp2 <= 10 and b.col1 + 1 < 33333" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "b.kp1 = a.a" + } + } +} +drop table t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index df89fc30dee..62ca5232c7f 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -3988,5 +3988,44 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size = default; +--echo # +--echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access +--echo # +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; + +create table t3 ( + kp1 int, + kp2 int, + col1 int, + col2 int, + key (kp1, kp2) +); + +insert into t3 +select + A.a, + B.a, + A.a + 100*B.a, + A.a + 100*B.a +from + t2 A, t2 B; +analyze table t3; + +--echo # The following must have "B.col1 + 1 < 33333" attached to table B +--echo # and not to the block-nl-join node: +explain format=json +select * +from t1 a, t3 b +where + b.kp1=a.a and + b.kp1 <= 10 and + b.kp2 <= 10 and + b.col1 +1 < 33333; + +drop table t1,t2,t3; + # The following command must be the last one the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd8f3d07824..491e684f7a0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10734,6 +10734,13 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (i != join->const_tables && tab->use_quick != 2 && !tab->first_inner) { /* Read with cache */ + /* + TODO: the execution also gets here when we will not be using + join buffer. Review these cases and perhaps, remove this call. + (The final decision whether to use join buffer is made in + check_join_cache_usage, so we should only call make_scan_filter() + there, too). + */ if (tab->make_scan_filter()) DBUG_RETURN(1); } @@ -11673,6 +11680,9 @@ uint check_join_cache_usage(JOIN_TAB *tab, if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache))) { tab->icp_other_tables_ok= FALSE; + /* If make_join_select() hasn't called make_scan_filter(), do it now */ + if (!tab->cache_select && tab->make_scan_filter()) + goto no_join_cache; return (2 - MY_TEST(!prev_cache)); } goto no_join_cache;

1 0

[Commits] b792aad283e: MDEV-21243: Join buffer: condition is checked in wrong place for range access
by psergey 11 Dec '19

11 Dec '19

revision-id: b792aad283e59b5d2f81fefb6886432b116c7770 (mariadb-10.3.10-1-gb792aad283e) parent(s): bad2f1569da57c4a81cc84ec2f4a79924df9c8d6 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-11 14:17:14 +0300 message: MDEV-21243: Join buffer: condition is checked in wrong place for range access In this scenario: - There is a possible range access for table T - And there is a ref access on the same index which uses fewer key parts - The join optimizer picks the ref access (because it is cheaper) - make_join_select applies this heuristic to switch to range: /* Range uses longer key; Use this instead of ref on key */ Join buffer will be used without having called JOIN_TAB::make_scan_filter(). This means, conditions that should be checked when reading table T will be checked after T is joined with the contents of the join buffer, instead. Fixed this by adding a make_scan_filter() check. (updated patch after backport to 10.3) (Backported to 10.3.10) (rebuild) --- mysql-test/main/join_cache.result | 72 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/join_cache.test | 39 +++++++++++++++++++++ sql/sql_select.cc | 10 ++++++ 3 files changed, 121 insertions(+) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fde6e0fec6b..4f9616d5235 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6049,4 +6049,76 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size = default; +# +# MDEV-21243: Join buffer: condition is checked in wrong place for range access +# +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; +create table t3 ( +kp1 int, +kp2 int, +col1 int, +col2 int, +key (kp1, kp2) +); +insert into t3 +select +A.a, +B.a, +A.a + 100*B.a, +A.a + 100*B.a +from +t2 A, t2 B; +analyze table t3; +Table Op Msg_type Msg_text +test.t3 analyze status Table is already up to date +# The following must have "B.col1 + 1 < 33333" attached to table B +# and not to the block-nl-join node: +explain format=json +select * +from t1 A, t3 B +where +B.kp1=A.a and +B.kp1 <= 10 and +B.kp2 <= 10 and +B.col1 +1 < 33333; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "A", + "access_type": "index", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "rows": 10, + "filtered": 100, + "attached_condition": "A.a <= 10", + "using_index": true + }, + "block-nl-join": { + "table": { + "table_name": "B", + "access_type": "range", + "possible_keys": ["kp1"], + "key": "kp1", + "key_length": "10", + "used_key_parts": ["kp1", "kp2"], + "rows": 836, + "filtered": 11.962, + "index_condition": "B.kp2 <= 10", + "attached_condition": "B.kp2 <= 10 and B.col1 + 1 < 33333" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "B.kp1 = A.a" + } + } +} +drop table t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index df89fc30dee..a3d16552fc4 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -3988,5 +3988,44 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size = default; +--echo # +--echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access +--echo # +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; + +create table t3 ( + kp1 int, + kp2 int, + col1 int, + col2 int, + key (kp1, kp2) +); + +insert into t3 +select + A.a, + B.a, + A.a + 100*B.a, + A.a + 100*B.a +from + t2 A, t2 B; +analyze table t3; + +--echo # The following must have "B.col1 + 1 < 33333" attached to table B +--echo # and not to the block-nl-join node: +explain format=json +select * +from t1 A, t3 B +where + B.kp1=A.a and + B.kp1 <= 10 and + B.kp2 <= 10 and + B.col1 +1 < 33333; + +drop table t1,t2,t3; + # The following command must be the last one the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd8f3d07824..491e684f7a0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10734,6 +10734,13 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (i != join->const_tables && tab->use_quick != 2 && !tab->first_inner) { /* Read with cache */ + /* + TODO: the execution also gets here when we will not be using + join buffer. Review these cases and perhaps, remove this call. + (The final decision whether to use join buffer is made in + check_join_cache_usage, so we should only call make_scan_filter() + there, too). + */ if (tab->make_scan_filter()) DBUG_RETURN(1); } @@ -11673,6 +11680,9 @@ uint check_join_cache_usage(JOIN_TAB *tab, if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache))) { tab->icp_other_tables_ok= FALSE; + /* If make_join_select() hasn't called make_scan_filter(), do it now */ + if (!tab->cache_select && tab->make_scan_filter()) + goto no_join_cache; return (2 - MY_TEST(!prev_cache)); } goto no_join_cache;

1 0

[Commits] d835a82fc55: MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data
by Varun 10 Dec '19

10 Dec '19

revision-id: d835a82fc55a6826a40345e04f3a5164a10e46dd (mariadb-10.2.29-50-gd835a82fc55) parent(s): 301a5e487bb625b41543f11626ab524ad348d0ba author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-10 19:43:56 +0530 message: MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data For window functions when the values need to be cached, make sure to create a copy as the values that need to be cached because these point to the fields of the temporary table(Field::Ptr). These temp tables are freed when one calls join->join_free() --- mysql-test/r/win.result | 9 +++++++++ mysql-test/t/win.test | 10 ++++++++++ sql/item.cc | 12 ++++++++++++ sql/item.h | 2 +- 4 files changed, 32 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index b543863bc50..3a45b6237aa 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3661,5 +3661,14 @@ x y z 10 7 10 drop table t1; # +# MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; +x +foo +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 79a357b3b34..411429bf676 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2363,6 +2363,16 @@ FROM t1; drop table t1; +--echo # +--echo # MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data +--echo # + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); + +SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item.cc b/sql/item.cc index 333d71ddf70..528282f74c2 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10044,6 +10044,18 @@ bool Item_cache_str::cache_value() value_buff.copy(*value); value= &value_buff; } + else if (example->type() == Item::WINDOW_FUNC_ITEM) + { + /* + For window functions make sure to create a copy as the values that + need to be cached because these point to the fields of the temporary + table(Field::Ptr). These temp tables are freed when one calls + join->join_free() + */ + value_buff2.set(buffer, sizeof(buffer), example->collation.collation); + value_buff2.copy(*value); + value= &value_buff2; + } return TRUE; } diff --git a/sql/item.h b/sql/item.h index ff4cfd6c1b8..0696162d0f9 100644 --- a/sql/item.h +++ b/sql/item.h @@ -5813,7 +5813,7 @@ class Item_cache_decimal: public Item_cache class Item_cache_str: public Item_cache { char buffer[STRING_BUFFER_USUAL_SIZE]; - String *value, value_buff; + String *value, value_buff, value_buff2; bool is_varbinary; public:

1 0