- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] a6ab8ef: MDEV-5997 - MySQL bug#11759114 - '51401: GRANT TREATS NONEXISTENT
by Sergei Golubchik 23 Jul '15

23 Jul '15

Hi, Sergey! On Jul 23, Sergey Vojtovich wrote: > revision-id: a6ab8ef9d7d465a3f0bc1cff6034ca97575629dd > parent(s): d897015d74d82464a21ed154f606adc69503c372 > committer: Sergey Vojtovich > branch nick: mariadb > timestamp: 2015-07-23 16:45:20 +0400 > message: > > MDEV-5997 - MySQL bug#11759114 - '51401: GRANT TREATS NONEXISTENT > FUNCTIONS/PRIVILEGES DIFFERENTLY' ok to push Regards, Sergei

1 0

Re: [Maria-developers] [Commits] 74b4ead: MDEV-5096 - Wrong error message on attempt to kill somebody else's query ID
by Sergei Golubchik 23 Jul '15

23 Jul '15

Hi, Sergey! On Feb 09, svoj(a)mariadb.org wrote: > revision-id: 74b4ead8ef32eb2bb96b17dafac514ffba28963a > parent(s): f13939061d468a47985dee0268652a6fe2db7862 > committer: Sergey Vojtovich > branch nick: 10.1 > timestamp: 2015-02-09 13:41:24 +0400 > message: > > MDEV-5096 - Wrong error message on attempt to kill somebody else's query ID > > Attempting to kill query owned by different user now returns better error: > "You are not owner of query N" instead of "You are not owner of thread N". Ok to push! Nice test. Regards, Sergei

1 0

[Maria-developers] MariaDB parallel replication
by Kristian Nielsen 18 Jul '15

18 Jul '15

Some interesting work is starting to be done by users to test parallel replication on real workloads. One issue that comes up is the performance on workloads that have a relatively high number of lock conflicts between transactions. I made some patches (against latest 10.1) to help investigate and improve the issues, and I wanted to mention them here in case someone wants to comment on or experiment with them: 1. Adding status variables for parallel replication. http://lists.askmonty.org/pipermail/commits/2015-July/008131.html This patch adds new status variables that measure the time spent by parallel replication worker threads being - Idle (waiting for work from the SQL thread). - Processing relay log events. - Waiting for a prior group commit to finish (measuring the overhead of insufficient parallelism recorded on the master, in conservative mode, or the overhead of serialisation around DDL, in optimistic/aggressive modes). - Waiting for the immediately prior transaction to commit (measuring the overhead of in-order parallel replication). - Rolling back and re-executing events due to deadlocks. It would be interesting to see how the different numbers compare on various workloads and parallel replication modes. 2. More aggressive retry of conflicting transactions. http://lists.askmonty.org/pipermail/commits/2015-July/008133.html When we get a deadlock and a transaction retry, the current MariaDB waits for all prior transactions to commit before retrying. The logic is that since we already got a conflict, there is a high risk that an immediate retry will just give another conflict. So if we have T1 T2 T3 T4, and T4 conflicts with T1, we roll back T4, wait for T3 to commit, and only then retry T4. If we have many such conflicts, we could end up wasting a lot of times on such waits. This patch changes aggressive mode so that T4 will only wait for T1 to commit, then it will retry. This allows T4 to run in parallel with T2 and T3. It will be interesting to see if this improves throughput in aggressive mode in some workloads, and also to see if/how much it increases the number of transaction retries and associated overhead. 3. Debug patch to log all row lock waits. http://lists.askmonty.org/pipermail/commits/2015-July/008132.html This is not a patch suitable for production use. It adds an option --gtid-log-all-lock-conflicts. When enabled, whenever one parallel replication transaction needs to wait on the InnoDB row lock of another, it will log a line to the error log, including the GTIDs of the transactions. This could be used to correlate back with the binlog and study exactly which transactions it is that are conflicting with each other. Such results could again be highly interesting. - Kristian.

1 1

[Maria-developers] MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column
by Alexander Barkov 15 Jul '15

15 Jul '15

Hi Sanja, Please review a patch for MDEV-8441. Thanks.

2 1

[Maria-developers] MDEV-4682 plugin - qc_info - add Query Cache flags - not-GSoc2015 patch for review
by Daniel Black 14 Jul '15

14 Jul '15

This expands out all the query cache info from the flags that wasn't previously available in the information_schema.query_cache_info table by the query_cache_info plugin. https://github.com/MariaDB/server/pull/67 -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.

1 0

Re: [Maria-developers] Likely a dead optimizer related code
by Michael Widenius 13 Jul '15

13 Jul '15

Hi! > Hi Igor, > > I noticed that during Item_cond::fix_fields() and > Item_func_between::fix_fields() update these optimizer > related SELECT_LEX members: > > thd->lex->current_select->cond_count and > thd->lex->current_select->between_count > > The purpose of these members is to allocate optimizer related buffers > in: > - update_ref_and_keys() in sql_select.cc and > - check_func_dependency() in opt_table_elimination,cc > and it seems they have no any other purposes.. Correct. > From my understanding, this is a dead code and the collected values are > never used: > > - cond_count and_between_count are later always initialized to 0 > before the walk(count_sargable_conds) call in update_ref_and_keys(). And the call to check_func_dependency() is done after this call. > - moreover, using the values collected during fix_fields() would not be > correct to allocate optimized related buffers, because the code > in fix_fields() does not distinguish between different query parts, > so the items can be just a part of the SELECT list rather than a > part of WHERE/HAVING/ON. This would note be a problem as the count was supposed to be an upper bound. As it's not normal that you have many IF or BETWEEN in the SELECT part, this was not a problem. I looked at the patch and it's ok to commit. Regards, Monty

2 1

Re: [Maria-developers] Probably in a bug in simple_pred()
by Michael Widenius 09 Jul '15

09 Jul '15

Hi! > From: Alexander Barkov <bar(a)mariadb.org> > Hi Sergey, > > I probably found a bug in simple_pred() in opt_sum.cc. > > In this piece of code: > >> case 3: >> /* field BETWEEN const AND const */ >> item= func_item->arguments()[0]->real_item(); >> if (item->type() == Item::FIELD_ITEM) >> { >> args[0]= item; >> for (int i= 1 ; i <= 2; i++) >> { >> item= func_item->arguments()[i]->real_item(); >> if (!item->const_item()) >> return 0; >> args[i]= item; >> if (check_item1_shorter_item2(args[0], args[1])) > > > Should't it be > > if (check_item1_shorter_item2(args[0], args[i])) > > ? > >> return 0; >> } >> } > > > If this is a bug, can you please help to make an SQL scrip which would > be affected by this bug? yes, it's a bug. Here is a script that shows that: drop table if exists t1; create table t1 (a varchar(10), key (a)); insert into t1 values("bar"),("Cafe"); explain select min(a) from t1 where a between "a" and "Cafe2"; explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee"; explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; The output is: 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | For the two first cases and: | 1 | SIMPLE | t1 | index | a | a | 13 | NULL | 2 | Using where; Using index | for the last The second case should have the same output as the third. Will fix and push into 10.1 Regards, Monty

1 0

[Maria-developers] MDEV-4119 patch comments
by Sergey Petrunia 07 Jul '15

07 Jul '15

Hi Monty, Please find my comments on the patch for MDEV-4119 below. Sorry for the delay. > diff --git a/sql/item.cc b/sql/item.cc > index b4de973..3b1f8b7 100644 > --- a/sql/item.cc > +++ b/sql/item.cc > @@ -1705,69 +1705,98 @@ class Item_aggregate_ref : public Item_ref > > @param thd Thread handler > @param ref_pointer_array Pointer to array of reference fields > - @param fields All fields in select > + @param fields All fields in select > @param ref Pointer to item > - @param skip_registered <=> function be must skipped for registered > - SUM items > + @param split_flags Zero or more of the following flags > + SPLIT_FUNC_SKIP_REGISTERED: > + Function be must skipped for registered SUM > + SUM items > + SPLIT_FUNC_SELECT > + We are called on the select level and have to > + register items operated on sum function Typo, it's not SPLIT_FUNC_SELECT, it is SPLIT_SUM_SELECT > > @note > - This is from split_sum_func2() for items that should be split > - > All found SUM items are added FIRST in the fields list and > we replace the item with a reference. > > + If this is an item in the SELECT list then we also have to split out > + all arguments to functions used together with the sum function. > + For example in case of SELECT A*sum(B) we have to split out both > + A and sum(B). > + This is not needed for ORDER BY, GROUP BY or HAVING as all references > + to items in the select list are already of type REF > + > thd->fatal_error() may be called if we are out of memory > */ > > void Item::split_sum_func2(THD *thd, Item **ref_pointer_array, > List<Item> &fields, Item **ref, > - bool skip_registered) > + uint split_flags) > { > - /* An item of type Item_sum is registered <=> ref_by != 0 */ > - if (type() == SUM_FUNC_ITEM && skip_registered && > - ((Item_sum *) this)->ref_by) > - return; > - if ((type() != SUM_FUNC_ITEM && with_sum_func) || > - (type() == FUNC_ITEM && > - (((Item_func *) this)->functype() == Item_func::ISNOTNULLTEST_FUNC || > - ((Item_func *) this)->functype() == Item_func::TRIG_COND_FUNC))) > + if (unlikely(type() == SUM_FUNC_ITEM)) > { > - /* Will split complicated items and ignore simple ones */ > - split_sum_func(thd, ref_pointer_array, fields); > + /* An item of type Item_sum is registered if ref_by != 0 */ > + if ((split_flags & SPLIT_SUM_SKIP_REGISTERED) && > + ((Item_sum *) this)->ref_by) > + return; > } > - else if ((type() == SUM_FUNC_ITEM || (used_tables() & ~PARAM_TABLE_BIT)) && > - type() != SUBSELECT_ITEM && > - (type() != REF_ITEM || > - ((Item_ref*)this)->ref_type() == Item_ref::VIEW_REF)) > + else > { > - /* > - Replace item with a reference so that we can easily calculate > - it (in case of sum functions) or copy it (in case of fields) > - > - The test above is to ensure we don't do a reference for things > - that are constants (PARAM_TABLE_BIT is in effect a constant) > - or already referenced (for example an item in HAVING) > - Exception is Item_direct_view_ref which we need to convert to > - Item_ref to allow fields from view being stored in tmp table. > - */ > - Item_aggregate_ref *item_ref; > - uint el= fields.elements; > - /* > - If this is an item_ref, get the original item > - This is a safety measure if this is called for things that is > - already a reference. > - */ > - Item *real_itm= real_item(); > + /* Not a SUM() function */ > + if (unlikely((!with_sum_func && !(split_flags & SPLIT_SUM_SELECT)))) > + { > + /* > + This is not a SUM function and there are no SUM functions inside. > + Nothing more to do. > + */ > + return; > + } > + if (likely(with_sum_func || > + (type() == FUNC_ITEM && > + (((Item_func *) this)->functype() == > + Item_func::ISNOTNULLTEST_FUNC || > + ((Item_func *) this)->functype() == > + Item_func::TRIG_COND_FUNC)))) > + { > + /* Will call split_sum_func2() for all items */ > + split_sum_func(thd, ref_pointer_array, fields, split_flags); > + return; > + } > > - ref_pointer_array[el]= real_itm; > - if (!(item_ref= new Item_aggregate_ref(&thd->lex->current_select->context, > - ref_pointer_array + el, 0, name))) > - return; // fatal_error is set > - if (type() == SUM_FUNC_ITEM) > - item_ref->depended_from= ((Item_sum *) this)->depended_from(); > - fields.push_front(real_itm); > - thd->change_item_tree(ref, item_ref); > + if (unlikely((!(used_tables() & ~PARAM_TABLE_BIT) || > + type() == SUBSELECT_ITEM || > + (type() == REF_ITEM && > + ((Item_ref*)this)->ref_type() != Item_ref::VIEW_REF)))) > + return; > } > + > + /* > + Replace item with a reference so that we can easily calculate > + it (in case of sum functions) or copy it (in case of fields) > + > + The test above is to ensure we don't do a reference for things > + that are constants (PARAM_TABLE_BIT is in effect a constant) > + or already referenced (for example an item in HAVING) > + Exception is Item_direct_view_ref which we need to convert to > + Item_ref to allow fields from view being stored in tmp table. > + */ > + Item_aggregate_ref *item_ref; > + uint el= fields.elements; > + /* > + If this is an item_ref, get the original item > + This is a safety measure if this is called for things that is > + already a reference. > + */ > + Item *real_itm= real_item(); > + > + ref_pointer_array[el]= real_itm; > + if (!(item_ref= new Item_aggregate_ref(&thd->lex->current_select->context, > + ref_pointer_array + el, 0, name))) > + return; // fatal_error is set > + if (type() == SUM_FUNC_ITEM) > + item_ref->depended_from= ((Item_sum *) this)->depended_from(); > + fields.push_front(real_itm); > + thd->change_item_tree(ref, item_ref); > } > > > diff --git a/sql/item.h b/sql/item.h > index 8254359..44649bf 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -74,6 +74,10 @@ char_to_byte_length_safe(uint32 char_length_arg, uint32 mbmaxlen_arg) > } > > > +/* Bits for the split_sum_func() function */ > +#define SPLIT_SUM_SKIP_REGISTERED 1 /* Skip registered funcs */ > +#define SPLIT_SUM_SELECT 2 /* SELECT item; Split all parts */ > + > /* > "Declared Type Collation" > A combination of collation and its derivation. > @@ -1169,10 +1173,10 @@ class Item: public Type_std_attributes > return false; > } > virtual void split_sum_func(THD *thd, Item **ref_pointer_array, > - List<Item> &fields) {} > + List<Item> &fields, uint flags) {} > /* Called for items that really have to be split */ > void split_sum_func2(THD *thd, Item **ref_pointer_array, List<Item> &fields, > - Item **ref, bool skip_registered); > + Item **ref, uint flags); > virtual bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); > bool get_time(MYSQL_TIME *ltime) > { return get_date(ltime, TIME_TIME_ONLY | TIME_INVALID_DATES); } > diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc > index 7b34e64..caa645d 100644 > --- a/sql/item_cmpfunc.cc > +++ b/sql/item_cmpfunc.cc > @@ -4575,12 +4575,13 @@ void Item_cond::traverse_cond(Cond_traverser traverser, > */ > > void Item_cond::split_sum_func(THD *thd, Item **ref_pointer_array, > - List<Item> &fields) > + List<Item> &fields, uint flags) > { > List_iterator<Item> li(list); > Item *item; > while ((item= li++)) > - item->split_sum_func2(thd, ref_pointer_array, fields, li.ref(), TRUE); > + item->split_sum_func2(thd, ref_pointer_array, fields, li.ref(), > + flags | SPLIT_SUM_SKIP_REGISTERED); Why not clear SPLIT_SUM_SELECT flag here? The Item_cond may be at the select list, but its parts are definitely not in the select list? The same applies to Item_row::split_sum_func and Item_func::split_sum_func. > } > > > diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h > index 7d89cf5..ceab597 100644 > --- a/sql/item_cmpfunc.h > +++ b/sql/item_cmpfunc.h > @@ -1759,7 +1759,8 @@ class Item_cond :public Item_bool_func > SARGABLE_PARAM **sargables); > SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr); > virtual void print(String *str, enum_query_type query_type); > - void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields); > + void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields, > + uint flags); > friend int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, > COND **conds); > void top_level_item() { abort_on_null=1; } > diff --git a/sql/item_func.cc b/sql/item_func.cc > index 3e78be8..684724b 100644 > --- a/sql/item_func.cc > +++ b/sql/item_func.cc > @@ -419,11 +419,12 @@ Item *Item_func::compile(Item_analyzer analyzer, uchar **arg_p, > */ > > void Item_func::split_sum_func(THD *thd, Item **ref_pointer_array, > - List<Item> &fields) > + List<Item> &fields, uint flags) > { > Item **arg, **arg_end; > for (arg= args, arg_end= args+arg_count; arg != arg_end ; arg++) > - (*arg)->split_sum_func2(thd, ref_pointer_array, fields, arg, TRUE); > + (*arg)->split_sum_func2(thd, ref_pointer_array, fields, arg, > + flags | SPLIT_SUM_SKIP_REGISTERED); Why not clear SPLIT_SUM_SELECT flag here? (same question as in Item_cond::split_sum_func) > } > > ........ > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 4a50003..ce02484 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -1944,8 +1954,9 @@ int JOIN::init_execution() > /* > Enable LIMIT ROWS EXAMINED during query execution if: > (1) This JOIN is the outermost query (not a subquery or derived table) > - This ensures that the limit is enabled when actual execution begins, and > - not if a subquery is evaluated during optimization of the outer query. > + This ensures that the limit is enabled when actual execution begins, > + and not if a subquery is evaluated during optimization of the outer > + query. > (2) This JOIN is not the result of a UNION. In this case do not apply the > limit in order to produce the partial query result stored in the > UNION temp table. > @@ -2562,8 +2573,12 @@ void JOIN::exec_inner() > skip_sort_order= 0; > } > bool made_call= false; > + SQL_SELECT *select= join_tab[const_tables].select; This line causes a crash in a few tests: main.select_pkeycache main.select main.select_jcl6 main.subselect3 main.subselect3_jcl6 The reason is that execution gets here when the join is degenerate and join_tab==NULL. > if (order && > - (order != group_list || !(select_options & SELECT_BIG_RESULT)) && > + (order != group_list || !(select_options & SELECT_BIG_RESULT) || > + (select && select->quick && > + select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) > + && > (const_tables == table_count || > ((simple_order || skip_sort_order) && > (made_call=true) && BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] MDEV-8433 Make field<'broken-string' use indexes
by Alexander Barkov 07 Jul '15

07 Jul '15

Hi Sergei, I created a task according to our discussion a few weeks ago: https://mariadb.atlassian.net/browse/MDEV-8433 Which version should we do this task in? Thanks.

1 0

Re: [Maria-developers] GSoc2015: MDEV-8356 first patch
by Sergey Petrunia 03 Jul '15

03 Jul '15

Hi Diwas, On Fri, Jul 03, 2015 at 02:54:28PM +0530, Diwas Joshi wrote: > hello sergey, I have fixed the issues from the previous patch in this one. > Please find some feedback below. After you have addressed it, please commit the change and push it to your tree. The next task to focus on after this one is MDEV-8389. > diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result > index 897895e..cacfeea 100644 > --- a/mysql-test/r/sp-error.result > +++ b/mysql-test/r/sp-error.result > @@ -2866,12 +2866,6 @@ SELECT @msg; > DROP FUNCTION f1; > DROP FUNCTION f2; > DROP TABLE t1; > -CREATE FUNCTION f1(a INT, b VARCHAR(11)) > -RETURNS TABLE t1(id INT, name VARCHAR(11)) > -BEGIN > -INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > -END| > -ERROR 42000: This version of MariaDB doesn't yet support 'Table functions' > SELECT * FROM f1('xyz'); > ERROR 42000: This version of MariaDB doesn't yet support 'Queries using Table Functions' > SELECT * FROM t1, f1('xyz',t1.a); > diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result > index 3f28df9..2a931b1 100644 > --- a/mysql-test/r/sp.result > +++ b/mysql-test/r/sp.result > @@ -7937,3 +7937,17 @@ return 1; > end | > ERROR 0A000: Not allowed to return a result set from a function > drop table t1,t2; > +# Tests for Table functions > +CREATE FUNCTION f1(a INT, b VARCHAR(11)) > +RETURNS TABLE t1(id INT, name VARCHAR(11)) > +BEGIN > +INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > +END| Please add "--vertical_results" here so that the SELECT results are printed vertically. It will be easier to see what the return value is. > +SELECT * FROM mysql.proc WHERE db = database() and name = 'f1'; > +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 > +test f1 FUNCTION f1 SQL CONTAINS_SQL NO DEFINER a INT, b VARCHAR(11) TABLE t1(id int(11), name varchar(11)) BEGIN > +INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > +END root@localhost 2015-07-03 12:18:04 2015-07-03 12:18:04 latin1 latin1_swedish_ci latin1_swedish_ci BEGIN > +INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > +END > +DROP FUNCTION f1; -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0