- developers - lists.mariadb.org

[Maria-developers] Binlog handling of faulty DDL query
by Sachin Setiya 03 Sep '18

03 Sep '18

Hi Everyone! Suppose this case CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85', user2@localhost IDENTIFIED BY 'x'; user2 has too short passowrd which will give error (if we use security plugin) IN the case of *mariadb* we will create user1 and wont create user2 In the case of *mysql (8.0)* we will return error and we will not create any user Our approach create a lots of problem for binary log and replication(MDEV-14716, MDEV-16728) Because in Query_log_event::write() we write the whole query not the parts of it. So we have 2 options to solve this either write modified query into binlog or do what mysql does. So what you think , which approach we should use ? Or there is another better way ? -- Regards sachin

5 6

Re: [Maria-developers] 63ad6a9: MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>.
by Sergei Golubchik 02 Sep '18

02 Sep '18

Hi, Alexey! On Sep 02, Alexey Botchkov wrote: > revision-id: 63ad6a9e1a33ddd5547767b2894e09ae66196f69 (mariadb-10.1.35-23-g63ad6a9) > parent(s): 288212f489c8cd88c4cc98f8aecc3366c85a90be > committer: Alexey Botchkov > timestamp: 2018-09-02 09:24:33 +0400 > message: > > MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>. > > Check if the argument of the FLUSH TABLE is a VIEW and handle it > accordingly. > > diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result > index b643510..2d7b81b 100644 > --- a/mysql-test/r/flush.result > +++ b/mysql-test/r/flush.result > @@ -496,3 +496,27 @@ flush relay logs,relay logs; > ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS > flush slave,slave; > ERROR HY000: Incorrect usage of FLUSH and SLAVE > +# > +# MDEV-15890 Strange error message if you try to > +# FLUSH TABLES <view> after LOCK TABLES <view>. > +# > +CREATE TABLE t1 (qty INT, price INT); > +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; > +LOCK TABLES v1 READ; > +FLUSH TABLES v1; > +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated > +UNLOCK TABLES; > +LOCK TABLES v1 WRITE; > +FLUSH TABLES v1; > +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated Why is that? Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] MariaDB v10 on AIX
by REIX, Tony 31 Aug '18

31 Aug '18

Hi, We are porting MariaDB v10(.3.7/9) on AIX now. First, I'd like to know if there are people porting (or who has ported) MariaDB on AIX in this mailing list. Second, we have found some issue dealing with: AIX, -O0, and inline, which leads to a symbol missing at link time: my_ulonglong2double . It deals with the code below. Since we are beginners with MariaDB, I'd like to have some comments and/or recommendations about how it should be fixed. By using "static __inline" instead of "inline" below, and adding it too to the ma_global.h definition of my_ulonglong2double() , we are able to fix it. However, probably that using: #define my_ulonglong2double(nr) ((double) nr) would be enough. Or adding my_port.c to the MYSYS_SOURCES variable in mysys/CMakeLists.txt would help: IF (AIX) SET (MYSYS_SOURCES ${MYSYS_SOURCES} my_port.c) ENDIF() however, that does not work, and I do not see my_port.c being added to the list of files to be added to mysys. Any idea why "IF (AIX)" does not work? (I'm also a CMake beginner). Thanks/Regards, Tony Reix mysys/my_port.c: #include "mysys_priv.h" #ifdef _AIX double my_ulonglong2double(unsigned long long nr) { return (double) nr; } mysys/mysys_priv.h: #include <my_global.h> mysys/CMakeLists.txt : SET(MYSYS_SOURCES ........ No my_port.c !! include/my_global.h: #ifdef _AIX /* By soren(a)t.dk */ #define ulonglong2double(A) my_ulonglong2double(A) C_MODE_START inline double my_ulonglong2double(unsigned long long A) { return (double)A; } C_MODE_END libmariadb/include/ma_global.h: #ifdef _AIX /* By soren(a)t.dk */ #define ulonglong2double(A) my_ulonglong2double(A) #ifdef __cplusplus extern "C" { #endif double my_ulonglong2double(unsigned long long A); déclaration externe #ifdef __cplusplus Cordialement, Tony Reix tony.reix(a)atos.net ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net<https://mail.ad.bull.net/owa/redir.aspx?C=PvphmPvCZkGrAgHVnWGsdMcDKgzl_dEIs…>

2 1

Re: [Maria-developers] 44692f17a62: MDEV-15436: If log_bin and log_bin_index is different SST with rsync fails.
by Sergei Golubchik 31 Aug '18

31 Aug '18

Hi, Jan! On Aug 31, serg(a)mariadb.org wrote: > revision-id: 44692f17a621d7f0261d84b97a7334352463038c (mariadb-10.2.16-6-g44692f17a62) > parent(s): 7d0d934ca642e485b2c008727dc20c83e26cce10 > author: Jan Lindström > committer: Jan Lindström > timestamp: 2018-06-26 12:56:19 +0300 > message: > > MDEV-15436: If log_bin and log_bin_index is different SST with rsync fails. > > Problem was that in SST log_bin_index name and directory was not > handled and passed to rsync SST script. > > mysql-test/suite/galera/r/galera_sst_rsync2.result | 396 +++++++++++++++++++++ > mysql-test/suite/galera/t/galera_sst_rsync2.cnf | 15 + > mysql-test/suite/galera/t/galera_sst_rsync2.test | 12 + I didn't review the test, but I presume you've verified that it fails without the bug fix. > diff --git a/scripts/wsrep_sst_rsync.sh b/scripts/wsrep_sst_rsync.sh > index 05c99880d75..6886d2ce1e1 100644 > --- a/scripts/wsrep_sst_rsync.sh > +++ b/scripts/wsrep_sst_rsync.sh > @@ -132,6 +132,12 @@ then > BINLOG_FILENAME=$(basename $WSREP_SST_OPT_BINLOG) > fi > > +if ! [ -z $WSREP_SST_OPT_BINLOG_INDEX ] > +then > + BINLOG_INDEX_DIRNAME=$(dirname $WSREP_SST_OPT_BINLOG_INDEX) > + BINLOG_INDEX_FILENAME=$(basename $WSREP_SST_OPT_BINLOG_INDEX) > +fi > + > WSREP_LOG_DIR=${WSREP_LOG_DIR:-""} > # if WSREP_LOG_DIR env. variable is not set, try to get it from my.cnf > if [ -z "$WSREP_LOG_DIR" ]; then > @@ -204,12 +210,20 @@ then > OLD_PWD="$(pwd)" > cd $BINLOG_DIRNAME > > - binlog_files_full=$(tail -n $BINLOG_N_FILES ${BINLOG_FILENAME}.index) > + if ! [ -z $WSREP_SST_OPT_BINLOG_INDEX ] first, I think it'd be simpler to do, like if [ -z $WSREP_SST_OPT_BINLOG_INDEX ] then WSREP_SST_OPT_BINLOG_INDEX=$WSREP_SST_OPT_BINLOG.index fi it's enough to do it once before setting BINLOG_INDEX_DIRNAME and BINLOG_INDEX_FILENAME and you woudn't need if's here and below. second, you apparently are trying to take into account that binlog index may be a different directory. Please use this configuration in your test case, put binlog index in a different directory. otherwise ok. please, push after adjusting the test case to put binlog index in a different directory. > + binlog_files_full=$(tail -n $BINLOG_N_FILES ${BINLOG_FILENAME}.index) > + then > + cd $BINLOG_INDEX_DIRNAME > + binlog_files_full=$(tail -n $BINLOG_N_FILES ${BINLOG_INDEX_FILENAME}.index) > + fi > + > + cd $BINLOG_DIRNAME > binlog_files="" > for ii in $binlog_files_full > do > binlog_files="$binlog_files $(basename $ii)" > done > + > if ! [ -z "$binlog_files" ] > then > wsrep_log_info "Preparing binlog files for transfer:" > @@ -391,7 +405,11 @@ EOF > tar -xvf $BINLOG_TAR_FILE >&2 > for ii in $(ls -1 ${BINLOG_FILENAME}.*) > do > - echo ${BINLOG_DIRNAME}/${ii} >> ${BINLOG_FILENAME}.index > + if ! [ -z $WSREP_SST_OPT_BINLOG_INDEX ] > + echo ${BINLOG_DIRNAME}/${ii} >> ${BINLOG_FILENAME}.index > + then > + echo ${BINLOG_DIRNAME}/${ii} >> ${BINLOG_INDEX_DIRNAME}/${BINLOG_INDEX_FILENAME}.index > + fi > done > fi > cd "$OLD_PWD" Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 6be8bd6: MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>.
by Sergei Golubchik 31 Aug '18

31 Aug '18

Hi, Alexey! On Jun 24, Alexey Botchkov wrote: > revision-id: 6be8bd6ee05c6f18b6a6a0375f9bc2b68ef6c43e (mariadb-10.3.7-59-g6be8bd6) > parent(s): 1abd877e2df9e83bc1c2f5195796f427a35bd3f1 > committer: Alexey Botchkov > timestamp: 2018-06-24 23:12:20 +0400 > message: > > MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>. > > Check if the argument of the FLUSH TABLE is a VIEW and handle it > accordingly. Looks great! But please add more tests. A test with a write lock, and tests with locking the view and flushing the table or vice versa. After that ok to push! Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] 10.2 and merges (e.g. TokuDB)
by Phil Sweeney 28 Aug '18

28 Aug '18

Hi there, It seems 10.2.6 is still shipping with TokuDB from PS 5.6.35-80 (Feb 8th 2017). I can't see a jira issue for a 10.2 merge since 10.2.4. Perhaps intentional to help stabilise 10.2 for release? 10.0.30 also shipped with the same Feb version (in March). 10.1.23 however was released in May and still has this version from Feb, which seems unfortunate, but I know it's because it has to flow through 10.0 first. I see 10.0.31 is due to get TokuDB from PS 5.6.36-82 And that should flow into 10.1.24 soon too. However, 10.2.7 isn't due until July, so by then TokuDB version will be 5 months old in 10.2 I'm waiting on a fairly important row count fix that should appear in the next Percona Server release. When it does, if the current merging strategy continues, do I have to first wait for 10.0.32 to get it in late July, then 10.1.26 in August, then 10.2 after that? Or with 10.0, 10.1 and 10.2 all being supported and needing merges, is there a new strategy on the cards to shorten this lag? Thanks! Phil

3 7

[Maria-developers] MDEV-371 Long unique Index
by Sachin Setiya 28 Aug '18

28 Aug '18

Hi Serg! Please review the patch for Unique Index For Long (MDEV-371) (It is in bb-10.4-blob-index branch) Architecture:- We will create a virtual column DB_ROW_HASH_X (X is any number which makes this field name unique) for each long unique column which will store hash. At the time of ha_write_row we will check for hash collusions if we find same hash then we will retrieve the blob and then compare byte by byte. key_info for long unique have one extra key_part which will point to hash field. For example unique(a,b) key will have 3 key_parts :- a ,b DB_ROW_HASH_X But on server layer we can just see a,b parts on storage layer we can just see DB_ROW_HASH_X key_part. I have created 2 function setup_table_hash and resetup_table which helps in this transition. If user specify length of blob then we use LEFT function to trim the value. -- Regards Sachin Setiya Software Engineer at MariaDB

1 0

Re: [Maria-developers] [Commits] f522fb9: MDEV-16934 Query with very large IN clause lists runs slowly
by Sergey Petrunia 17 Aug '18

17 Aug '18

Hello Igor, I am debugging with the patch and I see that PARAM::range_count is not reset after the eq_ranges_exceeds_limit() call. This causes different kinds of undesired effects. For example, here create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (pk int, kp1 int, kp2 int, kp3 int, col1 int, key(kp1,kp2,kp3), primary key(pk)); insert into t1 select A.a + 10*B.a+100* C.a, A.a, B.a , C.a, 1234 from ten A, ten B, ten C; alter table t1 add key3 int, add key2 int; update t1 set key3=kp1; update t1 set key2=kp1; alter table t1 add key(key2), add key(key3); set eq_range_index_dive_limit=10; explain format=json select 1 from t1 force index (key2, key3) where key3=1 or key2=1; index_merge will use Sort-Union instead of Union. I assume we are targeting only IN (...) cases? Both our and MySQL's fixes will still do many records_in_range calls for queries like keypart1 IN (1,2,3,...) AND keypart2 IN (1,2,3,...) AND keypart3 >= 10 (because here the ranges will not be equality ranges). I assume this is outside of the scope of this fix? If so, then the patch is ok to push after PARAM::range_count issue is resolved. On Wed, Aug 15, 2018 at 07:48:30PM -0700, IgorBabaev wrote: > revision-id: f522fb9f63f88195bccbbc0326a4a318cc2d3db0 (mariadb-10.2.16-50-gf522fb9) > parent(s): 3c141e319ab29afdfe843553da385fe5d981906e > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-08-15 19:48:30 -0700 > message: > > MDEV-16934 Query with very large IN clause lists runs slowly > > This patch introduces support for the system variable eq_range_index_dive_limit > that existed in MySQL starting from 5.6. The variable sets a limit for > index dives into equality ranges. Index dives are performed by optimizer > to estimate the number of rows in range scans. Index dives usually provide > good estimate but they are pretty expensive. To estimate the number of rows > in equality ranges statistical data on indexes can be employed. Its usage gives > not so good estimates but it's cheap. So if the number of equality dives > required by an index scan exceeds the set limit no dives for equality > ranges are performed by the optimizer for this index. > > As the new system variable is introduced in a stable version the default > value for it is set to a special value meaning there is no limit for the number > of index dives performed by the optimizer. > > The patch partially uses the MySQL code for WL 5957 > 'Statistics-based Range optimization for many ranges'. > > --- > mysql-test/r/mysqld--help.result | 6 +++ > mysql-test/r/range.result | 41 +++++++++++++++++ > mysql-test/r/range_mrr_icp.result | 41 +++++++++++++++++ > .../sys_vars/r/sysvars_server_embedded.result | 14 ++++++ > .../sys_vars/r/sysvars_server_notembedded.result | 14 ++++++ > mysql-test/t/range.test | 33 ++++++++++++++ > sql/multi_range_read.cc | 11 +++++ > sql/opt_range.cc | 27 +++++++++++ > sql/opt_range.h | 4 +- > sql/opt_range_mrr.cc | 53 +++++++++++++++------- > sql/sql_class.h | 1 + > sql/sql_statistics.h | 2 +- > sql/sys_vars.cc | 10 ++++ > 13 files changed, 238 insertions(+), 19 deletions(-) > > diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result > index 3b7d5e2..51ece33 100644 > --- a/mysql-test/r/mysqld--help.result > +++ b/mysql-test/r/mysqld--help.result > @@ -197,6 +197,11 @@ The following specify which files/extra groups are read (specified before remain > cache, etc) > --enforce-storage-engine=name > Force the use of a storage engine for new tables > + --eq-range-index-dive-limit=# > + The optimizer will use existing index statistics instead > + of doing index dives for equality ranges if the number of > + equality ranges for the index is larger than or equal to > + this number. If set to 0, index dives are always used. > --event-scheduler[=name] > Enable the event scheduler. Possible values are ON, OFF, > and DISABLED (keep the event scheduler completely > @@ -1259,6 +1264,7 @@ encrypt-binlog FALSE > encrypt-tmp-disk-tables FALSE > encrypt-tmp-files FALSE > enforce-storage-engine (No default value) > +eq-range-index-dive-limit 0 > event-scheduler OFF > expensive-subquery-limit 100 > expire-logs-days 0 > diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result > index 3a71d08..0c6be5e 100644 > --- a/mysql-test/r/range.result > +++ b/mysql-test/r/range.result > @@ -3002,5 +3002,46 @@ deallocate prepare stmt; > set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > # > +# MDEV-16934: using system variable eq_range_index_dive_limit > +# to reduce the number of index dives > +# > +create table t1 (a int, b varchar(31), index idx(a)); > +insert into t1 values > +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > +analyze table t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > +rec_per_key > +2 > +set eq_range_index_dive_limit=0; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +1 cc > +9 zzzzz > +15 ffffzz > +set eq_range_index_dive_limit=2; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +1 cc > +9 zzzzz > +15 ffffzz > +set eq_range_index_dive_limit=default; > +drop table t1; > +# > # End of 10.2 tests > # > diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result > index 799a299..93f414f 100644 > --- a/mysql-test/r/range_mrr_icp.result > +++ b/mysql-test/r/range_mrr_icp.result > @@ -3014,6 +3014,47 @@ deallocate prepare stmt; > set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > # > +# MDEV-16934: using system variable eq_range_index_dive_limit > +# to reduce the number of index dives > +# > +create table t1 (a int, b varchar(31), index idx(a)); > +insert into t1 values > +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > +analyze table t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > +rec_per_key > +2 > +set eq_range_index_dive_limit=0; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +9 zzzzz > +1 cc > +15 ffffzz > +set eq_range_index_dive_limit=2; > +explain select * from t1 where a in (8, 15, 31, 1, 9); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan > +select * from t1 where a in (8, 15, 31, 1, 9); > +a b > +1 yy > +1 bbb > +9 zzzzz > +1 cc > +15 ffffzz > +set eq_range_index_dive_limit=default; > +drop table t1; > +# > # End of 10.2 tests > # > set optimizer_switch=@mrr_icp_extra_tmp; > diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > index f7a5cf5..9d7fa10 100644 > --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result > @@ -765,6 +765,20 @@ NUMERIC_BLOCK_SIZE NULL > ENUM_VALUE_LIST NULL > READ_ONLY NO > COMMAND_LINE_ARGUMENT NULL > +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT > +SESSION_VALUE 0 > +GLOBAL_VALUE 0 > +GLOBAL_VALUE_ORIGIN COMPILE-TIME > +DEFAULT_VALUE 0 > +VARIABLE_SCOPE SESSION > +VARIABLE_TYPE INT UNSIGNED > +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. > +NUMERIC_MIN_VALUE 0 > +NUMERIC_MAX_VALUE 4294967295 > +NUMERIC_BLOCK_SIZE 1 > +ENUM_VALUE_LIST NULL > +READ_ONLY NO > +COMMAND_LINE_ARGUMENT REQUIRED > VARIABLE_NAME ERROR_COUNT > SESSION_VALUE 0 > GLOBAL_VALUE NULL > diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > index 034195e..cada139 100644 > --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result > @@ -779,6 +779,20 @@ NUMERIC_BLOCK_SIZE NULL > ENUM_VALUE_LIST NULL > READ_ONLY NO > COMMAND_LINE_ARGUMENT NULL > +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT > +SESSION_VALUE 0 > +GLOBAL_VALUE 0 > +GLOBAL_VALUE_ORIGIN COMPILE-TIME > +DEFAULT_VALUE 0 > +VARIABLE_SCOPE SESSION > +VARIABLE_TYPE INT UNSIGNED > +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. > +NUMERIC_MIN_VALUE 0 > +NUMERIC_MAX_VALUE 4294967295 > +NUMERIC_BLOCK_SIZE 1 > +ENUM_VALUE_LIST NULL > +READ_ONLY NO > +COMMAND_LINE_ARGUMENT REQUIRED > VARIABLE_NAME ERROR_COUNT > SESSION_VALUE 0 > GLOBAL_VALUE NULL > diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test > index ab95180..ed68cfb 100644 > --- a/mysql-test/t/range.test > +++ b/mysql-test/t/range.test > @@ -2046,6 +2046,39 @@ set optimizer_switch=@save_optimizer_switch; > drop table t1,t2,t3; > > --echo # > +--echo # MDEV-16934: using system variable eq_range_index_dive_limit > +--echo # to reduce the number of index dives > +--echo # > + > +create table t1 (a int, b varchar(31), index idx(a)); > + > +insert into t1 values > + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), > + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); > +insert into t1 select a+10, concat(b,'zz') from t1; > +insert into t1 select a+15, concat(b,'yy') from t1; > +insert into t1 select a+100, concat(b,'xx') from t1; > + > +analyze table t1; > + > +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; > + > +let $q= > +select * from t1 where a in (8, 15, 31, 1, 9); > + > +set eq_range_index_dive_limit=0; > +eval explain $q; > +eval $q; > + > +set eq_range_index_dive_limit=2; > +eval explain $q; > +eval $q; > + > +set eq_range_index_dive_limit=default; > + > +drop table t1; > + > +--echo # > --echo # End of 10.2 tests > --echo # > > diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc > index 50918d8..94a96c2 100644 > --- a/sql/multi_range_read.cc > +++ b/sql/multi_range_read.cc > @@ -17,6 +17,7 @@ > #include <my_bit.h> > #include "sql_select.h" > #include "key.h" > +#include "sql_statistics.h" > > /**************************************************************************** > * Default MRR implementation (MRR to non-MRR converter) > @@ -67,6 +68,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, > /* Default MRR implementation doesn't need buffer */ > *bufsz= 0; > > + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, > + seq_init_param); > + > seq_it= seq->init(seq_init_param, n_ranges, *flags); > while (!seq->next(seq_it, &range)) > { > @@ -87,8 +91,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, > min_endp= range.start_key.length? &range.start_key : NULL; > max_endp= range.end_key.length? &range.end_key : NULL; > } > + int keyparts_used= my_count_bits(range.start_key.keypart_map); > if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) > rows= 1; /* there can be at most one row */ > + else if (use_statistics_for_eq_range && > + !(range.range_flag & NULL_RANGE) && > + (range.range_flag & EQ_RANGE) && > + table->key_info[keyno].actual_rec_per_key(keyparts_used - 1) > 0.5) > + rows= > + (ha_rows) table->key_info[keyno].actual_rec_per_key(keyparts_used - 1); > else > { > if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, > diff --git a/sql/opt_range.cc b/sql/opt_range.cc > index b011c01..c9c37a2 100644 > --- a/sql/opt_range.cc > +++ b/sql/opt_range.cc > @@ -14616,6 +14616,33 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, > } > > > +/* Check whether the number for equality ranges exceeds the set threshold */ > + > +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) > +{ > + KEY_MULTI_RANGE range; > + range_seq_t seq_it; > + uint count = 0; > + PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; > + uint limit= param->thd->variables.eq_range_index_dive_limit; > + > + if (limit == 0) > + { > + /* 'Statistics instead of index dives' feature is turned off */ > + return false; > + } > + seq_it= seq->init(seq_init_param, 0, 0); > + while (!seq->next(seq_it, &range)) > + { > + if ((range.range_flag & EQ_RANGE) && !(range.range_flag & NULL_RANGE)) > + { > + if (++count >= limit) > + return true; > + } > + } > + return false; > +} > + > #ifndef DBUG_OFF > > static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map, > diff --git a/sql/opt_range.h b/sql/opt_range.h > index c1f7079..6698c98 100644 > --- a/sql/opt_range.h > +++ b/sql/opt_range.h > @@ -242,7 +242,7 @@ class SEL_ARG :public Sql_alloc > Number of children of this element in the RB-tree, plus 1 for this > element itself. > */ > - uint16 elements; > + uint32 elements; > /* > Valid only for elements which are RB-tree roots: Number of times this > RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by > @@ -1664,6 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, > > bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); > > +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); > + > #ifdef WITH_PARTITION_STORAGE_ENGINE > bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); > #endif > diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc > index ace6208..7f80a3e 100644 > --- a/sql/opt_range_mrr.cc > +++ b/sql/opt_range_mrr.cc > @@ -272,25 +272,44 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) > key_info= NULL; > else > key_info= &seq->param->table->key_info[seq->real_keyno]; > - > + > /* > - Conditions below: > - (1) - range analysis is used for estimating condition selectivity > - (2) - This is a unique key, and we have conditions for all its > - user-defined key parts. > - (3) - The table uses extended keys, this key covers all components, > - and we have conditions for all key parts. > + This is an equality range (keypart_0=X and ... and keypart_n=Z) if > + (1) - There are no flags indicating open range (e.g., > + "keypart_x > y") or GIS. > + (2) - The lower bound and the upper bound of the range has the > + same value (min_key == max_key). > */ > - if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && > - (!key_info || // (1) > - ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) > - key_info->flags & HA_NOSAME) || // (2) > - ((key_info->flags & HA_EXT_NOSAME) && // (3) > - (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) > - ) && > - range->start_key.length == range->end_key.length && > - !memcmp(seq->param->min_key,seq->param->max_key,range->start_key.length)) > - range->range_flag= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); > + const uint is_open_range = > + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | NEAR_MAX | GEOM_FLAG); > + const bool is_eq_range_pred = > + !(cur->min_key_flag & is_open_range) && // (1) > + !(cur->max_key_flag & is_open_range) && // (1) > + range->start_key.length == range->end_key.length && // (2) > + !memcmp(seq->param->min_key, seq->param->max_key, // (2) > + range->start_key.length); > + > + if (is_eq_range_pred) > + { > + range->range_flag = EQ_RANGE; > + > + /* > + Conditions below: > + (1) - Range analysis is used for estimating condition selectivity > + (2) - This is a unique key, and we have conditions for all its > + user-defined key parts. > + (3) - The table uses extended keys, this key covers all components, > + and we have conditions for all key parts. > + */ > + if ( > + !key_info || // (1) > + ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) > + key_info->flags & HA_NOSAME) || // (2) > + ((key_info->flags & HA_EXT_NOSAME) && // (3) > + (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) > + ) > + range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); > + } > > if (seq->param->is_ror_scan) > { > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 6663d9e..d2f00b2d 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -569,6 +569,7 @@ typedef struct system_variables > ha_rows max_join_size; > ha_rows expensive_subquery_limit; > ulong auto_increment_increment, auto_increment_offset; > + uint eq_range_index_dive_limit; > ulong lock_wait_timeout; > ulong join_cache_level; > ulong max_allowed_packet; > diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h > index f465838..4ff85cd 100644 > --- a/sql/sql_statistics.h > +++ b/sql/sql_statistics.h > @@ -21,7 +21,7 @@ enum enum_use_stat_tables_mode > { > NEVER, > COMPLEMENTARY, > - PEFERABLY, > + PREFERABLY, > } Use_stat_tables_mode; > > typedef > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index e6aec47..650b17a 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -2578,6 +2578,16 @@ static Sys_var_ulong Sys_div_precincrement( > SESSION_VAR(div_precincrement), CMD_LINE(REQUIRED_ARG), > VALID_RANGE(0, DECIMAL_MAX_SCALE), DEFAULT(4), BLOCK_SIZE(1)); > > +static Sys_var_uint Sys_eq_range_index_dive_limit( > + "eq_range_index_dive_limit", > + "The optimizer will use existing index statistics instead of " > + "doing index dives for equality ranges if the number of equality " > + "ranges for the index is larger than or equal to this number. " > + "If set to 0, index dives are always used.", > + SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG), > + VALID_RANGE(0, UINT_MAX32), DEFAULT(0), > + BLOCK_SIZE(1)); > + > static Sys_var_ulong Sys_range_alloc_block_size( > "range_alloc_block_size", > "Allocation block size for storing ranges during optimization", > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] adaafcf5184: MDEV-16930: Crash when VALUES in derived table contains expressions
by Sergei Golubchik 16 Aug '18

16 Aug '18

Hi, Oleksandr! On Aug 16, Oleksandr Byelkin wrote: > revision-id: adaafcf5184375369c0c4b3ccb1e82e846024ef8 (mariadb-10.3.7-137-gadaafcf5184) > parent(s): 8716bb3b72b7f5fed69b6dde413c2138f6d175b2 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-08-16 13:55:36 +0200 > message: > > MDEV-16930: Crash when VALUES in derived table contains expressions > > Give names to the value constructor columns as in SELECT-list. > > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index f915895a260..20cea4959eb 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -13306,15 +13306,20 @@ opt_values: > ; > > values: > - values ',' expr_or_default > + values ',' remember_name expr_or_default remember_end > { > - if (unlikely(Lex->insert_list->push_back($3, thd->mem_root))) > + if (unlikely(Lex->insert_list->push_back($4, thd->mem_root))) > MYSQL_YYABORT; > + // give some name in case of using in table value constuctor (TVC) > + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); > + > } > - | expr_or_default > + | remember_name expr_or_default remember_end > { > - if (unlikely(Lex->insert_list->push_back($1, thd->mem_root))) > + if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) > MYSQL_YYABORT; > + // give some name in case of using in table value constuctor (TVC) > + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); > } > ; Could you please create a special rule for that? Like named_value: remember_name expr_or_default remember_end { if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); } values: values ',' named_value | named_value ; if it'll work, that is Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 7fc57734575: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
by Oleksandr Byelkin 15 Aug '18

15 Aug '18

Hi! See small comment inline. (fix and OK to push) Am 15.08.2018 um 13:36 schrieb Sergey Petrunia: > MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > > Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever > it saw that one of SELECTs in the Subquery's UNION is degenerate. It > ignored the fact that other parts of the UNION might not be inexpensive, > including the case where pther parts of the UNION have no query plan yet. > > For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar') > this would cause the query to be considered inexpensive when there is > no query plan for the second part of the UNION, which in turn would > cause the SELECT 'foo' to compute and free itself while still inside > JOIN::optimize for that SELECT (See MDEV comment for full description). > > diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result > index 79bca38..200682b 100644 > --- a/mysql-test/r/subselect_extra_no_semijoin.result > +++ b/mysql-test/r/subselect_extra_no_semijoin.result > @@ -482,3 +482,22 @@ DROP TABLE t1,t2; > set optimizer_switch= @tmp_subselect_extra_derived; > set optimizer_switch= @subselect_extra_no_sj_tmp; > set @optimizer_switch_for_subselect_extra_test=null; > +# > +# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > +# > +SET NAMES tis620; > +set @tmp= @@global.slow_query_log; > +SET GLOBAL slow_query_log = 1; > +SET long_query_time = 0.000001; > +SET log_slow_verbosity = 'explain'; > +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); > +a > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); > +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' > +create table t2 (b int); > +insert into t2 values (1),(2),(3); > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); > +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' > +drop table t1,t2; > +SET GLOBAL slow_query_log=@tmp; > diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test > index 8aba3dd..41593fb 100644 > --- a/mysql-test/t/subselect_extra_no_semijoin.test > +++ b/mysql-test/t/subselect_extra_no_semijoin.test > @@ -6,4 +6,34 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo > > set optimizer_switch= @subselect_extra_no_sj_tmp; > > -set @optimizer_switch_for_subselect_extra_test=null; > \ No newline at end of file > +set @optimizer_switch_for_subselect_extra_test=null; > + > +--echo # > +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset > +--echo # > + > +## Using a separate client connection is easier than restoring state > +connect(con1,localhost,root,,); > + > +SET NAMES tis620; > +set @tmp= @@global.slow_query_log; > +SET GLOBAL slow_query_log = 1; > +SET long_query_time = 0.000001; > +SET log_slow_verbosity = 'explain'; > + please remove above space at the end of line and push. > +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); > +--error ER_CANT_AGGREGATE_2COLLATIONS > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); > + > +create table t2 (b int); > +insert into t2 values (1),(2),(3); > + > +--error ER_CANT_AGGREGATE_2COLLATIONS > +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); > + > +drop table t1,t2; > +SET GLOBAL slow_query_log=@tmp; > +disconnect con1; > +connection default; > + > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index a8dfdff..14b2ccd 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -575,7 +575,7 @@ bool Item_subselect::is_expensive() > */ > if (cur_join->optimized && > (cur_join->zero_result_cause || !cur_join->tables_list)) > - return false; > + continue; > > /* > If a subquery is not optimized we cannot estimate its cost. A subquery is

1 0