- commits - lists.mariadb.org

[Commits] 6db0b0f64a3: Better comment
by Sergei Petrunia 30 Dec '18

30 Dec '18

revision-id: 6db0b0f64a3bd6e647e8f1d111d4a526f14fc998 (mariadb-10.1.37-49-g6db0b0f64a3) parent(s): 588c5aea9cf5eda0bb4dc29437c467ddf39eac6e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-12-30 11:36:37 +0300 message: Better comment --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d106a49bda8..9a5de677b40 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25962,7 +25962,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, for (uint i= tablenr+1; i < join->table_count; i++) { fanout*= join->best_positions[i].records_read; // fanout is always >= 1 - // But selectivity is =< 1 : + // But selectivity is 1 or less : fanout*= join->best_positions[i].cond_selectivity; } }

1 0

[Commits] aee96bedc6a: Revert unnecessary changes - Part II
by jan 29 Dec '18

29 Dec '18

revision-id: aee96bedc6a1e8f90bd2e9ef8e6518ee126f7ff2 (mariadb-10.3.6-276-gaee96bedc6a) parent(s): 639d2eff98daf63926564332126e10feee7e5eaa author: Jan Lindström committer: Jan Lindström timestamp: 2018-12-29 10:11:32 +0200 message: Revert unnecessary changes - Part II --- mysql-test/include/have_ipv6.inc | 20 -------- mysql-test/include/restart_mysqld.inc | 1 + .../suite/galera_3nodes/include/have_ipv6.inc | 7 ++- mysql-test/suite/innodb/t/galera.skip | 53 ---------------------- 4 files changed, 7 insertions(+), 74 deletions(-) diff --git a/mysql-test/include/have_ipv6.inc b/mysql-test/include/have_ipv6.inc deleted file mode 100644 index 752dd0db53e..00000000000 --- a/mysql-test/include/have_ipv6.inc +++ /dev/null @@ -1,20 +0,0 @@ -# Check if ipv6 is available. -# ---disable_query_log ---disable_result_log ---disable_abort_on_error -connect (checkcon123456789,::1,root,,test); -if($mysql_errno) -{ - skip No IPv6 support; -} -connection default; -if(!$mysql_errno) -{ - disconnect checkcon123456789; -} ---enable_abort_on_error ---enable_result_log ---enable_query_log -# end check - diff --git a/mysql-test/include/restart_mysqld.inc b/mysql-test/include/restart_mysqld.inc index 8cf2798b592..3d3e55db4ac 100644 --- a/mysql-test/include/restart_mysqld.inc +++ b/mysql-test/include/restart_mysqld.inc @@ -5,5 +5,6 @@ # --source include/restart_mysqld.inc --source include/not_embedded.inc + --source include/shutdown_mysqld.inc --source include/start_mysqld.inc diff --git a/mysql-test/suite/galera_3nodes/include/have_ipv6.inc b/mysql-test/suite/galera_3nodes/include/have_ipv6.inc index 560cad03350..752dd0db53e 100644 --- a/mysql-test/suite/galera_3nodes/include/have_ipv6.inc +++ b/mysql-test/suite/galera_3nodes/include/have_ipv6.inc @@ -2,13 +2,18 @@ # --disable_query_log --disable_result_log +--disable_abort_on_error connect (checkcon123456789,::1,root,,test); if($mysql_errno) { skip No IPv6 support; } connection default; -disconnect checkcon123456789; +if(!$mysql_errno) +{ + disconnect checkcon123456789; +} +--enable_abort_on_error --enable_result_log --enable_query_log # end check diff --git a/mysql-test/suite/innodb/t/galera.skip b/mysql-test/suite/innodb/t/galera.skip deleted file mode 100644 index 1991ce10cc4..00000000000 --- a/mysql-test/suite/innodb/t/galera.skip +++ /dev/null @@ -1,53 +0,0 @@ -innodb : deadlock, failure in UPDATE IGNORE, lp1372296 -innodb_ctype_ldml : Test contains statements unsafe to replicate in statement-based replication -innodb-autoinc : deadlock, failure in REPLACE, lp1372296 -innodb_mysql : deadlock due to DDL -innodb_buffer_pool_load : Test contains statements unsafe to replicate in statement-based replication -innodb-autoinc-56228 : deadlock, lp1372301 -innodb_lock_wait_timeout_1 : Test contains statements unsafe to replicate in statement-based replication -innodb-consistent : Test contains statements unsafe to replicate in statement-based replication -innodb-semi-consistent : Test contains statements unsafe to replicate in statement-based replication -innodb-index : DDL concurrent with transaction -innodb-lock : deadlock on INSERT IGNORE, lp1372296 -innodb-status-output : Test performs server restart -innodb-wl5522 : Test contains statements unsafe to replicate in statement-based replication -innodb-wl6445 : Test performs server restart -innodb_bug40360 : Test contains statements unsafe to replicate in statement-based replication -innodb_bug40565 : Galera git bug #137 - Invalid deadlock on UPDATE to NULL without a PK -innodb_bug42419 : Test contains statements unsafe to replicate in statement-based replication -innodb_bug49164 : Test contains statements unsafe to replicate in statement-based replication -innodb_bug45357 : impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging -innodb_bug52663 : Test contains statements unsafe to replicate in statement-based replication -innodb_bug59733 : Test contains statements unsafe to replicate in statement-based replication -innodb_gis : Test contains statements unsafe to replicate in statement-based replication -innodb_prefix_index_restart_server : crash, lp1372288 -innodb_stats_external_pages : Test contains statements unsafe to replicate in statement-based replication -innodb-2byte-collation : Unsafe statement written to the binary log -innodb-change-buffer-recovery : Test contains statements unsafe to replicate in statement-based replication -innodb_bug53756 : Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT -innodb-alter-autoinc : AUTO_INCREMENT differences -innodb_bug13635833 : Test contains statements unsafe to replicate in statement-based replication -innodb_bug13867871 : mysql-wsrep#3 - innodb_bug13867871 test fails with wsrep loaded -innodb_bug14006907 : DDL lock wait timeout -innodb_bug38231 : Deadlock on UNLOCK TABLE -innodb_bug-13628249 : mysql-wsrep#12 InnoDB: Failing assertion: !srv_read_only_mode with server restart -innodb-wl6445-1 : mysql-wsrep#12 InnoDB: Failing assertion: !srv_read_only_mode with server restart -innodb-wl6445-2 : mysql-wsrep#12 InnoDB: Failing assertion: !srv_read_only_mode with server restart -innodb_bug12400341 : Test does not account for applier threads when performing SHOW PROCESSLIST -innodb-blob : 'Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging' -innodb_corrupt_bit : 'Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.' -innodb-index-online : ALTER succeeds as it is given a higher priority -innodb-table-online : ALTER succeeds as it is given a higher priority -innodb-index-online-purge : ALTER succeeds as it is given a higher priority -innodb-wl5522-debug : Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. -innodb_stats_table_flag_auto_recalc : Performs multiple restarts in a row which causes '1047: Unknown command' errors -innodb-lock-inherit-read_commited : Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT -innodb-index-debug : Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT -flush-hang : Unsafe statement written to the binary log -innodb_deadlock_with_autoinc : Test uses autoinc_lock_mode = 0 -innodb_stats_del_mark : Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. -ibuf_not_empty : InnoDB: Failing assertion: !srv_read_only_mode with server restart -innodb_force_recovery : InnoDB: Failing assertion: !srv_read_only_mode with server restart -innodb-system-table-view : Result depends on whether galera is loaded or not -innodb-wl5980-linux : Fails because portability_wl5980_linux.zip does not contain wsrep_schema -innodb-multiple-tablespaces : No changes to SR table are allowed if innodb-force-recovery > 0 \ No newline at end of file

1 0

[Commits] 09cb493b301: Revert unnecessary changes - part I.
by jan 28 Dec '18

28 Dec '18

revision-id: 09cb493b301b0fb953b3ff85bffab19508c86714 (mariadb-10.3.6-274-g09cb493b301) parent(s): 49e41bfb50769696ff594cd439ebe3599abac984 author: Jan Lindström committer: Jan Lindström timestamp: 2018-12-28 16:59:24 +0200 message: Revert unnecessary changes - part I. --- storage/innobase/handler/ha_innodb.cc | 6 ++++++ storage/innobase/include/trx0trx.h | 2 ++ storage/innobase/lock/lock0wait.cc | 1 + storage/innobase/trx/trx0roll.cc | 6 +----- storage/innobase/trx/trx0trx.cc | 5 +---- 5 files changed, 11 insertions(+), 9 deletions(-) diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 5c887bca109..2924ed8a45c 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -6529,6 +6529,7 @@ ha_innobase::close() UNIV_INTERN ulint wsrep_innobase_mysql_sort( +/*======================*/ /* out: str contains sort string */ int mysql_type, /* in: MySQL type */ uint charset_number, /* in: number of the charset */ @@ -6952,6 +6953,7 @@ Stores a key value for a row to a buffer. UNIV_INTERN uint wsrep_store_key_val_for_row( +/*=========================*/ THD* thd, TABLE* table, uint keynr, /*!< in: key number */ @@ -8675,6 +8677,7 @@ calc_row_difference( static int wsrep_calc_row_hash( +/*================*/ byte* digest, /*!< in/out: md5 sum */ const uchar* row, /*!< in: row in MySQL format */ TABLE* table, /*!< in: table in MySQL data @@ -10177,6 +10180,7 @@ wsrep_key_type_to_str(Wsrep_service_key_type type) extern dberr_t wsrep_append_foreign_key( +/*===========================*/ trx_t* trx, /*!< in: trx */ dict_foreign_t* foreign, /*!< in: foreign key constraint */ const rec_t* rec, /*!<in: clustered index record */ @@ -10348,6 +10352,7 @@ wsrep_append_foreign_key( static int wsrep_append_key( +/*=============*/ THD *thd, trx_t *trx, TABLE_SHARE *table_share, @@ -10426,6 +10431,7 @@ referenced_by_foreign_key2( int ha_innobase::wsrep_append_keys( +/*===========================*/ THD *thd, Wsrep_service_key_type key_type, /*!< in: access type of this row operation: diff --git a/storage/innobase/include/trx0trx.h b/storage/innobase/include/trx0trx.h index 5dded9ee7af..119a0c1f214 100644 --- a/storage/innobase/include/trx0trx.h +++ b/storage/innobase/include/trx0trx.h @@ -579,9 +579,11 @@ struct trx_lock_t { lock_sys.mutex. Otherwise, this may only be modified by the thread that is serving the running transaction. */ +#ifdef WITH_WSREP bool was_chosen_as_wsrep_victim; /*!< high priority wsrep thread has marked this trx to abort */ +#endif /* WITH_WSREP */ /** Pre-allocated record locks */ struct { diff --git a/storage/innobase/lock/lock0wait.cc b/storage/innobase/lock/lock0wait.cc index 6d482984f3f..721f6151c36 100644 --- a/storage/innobase/lock/lock0wait.cc +++ b/storage/innobase/lock/lock0wait.cc @@ -36,6 +36,7 @@ Created 25/5/2010 Sunny Bains #include "row0mysql.h" #include "srv0start.h" #include "lock0priv.h" + /*********************************************************************//** Print the contents of the lock_sys_t::waiting_threads array. */ static diff --git a/storage/innobase/trx/trx0roll.cc b/storage/innobase/trx/trx0roll.cc index 249112a22e3..a33eb1fa0f9 100644 --- a/storage/innobase/trx/trx0roll.cc +++ b/storage/innobase/trx/trx0roll.cc @@ -451,12 +451,8 @@ trx_rollback_to_savepoint_for_mysql_low( trx_mark_sql_stat_end(trx); trx->op_info = ""; - + trx->lock.was_chosen_as_deadlock_victim = FALSE; #ifdef WITH_WSREP - if (wsrep_on(trx->mysql_thd) && - trx->lock.was_chosen_as_deadlock_victim) { - trx->lock.was_chosen_as_deadlock_victim = FALSE; - } trx->lock.was_chosen_as_wsrep_victim = FALSE; #endif return(err); diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index bf6edcfdca2..ff62101e3de 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -1443,11 +1443,8 @@ trx_commit_in_memory( trx_mutex_enter(trx); trx->dict_operation = TRX_DICT_OP_NONE; - + trx->lock.was_chosen_as_deadlock_victim = FALSE; #ifdef WITH_WSREP - if (trx->mysql_thd && wsrep_on(trx->mysql_thd)) { - trx->lock.was_chosen_as_deadlock_victim = FALSE; - } trx->lock.was_chosen_as_wsrep_victim = FALSE; #endif

1 0

[Commits] 66048879: Trigger build
by jan 28 Dec '18

28 Dec '18

revision-id: 66048879708e3c341dad1ce0b89acf458d67b364 (mariadb-25.3.19-19-g66048879) parent(s): addae7e61e8e4cc08d96d659373afcbc4ab8ca1a author: Jan Lindström committer: Jan Lindström timestamp: 2018-12-28 13:22:25 +0200 message: Trigger build

1 0

[Commits] 348797717b3: Fix Windows test failure.
by jan 27 Dec '18

27 Dec '18

revision-id: 348797717b351b22de7da44cdf42d45c8901f9bd (mariadb-10.3.6-270-g348797717b3) parent(s): c0b7eb3d089283c0a934b643f5e3a5267ea01c4a author: Jan Lindström committer: Jan Lindström timestamp: 2018-12-27 09:38:57 +0200 message: Fix Windows test failure. --- mysql-test/include/wait_wsrep_ready.inc | 15 +++++++++------ 1 file changed, 9 insertions(+), 6 deletions(-) diff --git a/mysql-test/include/wait_wsrep_ready.inc b/mysql-test/include/wait_wsrep_ready.inc index 7ff65136c92..644ac2351c0 100644 --- a/mysql-test/include/wait_wsrep_ready.inc +++ b/mysql-test/include/wait_wsrep_ready.inc @@ -6,26 +6,29 @@ --disable_query_log --disable_result_log +# +# Firstly, make sure that wsrep plugin is available +# +if (`SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'wsrep' AND PLUGIN_STATUS='ACTIVE'`) +{ # # if node is not ready, SELECT @@GLOBAL.WSREP_ON below does not # succeed. However, we can force it to succeed by running the SELECT # with dirty reads configuration. So we prepare for that # ---let $my_dirty_reads= `select @@wsrep_dirty_reads` -SET wsrep_dirty_reads = ON; + --let $my_dirty_reads= `select @@wsrep_dirty_reads` + SET wsrep_dirty_reads = ON; -if (`SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'wsrep' AND PLUGIN_STATUS='ACTIVE'`) -{ if (`SELECT @@GLOBAL.WSREP_ON`) { --source include/galera_wait_ready.inc --disable_query_log --disable_result_log } -} # ..and returning original dirty read selection here ---eval SET wsrep_dirty_reads = $my_dirty_reads; + --eval SET wsrep_dirty_reads = $my_dirty_reads; +} --enable_query_log --enable_result_log

1 0

[Commits] 588c5aea9cf: MDEV-18073: get_range_limit_read_cost() doesnt adjust LIMIT for the range access
by Sergei Petrunia 24 Dec '18

24 Dec '18

revision-id: 588c5aea9cf5eda0bb4dc29437c467ddf39eac6e (mariadb-10.1.37-48-g588c5aea9cf) parent(s): fb230256ae3473e4b4191abfea645bd0faa58a82 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-12-24 16:46:17 +0300 message: MDEV-18073: get_range_limit_read_cost() doesnt adjust LIMIT for the range access The computation about which "fraction" of range/ref access cost we will need to perform, was incorrect. Adjusted the computation. --- sql/sql_select.cc | 43 +++++++++++++++++++++++++++++++++++++------ 1 file changed, 37 insertions(+), 6 deletions(-) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2682c06f0cf..d106a49bda8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25730,16 +25730,22 @@ void JOIN::cache_const_exprs() /* - Get a cost of reading rows_limit rows through index keynr. + Get the cost of using index keynr to read #LIMIT matching rows @detail - If there is a quick select, we try to use it. - if there is a ref(const) access, we try to use it, too. - quick and ref(const) use different cost formulas, so if both are possible we should make a cost-based choice. - + + rows_limit is the number of rows we would need to read when using a full + index scan. This is generally higher than the N from "LIMIT N" clause, + because there's a WHERE condition (a part of which is used to construct a + range access we are considering using here) + @param tab JOIN_TAB with table access (is NULL for single-table UPDATE/DELETE) + @param rows_limit See explanation above @param read_time OUT Cost of reading using quick or ref(const) access. @@ -25752,6 +25758,7 @@ void JOIN::cache_const_exprs() static bool get_range_limit_read_cost(const JOIN_TAB *tab, const TABLE *table, + ha_rows table_records, uint keynr, ha_rows rows_limit, double *read_time) @@ -25818,8 +25825,32 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, } } } + + /* + Consider an example: + + SELECT * + FROM t1 + WHERE key1 BETWEEN 10 AND 20 AND col2='foo' + ORDER BY key1 LIMIT 10 + + If we were using a full index scan on key1, we would need to read this + many rows to get 10 matches: + + 10 / selectivity(key1 BETWEEN 10 AND 20 AND col2='foo') + + This is the number we get in rows_limit. + But we intend to use range access on key1. The rows returned by quick + select will satisfy the range part of the condition, + "key1 BETWEEN 10 and 20". We will still need to filter them with + the remainder condition, (col2='foo'). + + The selectivity of the range access is (best_rows/table_records). We need + to discount it from the rows_limit: + */ + double rows_limit_for_quick= rows_limit * (best_rows / table_records); - if (best_rows > rows_limit) + if (best_rows > rows_limit_for_quick) { /* LIMIT clause specifies that we will need to read fewer records than @@ -25828,7 +25859,7 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, only need 1/3rd of records, it will cost us 1/3rd of quick select's read time) */ - best_cost *= rows_limit / best_rows; + best_cost *= rows_limit_for_quick / best_rows; } *read_time= best_cost; res= true; @@ -26121,8 +26152,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, index_scan_time= select_limit/rec_per_key * MY_MIN(rec_per_key, table->file->scan_time()); double range_scan_time; - if (get_range_limit_read_cost(tab, table, nr, select_limit, - &range_scan_time)) + if (get_range_limit_read_cost(tab, table, table_records, nr, + select_limit, &range_scan_time)) { if (range_scan_time < index_scan_time) index_scan_time= range_scan_time;

1 0

[Commits] 54411ba: MDEV-17148 DROP DATABASE throw "Directory not empty" after changed lower_case_table_names.
by holyfoot＠askmonty.org 24 Dec '18

24 Dec '18

revision-id: 54411ba5caee35d0a64e727c117c222b6eef8812 (mariadb-10.2.19-98-g54411ba) parent(s): 975f4a1295f2b678c5ecea2cf4cfc69e177d88f5 committer: Alexey Botchkov timestamp: 2018-12-24 16:41:16 +0400 message: MDEV-17148 DROP DATABASE throw "Directory not empty" after changed lower_case_table_names. No need to lowercase table names on case-sensitive file systems, as the cache won't contain the 'lowercased' table anyway. And it prevents the UPPERCASE.frm from being deleted. --- mysql-test/r/lowercase_table.result | 2 ++ mysql-test/std_data/mdev17148 | Bin 0 -> 433 bytes mysql-test/t/lowercase_table.test | 10 ++++++++++ sql/sql_db.cc | 8 ++++++-- 4 files changed, 18 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result index ac7d3e6..275edd8 100644 --- a/mysql-test/r/lowercase_table.result +++ b/mysql-test/r/lowercase_table.result @@ -127,3 +127,5 @@ Database (mysql_TE%) mysql_test drop database mysql_TEST; End of 10.0 tests +create database db1; +drop database db1; diff --git a/mysql-test/std_data/mdev17148 b/mysql-test/std_data/mdev17148 new file mode 100644 index 0000000..28ba724 Binary files /dev/null and b/mysql-test/std_data/mdev17148 differ diff --git a/mysql-test/t/lowercase_table.test b/mysql-test/t/lowercase_table.test index c339105..df869af 100644 --- a/mysql-test/t/lowercase_table.test +++ b/mysql-test/t/lowercase_table.test @@ -118,3 +118,13 @@ show databases like "mysql_TE%"; drop database mysql_TEST; --echo End of 10.0 tests + +# +# MDEV-17148 DROP DATABASE throw "Directory not empty" after changed lower_case_table_names. +# + +let $datadir=`select @@datadir`; +create database db1; +copy_file std_data/mdev17148 $datadir/db1/T1.frm; +drop database db1; + diff --git a/sql/sql_db.cc b/sql/sql_db.cc index e3f0506..36b20f2 100644 --- a/sql/sql_db.cc +++ b/sql/sql_db.cc @@ -1102,8 +1102,12 @@ static bool find_db_tables_and_rm_known_files(THD *thd, MY_DIR *dirp, table_list->table_name_length= table->length; table_list->open_type= OT_BASE_ONLY; - /* To be able to correctly look up the table in the table cache. */ - if (lower_case_table_names) + /* + On the case-insensitive file systems table is opened + with the lowercased file name. So we should lowercase + as well to look up the cache properly. + */ + if (lower_case_file_system) table_list->table_name_length= my_casedn_str(files_charset_info, table_list->table_name);

1 0

[Commits] fb230256ae3: MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity
by Sergei Petrunia 23 Dec '18

23 Dec '18

revision-id: fb230256ae3473e4b4191abfea645bd0faa58a82 (mariadb-10.1.37-47-gfb230256ae3) parent(s): 9ad1663f785d28a731ab8212ea62b8125282a27f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-12-23 23:50:33 +0300 message: MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity Make the "ORDER BY ... LIMIT n" optimizer take into account condition selectivity data from EITS (not just from potential range accesses). (Commit into 10.1) --- mysql-test/r/order_by.result | 37 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by.test | 37 +++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 22 ++++++++++++++++++++++ 3 files changed, 96 insertions(+) diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 4cd9aebdf49..0772322dde3 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3207,3 +3207,40 @@ pk 2 3 DROP TABLE t1; +# +# MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity +# +create table t1(a int); +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 + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3(a int); +insert into t3 select A.a + 1000 *B.a from t2 A, t1 B; +create table t4 ( +a int, +b int, +c int, +filler1 char(255), +filler2 char(255), +key(a) +); +insert into t4 select a,a,a, a,a from t3; +set @tmp_h=@@histogram_size, @tmp_u=@@use_stat_tables, +@tmp_o=@@optimizer_use_condition_selectivity; +set histogram_size=100; +set use_stat_tables=preferably; +set optimizer_use_condition_selectivity=4; +analyze table t4 persistent for columns(b) indexes (); +Table Op Msg_type Msg_text +test.t4 analyze status Engine-independent statistics collected +test.t4 analyze status Table is already up to date +# rows must be around 1200, not 600: +explain extended +select * from t4 where b < 5000 order by a limit 600; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 index NULL a 5 NULL 1188 100.00 Using where +Warnings: +Note 1003 select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler1` AS `filler1`,`test`.`t4`.`filler2` AS `filler2` from `test`.`t4` where (`test`.`t4`.`b` < 5000) order by `test`.`t4`.`a` limit 600 +set histogram_size=@tmp_h, use_stat_tables=@tmp_u, +optimizer_use_condition_selectivity=@tmp_o; +drop table t1,t2,t3,t4; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 1ca258d1d48..2696fd2a6c2 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2141,3 +2141,40 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT DISTINCT pk FROM t1 GROUP BY 'foo'; SELECT DISTINCT pk FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3(a int); +insert into t3 select A.a + 1000 *B.a from t2 A, t1 B; + +create table t4 ( + a int, + b int, + c int, + filler1 char(255), + filler2 char(255), + key(a) +); +insert into t4 select a,a,a, a,a from t3; + +set @tmp_h=@@histogram_size, @tmp_u=@@use_stat_tables, + @tmp_o=@@optimizer_use_condition_selectivity; +set histogram_size=100; +set use_stat_tables=preferably; +set optimizer_use_condition_selectivity=4; +analyze table t4 persistent for columns(b) indexes (); + +--echo # rows must be around 1200, not 600: +explain extended +select * from t4 where b < 5000 order by a limit 600; + +set histogram_size=@tmp_h, use_stat_tables=@tmp_u, + optimizer_use_condition_selectivity=@tmp_o; + +drop table t1,t2,t3,t4; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 62f40eeb99c..2682c06f0cf 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25929,7 +25929,11 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, uint tablenr= tab - join->join_tab; read_time= join->best_positions[tablenr].read_time; for (uint i= tablenr+1; i < join->table_count; i++) + { fanout*= join->best_positions[i].records_read; // fanout is always >= 1 + // But selectivity is =< 1 : + fanout*= join->best_positions[i].cond_selectivity; + } } else read_time= table->file->scan_time(); @@ -26067,6 +26071,23 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ select_limit= (ha_rows) (select_limit < fanout ? 1 : select_limit/fanout); + + /* + refkey_rows_estimate is E(#rows) produced by the table access + strategy that was picked without regard to ORDER BY ... LIMIT. + + It will be used as the source of selectivity data. + Use table->cond_selectivity as a better estimate which includes + condition selectivity too. + */ + { + // we use MIN(...), because "Using LooseScan" queries have + // cond_selectivity=1 while refkey_rows_estimate has a better + // estimate. + refkey_rows_estimate= MY_MIN(refkey_rows_estimate, + table_records * table->cond_selectivity); + } + /* We assume that each of the tested indexes is not correlated with ref_key. Thus, to select first N records we have to scan @@ -26077,6 +26098,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, N/(refkey_rows_estimate/table_records) > table_records <=> N > refkey_rows_estimate. */ + if (select_limit > refkey_rows_estimate) select_limit= table_records; else

1 0

[Commits] c4777e0c028: MDEV-16272 rpl.rpl_semisync_ali_issues failed in buildbot with wrong result
by andrei.elkin＠pp.inet.fi 20 Dec '18

20 Dec '18

revision-id: c4777e0c0284d7d255a6ca9eabca84c041103915 (mariadb-10.3.10-121-gc4777e0c028) parent(s): f6266f19d453b37c94b660a5d00c1a7234f48931 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-12-20 22:27:28 +0200 message: MDEV-16272 rpl.rpl_semisync_ali_issues failed in buildbot with wrong result The failure was caused by race between an event acknowledgment by the IO thread and its execution by the SQL thread. Rarely the latter may win, which was confirmed by BB. Fixed with turning to wait for the status expected value. --- mysql-test/suite/rpl/r/rpl_semisync_ali_issues.result | 3 --- mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test | 7 ++++++- 2 files changed, 6 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_semisync_ali_issues.result b/mysql-test/suite/rpl/r/rpl_semisync_ali_issues.result index 9607e8a7998..05af93e3a3b 100644 --- a/mysql-test/suite/rpl/r/rpl_semisync_ali_issues.result +++ b/mysql-test/suite/rpl/r/rpl_semisync_ali_issues.result @@ -177,9 +177,6 @@ connection con1; show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 -show status like 'Rpl_semi_sync_master_status'; -Variable_name Value -Rpl_semi_sync_master_status ON connection slave; STOP SLAVE IO_THREAD; include/wait_for_slave_io_to_stop.inc diff --git a/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test b/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test index 52cd9e31753..e231c7d9ab5 100644 --- a/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test +++ b/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test @@ -191,7 +191,12 @@ INSERT INTO t1 values (2); sync_slave_with_master; connection con1; show status like 'Rpl_semi_sync_master_clients'; -show status like 'Rpl_semi_sync_master_status'; +# The semi_sync master status is turning to ON by the 1st event +# after restart. The event execution and its acknowledgment are in race. +# That's why to wait here. +--let $status_var= Rpl_semi_sync_master_status +--let $status_var_value= ON +--source include/wait_for_status_var.inc connection slave; STOP SLAVE IO_THREAD;

1 0

[Commits] 82b525af869: Postreview fix
by Oleksandr Byelkin 20 Dec '18

20 Dec '18

revision-id: 82b525af8697fe5a97dc1db18df6ba0c31af8957 (mariadb-10.3.10-121-g82b525af869) parent(s): 5b02c215cd600ce762892526e2f5604511308fb3 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-12-20 14:52:58 +0100 message: Postreview fix --- sql/sql_signal.cc | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/sql/sql_signal.cc b/sql/sql_signal.cc index 70a61185618..1317308ceb9 100644 --- a/sql/sql_signal.cc +++ b/sql/sql_signal.cc @@ -343,6 +343,11 @@ bool Sql_cmd_common_signal::raise_condition(THD *thd, Sql_condition *cond) if (eval_signal_informations(thd, cond)) DBUG_RETURN(result); + /* SIGNAL should not signal WARN_LEVEL_NOTE, but RESIGNAL can */ + DBUG_ASSERT(cond->m_level == Sql_condition::WARN_LEVEL_ERROR || + cond->m_level != Sql_condition::WARN_LEVEL_NOTE || + sql_command_code() == SQLCOM_RESIGNAL); + (void) thd->raise_condition(cond); if (cond->m_level == Sql_condition::WARN_LEVEL_WARN ||

1 0