- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Improve filesort performance for small sorts
by Sergei Golubchik 24 Mar '12

24 Mar '12

Hi, Kristian! FYI: http://bazaar.launchpad.net/~mysql/mysql-server/trunk/revision/tor.didrikse… On Mar 24, knielsen(a)knielsen-hq.org wrote: > ------------------------------------------------------------ > revno: 3471 > revision-id: knielsen(a)knielsen-hq.org-20120324160859-6pzy6tcxvd6j4fbx > parent: monty(a)askmonty.org-20120323162239-h18wbqpbjz0p00jd > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-5.3 > timestamp: Sat 2012-03-24 17:08:59 +0100 > message: > Improve filesort performance for small sorts: Don't write pointers to records that we will never use. Regards, Sergei

1 0

[Maria-developers] Igor please review: [Commits] Rev 3464: BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view in file:///home/psergey/dev2/5.3-look60/]
by Sergei Petrunia 24 Mar '12

24 Mar '12

Hello Igor, Could you please review the below: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sat, 24 Mar 2012 01:40:02 +0400 (GST) Subject: [Commits] Rev 3464: BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view in file:///home/psergey/dev2/5.3-look60/ At file:///home/psergey/dev2/5.3-look60/ ------------------------------------------------------------ revno: 3464 revision-id: psergey(a)askmonty.org-20120323213956-ohxb9jitekne7yya parent: psergey(a)askmonty.org-20120321071820-t2azo8xxlm73mezd committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-look60 timestamp: Sat 2012-03-24 01:39:56 +0400 message: BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view - Fix equality propagation to work with SJM nests and OR clauses (full descirption of problem and solution in the comment in the patch) === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2012-02-25 00:50:22 +0000 +++ b/mysql-test/r/subselect_sj2.result 2012-03-23 21:39:56 +0000 @@ -903,5 +903,48 @@ 3 1 1 4 1 1 DROP TABLE t1,t2; +# +# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +# +CREATE TABLE t1 ( +a VARCHAR(1), +b VARCHAR(1) NOT NULL, +KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM v1 +WHERE ( a, a ) IN ( +SELECT alias2.b, alias2.a +FROM t1 AS alias1, t1 AS alias2 +WHERE alias2.b = alias1.a +AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); +a b +j j +v v +c c +m m +m m +d d +d d +d d +y y +t t +s s +r r +b b +x x +g g +p p +q q +w w +e e +DROP VIEW v1; +DROP TABLE t1; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2012-02-25 00:50:22 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2012-03-23 21:39:56 +0000 @@ -917,6 +917,49 @@ 3 1 1 4 1 1 DROP TABLE t1,t2; +# +# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +# +CREATE TABLE t1 ( +a VARCHAR(1), +b VARCHAR(1) NOT NULL, +KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM v1 +WHERE ( a, a ) IN ( +SELECT alias2.b, alias2.a +FROM t1 AS alias1, t1 AS alias2 +WHERE alias2.b = alias1.a +AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); +a b +j j +v v +c c +m m +m m +d d +d d +d d +y y +t t +s s +r r +b b +x x +g g +p p +q q +w w +e e +DROP VIEW v1; +DROP TABLE t1; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # === modified file 'mysql-test/r/subselect_sj2_mat.result' --- a/mysql-test/r/subselect_sj2_mat.result 2012-02-25 00:50:22 +0000 +++ b/mysql-test/r/subselect_sj2_mat.result 2012-03-23 21:39:56 +0000 @@ -905,6 +905,49 @@ 3 1 1 4 1 1 DROP TABLE t1,t2; +# +# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +# +CREATE TABLE t1 ( +a VARCHAR(1), +b VARCHAR(1) NOT NULL, +KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM v1 +WHERE ( a, a ) IN ( +SELECT alias2.b, alias2.a +FROM t1 AS alias1, t1 AS alias2 +WHERE alias2.b = alias1.a +AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); +a b +j j +v v +c c +m m +m m +d d +d d +d d +y y +t t +s s +r r +b b +x x +g g +p p +q q +w w +e e +DROP VIEW v1; +DROP TABLE t1; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; === modified file 'mysql-test/t/subselect_sj2.test' --- a/mysql-test/t/subselect_sj2.test 2012-01-19 19:44:43 +0000 +++ b/mysql-test/t/subselect_sj2.test 2012-03-23 21:39:56 +0000 @@ -1090,5 +1090,32 @@ DROP TABLE t1,t2; +--echo # +--echo # BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +--echo # +CREATE TABLE t1 ( + a VARCHAR(1), + b VARCHAR(1) NOT NULL, + KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +# This query returns 6 rows instead of 19 + +SELECT * FROM v1 +WHERE ( a, a ) IN ( + SELECT alias2.b, alias2.a + FROM t1 AS alias1, t1 AS alias2 + WHERE alias2.b = alias1.a + AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); +DROP VIEW v1; +DROP TABLE t1; --echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2012-02-28 14:41:55 +0000 +++ b/sql/item_cmpfunc.cc 2012-03-23 21:39:56 +0000 @@ -5388,7 +5388,7 @@ */ Item_equal::Item_equal(Item *f1, Item *f2, bool with_const_item) - : Item_bool_func(), eval_item(0), cond_false(0) + : Item_bool_func(), eval_item(0), cond_false(0), context_field(NULL) { const_item_cache= 0; with_const= with_const_item; @@ -5411,7 +5411,7 @@ */ Item_equal::Item_equal(Item_equal *item_equal) - : Item_bool_func(), eval_item(0), cond_false(0) + : Item_bool_func(), eval_item(0), cond_false(0), context_field(NULL) { const_item_cache= 0; List_iterator_fast<Item> li(item_equal->equal_items); === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2012-01-19 22:11:53 +0000 +++ b/sql/item_cmpfunc.h 2012-03-23 21:39:56 +0000 @@ -1700,9 +1700,16 @@ as datetimes. The comparator is used only if compare_as_dates=TRUE */ Arg_comparator cmp; + + /* + For Item_equal objects inside an OR clause: one of the fields that were + used in the original comparison. + */ + Item_field *context_field; public: inline Item_equal() - : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0) + : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0), + context_field(NULL) { const_item_cache=0 ;} Item_equal(Item *f1, Item *f2, bool with_const_item); Item_equal(Item_equal *item_equal); @@ -1729,6 +1736,8 @@ Item *transform(Item_transformer transformer, uchar *arg); virtual void print(String *str, enum_query_type query_type); CHARSET_INFO *compare_collation(); + + void set_context_field(Item_field *ctx_field) { context_field= ctx_field; } friend class Item_equal_fields_iterator; friend Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, Item_equal *item_equal); === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2012-03-18 19:58:20 +0000 +++ b/sql/opt_subselect.cc 2012-03-23 21:39:56 +0000 @@ -171,6 +171,246 @@ */ +/* +EqualityPropagationAndSjmNests +****************************** + +The process of equality propagation has two parts +P1. Equality propagation +P2. Equality substitution [for a certain join order] + +The propagation part is not affected by SJM nests. In fact, it is done before +we determine the execution plan, i.e. before we even know we will use +SJM-nests for execution. + +The substitution part is affected. + +Substitution without SJMs +========================= +When one doesn't have SJM nests, tables have a strict join order: + + ---------------------------------> + t1 -- t2 -- t3 -- t4 --- t5 + + + ? ^ + \ + --(part-of-WHERE) + + +parts WHERE/ON and ref. expressions are attached at some point along the axis. +Expression is allowed to refer to a table column if the table is to the left of +the attachment point. For any given expression, we have a goal: + + "Move leftmost allowed attachment point as much as possible to the left" + +Substitution with SJMs - task setting +===================================== + +When SJM nests are present, there is no global strict table ordering anymore: + + + ---------------------------------> + + ot1 -- ot2 --- sjm -- ot4 --- ot5 + | + | Main execution + - - - - - - - - - - - - - - - - - - - - - - - - + | Materialization + it1 -- it2 --/ + + +Besides that, we must take into account that + - values for outer table columns, otN.col, are inaccessible at + materialization step (SJM-RULE) + - values for inner table columns, itN.col, are inaccessible at Main execution + step, except for SJ-Materialization-Scan and columns that are in the + subquery's select list. (SJM-RULE) + +Substitution with SJMs - solution +================================= + +First, we introduce global strict table ordering like this: + + ot1 - ot2 --\ /--- ot3 -- ot5 + \--- it1 --- it2 --/ + +Now, let's see how to meet (SJM-RULE). + +SJ-Materialization is only applicable for uncorrelated subqueries. From this, it +follows that any multiple equality will either +1. include only columns of outer tables, or +2. include only columns of inner tables, or +3. include columns of inner and outer tables, joined together through one + of IN-equalities. + +Cases #1 and #2 can be handled in the same way as with regular inner joins. + +Case #3 requires special handling, so that we don't construct violations of +(SJM-RULE). Let's consider possible ways to build violations. + +Equality propagation starts with the clause in this form + + top_query_where AND subquery_where AND in_equalities + +First, it builds multi-equalities. It can also build a mixed multi-equality + + multiple-equal(ot1.col, ot2.col, ... it1.col, itN.col) + +Multi-equalities are pushed down the OR-clauses in top_query_where and in +subquery_where, so it's possible that clauses like this one are built: + + subquery_cond OR (multiple-equal(it1.col, ot1.col,...) AND ...) + ^^^^^^^^^^^^^ \ + | this must be evaluated + \- can only be invaluated on the main phase. + on materialization phase + +Finally, equality substitution is started. It does two operations: + + +1. Field reference substitution +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +(In the code, this is Item_field::replace_equal_field) + +This is a process of replacing each reference to "tblX.col" +with the first element of the multi-equality. (REF-SUBST-ORIG) + +This behaviour can cause problems with Semi-join nests. Suppose, we have a +condition: + + func(it1.col, it2.col) + +and a multi-equality(ot1.col, it1.col). Then, reference to "it1.col" will be +replaced with "ot1.col", constructing a condition + + func(ot1.col, it2.col) + +which will be a vioation of (SJM-RULE). + +In order to avoid this, (REF-SUBST-ORIG) is amended as follows: + +- references to tables "itX.col" that are inner wrt some SJM nest, are + replaced with references to the first inner table from the same SJM nest. + +- references to top-level tables "otX.col" are replaced with references to + the first element of the multi-equality, no matter if that first element is + a column of a top-level table or of table from some SJM nest. + (REF-SUBST-SJM) + + The case where the first element is a table from an SJM nest $SJM is ok, + because it can be proven that $SJM uses SJ-Materialization-Scan, and + "unpacks" correct column values to the first element during the main + execution phase. + +2. Item_equal elimination +~~~~~~~~~~~~~~~~~~~~~~~~~ +(In the code: eliminate_item_equal) This is a process of taking + + multiple-equal(a,b,c,d,e) + +and replacing it with an equivalent expression which is an AND of pair-wise +equalities: + + a=b AND a=c AND ... + +The equalities are picked such that for any given join prefix (t1,t2...) the +subset of equalities that can be evaluated gives the most restrictive +filtering. + +Without SJM nests, it is sufficient to compare every multi-equality member +with the first one: + + elem1=elem2 AND elem1=elem3 AND elem1=elem4 ... + +When SJM nests are present, we should take care not to construct equalities +that violate the (SJM-RULE). This is achieved by generating separate sets of +equalites for top-level tables and for inner tables. That is, for the join +order + + ot1 - ot2 --\ /--- ot3 -- ot5 + \--- it1 --- it2 --/ + +we will generate + ot1.col=ot2.col + ot1.col=ot3.col + ot1.col=ot5.col + it2.col=it1.col + + +2.1 The problem with Item_equals and ORs +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +As has been mentioned above, multiple equalities are pushed down into OR +clauses, possibly building clauses like this: + + func(it.col2) OR multiple-equal(it1.col1, it1.col2, ot1.col) (1) + +where the first part of the clause has references to inner tables, while the +second has references to the top-level tables, which is a violation of +(SJM-RULE). + +AND-clauses of this kind do not create problems, because make_cond_for_table() +will take them apart. OR-clauses will not be split. It is possible to +split-out the part that's dependent on the inner table: + + func(it.col2) OR it1.col1=it1.col2 + +but this is a less-restrictive condition than condition (1). Current execution +scheme will still try to generate the "remainder" condition: + + func(it.col2) OR it1.col1=ot1.col + +which is a violation of (SJM-RULE). + +QQ: "ot1.col=it1.col" is checked at the upper level. Why was it not removed +here? +AA: because has a proper subset of conditions that are found on this level. + consider a join order of ot, sjm(it) + and a condition + ot.col=it.col AND ( ot.col=it.col='foo' OR it.col2='bar') + + we will produce: + table ot: nothing + table it: ot.col=it.col AND (ot.col='foo' OR it.col2='bar') + ^^^^ ^^^^^^^^^^^^^^^^ + | \ the problem is that + | this part condition didnt + | receive a substitution + | + +--- it was correct to subst, 'ot' is + the left-most. + + +Is there any merit in pushing inner=outer down into ORs? +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Yes, there is. Consider the query: + + select * from ot + where ot.col in (select it.col from it where (it.col='foo' OR it.col='bar')) + +here, it may be useful to infer that + + (ot.col='foo' OR ot.col='bar') (CASE-FOR-SUBST) + +and attach that condition to the table 'ot'. + +Solution #1 +~~~~~~~~~~~ +Let make_cond_for_table() chop off the bad parts of produced OR clause. If it +has produced an OR clause: +- check if all of its branches are evaluable for the context join_tab +- If some aren't, destroy the OR clause + +Solution #2 +~~~~~~~~~~~ +Before the equality propagation phase, none of the OR clauses violate the +(SJM-RULE). This way, if we remember which tables the original equality +referred to, we can only generate equalities that refer to outer or inner +tables. Note that this will disallow handling of cases like (CASE-FOR-SUBST). +*/ + static bool subquery_types_allow_materialization(Item_in_subselect *in_subs); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-03-13 20:49:18 +0000 +++ b/sql/sql_select.cc 2012-03-23 21:39:56 +0000 @@ -10666,6 +10666,9 @@ acceptable, as this happens rarely. The implementation without copying would be much more complicated. + For description of how equality propagation works with SJM nests, grep + for EqualityPropagationAndSjmNests. + @param left_item left term of the quality to be checked @param right_item right term of the equality to be checked @param item equality item if the equality originates from a condition @@ -10739,12 +10742,14 @@ { /* left_item_equal of an upper level contains left_item */ left_item_equal= new Item_equal(left_item_equal); + left_item_equal->set_context_field(((Item_field*) left_item)); cond_equal->current_level.push_back(left_item_equal); } if (right_copyfl) { /* right_item_equal of an upper level contains right_item */ right_item_equal= new Item_equal(right_item_equal); + right_item_equal->set_context_field(((Item_field*) right_item)); cond_equal->current_level.push_back(right_item_equal); } @@ -10830,6 +10835,7 @@ { item_equal= new Item_equal(item_equal); cond_equal->current_level.push_back(item_equal); + item_equal->set_context_field(field_item); } if (item_equal) { @@ -11480,6 +11486,8 @@ Item_equal::get_first() also takes similar measures for dealing with equality substitution in presense of SJM nests. + Grep for EqualityPropagationAndSjmNests for a more verbose description. + @return - The condition with generated simple equalities or a pointer to the simple generated equality, if success. @@ -11543,9 +11551,13 @@ on upper AND-levels. */ if (upper) - { + { + TABLE_LIST *native_sjm= embedding_sjm(item_equal->context_field); if (item_const && upper->get_const()) + { + /* Upper item also has "field_item=const". Don't produce equality here */ item= 0; + } else { Item_equal_fields_iterator li(*item_equal); @@ -11556,6 +11568,8 @@ break; } } + if (embedding_sjm(field_item) != native_sjm) + item= NULL; /* Don't produce equality */ } bool produce_equality= test(item == field_item); _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Autolinks to JIRA from the KB
by Daniel Bartholomew 21 Mar '12

21 Mar '12

All, I've created some autolinks for referring to JIRA tasks in the AskMonty Knowledgebase. I've documented how to use them on the "JIRA - project planning and tracking" page, but I wanted to make everyone here aware of them. http://kb.askmonty.org/en/jira-project-planning-and-tracking Basically, when you use mdev-xxx or todo-xxx (where xxx is the mdev or todo number) in a Knowledgebase article, the text will automatically be turned into a link to the task in JIRA. No need to copy the full url and paste it into the KB. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] Need to lock temporary tables?
by webmonkeys＠alibi.com 20 Mar '12

20 Mar '12

>Description: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() >How-To-Repeat: create table foo ( data int ); create temporary table tmp_foo (select * from foo limit 0); insert into tmp_foo (data) values (1); lock tables foo write; # add ", tmp_foo read" to the above, to avoid the error. #"LOCK TABLES is permitted (but ignored) for a TEMPORARY table." # -- http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html delete from foo; # The next statement causes # InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() insert into foo (select * from tmp_foo); unlock tables; drop table tmp_foo; drop table foo; >Fix: Lock the temporary table, though mysql docs say you shouldn't have to. >Submitter-Id: <submitter ID> >Originator: >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: temporary table wants a lock >Severity: non-critical >Priority: low >Category: mysql >Class: >Release: mysql-5.3.5-MariaDB-ga-mariadb113~squeeze ((MariaDB - http://mariadb.com/)) >C compiler: gcc-4.4.real (Debian 4.4.5-8) 4.4.5 >C++ compiler: g++-4.4.real (Debian 4.4.5-8) 4.4.5 >Environment: System: Linux machibashiri 2.6.32-5-amd64 #1 SMP Mon Mar 7 21:35:22 UTC 2011 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.4.5-8' --with-bugurl=file:///usr/share/doc/gcc-4.4/README.Bugs --enable-languages=c,c++,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.4 --enable-shared --enable-multiarch --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.4 --libdir=/usr/lib --enable-nls --enable-clocale=gnu --enable-libstdcxx-debug --enable-objc-gc --with-arch-32=i586 --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu Thread model: posix gcc version 4.4.5 (Debian 4.4.5-8) Compilation info (call): CC='gcc -static-libgcc' CFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' CXX='g++ -static-libgcc' CXXFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='gcc -static-libgcc' CFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -DUNIV_LINUX -DUNIV_LINUX' CXX='g++ -static-libgcc' CXXFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -fno-implicit-templates -fno-exceptions -fno-rtti' LDFLAGS=' -rdynamic ' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 14 Apr 13 2011 /lib/libc.so.6 -> libc-2.11.2.so -rwxr-xr-x 1 root root 1432968 Jan 23 2011 /lib/libc-2.11.2.so -rw-r--r-- 1 root root 4433334 Jan 23 2011 /usr/lib/libc.a -rw-r--r-- 1 root root 247 Jan 23 2011 /usr/lib/libc.so Configure command: ../configure '--build=x86_64-linux-gnu' '--host=x86_64-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-mariadb113~squeeze' '--with-comment=(MariaDB - http://mariadb.com/)' '--with-system-type=debian-linux-gnu' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-pstack' '--with-fast-mutexes' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-big-tables' '--without-docs' '--with-extra-charsets=all' '--with-libwrap' '--with-ssl=/usr' '--with-libevent' '--without-readline' '--with-zlib-dir=' '--with-embedded-server' '--with-embedded-privilege-control' '--with-plugins=max-no-ndb' 'build_alias=x86_64-linux-gnu' 'host_alias=x86_64-linux-gnu' 'CC=gcc -static-libgcc' 'CFLAGS=-O2! -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' 'LDFLAGS=' 'CPPFLAGS=' 'CXX=g++ -static-libgcc' 'CXXFLAGS=-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized'

1 0

[Maria-developers] New KB Question: mariadb compilation flags
by Daniel Bartholomew 19 Mar '12

19 Mar '12

A new question has been posted to the Knowledgebase: http://kb.askmonty.org/en/mariadb-compilation-flags ---------------------------------------------------------------------- how come mariadb is compiled on linux 64 bit with -O2 rather than -O3? mysql is compiled with -O3. does it make any difference? thanks! ---------------------------------------------------------------------- Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-developers] MariaDB 5.5.21
by Daniel Bartholomew 15 Mar '12

15 Mar '12

All, I've uploaded the MariaDB 5.5.21-beta packages to the primary mirror and within a few hours the files will be distributed to all of the mirrors. Over the past couple of weeks I've added several items to the release notes page for 5.5.21 and today I created the changelog page. The pages are here: http://kb.askmonty.org/en/mariadb-5521-release-notes http://kb.askmonty.org/en/mariadb-5521-changelog Once the files are distributed I will update the pages and add in the download links and remove the "unreleased" banners from the pages linked to from the release notes page. I will also send out an announcement email, and post announcements to twitter, FB, G+, and so on as usual. Please let me know ASAP if there is anything which is not on the release notes page which should be (or jump in and add it). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

4 4

Re: [Maria-developers] mariadb 5.3.5 build on OpenBSD 5.0
by Sergei 15 Mar '12

15 Mar '12

Hi! On Mar 14, U Orsini wrote: > I know this isn't a good way of submitting reports, but I really don't > want to join mailing lists and teams just for this. Hope you can still > use this information. Some of it likely affects other *BSDs too. A good way of submitting bug reports is bugs.launchpad.net/maria You don't need to join a team to use it. I've submitted a bug report on your behalf: https://bugs.launchpad.net/maria/+bug/955760 Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 3329: MDEV-15 Log SQL errors. in file:///home/hf/wmar/mdev15/
by Sergei Golubchik 14 Mar '12

14 Mar '12

Hi, Holyfoot! On Mar 14, holyfoot(a)askmonty.org wrote: > revno: 3329 > revision-id: holyfoot(a)askmonty.org-20120314123749-5kdm2u6qxrd1wtae > parent: holyfoot(a)askmonty.org-20120314054054-64q35vr6254t2ja6 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev15 > timestamp: Wed 2012-03-14 16:37:49 +0400 > message: > MDEV-15 Log SQL errors. > mysys/my_logger.c was moved to sql/sql_logger.c > Logger service was rewritten with file functions instead of stream, so it > can handle huge files. Ok for me Regards, Sergei

1 0

Re: [Maria-developers] MDEV-15 Log all SQL errors. in file:///home/hf/wmar/mdev15/
by Sergei Golubchik 14 Mar '12

14 Mar '12

Hi, Holyfoot! Just two comments, see below. Please fix and push ASAP! On Mar 13, holyfoot(a)askmonty.org wrote: > > ------------------------------------------------------------ > revno: 3320 > revision-id: holyfoot(a)askmonty.org-20120313160142-ljcsacc11zb34uu6 > parent: sergii(a)pisem.net-20120309082045-iyihz9ysqo82qb71 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev15 > timestamp: Tue 2012-03-13 20:01:42 +0400 > message: > MDEV-15 Log all SQL errors. > The logger service added to provide the rotating log functions. > The SQL_ERROR_LOG plugin created to log the SQL errors usint that logger service. > === added file 'include/mysql/service_logger.h' > --- a/include/mysql/service_logger.h 1970-01-01 00:00:00 +0000 > +++ b/include/mysql/service_logger.h 2012-03-13 16:01:42 +0000 > + > +typedef struct logger_service_file_st LOGGER_SERVICE_FILE; small comment: please, rename to LOGGER_HANDLE or LOGGER_DESCRIPTOR, or, say, MYSQL_LOGGER. The point is to avoid the word "FILE", because, in principle, it may log to syslog or to a table or whatever. (and better to avoid SERVICE too, because 1) this structure is not a service, it's a handle, 2) services are supposed to be transparent and invisible, they pretend to be regular function calls) > +maria_declare_plugin(sql_errlog) > +{ > + MYSQL_AUDIT_PLUGIN, > + &descriptor, > + "SQL_ERROR_LOG", > + "Alexey Botchkov", > + "Log SQL level errors to a file with rotation", > + PLUGIN_LICENSE_GPL, > + sql_error_log_init, > + sql_error_log_deinit, > + 0x0100, > + NULL, > + vars, > + NULL, > + 0 Fix this, please. Two last values are - version (as a string), and maturity. For example, "1.0", MariaDB_PLUGIN_MATURITY_ALPHA > +} > +maria_declare_plugin_end; Regards, Sergei

2 2

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 13 Mar '12

13 Mar '12

On Mon, Mar 12, 2012 at 1:16 PM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Hello Mark! > > In summary, the feature is ready. It has been reviewed by Monty, and > tested by Elena via the random query generator. All known bugs have been > fixed. The implementation turned out to be a lot more tricky than > initially expected. I hope the result is satisfactory. These are > the main changes/highlights since our last email exchange: Thank you. > - The implementation still allows the query engine to have what I call > "momentum". In various cases it is possible that the query engine > examines even an order of magnitude more rows than the give row limit. This is OK for us. We won't be using it in those cases. > Given that we cannot push this feature into 5.3 (it is GA), an important > question is whether I should continue maintaining the 5.3 version of the > feature in addition to the released version in 5.5. You don't need to maintain the 5.3 version. -- Mark Callaghan mdcallag(a)gmail.com

1 0

Re: [Maria-developers] [Commits] mdev-15 sql log errors
by Sergei Golubchik 09 Mar '12

09 Mar '12

Hi, Alexey! On Mar 09, Alexey Botchkov wrote: > 3316 Alexey Botchkov 2012-03-09 > MDEV-15 Log all SQL errors. > Added the logger service that provides us with the rotating logs. > The plugin SQL_ERROR_LOG added. It logs the errors using the 'logger service' > for the rotating log files. > the example record from the log: > 2012-03-09 15:07:29 root[root] @ localhost [] ERROR 1146: Table 'test.xyz' doesn't exist : select * from test.xyz Pretty much ok. See comments below. Six of them (that use imperative) I ask you to do. The rest are suggestions, opinions, or questions. And one question (static declaration of logs) I will discuss with you on irc. > === added file 'include/mysql/service_logger.h' > --- include/mysql/service_logger.h 1970-01-01 00:00:00 +0000 > +++ include/mysql/service_logger.h 2012-03-07 10:29:14 +0000 > @@ -0,0 +1,97 @@ > +/* Copyright (C) 2012 Monty Program Ab > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ > + > +#ifndef MYSQL_SERVICE_LOGGER_INCLUDED > +#define MYSQL_SERVICE_LOGGER_INCLUDED > + > +#ifndef MYSQL_ABI_CHECK > +#include <stdarg.h> > +#endif > + > +/** > + @file > + logger service > + > + Log file with rotation implementation. > + > + This service implements logging with possible rotation > + of the log files. Interface intentionally tries to be similar to FILE* > + related functions. > + > + So that one can open the log with logger_open(), specifying > + the limit on the logfile size and the rotations number. > + > + Then it's possible to write messages to the log with > + logger_printf or logger_vprintf functions. > + > + As the size of the logfile grows over the specified limit, > + it is renamed to 'logfile.1'. The former 'logfile.1' becomes > + 'logfile.2', etc. The file 'logfile.rotations' is removed. > + That's how the rotation works. > + > + The rotation can be forced with the logger_rotate() call. > + > + Finally the log should be closed with logger_close(). > + > +@notes: > + Implementation checks the size of the log file before it starts new > + printf into it. So the size of the file gets over the limit when it rotates. > + > + The access is secured with the mutex, so the log is threadsafe. > +*/ > + > + > +#ifdef __cplusplus > +extern "C" { > +#endif > + > +typedef struct logger_service_file_st LOGGER_SERVICE_FILE; > + > +extern struct logger_service_st { > + LOGGER_SERVICE_FILE* (*open)(const char *path, > + unsigned long size_limit, > + unsigned int rotations); > + int (*close)(LOGGER_SERVICE_FILE *log); > + int (*vprintf)(LOGGER_SERVICE_FILE *log, const char *fmt, va_list argptr); > + int (*printf)(LOGGER_SERVICE_FILE *log, const char *fmt, ...); > + int (*rotate)(LOGGER_SERVICE_FILE *log); > +} *logger_service; > + > +#ifdef MYSQL_DYNAMIC_PLUGIN > + > +#define logger_open logger_service->open you can write this with parentheses #define logger_open(P,L,R) logger_service->open(P,L,R) > +#define logger_close(log) (logger_service->close(log)) > +#define logger_rotate(log) (logger_service->rotate(log)) > +#define logger_vprintf(log, fmt, argptr) (logger_service->\ > + vprintf(log, fmt, argptr)) > +#define logger_printf logger_service->printf > +#else > + > + LOGGER_SERVICE_FILE *logger_open(const char *path, > + unsigned long size_limit, > + unsigned int rotations); > + int logger_close(LOGGER_SERVICE_FILE *log); > + int logger_vprintf(LOGGER_SERVICE_FILE *log, const char *fmt, va_list argptr); > + int logger_rotate(LOGGER_SERVICE_FILE *log); > + int logger_printf(LOGGER_SERVICE_FILE *log, const char *fmt, ...); > +#endif > + > + > +#ifdef __cplusplus > +} > +#endif > + > +#endif /*MYSQL_SERVICE_LOGGER_INCLUDED*/ > + > === added file 'mysys/my_logger.c' > --- mysys/my_logger.c 1970-01-01 00:00:00 +0000 > +++ mysys/my_logger.c 2012-03-09 13:03:47 +0000 > @@ -0,0 +1,167 @@ > +/* Copyright (C) 2012 Monty Program Ab > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ > + > + > +#include "my_global.h" > +#include <my_sys.h> > +#include <mysql/service_logger.h> > + > +extern MYSQL_PLUGIN_IMPORT char *mysql_data_home; why MYSQL_PLUGIN_IMPORT? Because the linker cannot find the symbol otherwise? that is, MYSQL_PLUGIN_IMPORT affects how the name is mangled? > +extern PSI_mutex_key key_LOCK_logger_service; > + > +typedef struct logger_service_file_st { > + FILE *file; > + char path[FN_REFLEN]; > + long size_limit; > + unsigned int rotations; > + unsigned int index_len; > + size_t path_len; > + mysql_mutex_t lock; > +} LSFS; > + > + > +static unsigned int n_dig(unsigned int i) > +{ > + return (i == 0) ? 0 : ((i < 10) ? 1 : ((i < 100) ? 2 : 3)); > +} > + > + > +LOGGER_SERVICE_FILE *logger_open(const char *path, > + unsigned long size_limit, > + unsigned int rotations) > +{ > + LOGGER_SERVICE_FILE new_log, *l_perm; > + > + /* I don't think we need more rotations, */ > + /* but if it's so, the rotation procedure should be adapted to it. */ please write multi-line comments as we do elsewhere in the code > + if (rotations > 999) > + return 0; > + > + new_log.rotations= rotations; > + new_log.size_limit= size_limit; > + new_log.index_len= n_dig(rotations); I don't think you need to cache the value of n_dig(rotations) it's only used once, in logname() function. > + new_log.path_len= strlen(fn_format(new_log.path, path, > + mysql_data_home, "", 4)); replace 4 with a named constant, they exist in 5.5 > + > + if (new_log.path_len+new_log.index_len+1 > FN_REFLEN || > + !(new_log.file= fopen(new_log.path, "a+"))) > + { > + /* File path too long || Check errno for the cause */ set errno=ENAMETOOLONG here. That is, split this if() in two, and set errno appropriately in the first one. > + return 0; > + } > + > + setbuf(new_log.file, 0); > + fseek(new_log.file, 0, SEEK_END); > + if (!(l_perm= (LOGGER_SERVICE_FILE *) > + my_malloc(sizeof(LOGGER_SERVICE_FILE), MYF(0)))) close the file here > + return 0; /* End of memory */ > + *l_perm= new_log; > + mysql_mutex_init(key_LOCK_logger_service, &l_perm->lock, MY_MUTEX_INIT_FAST); > + return l_perm; > +} > + > +int logger_close(LOGGER_SERVICE_FILE *log) > +{ > + int result; > + mysql_mutex_destroy(&log->lock); > + result= fclose(log->file); > + my_free(log); > + return result; > +} > + > + > +static char *logname(LOGGER_SERVICE_FILE *log, char *buf, unsigned int n_log) > +{ > + unsigned int n_zeroes; > + size_t path_len= log->path_len; > + buf[path_len++]= '.'; > + for (n_zeroes= log->index_len - n_dig(n_log); > + n_zeroes > 0; n_zeroes--, path_len++) > + buf[path_len]= '0'; > + > + int10_to_str(n_log, buf+path_len, 10); > + return buf; as I wrote earlier, I'd simply used sprintf("%0*d") here. Like this: sprintf(buf + path_len, ".%0*u", log->index_len, n_log); but ok, as you like. > +} > + > + > +static int do_rotate(LOGGER_SERVICE_FILE *log) > +{ > + char namebuf[FN_REFLEN]; > + struct stat stbuf; > + int result; > + unsigned int i; > + char *buf_old, *buf_new, *tmp; > + > + memcpy(namebuf, log->path, log->path_len); > + > + buf_new= logname(log, namebuf, log->rotations); > + buf_old= log->path; > + for (i=log->rotations-1; i>0; i--) > + { > + logname(log, buf_old, i); > + if (!stat(buf_old, &stbuf) && why stat? iirc, access(name, F_OK) is supposed to be used for checking if a file exists. We aren't interested in the values from stbuf here, are we? > + (result= my_rename(buf_old, buf_new, MYF(0)))) > + return result; > + tmp= buf_old; > + buf_old= buf_new; > + buf_new= tmp; > + } > + if ((result= fclose(log->file))) > + return result; > + namebuf[log->path_len]= 0; > + result= my_rename(namebuf, logname(log, log->path, 1), MYF(0)); > + log->file= fopen(namebuf, "a+"); > + return log->file==NULL || result; > +} > + > + > +int logger_vprintf(LOGGER_SERVICE_FILE *log, const char* fmt, va_list ap) > +{ > + int result; > + mysql_mutex_lock(&log->lock); > + if (ftell(log->file) >= log->size_limit && > + do_rotate(log)) > + { > + result= -1; > + goto exit; /* Log rotation needed but failed */ > + } > + > + result= vfprintf(log->file, fmt, ap); use my_vfprintf > +exit: > + mysql_mutex_unlock(&log->lock); > + return result; > +} > + > + > +int logger_rotate(LOGGER_SERVICE_FILE *log) > +{ > + int result; > + mysql_mutex_lock(&log->lock); > + result= do_rotate(log); > + mysql_mutex_unlock(&log->lock); > + return result; > +} > + > + > +int logger_printf(LOGGER_SERVICE_FILE *log, const char *fmt, ...) > +{ > + int result; > + va_list args; > + va_start(args,fmt); > + result= logger_vprintf(log, fmt, args); > + va_end(args); > + return result; > +} > + > === added file 'plugin/sql_errlog/sql_errlog.c' > --- plugin/sql_errlog/sql_errlog.c 1970-01-01 00:00:00 +0000 > +++ plugin/sql_errlog/sql_errlog.c 2012-03-09 10:24:35 +0000 > @@ -0,0 +1,147 @@ > +/* Copyright (C) 2012 Monty Program Ab. > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ > + > +#include <mysql/plugin_audit.h> > +#include <stdio.h> > +#include <time.h> > + > +/* > + rate 0 means the logging was disabled. > +*/ > + > +static char *filename; > +static unsigned int rate; > +static unsigned int size_limit; > +static unsigned int rotations; > +static char rotate; > + > +static unsigned int count; > +LOGGER_SERVICE_FILE *logfile; > + > +static void rotate_log(MYSQL_THD thd, struct st_mysql_sys_var *var, > + void *var_ptr, const void *save); > + > +static MYSQL_SYSVAR_UINT(rate, rate, PLUGIN_VAR_RQCMDARG, > + "Sampling rate. If set to 0(zero), the logging disabled.", NULL, NULL, typo: the logging *is* disabled > + 1, 0, 1000000, 1); > + > +static MYSQL_SYSVAR_UINT(size_limit, size_limit, > + PLUGIN_VAR_READONLY, "Log file size limit", NULL, NULL, > + 1000000, 100, 1024*1024L*1024L, 1); > + > +static MYSQL_SYSVAR_UINT(rotations, rotations, > + PLUGIN_VAR_READONLY, "Number of rotations before log is removed.", > + NULL, NULL, 9, 1, 999, 1); > + > +static MYSQL_SYSVAR_BOOL(rotate, rotate, > + PLUGIN_VAR_NOSYSVAR, "Force log rotation", NULL, rotate_log, What do you mean PLUGIN_VAR_NOSYSVAR? It only works from a command line? It'd be more useful to force a log rotation from SQL. if users wouldn't need it, we woldn't have FLUSH LOGS :) > + 0); > + > +static MYSQL_SYSVAR_STR(filename, filename, > + PLUGIN_VAR_READONLY | PLUGIN_VAR_RQCMDARG, > + "The file to log sql errors to", NULL, NULL, > + "sql_errors.log"); > + > +static struct st_mysql_sys_var* vars[] = { > + MYSQL_SYSVAR(rate), > + MYSQL_SYSVAR(size_limit), > + MYSQL_SYSVAR(rotations), > + MYSQL_SYSVAR(rotate), > + MYSQL_SYSVAR(filename), > + NULL > +}; > + > + > +static void log_sql_errors(MYSQL_THD thd __attribute__((unused)), > + unsigned int event_class __attribute__((unused)), > + const void *ev) > +{ > + const struct mysql_event_general *event = > + (const struct mysql_event_general*)ev; > + if (rate && > + event->event_subclass == MYSQL_AUDIT_GENERAL_ERROR) > + { > + if (++count >= rate) > + { > + struct tm t; > + time_t event_time = event->general_time; > + > + count = 0; > + localtime_r(&event_time, &t); > + logger_printf(logfile, "%04d-%02d-%02d %2d:%02d:%02d " > + "%s ERROR %d: %s : %s\n", > + t.tm_year + 1900, t.tm_mon + 1, > + t.tm_mday, t.tm_hour, t.tm_min, t.tm_sec, > + event->general_user, event->general_error_code, > + event->general_command, event->general_query); > + } > + } > +} > + > + > +static int sql_error_log_init(void *p __attribute__((unused))) > +{ > + logfile= logger_open(filename, size_limit, rotations); > + if (logfile == NULL) { > + fprintf(stderr, "Could not create file '%s'\n", > + filename); > + return 1; > + } > + count = 0; > + return 0; > +} > + > + > +static int sql_error_log_deinit(void *p __attribute__((unused))) > +{ > + logger_close(logfile); > + return 0; > +} > + > + > +static void rotate_log(MYSQL_THD thd __attribute__((unused)), > + struct st_mysql_sys_var *var __attribute__((unused)), > + void *var_ptr __attribute__((unused)), > + const void *save __attribute__((unused))) > +{ > + (void) logger_rotate(logfile); > +} > + > + > +static struct st_mysql_audit descriptor = > +{ > + MYSQL_AUDIT_INTERFACE_VERSION, > + NULL, > + log_sql_errors, > + { MYSQL_AUDIT_GENERAL_CLASSMASK } > +}; > + > +maria_declare_plugin(sql_errlog) > +{ > + MYSQL_AUDIT_PLUGIN, > + &descriptor, > + "SQL_ERROR_LOG", > + "Alexey Botchkov", > + "Log SQL level errors to a file with rotation", > + PLUGIN_LICENSE_GPL, > + sql_error_log_init, > + sql_error_log_deinit, > + 0x0100, > + NULL, > + vars, > + NULL, > + 0 > +} > +maria_declare_plugin_end; Regards, Sergei

1 0

Re: [Maria-developers] Clang build failure
by Kristian Nielsen 05 Mar '12

05 Mar '12

Terin Stock <terinjokes(a)gmail.com> writes: >> Could it be that Clang is lying to autoconf in Xcode 4.3, pretending to be >> GCC? Maybe it would be worth investigating, and filing a bug against Clang or >> Autoconf, if appropriate. > It's possible this is the case, my configure output: > checking whether we are using the GNU C compiler… yes > It looks like autoconf checks for the GCC C compiler by seeing if __GNUC__ is defined. If it is, the code is compiled and no errors happen. If it isn't, it fails to compile, and autoconf treats that as not being the C compiler. Maybe this is a Clang bug then. If Clang wants to pretend it is GCC, then it needs to work the same way... Perhaps a bug should be filed against Clang that the preprocessor option -dI is not working correctly (especially since Clang apparently accepts the option without error, so they seem to have intended to implement it). If you can come up with something for configure.in that works-around the Clang problem until it might be fixed we will be happy to include it (it should be possible to do without changing autoconf itself). - Kristian.

1 0

[Maria-developers] Documenation for MWL#234
by Kristian Nielsen 02 Mar '12

02 Mar '12

Hi Daniel, Here is another new feature in 5.5 that needs documentation: I have now pushed MWL#234, @@skip_replication, into MariaDB 5.5.21. User documentation is written up in the worklog: http://askmonty.org/worklog/Server-Sprint/?tid=234#hld Can you get the text added to the knowledgebase, and also add something to the release notes? Thanks, - Kristian.

2 1

[Maria-developers] Clang build failure
by Terin Stock 01 Mar '12

01 Mar '12

MariaDB 5.3.5, 5.2.10 and possibly other versions, fails to compile using Apple's Clang compiler. As of Xcode 4.3, Clang is the default compiler, replacing LLVM-GCC. The issue seems to occur when checking for an ABI incompatibility in mysql.h. As Clang preprocesses the file differently than GCC, the output is different, which fails the abi_check. MariaDB compiles fine using the non-default LLVM-GCC compiler. https://gist.github.com/1944688 -- #Terin Stock

2 1

Re: [Maria-developers] How to check for active transaction?
by Kristian Nielsen 29 Feb '12

29 Feb '12

Davi Arnaut <davi(a)twitter.com> writes: >>> I have a session variable @@skip_replication. I want to ensure that this >>> variable is not modified in the middle of a statement or while a transaction >>> is active. > Copy and paste fail. I meant THD::in_multi_stmt_transaction_mode. Try > to avoid using the flag directly, in the 5.5 cycle we cleaned up some > of this stuff. Thanks Davi! I also need to check if one is in the middle of a statement, ie to prevent this: CREATE FUNCTION foo (x INT) RETURNS INT BEGIN SET SESSION skip_replication=x; RETURN x; END UPDATE t1 SET b=foo(0); It seems to work with this: if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction()) ... but I am not sure if this is the correct way? - Kristian.

1 0

[Maria-developers] 5.3.5 GA release : binaries are ready and tagged
by Sergei Petrunia 29 Feb '12

29 Feb '12

Hi Daniel, MariaDB 5.3.5 GA has been built by buildbot and is ready for upload/release. Tip revision number is 3450, tarbuildnum: 180 I've also made a tag: mariadb-5.3.5-ga Could you please make the rest of the release steps? I'm available to help if there are any problems. (I will also look through the changes to see what should be highlighted in the changelog). BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 5

Re: [Maria-developers] regex enhancements
by Line Bie Pedersen 29 Feb '12

29 Feb '12

Hi Sergei, > I have some questions related to this project, foremost are of course > > what kind of enhancement are you looking for? Are you looking for an > > update of existing code? I looked at the existing code and it seems to > > be the old regex library written by Henry Spencer. I did not look into > > it in detail, so I have no clear idea of the changes needed to bring > > it up to speed. Or are you looking to upgrade with an existing > > library? Or perhaps a rewrite suited to your needs? I can't see if you > > use the regex library for anything but deciding simple acceptance, but > > this would probably be a big factor in deciding. I would be happy to > > look into all three suggestions, or even a fourth, if you prefer > > something else. > > There're two answers to that. > > The first one - on the level of requirements, I am very much looking for > multi-byte support in the regex library. And REGEX_REPLACE() function > would be nice too - users ask for it quite often. How will the REGEX_REPLACE() function work? The second answer - I thought that the simple way of getting multi-byte > support, would be to remove Henry Spencer library, and put some modern > regex implementation instead. That could be relatively easy to do for > any undegraduate student with little experience - this task was one > of ideas we had for Google Summer of Code. > Agree, this is the simple solution. Did you already have a library picked out? Are there any requirements to license and language? Would it be useful if you could limit the amount of memory the library can use? > Now, with your experience, you may prefer to do something else - > not replace the library, but rewrite it, or extend, whatever. > I'm fine either way. It's only important that the result works with > multi-byte character sets (ideally - our character set code), and that > it can support Henry Spencer regex syntax. > What I would really like to do, is rewrite the thing! However, I do not have an unlimited amount of time for this project. I'll look into Henry Spencers old library and let you know what I decide to do. -- Line Bie Pedersen

2 1

[Maria-developers] How to check for active transaction?
by Kristian Nielsen 29 Feb '12

29 Feb '12

Hi Serg, I'm merging MWL#234 into 5.5. I have a session variable @@skip_replication. I want to ensure that this variable is not modified in the middle of a statement or while a transaction is active. (The reason for this is that it changes whether changes are replicated or not - and so changing in the middle of statement or transaction could cause us to replicate only half of the event/transaction). In my 5.2-based tree, I had this condition, but this seems to no longer work: thd->locked_tables || thd->active_transaction() Can you help me find how to do this check in 5.5 code base? - Kristian.

2 1

[Maria-developers] Documentation for MWL#192: non-blocking client API
by Kristian Nielsen 28 Feb '12

28 Feb '12

Hi Daniel! I have pushed MWL#192. It will be included in upcoming MariaDB 5.5.21. So we need documentation of the feature :-) I have written a first draft of this. I included it in the high-level description in the WL: http://askmonty.org/worklog/Client-Sprint/?tid=192#hld There are two things I would ask of you: 1. Can you take the text and prepare it properly for the Knowledgebase? Find an appropriate place, split it up as/if needed, handle markup/formatting? And feel free to correct my text in any way you see fit. 2. The last part, "Non-blocking API reference", is basically a long list of all the new functions and their parameters. In the draft, I just listed them, along with specific comments to a few of them as relate to the new feature. For the majority of them, they are just direct translation of the corresponding functions from the old client library, which is documented in the MySQL manual. Can you help with a suggestion on how to present this in a nice way? Maybe you want to format them each individually in some nice way, maybe link to the MySQL manual? Any suggestion and/or help is appreciated. Can you take over from here? Of course, just let me know if there is anything lacking that you need me to do, or any questions or so on. (The task is not urgent as such - the feature will be available from the next MariaDB 5.5 release, which is as yet unscheduled - maybe in 2-3 weeks?) - Kristian.

2 1

[Maria-developers] Text for 5.5.21 documentation and release notes
by Kristian Nielsen 28 Feb '12

28 Feb '12

Hi Daniel, I have pushed MWL#192 to 5.5.21. This introduces a minor change in functionality. I hope you can find a good place to put this in the documentation/knowledgebase. Here is suggested text, let me know if you have questions, and feel free to improve the text as you like: "In MySQL, and in MariaDB versions before 5.5.21, mysql_real_connect() removes from the MYSQL object any options set with mysql_option() when it fails. From MariaDB 5.5.21, options are preserved by a failing mysql_real_connect(); use mysql_close(), as normal, to clear them. This only has effect if the MYSQL object is reused after a mysql_real_connect() failure (which is unusual). No real-life incompatibilities are expected from this change; it is unlikely that an application will rely on options being automatically removed between connection attempts." (I am preparing information for you to fully document the new MWL#192 feature; I will send it to you later). - Kristian.

2 1

[Maria-developers] Upgrading from MySQL 5.0.34 to MariaDB 5.3.4
by Bruce Carlson 28 Feb '12

28 Feb '12

Hi to all, Firstly, I'm new here and not sure of the correct place to ask for this type of assistance but i'm hoping someone can help. I have been using MySQL enterprise in a company production environment for many years. We run 5 databases in a daisy chained replication system on five servers in five cities across Australia. This has worked flawlessly since 2007 when we setup MySQL version 3.0.34. It worked flawlessly before then too but back then we were still learning how much MySQL could do. Our first full production version was 5.0.34. We have had enterprise licenses from MySQL for the five servers ever since we started with mySQL in 2003. When MySQL was aquired by Sun the level of service provided remained extremely high with the only real change being that our account manager changed from being Iko in Europe to Ralph in Australia. (I still have the greatest respect for both Iko and Ralph. I wish I could say the same for Oracle.) The average time for a resolution of help issues submitted was less than one hour, the best was 11 minutes from submission to resolution, I was extemely happy with this but since MySQL has been taken over by Oracle I have not been able to get system suport at all. I mean -- never... not once.... Even after spending many hours trying to follow the instructions to get help from the Oracle help system and after paying many thousands of dollars to buy the suport still nothing from MySQL/Oracle suport. They are the most arrogant and rude organisiation I've ever had to deal with. I have began to look at moving to Maria DB as an alternative, especially since the MariaDB project is made up of many of the original MySQL people. In recent weeks we have upgraded much of our company's infrastructure and now have new servers running win server 2008 64 bit OS but three of our DB servers are still running the old win 32 bit 2003 server OS. The older servers are still running MySQL 5.0.34 enterprise (I have not upgraded for years because the system has not stopped for even one second since we finalised the configuration in 2007. That in it's self is an indication of how good the program is.) Since installing the new servers at two of our sites I have had to pull them out of the replicator chain and link those sites to the main server in Sydney. This is causing big delays in the sites with the new servers and I need to get them back into the replicator ASAP. I have a plan to replace all our MySQL servers with MariaDB because I can no longer suffer the awfull disgusting lack of suport from Oracle. I have installed MariaDB 5.3.4 on the new server in Perth as a test and a starting point but I can not get it to load up data from an .sql dump file created by the older MySQL version 5.0.34. (Not Suprised given the age of the older versions) The syntax I use for creating the dump file is :- "mysqldump --opt --user=specialUser1 --password=specialpassword1 --routines mydatabase > mydatadump.sql" This brings me to my three questions. 1. Can I run version 5.3.4 with any other version in a daisy chain replication environment? 2. is there a way that I can dump all tables, views, procedures, functions, triggers and users from the old 5.0.34 version to the new 5.3.4 version for a selected DB schema? 3. Can I run MariaDB 5.3.4 on a mixture of 32 bit Win Server 2003 and 64 bit Win Server 2008 OS's and still get them to replicate Future direction... I am keen to move away from MySQL/Oracle and have noted at least one other company (SkyDB) that offers enterprise solutions for MariaDB. I would like to hear from anyone who has more information on enterprise solutions available for MariaDB. Our current agreement with MySQL ends at the end of March and I hope to be well clear of Oracle before then as I do not want to spend another ten to twenty thousand US dollars just to get nothing in return again, like the last two years. Thanks and Best Regards, Bruce Carlson. (Business Systems Manager) (www.Nepean.com) -- This message was sent from Launchpad by Bruce Carlson (https://launchpad.net/~bruce-carlson) to each member of the Maria Discuss team using the "Contact this team" link on the Maria Discuss team page (https://launchpad.net/~maria-discuss) For more information see https://help.launchpad.net/YourAccount/ContactingPeople

1 0

[Maria-developers] MariaDB 5.5.20 Alpha
by Daniel Bartholomew 26 Feb '12

26 Feb '12

All, Nearly all of the mirrors have the MariaDB 5.5.20 Alpha packages and source tarball now, so I've gone ahead and activated the various pages for the release. The release notes are pretty sparse at the moment. If there is anything that should be added to them, please let me know ASAP, or jump in and add it directly. http://kb.askmonty.org/en/mariadb-5520-release-notes http://kb.askmonty.org/en/mariadb-5520-changelog http://downloads.askmonty.org/mariadb/5.5.20/ Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] BUG#939009: Making sj-materialized fields non-nullable doesn't remove the crash
by Sergei Petrunia 23 Feb '12

23 Feb '12

Hello Igor, I've made a patch that makes temporary table's fields non-nullable. It still has one part missing, but I have discovered that it will not remove the crash. I was able to construct an example with inner join and a non-nullable key field for which best_access_path() will still access rec_per_key[...]. <testcase> create table t21 (a int NOT NULL, b int, key(a)); # note the NOT NULL key column... # we can make the key unique: create table t21a (a int NOT NULL, b int, unique key(a)); insert into t21 select a,a from test.one_k; explain select * from t21a where a=b and (b=10 or b is null); </testcase> When I debug the above EXPLAIN, I get this execution in best_access_path(): if (found_part == PREV_BITS(uint,keyinfo->key_parts) && !ref_or_null_part) //(branch not taken) if ((found_part & 1) && (!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) || found_part == PREV_BITS(uint,keyinfo->key_parts))) { //(branch taken) if (table->quick_keys.is_set(key) && !found_ref && //(C1) table->quick_key_parts[key] == max_key_part && //(C2) table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3) { //(branch not taken) tmp= records= (double) table->quick_rows[key]; } else { //(we end up here, where the crash is) /* Check if we have statistic about the distribution */ if ((records= keyinfo->rec_per_key[max_key_part-1])) Conclusion: marking temporary table's field as NOT NULL will not cause best_access_path to avoid accessing the " rec_per_key[max_key_part-1] " part. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] On whether BUG#920132 is repeatable in mysql-5.6
by Sergei Petrunia 22 Feb '12

22 Feb '12

<igor> spetrunia: so does mysql-5.6 contains a fix for that ICP bug #920132? It doesn't have the fix. They don't have the bug either, though. mysql-5.6 will execute the subquery at optimization phase and remove it from the WHERE. The stack trace will look like this: (gdb) wher 20 #0 JOIN::exec (this=0x977cf40) at /home/psergey/dev2/mysql-server-trunk/sql/sql_executor.cc:129 #1 0x0851962c in subselect_single_select_engine::exec (this=0x97227d0) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:2517 #2 0x0851fee2 in Item_subselect::exec (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:340 #3 0x08515902 in Item_exists_subselect::val_bool (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:1005 #4 0x0822a144 in Item_func_not::val_int (this=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_cmpfunc.cc:290 #5 0x0825abff in eval_const_cond (cond=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_func.cc:79 #6 0x0839c385 in internal_remove_eq_conds (thd=0x964a390, cond=0x97227f0, cond_value=0xa95a9fb4) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8127 #7 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722870, cond_value=0xa95aa054) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #8 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #9 0x0839c71b in remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8220 #10 0x083ad51f in optimize_cond (join=0x977cbc8, conds=0x9722918, join_list=0x964bb18, build_equalities=true, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7947 #11 0x083adde9 in JOIN::optimize (this=0x977cbc8) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:246 #12 0x083f7efa in mysql_select (thd=0x964a390, tables=0x9721298, wild_num=1, fields=..., conds=0x9722918, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x9722a38, unit=0x964b5ec, select_lex=0x964ba48) at /home/psergey/dev2/mysql-server-trunk/sql/sql_select.cc:1074 so by the time we get to push_index_cond(), subquery will no longer be in the WHERE clause. As far as I udnerstand, we don't want this behavior, all subuqueries are expensive and should not be executed in EXPLAIN. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#249 New (by Monty): Locking SQL_MODE against client changes
by worklog-noreply＠askmonty.org 21 Feb '12

21 Feb '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Locking SQL_MODE against client changes CREATION DATE..: Tue, 21 Feb 2012, 19:36 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 249 (http://askmonty.org/worklog/?tid=249) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 4 (hours remain) ORIG. ESTIMATE.: 4 PROGRESS NOTES: DESCRIPTION: Add an option to lock sql_mode against client-side changes. For example: mysqld --lock_sql_mode ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Please review: Rev 3275: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050
by Sergei Petrunia 20 Feb '12

20 Feb '12

Hello Sanja, Igor, Could you please review the below? The new code, Item_cache::walk() passes the main testsuite and fixed the bug, however I'm concerned it may have implications that I am not aware of. ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Tue, 21 Feb 2012 01:50:51 +0400 (GST) Subject: [Commits] Rev 3275: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 in file:///home/psergey/dev2/5.5-look5/ At file:///home/psergey/dev2/5.5-look5/ ------------------------------------------------------------ revno: 3275 revision-id: psergey(a)askmonty.org-20120220215050-ovuvi8qigyidenum parent: psergey(a)askmonty.org-20120220210822-6yumujmervtemmwy committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.5-look5 timestamp: Tue 2012-02-21 01:50:50 +0400 message: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 - MySQL 5.5 introduced caching of constant items by means of wrapping them in Item_cache_XXX objects. If a subquery was wrapped in this cache, it could end up being pushed down by ICP. - The fix is to add Item_cache::walk() which lets ICP to see that the cache item has a subquery inside it, and disable pushdown for this case. === modified file 'mysql-test/r/innodb_icp.result' --- a/mysql-test/r/innodb_icp.result 2012-01-13 14:50:02 +0000 +++ b/mysql-test/r/innodb_icp.result 2012-02-20 21:50:50 +0000 @@ -808,5 +808,34 @@ 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# +# BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 +# +CREATE TABLE t1 ( a INT ) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (7),(7); +CREATE TABLE t2 ( b VARCHAR(1) ); +INSERT INTO t2 VALUES ('j'),('v'); +CREATE TABLE t3 ( +c INT, d VARCHAR(1), e VARCHAR(1), +KEY (d,c) +) ENGINE=InnoDB; +INSERT INTO t3 +VALUES (6,'w','w'), +(1,'v','v'),(7,'s','s'),(4,'l','l'), +(7,'y','y'),(0,'c','c'),(2,'i','i'), +(9,'h','h'),(4,'q','q'),(0,'a','a'), +(9,'v','v'),(1,'u','u'),(3,'s','s'), +(8,'z','z'),(1,'h','h'),(8,'p','p'), +(6,'e','e'),(3,'i','i'),(6,'y','y'); +SELECT * +FROM t1 INNER JOIN t2 INNER JOIN t3 +ON d = b +WHERE +NOT EXISTS ( SELECT * FROM t3 ) +OR a = c +ORDER BY e; +a b c d e +DROP TABLE t1,t2,t3; set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; === modified file 'mysql-test/t/innodb_icp.test' --- a/mysql-test/t/innodb_icp.test 2011-07-08 14:46:47 +0000 +++ b/mysql-test/t/innodb_icp.test 2012-02-20 21:50:50 +0000 @@ -12,6 +12,39 @@ --source include/icp_tests.inc +--echo # +--echo # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 +--echo # +CREATE TABLE t1 ( a INT ) + ENGINE=InnoDB; +INSERT INTO t1 VALUES (7),(7); + +CREATE TABLE t2 ( b VARCHAR(1) ); +INSERT INTO t2 VALUES ('j'),('v'); + +CREATE TABLE t3 ( + c INT, d VARCHAR(1), e VARCHAR(1), + KEY (d,c) +) ENGINE=InnoDB; +INSERT INTO t3 + VALUES (6,'w','w'), + (1,'v','v'),(7,'s','s'),(4,'l','l'), + (7,'y','y'),(0,'c','c'),(2,'i','i'), + (9,'h','h'),(4,'q','q'),(0,'a','a'), + (9,'v','v'),(1,'u','u'),(3,'s','s'), + (8,'z','z'),(1,'h','h'),(8,'p','p'), + (6,'e','e'),(3,'i','i'),(6,'y','y'); + +SELECT * +FROM t1 INNER JOIN t2 INNER JOIN t3 + ON d = b +WHERE + NOT EXISTS ( SELECT * FROM t3 ) + OR a = c +ORDER BY e; + +DROP TABLE t1,t2,t3; + set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; === modified file 'sql/item.h' --- a/sql/item.h 2012-02-15 17:08:08 +0000 +++ b/sql/item.h 2012-02-20 21:50:50 +0000 @@ -3901,6 +3901,12 @@ return example->is_expensive_processor(arg); } virtual void set_null(); + bool walk(Item_processor processor, bool walk_subquery, uchar *arg) + { + if (example && example->walk(processor, walk_subquery, arg)) + return TRUE; + return (this->*processor)(arg); + } }; _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Please review: Rev 3430: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN in file:///home/psergey/dev2/5.3-look55/
by Sergei Petrunia 20 Feb '12

20 Feb '12

Hi Monty, Could you please review the below: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Mon, 20 Feb 2012 20:04:54 +0400 (GST) Subject: [Commits] Rev 3430: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN in file:///home/psergey/dev2/5.3-look55/ At file:///home/psergey/dev2/5.3-look55/ ------------------------------------------------------------ revno: 3430 revision-id: psergey(a)askmonty.org-20120220160453-jtj27fub5fxl9re4 parent: psergey(a)askmonty.org-20120220113450-0foyjou7b1h0bid6 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-look55 timestamp: Mon 2012-02-20 20:04:53 +0400 message: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN - In mi_rkey(), do correct handling of case where mi_yield_and_check_if_killed() detects that the thread was killed (all other similar functions in MyISAM/Aria have slightly different code and do not have this problem). - Also fixed assignment in DBUG_ASSERT. === modified file 'mysql-test/r/myisam_icp.result' --- a/mysql-test/r/myisam_icp.result 2012-02-16 06:49:10 +0000 +++ b/mysql-test/r/myisam_icp.result 2012-02-20 16:04:53 +0000 @@ -909,4 +909,98 @@ c NULL NULL SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; +# +# BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN +# +CREATE TABLE t1 ( +b INT, +c VARCHAR(1) NOT NULL, +d DATETIME, +KEY (c, b) +) ENGINE=MyISAM; +INSERT INTO t1 ( b, d, c ) VALUES +(4,'2005-01-08 00:00:00','f'), +(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'), +(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'), +(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'), +(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'), +(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'), +(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'), +(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'), +(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'), +(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'), +(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'), +(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'), +(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'), +(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'), +(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'), +(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'), +(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'), +(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'), +(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'), +(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'), +(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'), +(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'), +(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'), +(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'), +(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'), +(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'), +(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'), +(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'), +(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'), +(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'), +(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'), +(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'), +(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'), +(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'), +(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'), +(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'), +(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'), +(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'), +(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'), +(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'), +(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'), +(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'), +(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'), +(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'), +(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'), +(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'), +(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'), +(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'), +(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'), +(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'), +(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'), +(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'), +(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'), +(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'), +(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'), +(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'), +(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'), +(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'), +(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'), +(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'), +(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'), +(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'), +(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'), +(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'), +(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'), +(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'), +(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'), +(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'), +(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'), +(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'), +(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'), +(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'), +(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'), +(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'), +(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'), +(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'), +(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'), +(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'), +(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r'); +CREATE TABLE t2 ( a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(7),(3),(7),(3); +# Now run a number of ICP queries while trying to kill them +DROP TABLE t1,t2; set optimizer_switch=@myisam_icp_tmp; === modified file 'mysql-test/t/myisam_icp.test' --- a/mysql-test/t/myisam_icp.test 2011-12-15 08:21:15 +0000 +++ b/mysql-test/t/myisam_icp.test 2012-02-20 16:04:53 +0000 @@ -262,4 +262,126 @@ DROP TABLE t1,t2,t3,t4; +--echo # +--echo # BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN +--echo # +CREATE TABLE t1 ( + b INT, + c VARCHAR(1) NOT NULL, + d DATETIME, + KEY (c, b) +) ENGINE=MyISAM; +INSERT INTO t1 ( b, d, c ) VALUES +(4,'2005-01-08 00:00:00','f'), +(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'), +(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'), +(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'), +(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'), +(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'), +(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'), +(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'), +(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'), +(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'), +(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'), +(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'), +(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'), +(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'), +(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'), +(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'), +(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'), +(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'), +(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'), +(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'), +(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'), +(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'), +(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'), +(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'), +(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'), +(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'), +(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'), +(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'), +(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'), +(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'), +(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'), +(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'), +(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'), +(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'), +(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'), +(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'), +(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'), +(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'), +(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'), +(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'), +(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'), +(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'), +(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'), +(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'), +(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'), +(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'), +(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'), +(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'), +(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'), +(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'), +(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'), +(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'), +(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'), +(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'), +(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'), +(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'), +(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'), +(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'), +(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'), +(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'), +(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'), +(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'), +(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'), +(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'), +(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'), +(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'), +(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'), +(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'), +(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'), +(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'), +(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'), +(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'), +(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'), +(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'), +(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'), +(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'), +(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'), +(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'), +(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r'); + +CREATE TABLE t2 ( a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES + (7),(3),(7),(3); + +--let $run = 300 +--let $con_id = `SELECT CONNECTION_ID()` + +--echo # Now run a number of ICP queries while trying to kill them +--disable_query_log +--disable_result_log +while ($run) +{ + --send + SELECT * FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 + ON alias2.c = alias1.c + WHERE alias2.b >= 9; + + --connect (con1,127.0.0.1,root,,test) + --eval KILL QUERY $con_id + --disconnect con1 + + --dec $run + + --connection default + --error 0,ER_QUERY_INTERRUPTED + --reap +} +--enable_result_log +--enable_query_log +DROP TABLE t1,t2; + set optimizer_switch=@myisam_icp_tmp; + === modified file 'storage/myisam/mi_rkey.c' --- a/storage/myisam/mi_rkey.c 2011-02-18 19:45:32 +0000 +++ b/storage/myisam/mi_rkey.c 2012-02-20 16:04:53 +0000 @@ -157,7 +157,11 @@ mi_yield_and_check_if_killed(info, inx)) { /* Aborted by user */ + DBUG_ASSERT(info->lastpos == HA_OFFSET_ERROR && + my_errno == HA_ERR_ABORTED_BY_USER); + res= ICP_ERROR; buf= 0; /* Fast abort */ + break; } } if (res == ICP_OUT_OF_RANGE) @@ -179,7 +183,7 @@ } else { - DBUG_ASSERT(info->lastpos= HA_OFFSET_ERROR); + DBUG_ASSERT(info->lastpos == HA_OFFSET_ERROR); } } if (share->concurrent_insert) _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 3335: Fix bug lp:825075 in file:///home/tsk/mprog/src/5.3/
by Sergei Petrunia 17 Feb '12

17 Feb '12

Hi Timour, Feedback follows skype discussion: First, here's a general comment Consider a query: SELECT b, min(a) FROM t1 GROUP BY b; Suppose there is an index (b,a). Loose scan would execute it as follows: S00 h->index_first(); S01 produce an output record of (b, a) // first value of 'a' is the minimum S02 S03 h->read_next_different_b(); // Jump to the next GROUP-BY group. S04 ... S05 If we modify the query by adding a WHERE: SELECT b, min(a) FROM t1 WHERE arbitrary_cond(a,b) GROUP BY b; then LooseScan (in its current form) will be unable to execute it. The problem is here: S00 h->index_first(); S01 produce an output record of (b, a) // first value of 'a' is the minimum ^^^ what if this record doesn't match the WHERE? We'll have to scan further within the record having b=first_record.b until we find a match. LooseScan executor doesn't support such "walk forward" action currently. If the WHERE clause has been converted into a range, such that there are no records that would be contained in the range but not match the WHERE clause (***) then LooseScan is able to handle it. Example: SELECT b, min(a) FROM t1 WHERE a>10 GROUP BY b; Execution would proceed as follows: S00 h->index_first(); S00 h->index_read(HA_READ_AFTER_KEY, {current_row.b, 'a>10'}) S01 produce an output record of (b, a) // first value of 'a>10' is the minimum Step S01 will produce correct results as long as the following holds: "The first record we've got in a range will satisfy the WHERE condition" This is not always the case. Here's a counterexample: SELECT b, min(a) FROM t1 WHERE a>10 GROUP BY b; SELECT b, min(a) FROM t1 WHERE a>10 and ((a MOD 2) = 0) GROUP BY b; -- (C1) Consider the second query and this dataset: b , a 1, 5 1, 7 1, 11 1, 13 1, 14 range optimizer would give a range of "a > 10", and the first matching record is (1,11). It doesn't satisfy the "(a MOD 2)=0" condition, neither does the next record of (1,13), and only the record (1,14) will satisfy the MOD part of the condition. The problem: current code doesn't seem to have the logic to detect such cases. The submitted patch disables LooseScan for apparently invalid cases where the WHERE condition covers a column while range condition does not, which means that condition (***) is certainly unmet. However, cases like (C1) are still not handled. Further comments are inline. On Wed, Dec 07, 2011 at 02:18:02PM +0200, Timour Katchaounov wrote: > Could you please review the below patch. > > Timour > > ----------------------------------------------------------- > revno: 3335 > revision-id: timour(a)askmonty.org-20111207115332-3oa536gbt20r22po > parent: igor(a)askmonty.org-20111206214218-lev0vyidyt8h3431 > fixes bug(s): https://launchpad.net/bugs/825075 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Wed 2011-12-07 13:53:32 +0200 > message: > Fix bug lp:825075 > > Analys: > The cause for the wrong result was that the optimizer > incorrectly chose min/max loose scan when it is not > applicable. The applicability test missed the case when > a condition on the MIN/MAX argument was OR-ed with a > condition on some other field. In this case, the MIN/MAX > condition cannot be used for loose scan. > > Solution: > The function check_group_min_max_predicates() duplicated > functionality performed by the range optimizer, but this > duplication was incomplete. The solution is to reuse instead > the analys and the ranges produced by the range optimizer. > If a suitable range could not be extracted for the MIN/MAX > argument, then loose scan is not applicable. > === modified file 'mysql-test/r/group_min_max.result' > --- a/mysql-test/r/group_min_max.result 2011-12-04 21:31:42 +0000 > +++ b/mysql-test/r/group_min_max.result 2011-12-07 11:53:32 +0000 > @@ -2103,7 +2103,7 @@ id select_type table type possible_keys > explain select a1,a2,b,min(c),max(c) from t2 > where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; > id select_type table type possible_keys key key_len ref rows Extra > -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index > +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 33 Using where; Using index for group-by > explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort Why there is no testcase from the bug report? I think it's better to have a testcase that - will explicitly mention BUG# - will show the wrong query result. A changed EXPLAIN in some irrelevant test is a too poor coverage. > === modified file 'sql/opt_range.cc' > --- a/sql/opt_range.cc 2011-11-18 17:35:51 +0000 > +++ b/sql/opt_range.cc 2011-12-07 11:53:32 +0000 > @@ -11984,164 +11996,41 @@ get_best_group_min_max(PARAM *param, SEL > } > > > -/* > - Check that the MIN/MAX attribute participates only in range predicates > - with constants. > - > - SYNOPSIS > - check_group_min_max_predicates() > - cond tree (or subtree) describing all or part of the WHERE > - clause being analyzed > - min_max_arg_item the field referenced by the MIN/MAX function(s) > - min_max_arg_part the keypart of the MIN/MAX argument if any > - > - DESCRIPTION > - The function walks recursively over the cond tree representing a WHERE > - clause, and checks condition (SA3) - if a field is referenced by a MIN/MAX > - aggregate function, it is referenced only by one of the following > - predicates: {=, !=, <, <=, >, >=, between, is null, is not null}. > +/** > + Find the range tree for the field that is an argument of MIN/MAX. > > - RETURN > - TRUE if cond passes the test > - FALSE o/w > + @param cond The WHERE clause of the query > + @param min_max_arg_item The field referenced by the MIN/MAX function(s) > + @param index_tree Range tree for the chosen index > + > + @note > + The function iterates over the range trees for each keypart of the > + chosen index until it finds the range for the MIN/MAX keypart. > + > + @retval > + If there is a range for the MIN/MAX argument > + @retval > + NULL if there is no range > */ > > -static bool > -check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, > - Field::imagetype image_type) > +static SEL_ARG * > +get_min_max_range(Item *cond, Item_field *min_max_arg_item, SEL_ARG *index_tree) > { > - DBUG_ENTER("check_group_min_max_predicates"); > - DBUG_ASSERT(cond && min_max_arg_item); > - > + if (!cond || !min_max_arg_item) > + return NULL; > cond= cond->real_item(); So we've got: get_min_max_range(Item *cond, Item_field *min_max_arg_item, SEL_ARG *index_tree) { if (!cond || !min_max_arg_item) return NULL; cond= cond->real_item(); 'cond' is passed as parameter, checked, modified, and .. then never used in the function. Please fix this. > - Item::Type cond_type= cond->real_type(); > - if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */ > - { > - DBUG_PRINT("info", ("Analyzing: %s", ((Item_func*) cond)->func_name())); > - List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list()); > - Item *and_or_arg; > - while ((and_or_arg= li++)) > - { > - if (!check_group_min_max_predicates(and_or_arg, min_max_arg_item, > - image_type)) > - DBUG_RETURN(FALSE); > - } > - DBUG_RETURN(TRUE); > - } > - > - /* > - Disallow loose index scan if the MIN/MAX argument field is referenced by > - a subquery in the WHERE clause. > - */ > - > - if (cond_type == Item::SUBSELECT_ITEM) > - { > - Item_subselect *subs_cond= (Item_subselect*) cond; > - if (subs_cond->is_correlated) > - { > - DBUG_ASSERT(subs_cond->upper_refs.elements > 0); > - List_iterator_fast<Item_subselect::Ref_to_outside> > - li(subs_cond->upper_refs); > - Item_subselect::Ref_to_outside *dep; > - while ((dep= li++)) > - { > - if (dep->item->eq(min_max_arg_item, FALSE)) > - DBUG_RETURN(FALSE); > - } > - } > - DBUG_RETURN(TRUE); > - } > - > /* > - Condition of the form 'field' is equivalent to 'field <> 0' and thus > - satisfies the SA3 condition. > + Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX > + attribute. > */ > - if (cond_type == Item::FIELD_ITEM) > + SEL_ARG *min_max_range= index_tree; > + while (min_max_range) /* Find the tree for the MIN/MAX key part. */ > { > - DBUG_PRINT("info", ("Analyzing: %s", cond->full_name())); > - DBUG_RETURN(TRUE); > - } > - > - /* We presume that at this point there are no other Items than functions. */ > - DBUG_ASSERT(cond_type == Item::FUNC_ITEM); > - > - /* Test if cond references only group-by or non-group fields. */ > - Item_func *pred= (Item_func*) cond; > - Item **arguments= pred->arguments(); > - Item *cur_arg; > - DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); > - for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++) > - { > - cur_arg= arguments[arg_idx]->real_item(); > - DBUG_PRINT("info", ("cur_arg: %s", cur_arg->full_name())); > - if (cur_arg->type() == Item::FIELD_ITEM) > - { > - if (min_max_arg_item->eq(cur_arg, 1)) > - { > - /* > - If pred references the MIN/MAX argument, check whether pred is a range > - condition that compares the MIN/MAX argument with a constant. > - */ > - Item_func::Functype pred_type= pred->functype(); > - if (pred_type != Item_func::EQUAL_FUNC && > - pred_type != Item_func::LT_FUNC && > - pred_type != Item_func::LE_FUNC && > - pred_type != Item_func::GT_FUNC && > - pred_type != Item_func::GE_FUNC && > - pred_type != Item_func::BETWEEN && > - pred_type != Item_func::ISNULL_FUNC && > - pred_type != Item_func::ISNOTNULL_FUNC && > - pred_type != Item_func::EQ_FUNC && > - pred_type != Item_func::NE_FUNC) > - DBUG_RETURN(FALSE); > - > - /* Check that pred compares min_max_arg_item with a constant. */ > - Item *args[3]; > - bzero(args, 3 * sizeof(Item*)); > - bool inv; > - /* Test if this is a comparison of a field and a constant. */ > - if (!simple_pred(pred, args, &inv)) > - DBUG_RETURN(FALSE); > - > - /* Check for compatible string comparisons - similar to get_mm_leaf. */ > - if (args[0] && args[1] && !args[2] && // this is a binary function > - min_max_arg_item->result_type() == STRING_RESULT && > - /* > - Don't use an index when comparing strings of different collations. > - */ > - ((args[1]->result_type() == STRING_RESULT && > - image_type == Field::itRAW && > - ((Field_str*) min_max_arg_item->field)->charset() != > - pred->compare_collation()) > - || > - /* > - We can't always use indexes when comparing a string index to a > - number. > - */ > - (args[1]->result_type() != STRING_RESULT && > - min_max_arg_item->field->cmp_type() != args[1]->result_type()))) > - DBUG_RETURN(FALSE); > - } > - } > - else if (cur_arg->type() == Item::FUNC_ITEM) > - { > - if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, > - image_type)) > - DBUG_RETURN(FALSE); > - } > - else if (cur_arg->const_item()) > - { > - /* > - For predicates of the form "const OP expr" we also have to check 'expr' > - to make a decision. > - */ > - continue; > - } > - else > - DBUG_RETURN(FALSE); > + if (min_max_range->field->eq(min_max_arg_item->field)) > + break; > + min_max_range= min_max_range->next_key_part; > } > - > - DBUG_RETURN(TRUE); > + return min_max_range; > } > > BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

[Maria-developers] MariaDB 5.3.4 release status
by Sergei Petrunia 15 Feb '12

15 Feb '12

Hi, I've pushed the last bug fix slated for MariaDB 5.3.4, so revno:3419 has a chance to become the 5.3.4 release. It is currently building in buildbot. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 7

Re: [Maria-developers] [Commits] Rev 3421: Counters for Index Condition Pushdown added (MDEV-130). in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-130/
by Sergei Petrunia 15 Feb '12

15 Feb '12

Hi Sanja, On Wed, Feb 15, 2012 at 04:13:54PM +0200, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-130/ > > ------------------------------------------------------------ > revno: 3421 > revision-id: sanja(a)montyprogram.com-20120215141351-rk4pi025utdztzt8 > parent: holyfoot(a)askmonty.org-20120214120406-02q2mv2t6xb37dno > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.3-MDEV-130 > timestamp: Wed 2012-02-15 16:13:51 +0200 > message: > Counters for Index Condition Pushdown added (MDEV-130). OK to push. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Failure in plugins.feedback_plugin_send
by Kristian Nielsen 14 Feb '12

14 Feb '12

Hi Serg, 5.5 Buildbot is getting greener, but there are still a couple of random failures left. One of them is in plugins.feedback_plugin_send: ----------------------------------------------------------------------- plugins.feedback_plugin_send w1 [ fail ] Test ended at 2012-02-12 00:41:06 CURRENT_TEST: plugins.feedback_plugin_send --- /home/buildbot/build/mysql-test/suite/plugins/r/feedback_plugin_send.result 2012-02-11 21:14:54.336622000 +0200 +++ /home/buildbot/build/mysql-test/suite/plugins/r/feedback_plugin_send.reject 2012-02-12 00:41:05.785412054 +0200 @@ -9,7 +9,4 @@ FEEDBACK_SEND_TIMEOUT 60 FEEDBACK_URL http://mariadb.org/feedback_plugin/post FEEDBACK_USER_INFO mysql-test -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' +feedback plugin: failed to send report to 'http://mariadb.org/feedback_plugin/post' mysqltest: Result length mismatch ----------------------------------------------------------------------- Any ideas? The test has been failing for a while, though rarely (but maybe some of the past failures have been fixed): http://buildbot.askmonty.org/buildbot/reports/cross_reference#branch=&revis… - Kristian.

2 1

[Maria-developers] Please try new fulltext storage engine "mroonga"
by Kazuhiko Shiozaki 07 Feb '12

07 Feb '12

Hello ! I would like to introduce a new fulltext storage engine "mroonga", that will be included in the future MariaDB release. Mroonga is a fulltext storage engine having the following characteristics: * CJK ready fulltext search * no read lock * faster than MyISAM for both indexing and searching * geolocalazation search is also supported Current development branch is : https://code.launchpad.net/~maria-captains/maria/mariadb-5.3-monty Further information about mroonga can be found : https://kb.askmonty.org/en/about-mroonga You can also see some presentations : http://mroonga.github.com/publication/ I sincerely ask you to try mroonga, at least compile and run tests, on various platforms, like the following procedure. (get source tree) $ ./BUILD/compile-pentium-max (wait for a while) $ cd mysql-test $ ./mtr --suite=mroonga_storage,mroonga_wrapper --force If you find any problem like compilation error, test failure, or whatever, please let me know. Thanks in advance ! -- Kazuhiko Shiozaki, Nexedi SA Senior Consultant Nexedi: Consulting and Development of Free / Open Source Software http://www.nexedi.co.jp/ ERP5: Full Featured High End Open Source ERP http://www.erp5.com/

1 0

[Maria-developers] New KB Question: SSL with other Clients than the original MariaDB-client?
by Daniel Bartholomew 03 Feb '12

03 Feb '12

A new question has been posted to the Knowledgebase. http://kb.askmonty.org/en/ssl-with-other-clients-than-the-original-mariadb-… ==================== It seems to be that there is a difference in handling the SSL handshake at the MariaDB-Server (5.2 or below) in contrast to the Mysql-Server 5.1. All JDBC-Clients can sucessfully connect to the Mysql-Server 5.1 via SSL, but with MariaDB-Server not. Without SSL all JDBC-Clients can sucessfully connect to MariaDB. Both server run with the same CA-, Server- and Client-Certificates and I have made the following tests (all with MariaDB Server 5.2.10 and also tested with 5.1.60 from the Deb-Repository): * Mysql-Client (5.1) cannot connect to MariaDB Server: "ERROR 2026 (HY000): SSL connection error" * Mysql-connector/J cannot connect to MariaDB Server: TLSv1 Handshake fails with "unexpected message" after ClientHello * Drizzle JDBC cannot connect to MariaDB-Server: TLSv1 Handshake fails also with "unexpected message" after ClientHello * Original MariaDB-Client (5.2) can connect easily via SSL with the CA-Certificate to MariaDB-Server. It seems to be a general communication problem in the SSL-Handshake after ClientHello. Is it a bug or a feature? Debug-Log of Java: <<fixed>> trigger seeding of SecureRandom done seeding SecureRandom Allow unsafe renegotiation: false Allow legacy hello messages: true Is initial handshake: true Is secure renegotiation: false %% No cached client session ClientHello, TLSv1 RandomCookie: GMT: 1328138424 bytes Session ID: {} Cipher Suites: [SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA, \\TLS_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, \\TLS_DHE_DSS_WITH_AES_128_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA, \\SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA, \\SSL_RSA_WITH_DES_CBC_SHA, SSL_DHE_RSA_WITH_DES_CBC_SHA, \\ SSL_DHE_DSS_WITH_DES_CBC_SHA, SSL_RSA_EXPORT_WITH_RC4_40_MD5, \\SSL_RSA_EXPORT_WITH_DES40_CBC_SHA, SSL_DHE_RSA_EXPORT_WITH_DES40_CBC_SHA, \\SSL_DHE_DSS_EXPORT_WITH_DES40_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV] \\ Compression Methods: { 0 } \\ [write] MD5 and SHA1 hashes: len = 75 \\ main, WRITE: TLSv1 Handshake, length = 75 \\ [Raw write]: length = 80 \\ [Raw read]: length = 5 \\ 0000: 16 00 00 02 FF .....\\ main, handling exception: javax.net.ssl.SSLException: Unsupported record version Unknown-0.0\\ main, SEND TLSv1 ALERT: fatal, description = unexpected_message\\ main, WRITE: TLSv1 Alert, length = 2\\ <</fixed>> ==================== Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] New KB Question: How can I install the pam_authentication plugin in deb based system?
by Daniel Bartholomew 01 Feb '12

01 Feb '12

A new question has been posted in the Knowledgebase: How can I install the pam_authentication plugin in deb based system? http://kb.askmonty.org/en/how-can-i-install-the-pam_authentication-plugin-i… ========== Hi all, I've been reading the pam_auth plugin howto: https://kb.askmonty.org/en/pam-authentication-plugin MariaDB [test]> INSTALL PLUGIN pam SONAME 'auth_pam.so'; ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/auth_pam.so' (errno: 2 cannot open shared object file: No such file or directory) Indeed that file does not exist on the system. Nor does the pam_mariadb_mtr.so file. I installed mariadb-server-5.2 using the deb repos on Ubuntu 10.04 64bit (Lucid). Is there any deb archive to include those files in binary? Or do I have to resort to source compilation? ========== Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] Preparing to release MariaDB 5.3.4-rc
by Daniel Bartholomew 31 Jan '12

31 Jan '12

All, Monty has informed me that we are ready to release MariaDB 5.3.4-rc. I'm uploading the packages to the primary mirror now and if all goes well the general release announcement will go out tomorrow morning (my time). The source tree has not been tagged yet but the release is from revision 3402: http://bazaar.launchpad.net/~maria-captains/maria/5.3/changes To all the devs: If there is anything notable which I should make sure to include in the release notes, please let me know ASAP. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] Is our annotate_rows_log_event same as mysql-5.6's?
by Kristian Nielsen 31 Jan '12

31 Jan '12

Sergei Petrunia <psergey(a)askmonty.org> writes: > Also, it seems that right now binary logs are not compatible between mysql-5.6 > and mariadb-5.3. When I try read one file with mysqlbinlog of the other I get > this: > > > mariadb-5.3 reading binlog from mysql-5.6 > #120131 2:43:14 server id 1 end_log_pos 295 Query thread_id=2 exec_time=0 error_code=0 > SET TIMESTAMP=1327963394/*!*/; > BEGIN > /*!*/; > ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 62, event_type: 29 > ERROR: Could not read entry at offset 295: Error in log format or read error. Yes, 29 is ROWS_QUERY_LOG_EVENT, their version of annotate_rows_log_event. I think you will find the same problem if you try to read with MySQL 5.1 or 5.5. I believe this only happens if you enable row annotation events on the master; it seems a general thing that if one enables a new feature on a MySQL master, it breaks old slaves. However, when we merge 5.6, we get the new IGNORABLE_LOG_EVENT flag, which allows a newer master (eg. 5.7) to inform an old slave that it is ok to ignore a specific event. When we merge 5.6, I suppose we will just make sure MariaDB 5.6 writes the MySQL event code on the master, and understands the MariaDB event code on the slave, so this will be solved. > Mysql 5.6 reading mariadb-5.3 > ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 48, event_type: -96 > ERROR: Could not read entry at offset 503: Error in log format or read error. > # at 434 > #120131 2:01:47 server id 1 end_log_pos 503 Query thread_id=1 exec_time=0 error_code=0 > SET TIMESTAMP=1327960907/*!*/; > BEGIN And this is the reverse situation, -96 is our ANNOTATE_ROWS_EVENT, which is not understood by MySQL. Again, this only happens when row annotation is explicitly enabled on the master, so one should not do this when having old slaves connected (whether MySQL or MariaDB). There is a deeper issue here. It is not possible on the master to simply filter out the events that an old slave does not understand, as would seem natural. Because the current slave code gets confused when there are "holes" in the binlog positions it receives from the master. In MariaDB 5.5 this will be fixed (if I'm allowed to push MWL#234 ...), allowing a MariaDB slave to adjust binlog positions correctly if the master filters out events. But this does not help MySQL slaves of course. Also, global transaction IDs, which MySQL@Oracle says they are working on, should fix this, but this is not in 5.6 currently. Thanks for taking up these subjects, it's good to get them more clearly understood! - Kristian.

1 0

Re: [Maria-developers] Is our annotate_rows_log_event same as mysql-5.6's?
by Kristian Nielsen 31 Jan '12

31 Jan '12

Sergei Petrunia <psergey(a)askmonty.org> writes: > I see MySQL have added an analog ours > http://kb.askmonty.org/en/annotate_rows_log_event > > http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#… > > The names of the options are different: binlog_annotate_row_events vs. binlog_rows_query_log_events > Questions (I assume you can answer them off the top of the head, if not, let's > first discuss it) > > - Is it true that they have re-implemented the feature after we did? Yes. > - Do I understand correctly that the features are not compatible? (different > event code) Yes. However, I checked the MySQL version some time ago. It is extremely similar to our version. I believe it will be quite feasible when merging to make them compatible, eg. just translate the event code from one to the other. Maybe we will just merge the MySQL version for compatibility (MySQL developers do not seem to care about compatibility themselves), and then just add backwards and forwards compatibility translation so we work with old versions of MariaDB. > - Do these features do the same thing or one is better than the other in some > respect? As I remember, they are essentially identical. - Kristian.

1 0

[Maria-developers] alternative for Field_timestamp::get_timestamp etc. ?
by Kazuhiko 30 Jan '12

30 Jan '12

Hello, I tried to build mroonga-1.3.0 ( http://mroonga.github.com/ ) with MariaDB 5.3.3 and got the following error: > g++ -DHAVE_CONFIG_H -I. -I/opt/mariadb__compile__/mariadb-5.3.3-rc/sql -I/opt/mariadb__compile__/mariadb-5.3.3-rc/include -I/opt/mariadb__compile__/mariadb-5.3.3-rc/regex -I/opt/mariadb__compile__/mariadb-5.3.3-rc -I/opt/mariadb/include/mysql -I/opt/groonga/include/groonga -I/opt/groonga/include/groonga -I/opt/mariadb/include/mysql -g -DUNIV_LINUX -g -O2 -Wall -Wextra -Wno-unused-parameter -Wno-strict-aliasing -Werror -fno-implicit-templates -fno-exceptions -fno-rtti -felide-constructors -MT libmroonga_a-ha_mroonga.o -MD -MP -MF .deps/libmroonga_a-ha_mroonga.Tpo -c -o libmroonga_a-ha_mroonga.o `test -f 'ha_mroonga.cc' || echo './'`ha_mroonga.cc > ha_mroonga.cc: In member function ‘int ha_mroonga::generic_store_bulk_timestamp(Field*, grn_obj*)’: > ha_mroonga.cc:6935:63: error: no matching function for call to ‘Field_timestamp::get_timestamp(my_bool*)’ > ha_mroonga.cc:6935:63: note: candidate is: > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:1221:21: note: virtual my_time_t Field_timestamp::get_timestamp(ulong*) const > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:1221:21: note: no known conversion for argument 1 from ‘my_bool* {aka char*}’ to ‘ulong* {aka long unsigned int*}’ > ha_mroonga.cc: In member function ‘void ha_mroonga::storage_store_field_timestamp(Field*, const char*, uint)’: > ha_mroonga.cc:7234:20: error: ‘class Field_timestamp’ has no member named ‘store_timestamp’ > ha_mroonga.cc: In member function ‘void ha_mroonga::storage_store_field_datetime(Field*, const char*, uint)’: > ha_mroonga.cc:7286:67: error: no matching function for call to ‘Field_datetime::store_time(MYSQL_TIME*, enum_mysql_timestamp_type)’ > ha_mroonga.cc:7286:67: note: candidate is: > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:209:7: note: int Field::store_time(MYSQL_TIME*) > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:209:7: note: candidate expects 1 argument, 2 provided > ha_mroonga.cc: In member function ‘void ha_mroonga::storage_store_field_new_date(Field*, const char*, uint)’: > ha_mroonga.cc:7306:62: error: no matching function for call to ‘Field_newdate::store_time(MYSQL_TIME*, enum_mysql_timestamp_type)’ > ha_mroonga.cc:7306:62: note: candidate is: > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:209:7: note: int Field::store_time(MYSQL_TIME*) > /opt/mariadb__compile__/mariadb-5.3.3-rc/sql/field.h:209:7: note: candidate expects 1 argument, 2 provided > make[2]: *** [libmroonga_a-ha_mroonga.o] Error 1 (you can see the code at https://github.com/mroonga/mroonga/blob/master/ha_mroonga.cc ) At a glance, sql/field.h seems much different from MySQL-5.1's one. What can be the alternative for the followings ? * Field_timestamp::get_timestamp(my_bool*) * Field_datetime::store_time(MYSQL_TIME*, enum_mysql_timestamp_type) * Field_newdate::store_time(MYSQL_TIME*, enum_mysql_timestamp_type) Thanks in advance. Kazuhiko

2 2

Re: [Maria-developers] [Commits] Rev 3398: Make testcase stable by adding --sorted_result for SHOW STATUS commands. in file:///home/psergey/dev2/5.3-look48/
by Sergei Golubchik 28 Jan '12

28 Jan '12

Hi, Sergey! What do you mean? SHOW STATUS returns a sorted list of variables. On Jan 27, Sergey Petrunya wrote: > At file:///home/psergey/dev2/5.3-look48/ > > revno: 3398 > revision-id: psergey(a)askmonty.org-20120127135140-4ues2tk6juf4iynx > parent: psergey(a)askmonty.org-20120126102034-tp33uzyuqnyk30mn > committer: Sergey Petrunya <psergey(a)askmonty.org> > branch nick: 5.3-look48 > timestamp: Fri 2012-01-27 17:51:40 +0400 > message: > Make testcase stable by adding --sorted_result for SHOW STATUS commands. Regards, Sergei

2 2

Re: [Maria-developers] [Commits] Rev 3241: Xtradb recently started to access thd members directly , e.g thd->stmt_da (ha_innodb.cc) in file:///H:/bzr/5.5/
by Kristian Nielsen 27 Jan '12

27 Jan '12

Vladislav Vaintroub <wlad(a)montyprogram.com> writes: > Xtradb recently started to access thd members directly ,e.g thd->stmt_da (ha_innodb.cc) > It needs recompilation for embedded server, as layout of THD is different in embedded. Did you consider instead fixing XtraDB to not access thd members directly? Eg. using proper accessors, maybe adding new accessors if needed? Or is this not possible for some reason? It would be much preferable if XtraDB didn't mess with server internals like this ... - Kristian.

3 2

[Maria-developers] mysql_client_test
by Kristian Nielsen 25 Jan '12

25 Jan '12

Just to finish our discussions on IRC... I found that mysql_client_test.test runs fine without any --plugin-dir=xxx option at all for mysql_client_test. The reason is that the correct option is in the my.cnf that we generate. So I decided to just remove the --plugin-dir option from the command line in mysql_client_test.test; this seems to work both when tested from source dir and when testing installed server. - Kristian.

1 0

[Maria-developers] Need help re. which files to include in 5.5 .debs
by Kristian Nielsen 25 Jan '12

25 Jan '12

Hi Serg, As you suggested, I compared .deb file contents with what `make install` produces in 5.5. I discovered that the following files are missing from .debs. I need help understanding which of these should be added to the .debs. (I think anything added should be to package libmariadbclient-dev): /usr/lib/libmysqlservices.a /usr/include/mysql.h /usr/include/mysql_com.h /usr/include/mysqld_ername.h /usr/include/mysqld_error.h /usr/include/mysql_embed.h /usr/include/mysql/psi /usr/include/mysql/psi/mysql_file.h /usr/include/mysql/psi/mysql_thread.h /usr/include/mysql/psi/psi_abi_v1.h /usr/include/mysql/psi/psi_abi_v2.h /usr/include/mysql/psi/psi.h /usr/include/client_plugin.h /usr/include/decimal.h /usr/include/errmsg.h /usr/include/keycache.h /usr/include/m_ctype.h /usr/include/m_string.h /usr/include/my_alloc.h /usr/include/my_attribute.h /usr/include/my_compiler.h /usr/include/my_dbug.h /usr/include/my_getopt.h /usr/include/my_global.h /usr/include/my_list.h /usr/include/my_net.h /usr/include/my_pthread.h /usr/include/mysql_time.h /usr/include/mysql_version.h /usr/include/my_sys.h /usr/include/my_valgrind.h /usr/include/my_xml.h /usr/include/plugin_audit.h /usr/include/plugin_auth.h /usr/include/plugin_ftparser.h /usr/include/plugin.h /usr/include/sql_common.h /usr/include/sql_state.h /usr/include/sslopt-case.h /usr/include/sslopt-longopts.h /usr/include/sslopt-vars.h /usr/include/typelib.h It seems pretty obvious that "mysql.h" is needed. But should I add _all_ the .h files? And what about libmysqlservices.a? - Kristian.

2 5

Re: [Maria-developers] something better than PMP
by Kristian Nielsen 25 Jan '12

25 Jan '12

Arun Sharma <asharma(a)fb.com> writes: > On 1/21/12 7:24 AM, Mark Callaghan wrote: >> 1 millisecond per thread would be much better than what we experience >> today. >> > > Is there a reason why latency per-thread is more interesting than the > absolute number? You are right of course - the interesting number is the time we spent with the target mysqld process suspended (stalled) under ptrace(). The reason I think in terms of latency-per-thread is this: Different mysqld instances can have widely different number of threads - from a few handful to perhaps thousands. Since we loop over threads while the process is ptrace()-suspended, it seems a nice measure to estimate the cost independent of number of threads in the process. >>> Arun: What I've done so far is mostly to cache reads of the same word, and >>> delay name resolve to after the target process is resumed. I think it can >>> be >>> made even faster by reading target process memory through /proc/PID/mem >>> instead of ptrace and if we can reduce repeated reads of /proc/PID/maps >>> and >>> such. > > Sounds like a good optimization to have. There is already a cache > internal to libunwind that maps a given IP address to it's unwind > info. If the repeated reads come from that code path, tweaking the > cache size might be a simpler fix. Ok, that sounds cool! - Kristian.

1 0

Re: [Maria-developers] [Commits] Rev 3129: remove few hard-coded checks from mtr in http://bazaar.launchpad.net/~maria-captains/maria/5.1/
by Kristian Nielsen 25 Jan '12

25 Jan '12

<serg(a)askmonty.org> writes: > At http://bazaar.launchpad.net/~maria-captains/maria/5.1/ > > ------------------------------------------------------------ > revno: 3129 > revision-id: sergii(a)pisem.net-20120124140839-iziwgt30gaalsiw9 > parent: sergii(a)pisem.net-20120124125357-b3o68crdtg2w3fzj > committer: Sergei Golubchik <sergii(a)pisem.net> > branch nick: 5.1 > timestamp: Tue 2012-01-24 15:08:39 +0100 > message: > remove few hard-coded checks from mtr Why?!? It's much faster to have the checks in mtr_cases.pm. Otherwise we have to start and stop the mysqld server(s), which is often the major part of time spent in a test case. The test suite is slow enough already ... Also, some of these tests _must_ be in mtr_cases, because just starting the server and executing the test for "skip" causes a failure. Eg. I remember that "not_valgrind" works this way, so I think your change will cause some tests to break in valgrind. If we want to improve things so that we have all checks in one place, I would prefer to move everything into mysql-test-run.pl. In the long term, not just for skipping tests but for everything. There is so much cruft in there just do deal with the testsuite being written partly in perl, partly in SQL (parse warnings...), partly in the mysqltest language, partly in C (mysqltest), ... > - if ( $tinfo->{'not_valgrind'} ) > - { > - if ( $::opt_valgrind_mysqld ) > - { > - $tinfo->{'skip'}= 1; > - $tinfo->{'comment'}= "Not compatible with Valgrind testing"; > - return $tinfo; > - } > - } - Kristian

2 2

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 23 Jan '12

23 Jan '12

Yes, I accept On Mon, Jan 23, 2012 at 4:12 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Mark, > > >> The semantics are good for us. The potential overhead from the >> implementation isn't a concern for our uses. What was the feedback >> from the MPAB code review? > > > I haven't submitted the code for review because there are few more > small items to complete, however, the current solution was discussed > with Monty and Igor, and we all came to an agreement that this is the > best solution. > > Speaking of overhead, if the LIMIT_ROWS_EXAMINED is not present, then > the overhead is an additional IF statement in > handler::increment_statistics(): > > inline void handler::increment_statistics(ulong SSV::*offset) const > { > status_var_increment(table->in_use->status_var.*offset); > + if (table->in_use->examined_rows_limit_cnt < HA_POS_ERROR) > + table->in_use->check_examined_rows_limit(); > } > > If the LIMIT_ROWS_EXAMINED clause is present, the overhead is this inline > method: > > /** > Check if the number of rows accessed by a statement exceeded > LIMIT_ROWS_EXAMINED. If so, stop execution. > */ > void check_examined_rows_limit() > { > if (++status_var.accessed_rows_and_keys > examined_rows_limit_cnt) > { > push_warning_printf(this, MYSQL_ERROR::WARN_LEVEL_WARN, > ER_QUERY_EXCEEDED_EXAMINED_ROWS_LIMIT, > ER(ER_QUERY_EXCEEDED_EXAMINED_ROWS_LIMIT), > status_var.accessed_rows_and_keys); > killed= ABORT_QUERY; > } > } > > I believe this overhead for each handler call is negligible. If the overhead > is measurable, then we could improve by making handler::increment_statistics > a function pointer and choosing the right implementation before execution. > This can easily be done if needed. > > Ok, so I will assume your reply means that you accept this approach, and > will > complete the feature ASAP. > > > Timour > > >> >> On Fri, Jan 20, 2012 at 7:37 AM, MARK CALLAGHAN<mdcallag(a)gmail.com> >> wrote: >>> >>> OK, will have feedback in a few days. >>> >>> On Fri, Jan 20, 2012 at 6:45 AM, Timour Katchaounov >>> <timour(a)montyprogram.com> wrote: >>>> >>>> Hello Mark, >>>> >>>> >>>>> I want a variant of the LIMIT clause that limits the number of rows >>>>> examined during SELECT processing. This should return OK when the >>>>> limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While >>>>> LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- >>>>> LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature >>>>> for me? We will sponsor the work. The purpose of the feature is to >>>>> prevent queries from taking too long when there isn't a great index >>>>> and many rows might be filtered. >>>>> >>>> >>>> After examining few alternatives, I implemented a pretty complete >>>> prototype of LIMIT_ROWS_EXAMINED. Please have a look at the description >>>> of the task here: >>>> https://mariadb.atlassian.net/browse/MDEV-28 >>>> >>>> It is important to notice that the current implementation will stop >>>> execution when LIMIT_ROWS_EXAMINED is reached irrespective of the >>>> query execution strategies used (thus not only for JOINs, but also >>>> writing/reading to temp tables, sorting, and single-table access methods >>>> that scan too many rows). This approach also is future-proof with >>>> respect to adding new query processing algorithms because the counting >>>> is done at the handler level. >>>> >>>> The current approach is such, that it should be fairly easy to backport >>>> the feature to almost any MariaDB/MySQL. >>>> >>>> Please let me know if this is what you want. If so, then I will proceed >>>> with the remaining polishing that is in the TODO list and will get an >>>> internal review. >>>> >>>> You can also have a look at the small example in the comments below the >>>> description to get a feeling what is the behavior. >>>> >>>> I can also send you a patch of the current version in case you want to >>>> play with the feature before it is pushed. >>>> >>>> Timour >>> >>> >>> >>> >>> -- >>> Mark Callaghan >>> mdcallag(a)gmail.com >> >> >> >> > -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] MDEV-30: "mtr extensions for storage engine testing" review
by Kristian Nielsen 23 Jan '12

23 Jan '12

Hi Serg! As promised, I read through your MDEV-30, "mtr extensions for storage engine testing". Generally I think it looks good and reasonable. Here are a few comments: - --record should have options so that it can generate .rdiff or .COMBI.result. So one could say eg. ./mtr --record=rdiff <some test> to produce <whatever>.rdiff. It's not entirely trivial what the exact syntax should be due to the complicated structure of result file names... - Windows (the usual problem child :-( ) has no native `patch` tool. Will we require it to be installed, or will we gracefully skip tests that need it if no patch is available? - About the "options" at the end about file names wrt. suites: I did not fully understand - but it seems important that whatever is done, existing lots of test cases from Oracle/MySQL can be used without having to rewrite their --source statements. Will this be the case? Hope this helps, - Kristian.

2 1

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 20 Jan '12

20 Jan '12

OK, will have feedback in a few days. On Fri, Jan 20, 2012 at 6:45 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Hello Mark, > > >> I want a variant of the LIMIT clause that limits the number of rows >> examined during SELECT processing. This should return OK when the >> limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While >> LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- >> LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature >> for me? We will sponsor the work. The purpose of the feature is to >> prevent queries from taking too long when there isn't a great index >> and many rows might be filtered. >> > > After examining few alternatives, I implemented a pretty complete > prototype of LIMIT_ROWS_EXAMINED. Please have a look at the description > of the task here: > https://mariadb.atlassian.net/browse/MDEV-28 > > It is important to notice that the current implementation will stop > execution when LIMIT_ROWS_EXAMINED is reached irrespective of the > query execution strategies used (thus not only for JOINs, but also > writing/reading to temp tables, sorting, and single-table access methods > that scan too many rows). This approach also is future-proof with > respect to adding new query processing algorithms because the counting > is done at the handler level. > > The current approach is such, that it should be fairly easy to backport > the feature to almost any MariaDB/MySQL. > > Please let me know if this is what you want. If so, then I will proceed > with the remaining polishing that is in the TODO list and will get an > internal review. > > You can also have a look at the small example in the comments below the > description to get a feeling what is the behavior. > > I can also send you a patch of the current version in case you want to > play with the feature before it is pushed. > > Timour -- Mark Callaghan mdcallag(a)gmail.com

1 1

Re: [Maria-developers] [Commits] Rev 3227: Fix embedded build on Windows. in file:///H:/bzr/5.5/
by Sergei Golubchik 20 Jan '12

20 Jan '12

Hi, Vladislav! On Jan 20, Vladislav Vaintroub wrote: > revno: 3227 > revision-id: wlad(a)montyprogram.com-20120120155435-y8tyd01st3fpbq1e > parent: wlad(a)montyprogram.com-20120120113906-f6wcj47dyo6jyt45 > committer: Vladislav Vaintroub <wlad(a)montyprogram.com> > branch nick: 5.5 > timestamp: Fri 2012-01-20 16:54:35 +0100 > message: > Fix embedded build on Windows. > === modified file 'mysys/CMakeLists.txt' > --- a/mysys/CMakeLists.txt 2012-01-20 00:57:58 +0000 > +++ b/mysys/CMakeLists.txt 2012-01-20 15:54:35 +0000 > @@ -46,7 +46,7 @@ > SET (MYSYS_SOURCES ${MYSYS_SOURCES} my_addr_resolve.c) > ENDIF() > > -IF(WITH_SAFEMALLOC OR NOT HAVE_CXX_NEW) > +IF(NOT HAVE_CXX_NEW) > ADD_DEFINITIONS( -DUSE_MYSYS_NEW) Why is that? I thought that WITH_SAFEMALLOC is always off on Windows? Note, that we should always use mysys/new.cc when safemalloc is enabled. Regards, Sergei

2 2

Re: [Maria-developers] [Commits] Rev 3020: MWL#192: Non-blocking client API for libmysqlclient. in http://bazaar.launchpad.net/~maria-captains/maria/5.2
by Michael Widenius 09 Jan '12

09 Jan '12

Hi! >>>>> "knielsen" == knielsen <knielsen(a)knielsen-hq.org> writes: knielsen> At http://bazaar.launchpad.net/~maria-captains/maria/5.2 knielsen> ------------------------------------------------------------ knielsen> revno: 3020 knielsen> revision-id: knielsen(a)knielsen-hq.org-20110920104925-huxke80yuq1zrvcx knielsen> parent: monty(a)askmonty.org-20110816160131-4kju8si1nqqnohxg knielsen> committer: knielsen(a)knielsen-hq.org knielsen> branch nick: tmp2 knielsen> timestamp: Tue 2011-09-20 12:49:25 +0200 knielsen> message: knielsen> MWL#192: Non-blocking client API for libmysqlclient. knielsen> All client functions that can block on I/O have alternate _start() and knielsen> _cont() versions that do not block but return control back to the knielsen> application, which can then issue I/O wait in its own fashion and later knielsen> call back into the library to continue the operation. knielsen> Works behind the scenes by spawning a co-routine/fiber to run the knielsen> blocking operation and suspend it while waiting for I/O. This knielsen> co-routine/fiber use is invisible to applications. knielsen> For i368/x86_64 on GCC, uses very fast assembler co-routine support. On knielsen> Windows uses native Win32 Fibers. Falls back to POSIX ucontext on other knielsen> platforms. Assembler routines for more platforms are relatively easy to knielsen> add by extending mysys/my_context.c, eg. similar to the Lua lcoco knielsen> library. knielsen> For testing, mysqltest and mysql_client_test are extended with the knielsen> option --non-blocking-api. This causes the programs to use the knielsen> non-blocking API for database access. mysql-test-run.pl has a similar knielsen> option --non-blocking-api that uses this, as well as additional knielsen> testcases. knielsen> An example program tests/async_queries.c is included that uses the new knielsen> non-blocking API with libevent to show how, in a single-threaded knielsen> program, to issue many queries in parallel against a database. <cut> +++ client/async_example.c 2011-09-20 10:49:25 +0000 @@ -0,0 +1,207 @@ +/* + Copyright 2011 Kristian Nielsen and Monty Program Ab. + + Experiments with non-blocking libmysql. + + This is free software: you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation, either version 2 of the License, or + (at your option) any later version. + + This is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this. If not, see <http://www.gnu.org/licenses/>. +*/ Please change license to LGPL for this and the other new files (so we can add them to our new LGPL client library) + + +#ifndef __WIN__ +#include <poll.h> +#else +#include <WinSock2.h> +#endif You don't need to incldue Winsock2.h; This is automaticly included by mysql.h. +#include <stdlib.h> +#include <stdio.h> +#include <mysql.h> <cut> === modified file 'client/mysqltest.cc' --- client/mysqltest.cc 2011-05-18 13:17:26 +0000 +++ client/mysqltest.cc 2011-09-20 10:49:25 +0000 @@ -60,6 +60,12 @@ #define SIGNAL_FMT "signal %d" #endif === modified file 'include/Makefile.am' --- include/Makefile.am 2011-05-03 16:10:10 +0000 +++ include/Makefile.am 2011-09-20 10:49:25 +0000 @@ -46,7 +46,7 @@ atomic/rwlock.h atomic/x86-gcc.h \ atomic/generic-msvc.h \ atomic/gcc_builtins.h my_libwrap.h my_stacktrace.h \ - wqueue.h waiting_threads.h + wqueue.h waiting_threads.h my_context.h EXTRA_DIST = mysql.h.pp mysql/plugin_auth.h.pp mysql/client_plugin.h.pp CMakeLists.txt === added file 'include/my_context.h' --- include/my_context.h 1970-01-01 00:00:00 +0000 +++ include/my_context.h 2011-09-20 10:49:25 +0000 @@ -0,0 +1,222 @@ +/* + Copyright 2011 Kristian Nielsen + + Experiments with non-blocking libmysql. + + This is free software: you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation, either version 2 of the License, or + (at your option) any later version. + + This is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this. If not, see <http://www.gnu.org/licenses/>. +*/ + +/* + Simple API for spawning a co-routine, to be used for async libmysqlclient. + + Idea is that by implementing this interface using whatever facilities are + available for given platform, we can use the same code for the generic + libmysqlclient-async code. + + (This particular implementation uses Posix ucontext swapcontext().) +*/ + +#ifdef __WIN__ +#define MY_CONTEXT_USE_WIN32_FIBERS 1 +#elif defined(__GNUC__) && __GNUC__ >= 3 && defined(__x86_64__) +#define MY_CONTEXT_USE_X86_64_GCC_ASM +#elif defined(__GNUC__) && __GNUC__ >= 3 && defined(__i386__) +#define MY_CONTEXT_USE_I386_GCC_ASM +#else +#define MY_CONTEXT_USE_UCONTEXT +#endif + +#ifdef MY_CONTEXT_USE_WIN32_FIBERS +struct my_context { + void (*user_func)(void *); + void *user_arg; + void *app_fiber; + void *lib_fiber; + int return_value; +#ifndef DBUG_OFF + void *dbug_state; +#endif +}; +#endif + + +#ifdef MY_CONTEXT_USE_UCONTEXT +#include <ucontext.h> + +struct my_context { + void (*user_func)(void *); + void *user_data; + void *stack; + size_t stack_size; + ucontext_t base_context; + ucontext_t spawned_context; + int active; +#ifdef HAVE_VALGRIND_VALGRIND_H + unsigned int valgrind_stack_id; +#endif +#ifndef DBUG_OFF + void *dbug_state; +#endif +}; +#endif + + +#ifdef MY_CONTEXT_USE_X86_64_GCC_ASM +#include <stdint.h> + +struct my_context { + uint64_t save[9]; + void *stack_top; + void *stack_bot; +#ifdef HAVE_VALGRIND_VALGRIND_H + unsigned int valgrind_stack_id; +#endif +#ifndef DBUG_OFF + void *dbug_state; +#endif +}; +#endif + + +#ifdef MY_CONTEXT_USE_I386_GCC_ASM +#include <stdint.h> + +struct my_context { + uint64_t save[7]; + void *stack_top; + void *stack_bot; +#ifdef HAVE_VALGRIND_VALGRIND_H + unsigned int valgrind_stack_id; +#endif +#ifndef DBUG_OFF + void *dbug_state; +#endif +}; +#endif + + +/* + Initialize an asynchroneous context object. + Returns 0 on success, non-zero on failure. +*/ +extern int my_context_init(struct my_context *c, size_t stack_size); + +/* Free an asynchroneous context object, deallocating any resources used. */ +extern void my_context_destroy(struct my_context *c); + +/* + Spawn an asynchroneous context. The context will run the supplied user + function, passing the supplied user data pointer. + + The context must have been initialised with my_context_init() prior to + this call. + + The user function may call my_context_yield(), which will cause this + function to return 1. Then later my_context_continue() may be called, which + will resume the asynchroneous context by returning from the previous + my_context_yield() call. + + When the user function returns, this function returns 0. + + In case of error, -1 is returned. +*/ +extern int my_context_spawn(struct my_context *c, void (*f)(void *), void *d); + +/* + Suspend an asynchroneous context started with my_context_spawn. + + When my_context_yield() is called, execution immediately returns from the + last my_context_spawn() or my_context_continue() call. Then when later + my_context_continue() is called, execution resumes by returning from this + my_context_yield() call. + + Returns 0 if ok, -1 in case of error. +*/ +extern int my_context_yield(struct my_context *c); + +/* + Resume an asynchroneous context. The context was spawned by + my_context_spawn(), and later suspended inside my_context_yield(). + + The asynchroneous context may be repeatedly suspended with + my_context_yield() and resumed with my_context_continue(). + + Each time it is suspended, this function returns 1. When the originally + spawned user function returns, this function returns 0. + + In case of error, -1 is returned. +*/ +extern int my_context_continue(struct my_context *c); + + +struct mysql_async_context { + /* + This is set to the value that should be returned from foo_start() or + foo_cont() when a call is suspended. + It is also set to the event(s) that triggered when a suspended call is + resumed, eg. whether we woke up due to connection completed or timeout + in mysql_real_connect_cont(). + */ + unsigned int ret_status; + /* + This is set to the result of the whole asynchronous operation when it + completes. It uses a union, as different calls have different return + types. + */ + union { + void *r_ptr; + const void *r_const_ptr; + int r_int; + my_bool r_my_bool; + } ret_result; + /* + The timeout value, for suspended calls that need to wake up on a timeout + (eg. mysql_real_connect_start(). + */ + unsigned int timeout_value; + /* + This flag is set when we are executing inside some asynchronous call + foo_start() or foo_cont(). It is used to decide whether to use the + synchronous or asynchronous version of calls that may block such as + recv(). + + Note that this flag is not set when a call is suspended, eg. after + returning from foo_start() and before re-entering foo_cont(). + */ + my_bool active; + /* + This flag is set when an asynchronous operation is in progress, but + suspended. Ie. it is set when foo_start() or foo_cont() returns because + the operation needs to block, suspending the operation. + + It is used to give an error (rather than crash) if the application + attempts to call some foo_cont() method when no suspended operation foo is + in progress. + */ + my_bool suspended; + /* + If non-NULL, this is a pointer to a callback hook that will be invoked with + the user data argument just before the context is suspended, and just after + it is resumed. + */ + void (*suspend_resume_hook)(my_bool suspend, void *user_data); + void *suspend_resume_hook_user_data; + /* + This is used to save the execution contexts so that we can suspend an + operation and switch back to the application context, to resume the + suspended context later when the application re-invokes us with + foo_cont(). + */ + struct my_context async_context; +}; === modified file 'include/my_dbug.h' --- include/my_dbug.h 2011-02-20 16:51:43 +0000 +++ include/my_dbug.h 2011-09-20 10:49:25 +0000 @@ -83,6 +83,8 @@ extern void _db_unlock_file_(void); extern FILE *_db_fp_(void); extern void _db_flush_(); +extern void dbug_swap_code_state(void **code_state_store); +extern void dbug_free_code_state(void **code_state_store); Please define a macros DBUG_FREE_CODE_STATE / DBUG_SWAP_CODE_STATE that are mapped to the aboe if DBUG is defined and to "do {} while (0)" otherwise. This allows you to replace: #ifndef DBUG_OFF dbug_free_code_state(&c->dbug_state); #endif with DBUG_FREE_CODE_STATE(&c->dbug_state); (It's always nice to not have a lot of #ifdef in the code). +++ include/mysql.h 2011-09-20 10:49:25 +0000 void *extension; } MYSQL_PARAMETERS; +/* + Flag bits, the asynchronous methods return a combination of these ORed + together to let the application know when to resume the suspended operation. +*/ +typedef enum { + MYSQL_WAIT_READ= 1, /* Wait for data to be available on socket to read */ + /* mysql_get_socket_fd() will return socket descriptor*/ + MYSQL_WAIT_WRITE= 2, /* Wait for socket to be ready to write data */ + MYSQL_WAIT_EXCEPT= 4, /* Wait for select() to mark exception on socket */ + MYSQL_WAIT_TIMEOUT= 8 /* Wait until timeout occurs. Value of timeout can be */ + /* obtained from mysql_get_timeout_value() */ +} MYSQL_ASYNC_STATUS; + Why have enum's instead of defines ? (As these are bits and you are not going to use 'switch' on these, defines makes more sense). #ifdef USE_OLD_FUNCTIONS MYSQL * STDCALL mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd); +int STDCALL mysql_connect_start(MYSQL **ret, MYSQL *mysql, + const char *host, const char *user, + const char *passwd); +int STDCALL mysql_connect_cont(MYSQL **ret, MYSQL *mysql, + int status); int STDCALL mysql_create_db(MYSQL *mysql, const char *DB); +int STDCALL mysql_create_db_start(int *ret, MYSQL *mysql, + const char *DB); +int STDCALL mysql_create_db_cont(int *ret, MYSQL *mysql, + int status); +int STDCALL mysql_drop_db(MYSQL *mysql, const char *DB); +int STDCALL mysql_drop_db_start(int *ret, MYSQL *mysql, + const char *DB); +int STDCALL mysql_drop_db_cont(int *ret, MYSQL *mysql, int status); int STDCALL mysql_drop_db(MYSQL *mysql, const char *DB); #define mysql_reload(mysql) mysql_refresh((mysql),REFRESH_GRANT) #endif If USE_OLD_FUNCTIONS is defined, you don't have to provide the new api. These are deprecated functions that we don't want people to use so there is no reason to provide 'better' versions of them... === modified file 'include/sql_common.h' --- include/sql_common.h 2010-04-01 09:04:26 +0000 +++ include/sql_common.h 2011-09-20 10:49:25 +0000 @@ -26,11 +26,18 @@ extern const char *cant_connect_sqlstate; extern const char *not_error_sqlstate; + +struct mysql_async_context; + struct st_mysql_options_extention { char *plugin_dir; char *default_auth; }; +struct st_mysql_extension { + struct mysql_async_context *async_context; +}; + Why provide 'st_mysql_extension' in a public file? Isn't it enough to have this only in a local file in the client directory? (We don't want anyone to access this structure in any clients) === modified file 'include/violite.h' --- include/violite.h 2010-01-29 10:42:31 +0000 +++ include/violite.h 2011-09-20 10:49:25 +0000 @@ -194,6 +194,8 @@ char *read_pos; /* start of unfetched data in the read buffer */ char *read_end; /* end of unfetched data */ + struct mysql_async_context *async_context; /* For non-blocking API */ + uint read_timeout, write_timeout; /* function pointers. They are similar for socket/SSL/whatever */ void (*viodelete)(Vio*); int (*vioerrno)(Vio*); Hm, I haven't thought about that it's safe to add new things to struct st_vio without breaking the interface. That's good and useful to know! <cut> <cut> === added file 'mysql-test/t/non_blocking_api.test' --- mysql-test/t/non_blocking_api.test 1970-01-01 00:00:00 +0000 +++ mysql-test/t/non_blocking_api.test 2011-09-20 10:49:25 +0000 @@ -0,0 +1,18 @@ +# Test mixing the use of blocking and non-blocking API in a single connection. + +--enable_non_blocking_api +connect (con_nonblock,localhost,root,,test); +--disable_non_blocking_api +connect (con_normal,localhost,root,,test); + Usually all test starts with drop table if exists t1; This is mostly useful when you want to run the tests against an external server. Not critical but good practice to do... <cut> === added file 'mysys/my_context.c' --- mysys/my_context.c 1970-01-01 00:00:00 +0000 +++ mysys/my_context.c 2011-09-20 10:49:25 +0000 @@ -0,0 +1,749 @@ +/* + Copyright 2011 Kristian Nielsen + + Experiments with non-blocking libmysql. + Add the information text after the copyright. (Is the comment still relevant?) + This is free software: you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation, either version 2 of the License, or + (at your option) any later version. <cut> + Implementation of async context spawning using Posix ucontext and + swapcontext(). +*/ + +#include <stdio.h> +#include <errno.h> + You can remove the above as mysys_priv.h will include these anyway. +#include "mysys_priv.h" +#include "my_context.h" + +#ifdef HAVE_VALGRIND_VALGRIND_H +#include <valgrind/valgrind.h> +#endif Shouldn't this also be depending on the HAVE_valgrind define? <cut> +int +my_context_init(struct my_context *c, size_t stack_size) +{ + if (2*sizeof(int) < sizeof(void *)) + { + fprintf(stderr, + "Error: Unable to store pointer in 2 ints on this architecture\n"); + return -1; + } Please make the above a compile time error instead. #if SIZEOF_CHARP < SIZEOF_INT*2 #error Error: Unable to store pointer in 2 ints on this architecture #endif I think it would be a good idea to do here: bzero(*c, sizeof(*c)); This is better than assuming that 'c' is zeroed on entry. + if (!(c->stack= malloc(stack_size))) + return -1; /* Out of memory */ + c->stack_size= stack_size; +#ifdef HAVE_VALGRIND_VALGRIND_H + c->valgrind_stack_id= + VALGRIND_STACK_REGISTER(c->stack, ((unsigned char *)(c->stack))+stack_size); +#endif +#ifndef DBUG_OFF + c->dbug_state= NULL; +#endif If we do the bzero, you can remove the above + return 0; +} <cut> +int +my_context_init(struct my_context *c, size_t stack_size) +{ + if (!(c->stack_bot= malloc(stack_size))) + return -1; /* Out of memory */ Add bzero of 'c' here. <cut> +int +my_context_init(struct my_context *c, size_t stack_size) +{ Add bzero() here. <cut> +int +my_context_init(struct my_context *c, size_t stack_size) +{ Add bzero here. +#ifndef DBUG_OFF + c->dbug_state= NULL; +#endif + c->lib_fiber= CreateFiber(stack_size, my_context_trampoline, c); + if (c->lib_fiber) + return 0; + else + return -1; Remove else or do: return c->lib_fiber ? 0 : -1; +} <cut> === modified file 'sql-common/client.c' --- sql-common/client.c 2011-03-18 15:03:43 +0000 +++ sql-common/client.c 2011-09-20 10:49:25 +0000 @@ -108,6 +108,7 @@ #include "client_settings.h" #include <sql_common.h> #include <mysql/client_plugin.h> +#include "my_context.h" Should probably be: #include <my_context.h> +#define mysql_extension_get(MYSQL, X) \ + ((MYSQL)->extension ? (MYSQL)->extension->X : NULL) +#define mysql_extension_set(MYSQL, X, VAL) \ + if (!(MYSQL)->extension) \ + (MYSQL)->extension= (struct st_mysql_extension *) \ + my_malloc(sizeof(struct st_mysql_extension), \ + MYF(MY_WME | MY_ZEROFILL)); \ + (MYSQL)->extension->X= VAL; + In 5.3 and 5.5 we have the following macro: #define extension_set(OPTS, X, VAL) \ if (!(OPTS)->extension) \ (OPTS)->extension= (struct st_mysql_options_extention *) \ my_malloc(sizeof(struct st_mysql_options_extention), \ MYF(MY_WME | MY_ZEROFILL)); \ (OPTS)->extension->X= VAL; Why not also store your new extension in mysql->option->extension instead of mysql->extensions ? This would allow you to reuse the existing macros.. +/* + Fetch the context for asynchronous API calls, allocating a new one if + necessary. +*/ +#define STACK_SIZE (4096*15) + +struct mysql_async_context * +mysql_get_async_context(MYSQL *mysql) +{ + struct mysql_async_context *b; + if ((b= mysql_extension_get(mysql, async_context))) + return b; + + if (!(b= (struct mysql_async_context *) + my_malloc(sizeof(*b), MYF(MY_ZEROFILL)))) + { + set_mysql_error(mysql, CR_OUT_OF_MEMORY, unknown_sqlstate); + return NULL; + } Remove the MY_ZEROFILL and let my_context_init() do it. (Makes the function cleaner). + if (my_context_init(&b->async_context, STACK_SIZE)) + { + my_free(b, MYF(0)); + return NULL; + } <cut> /************************************************************************** Get column lengths of the current row @@ -2537,6 +2577,26 @@ } +static int +connect_sync_or_async(MYSQL *mysql, NET *net, my_socket fd, + const struct sockaddr *name, uint namelen) +{ + extern int my_connect_async(struct mysql_async_context *b, my_socket fd, + const struct sockaddr *name, uint namelen, + uint timeout); Please move the above definitioon to header file that is also read by the file the defines the function. (Otherwise someone can change the function without you getting a compiler error). + struct mysql_async_context *actxt= mysql_extension_get(mysql, async_context); + + if (actxt && actxt->active) + { + my_bool old_mode; + vio_blocking(net->vio, FALSE, &old_mode); + return my_connect_async(actxt, fd, name, namelen, + mysql->options.connect_timeout); + } + else Remove else + return my_connect(fd, name, namelen, mysql->options.connect_timeout); +} + <cut> my_bool mysql_reconnect(MYSQL *mysql) { <cut> + if ((ctxt= mysql_extension_get(mysql, async_context)) && ctxt->active) + { + hook_data.orig_mysql= mysql; + hook_data.new_mysql= &tmp_mysql; + hook_data.orig_vio= mysql->net.vio; + my_context_install_suspend_resume_hook(ctxt, my_suspend_hook, &hook_data); + } if (!mysql_real_connect(&tmp_mysql,mysql->host,mysql->user,mysql->passwd, mysql->db, mysql->port, mysql->unix_socket, mysql->client_flag | CLIENT_REMEMBER_OPTIONS)) { + if (ctxt) + my_context_install_suspend_resume_hook(ctxt, NULL, NULL); Change the above to: res= mysql_real_connect(&tmp_mysql,mysql->host,mysql->user,mysql->passwd, mysql->db, mysql->port, mysql->unix_socket, mysql->client_flag | CLIENT_REMEMBER_OPTIONS); if (ctxt) my_context_install_suspend_resume_hook(ctxt, NULL, NULL); if (!res) .... This way you only need to call my_context_install_suspend_resume_hook(ctxt, NULL, NULL) once. mysql->net.last_errno= tmp_mysql.net.last_errno; strmov(mysql->net.last_error, tmp_mysql.net.last_error); strmov(mysql->net.sqlstate, tmp_mysql.net.sqlstate); @@ -3109,13 +3220,18 @@ if (mysql_set_character_set(&tmp_mysql, mysql->charset->csname)) { DBUG_PRINT("error", ("mysql_set_character_set() failed")); + tmp_mysql.extension= NULL; bzero((char*) &tmp_mysql.options,sizeof(tmp_mysql.options)); mysql_close(&tmp_mysql); + if (ctxt) + my_context_install_suspend_resume_hook(ctxt, NULL, NULL); Please remove the above call. mysql->net.last_errno= tmp_mysql.net.last_errno; strmov(mysql->net.last_error, tmp_mysql.net.last_error); strmov(mysql->net.sqlstate, tmp_mysql.net.sqlstate); DBUG_RETURN(1); } + if (ctxt) + my_context_install_suspend_resume_hook(ctxt, NULL, NULL); Please remove the above call. <cut> +static void +mysql_close_free_extension(MYSQL *mysql) +{ + if (mysql->extension) + { + if (mysql->extension->async_context) + { + my_context_destroy(&mysql->extension->async_context->async_context); + my_free(mysql->extension->async_context, MYF(0)); + } + my_free(mysql->extension, MYF(0)); + mysql->extension= NULL; + } +} I still think it would be better to use mysql->option.extension as in this case you can do the above with 2 lines of code and we would not need another function. <discussions over IRC about this....> The reason for not using mysql->options.extension was only becasue mysql_real_connect() freed the options on failure. This code is not needed as they will be freed by mysql_close() anyway. So I suggest you remove the code: if (!(client_flag & CLIENT_REMEMBER_OPTIONS)) mysql_close_free_options(mysql); from mysql_real_connect() and change to use mysql->options.extension instead static void mysql_close_free(MYSQL *mysql) { my_free((uchar*) mysql->host_info,MYF(MY_ALLOW_ZERO_PTR)); @@ -3304,6 +3439,33 @@ (As some clients call this after mysql_real_connect() fails) */ +/* + mysql_close() can actually block, at least in theory, if the socket buffer + is full when sending the COM_QUIT command. + + On the other hand, the latter part of mysql_close() needs to free the stack + used for non-blocking operation of blocking stuff, so that later part can + _not_ be done non-blocking. + + Therefore, mysql_pre_close() is used to run the parts of mysql_close() that + may block. It can be called before mysql_close(), and in that case + mysql_close() is guaranteed not to need to block. +*/ Consider changing the name to 'mysql_slow_part_of_close()' and make it static if possible. +void mysql_pre_close(MYSQL *mysql) +{ + if (!mysql) + return; Remove test; impossible with current code, see belove in mysql_close. + /* If connection is still up, send a QUIT message */ + if (mysql->net.vio != 0) + { + free_old_query(mysql); + mysql->status=MYSQL_STATUS_READY; /* Force command */ + mysql->reconnect=0; + simple_command(mysql,COM_QUIT,(uchar*) 0,0,1); + end_server(mysql); /* Sets mysql->net.vio= 0 */ + } +} + void STDCALL mysql_close(MYSQL *mysql) { DBUG_ENTER("mysql_close"); @@ -3311,16 +3473,9 @@ + +my_socket STDCALL +mysql_get_socket(const MYSQL *mysql) +{ + if (mysql->net.vio) + return mysql->net.vio->sd; + else remove else + return INVALID_SOCKET; +} +++ sql-common/mysql_async.c 2011-09-20 10:49:25 +0000 +#ifdef __WIN__ +/* + Windows does not support MSG_DONTWAIT for send()/recv(). So we need to ensure + that the socket is non-blocking at the start of every operation. +*/ +#define WIN_SET_NONBLOCKING(mysql) { \ + my_bool old_mode__; \ + if ((mysql)->net.vio) vio_blocking((mysql)->net.vio, FALSE, &old_mode__); \ + } old_mode__ -> old_mode +#else +#define WIN_SET_NONBLOCKING(mysql) +#endif + +extern struct mysql_async_context *mysql_get_async_context(MYSQL *mysql); Move to header file. +/* Asynchronous connect(); socket must already be set non-blocking. */ +int +my_connect_async(struct mysql_async_context *b, my_socket fd, + const struct sockaddr *name, uint namelen, uint timeout) +{ + int res; +#ifdef __WIN__ + int s_err_size; +#else + socklen_t s_err_size; +#endif It would be nice to just define socklen_t for windows, but this isn't critical. You could have a DBUG_ASSERT here to check if socket is really in non blocking. + + /* + Start to connect asynchronously. + If this will block, we suspend the call and return control to the + application context. The application will then resume us when the socket + polls ready for write, indicating that the connection attempt completed. + */ + res= connect(fd, name, namelen); +#ifdef __WIN__ + if (res != 0) + { + int wsa_err= WSAGetLastError(); + if (wsa_err != WSAEWOULDBLOCK) + return res; +#else + if (res < 0) To not have only one { inside the #ifdef / #else I suggest that you move if (res != 0) { before the +#ifdef __WIN__. This works on both windows and Unix This will make the number of { balance + { + if (errno != EINPROGRESS && errno != EALREADY && errno != EAGAIN) + return res; +#endif + b->timeout_value= timeout; + b->ret_status= MYSQL_WAIT_WRITE | + (timeout ? MYSQL_WAIT_TIMEOUT : 0); +#ifdef __WIN__ + b->ret_status|= MYSQL_WAIT_EXCEPT; +#endif How about setting b->ret_status to 0 before res= connect() ? Then you can move the above code to the previous #ifdef __WIN__ and change the setting of b->ret_status to use |= This removes the extra ifdef __WIN__ and also ensures that ret_status is 0 if evertything went well. (not required but still better than a random value when trying to understand what's going in). + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(TRUE, b->suspend_resume_hook_user_data); + my_context_yield(&b->async_context); + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(FALSE, b->suspend_resume_hook_user_data); + if (b->ret_status & MYSQL_WAIT_TIMEOUT) + return -1; + + s_err_size= sizeof(int); Better to use (safer and more descriptive): s_err_size= sizeof(res); + if (getsockopt(fd, SOL_SOCKET, SO_ERROR, (char*) &res, &s_err_size) != 0) + return -1; <cut> + +ssize_t +my_recv_async(struct mysql_async_context *b, int fd, + unsigned char *buf, size_t size, uint timeout) +{ + ssize_t res; + + for (;;) + { + res= recv(fd, buf, size, +#ifdef __WIN__ + 0 +#else + MSG_DONTWAIT +#endif + ); Better to do before function start: #ifdef __WIN__ #define MSG_DONTWAIT 0 #endif and remove the above #ifdef in middle of call. Another option is to use the IF_WIN() macro: IF_WIN(0, MSG_DONTWAIT) + if (res >= 0 || +#ifdef __WIN__ + WSAGetLastError() != WSAEWOULDBLOCK +#else + (errno != EAGAIN && errno != EINTR) +#endif Please define macro: #define IS_BLOCKING_ERROR IF_WIN(WSAGetLastError() != WSAEWOULDBLOCK, (errno != EAGAIN && errno != EINTR)) and use that instead of having an ifdef in the middle of the code. + ) + return res; + b->ret_status= MYSQL_WAIT_READ; + if (timeout) + { + b->ret_status|= MYSQL_WAIT_TIMEOUT; + b->timeout_value= timeout; + } + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(TRUE, b->suspend_resume_hook_user_data); + my_context_yield(&b->async_context); + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(FALSE, b->suspend_resume_hook_user_data); The following call was quite confusion as we are checking a variable that we where just setting. <phone call> The reasons is that b->ret_status is both an in an out parameter. We agree to do one of the following to make the code easier to understand: - Change to use different bits for in and out status. - Change to use different variables for in and out status. + if (b->ret_status & MYSQL_WAIT_TIMEOUT) + return -1; + } +} + Add empty row here +ssize_t +my_send_async(struct mysql_async_context *b, int fd, + const unsigned char *buf, size_t size, uint timeout) +{ + ssize_t res; + + for (;;) + { + res= send(fd, buf, size, +#ifdef __WIN__ + 0 +#else + MSG_DONTWAIT +#endif + ); + if (res >= 0 || +#ifdef __WIN__ + WSAGetLastError() != WSAEWOULDBLOCK +#else + (errno != EAGAIN && errno != EINTR) +#endif + ) See comments for previous function how to get rid of the ifdefs' <cut> +#ifdef HAVE_OPENSSL +int +my_ssl_read_async(struct mysql_async_context *b, SSL *ssl, + void *buf, int size) +{ + int res, ssl_err; + + for (;;) + { + res= SSL_read(ssl, buf, size); + if (res >= 0) + return res; + ssl_err= SSL_get_error(ssl, res); + if (ssl_err == SSL_ERROR_WANT_READ) + b->ret_status= MYSQL_WAIT_READ; + else if (ssl_err == SSL_ERROR_WANT_WRITE) + b->ret_status= MYSQL_WAIT_WRITE; + else + return res; + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(TRUE, b->suspend_resume_hook_user_data); + my_context_yield(&b->async_context); + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(FALSE, b->suspend_resume_hook_user_data); + } +} + +int +my_ssl_write_async(struct mysql_async_context *b, SSL *ssl, + const void *buf, int size) +{ + int res, ssl_err; + + for (;;) + { + res= SSL_write(ssl, buf, size); + if (res >= 0) + return res; + ssl_err= SSL_get_error(ssl, res); + if (ssl_err == SSL_ERROR_WANT_READ) + b->ret_status= MYSQL_WAIT_READ; + else if (ssl_err == SSL_ERROR_WANT_WRITE) + b->ret_status= MYSQL_WAIT_WRITE; + else + return res; + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(TRUE, b->suspend_resume_hook_user_data); + my_context_yield(&b->async_context); + if (b->suspend_resume_hook) + (*b->suspend_resume_hook)(FALSE, b->suspend_resume_hook_user_data); + } +} 90 % of the above functions are the same: I would do it this way: static my_bool my_ssl_async_check_result(int *res, struct mysql_async_context *b) { int ssl_err; if (*res >= 0) return 1; ssl_err= SSL_get_error(ssl, *res); if (ssl_err == SSL_ERROR_WANT_READ) b->ret_status= MYSQL_WAIT_READ; else if (ssl_err == SSL_ERROR_WANT_WRITE) b->ret_status= MYSQL_WAIT_WRITE; else return 1; if (b->suspend_resume_hook) (*b->suspend_resume_hook)(TRUE, b->suspend_resume_hook_user_data); my_context_yield(&b->async_context); if (b->suspend_resume_hook) (*b->suspend_resume_hook)(FALSE, b->suspend_resume_hook_user_data); return 0; } And then: my_ssl_read_async(struct mysql_async_context *b, SSL *ssl, void *buf, int size) { int res; for (;;) { res= SSL_read(ssl, buf, size); if (my_ssl_async_check_result(res, b)) return res; } } <cut> +#endif /* HAVE_OPENSSL */ + +unsigned int STDCALL +mysql_get_timeout_value(const MYSQL *mysql) +{ + if (mysql->extension && mysql->extension->async_context) + return mysql->extension->async_context->timeout_value; + else Remove else + return 0; +} + Add empty line +/* + Now create non-blocking definitions for all the calls that may block. + + Each call FOO gives rise to FOO_start() that prepares the MYSQL object for + doing non-blocking calls that can suspend operation mid-way, and then starts + the call itself. And a FOO_start_internal trampoline to assist with running + the real call in a co-routine that can be suspended. And a FOO_cont() that + can continue a suspended operation. +*/ + +#define MK_ASYNC_CALLS(call__, decl_args__, invoke_args__, cont_arg__, mysql_val__, parms_mysql_val__, parms_assign__, ret_type__, err_val__, ok_val__, extra1__) \ +static void \ +call__ ## _start_internal(void *d) \ +{ \ As discussed on IRC, it would be good to have the functions declarations outside of the macro; This makes it easier to make breakpoints in the code and help debugging. + struct call__ ## _params *parms; \ + ret_type__ ret; \ + struct mysql_async_context *b; \ + \ + parms= (struct call__ ## _params *)d; \ + b= (parms_mysql_val__)->extension->async_context; \ + \ + ret= call__ invoke_args__; \ + b->ret_result. ok_val__ = ret; \ + b->ret_status= 0; \ +} \ +int STDCALL \ +call__ ## _start decl_args__ \ +{ \ + int res; \ + struct mysql_async_context *b; \ + struct call__ ## _params parms; \ + \ + extra1__ \ + if (!(b= mysql_get_async_context((mysql_val__)))) \ + { \ + *ret= err_val__; \ + return 0; \ + } \ Instead of calling mysql_get_async_context, another option would be to do: mysql_options(mysql, MYSQL_OPT_ASYNC_IO, (char*) &STACK_SIZE) And if STACK_SIZE is 0, then we allocate our default stack size then you can change the above code to: b= mysql_val__->options.extension->async_context; If it doesn't exist, it's ok to crash on access. (Same as if mysql is 0) + parms_assign__ \ + \ + b->active= 1; \ + res= my_context_spawn(&b->async_context, call__ ## _start_internal, &parms);\ + b->active= 0; \ Set also b->suspended= 0 here. (Makes the rest of the code shorter) + if (res < 0) \ + { \ + set_mysql_error((mysql_val__), CR_OUT_OF_MEMORY, unknown_sqlstate); \ How do we know the reason is out of memory? (Just curious) + b->suspended= 0; \ + *ret= err_val__; \ + return 0; \ + } \ + else if (res > 0) \ Remove else + { \ + /* Suspended. */ \ + b->suspended= 1; \ + return b->ret_status; \ + } \ + else \ Remove else and {} + { \ + /* Finished. */ \ + b->suspended= 0; \ + *ret= b->ret_result. ok_val__; \ + return 0; \ + } \ +} \ If you change to test for (res > 0) first, you can combine the two return 0 to one row and thus remove some of the { }. Makes the code shorter (at least to read): b->active= b->suspended= 0; if (res > 0) { b->suspended= 1; return b->ret_status; } if (res < 0) { set_mysql_error((mysql_val__), CR_OUT_OF_MEMORY, unknown_sqlstate); *ret= err_val__; } else *ret= b->ret_result. ok_val__; /* Finished. */ return 0; +int STDCALL \ +call__ ## _cont(ret_type__ *ret, cont_arg__, int ready_status) \ +{ \ + int res; \ + struct mysql_async_context *b; \ + \ + b= (mysql_val__)->extension->async_context; \ + if (!b || !b->suspended) \ Remove test for !b; You can assume it's exists. (We can never protect aginst wrong usage) + { \ + set_mysql_error((mysql_val__), CR_COMMANDS_OUT_OF_SYNC, unknown_sqlstate);\ + *ret= err_val__; \ + return 0; \ + } \ + \ + b->active= 1; \ + b->ret_status= ready_status; \ + res= my_context_continue(&b->async_context); \ + b->active= 0; \ + if (res < 0) \ + { \ + set_mysql_error((mysql_val__), CR_OUT_OF_MEMORY, unknown_sqlstate); \ + b->suspended= 0; \ + *ret= err_val__; \ + return 0; \ + } \ + else if (res > 0) \ + { \ + /* Suspended. */ \ + return b->ret_status; \ + } \ + else \ + { \ + /* Finished. */ \ + b->suspended= 0; \ + *ret= b->ret_result. ok_val__; \ + return 0; \ + } \ You can do the same 'suspend and check for error' optimization I did in previous function to reduce the above code with 4 lines. +} + +#define MK_ASYNC_CALLS_VOID_RETURN(call__, decl_args__, invoke_args__, cont_arg__, mysql_val__, parms_mysql_val__, parms_assign__, extra1__) \ +static void \ +call__ ## _start_internal(void *d) \ +{ \ + struct call__ ## _params *parms; \ + struct mysql_async_context *b; \ + \ + parms= (struct call__ ## _params *)d; \ + b= (parms_mysql_val__)->extension->async_context; \ + \ + call__ invoke_args__; \ + b->ret_status= 0; \ +} \ +int STDCALL \ +call__ ## _start decl_args__ \ +{ \ + int res; \ + struct mysql_async_context *b; \ + struct call__ ## _params parms; \ + \ + extra1__ \ + if (!(b= mysql_get_async_context((mysql_val__)))) \ + { \ + return 0; \ + } \ You can remove test if we know that b always exists. At least you can remove '{' + parms_assign__ \ + \ + b->active= 1; \ + res= my_context_spawn(&b->async_context, call__ ## _start_internal, &parms);\ + b->active= 0; \ + if (res < 0) \ + { \ + set_mysql_error((mysql_val__), CR_OUT_OF_MEMORY, unknown_sqlstate); \ + b->suspended= 0; \ + return 0; \ + } \ + else if (res > 0) \ + { \ + /* Suspended. */ \ + b->suspended= 1; \ + return b->ret_status; \ + } \ + else \ + { \ + /* Finished. */ \ + b->suspended= 0; \ + return 0; \ + } \ +} \ See previous comment of how to remove 4 code lines. +int STDCALL \ +call__ ## _cont(cont_arg__, int ready_status) \ +{ \ + int res; \ + struct mysql_async_context *b; \ + \ + b= (mysql_val__)->extension->async_context; \ + if (!b || !b->suspended) \ Assume !b exists + { \ + set_mysql_error((mysql_val__), CR_COMMANDS_OUT_OF_SYNC, unknown_sqlstate);\ + return 0; \ + } \ + \ + b->active= 1; \ + b->ret_status= ready_status; \ + res= my_context_continue(&b->async_context); \ + b->active= 0; \ + if (res < 0) \ + { \ + set_mysql_error((mysql_val__), CR_OUT_OF_MEMORY, unknown_sqlstate); \ + b->suspended= 0; \ + return 0; \ + } \ + else if (res > 0) \ + { \ + /* Suspended. */ \ + return b->ret_status; \ + } \ + else \ + { \ + /* Finished. */ \ + b->suspended= 0; \ + return 0; \ + } \ +} Remove extra lines above. Add comment here (and for other similar structs): /* This struct is used by the next MK_ASYNC_CALL */ + +struct mysql_real_connect_params { + MYSQL *mysql; + const char *host; + const char *user; + const char *passwd; + const char *db; + unsigned int port; + const char *unix_socket; + unsigned long client_flags; +}; +MK_ASYNC_CALLS( + mysql_real_connect, + (MYSQL **ret, MYSQL *mysql, const char *host, const char *user, + const char *passwd, const char *db, unsigned int port, + const char *unix_socket, unsigned long client_flags), + (parms->mysql, parms->host, parms->user, parms->passwd, parms->db, + parms->port, parms->unix_socket, parms->client_flags), <cut> +mysql_close_start(MYSQL *sock) +{ + int res; + + /* It is legitimate to have NULL sock argument, which will do nothing. */ + if (sock) + { + res= mysql_pre_close_start(sock); + /* If we need to block, return now and do the rest in mysql_close_cont(). */ + if (res) + return res; + } + mysql_close(sock); + return 0; +} +int STDCALL +mysql_close_cont(MYSQL *sock, int ready_status) +{ + int res; + + res= mysql_pre_close_cont(sock, ready_status); + if (res) + return res; + mysql_close(sock); + return 0; +} You should use the above mysql_close functions also in client/async_example.cc +#ifdef USE_OLD_FUNCTIONS Forget these! We should remove them soon anyway... <cut> === added file 'tests/async_queries.c' +void +add_query(const char *q) +{ + struct query_entry *e; + char *q2; + size_t len; + + e= malloc(sizeof(*e)); + q2= strdup(q); Would be better if you used my_malloc / my_strdup here. (To get things safemalloc checked). + if (!e || !q2) + fatal(NULL, "Out of memory"); + + /* Remove any trailing newline. */ + len= strlen(q2); + if (q2[len] == '\n') + q2[len--]= '\0'; + if (q2[len] == '\r') + q2[len--]= '\0'; + + e->next= NULL; + e->query= q2; + e->index= query_counter++; + *tail_ptr= e; + tail_ptr= &e->next; +} <cut> === modified file 'vio/viosocket.c' --- vio/viosocket.c 2011-05-14 16:42:07 +0000 +++ vio/viosocket.c 2011-09-20 10:49:25 +0000 @@ -21,6 +21,7 @@ */ #include "vio_priv.h" +#include "my_context.h" int vio_errno(Vio *vio __attribute__((unused))) { @@ -31,18 +32,34 @@ size_t vio_read(Vio * vio, uchar* buf, size_t size) { size_t r; + extern ssize_t my_recv_async(struct mysql_async_context *b, int fd, + unsigned char *buf, size_t size, uint timeout); Move to include file. DBUG_ENTER("vio_read"); DBUG_PRINT("enter", ("sd: %d buf: 0x%lx size: %u", vio->sd, (long) buf, (uint) size)); /* Ensure nobody uses vio_read_buff and vio_read simultaneously */ DBUG_ASSERT(vio->read_end == vio->read_pos); + if (vio->async_context && vio->async_context->active) + r= my_recv_async(vio->async_context, vio->sd, buf, size, vio->read_timeout); + else + { <cut> === modified file 'vio/viossl.c' @@ -21,6 +21,7 @@ */ #include "vio_priv.h" +#include "my_context.h" #ifdef HAVE_OPENSSL @@ -90,11 +91,16 @@ size_t vio_ssl_read(Vio *vio, uchar* buf, size_t size) { size_t r; + extern int my_ssl_read_async(struct mysql_async_context *b, SSL *ssl, + void *buf, int size); Move to extern file (Same for other extern declarations in this file). <cut> Good and intresting work! Regards, Monty

4 9

[Maria-developers] IO statistic in slow log
by jiangchengyao 09 Jan '12

09 Jan '12

Hi, all I write a patch to show physical & logical IO statistics in slow log. Now it only support XtraDB storage engine, but it is easy to realize in other engine. You may found it like this in slow log: # Time: 111227 16:29:54 # User@Host: root[root] @ localhost [::1] # Query_time: 0.310018 Lock_time: 0.203012 Rows_sent: 1 Rows_examined: 30000 Logical_IO: 30145 Physical_IO: 50 use tpcc; SET timestamp=1324974594; select count(1) from history; I think this feature is much helpful for DBA or Developer to know what the real IO one SQL has. In addition, it is simple, only a few changes should be made. The branch is: https://code.launchpad.net/~jiangchengyao/+junk/mariadb Regards, David 2011-12-30 jiangchengyao

3 2

Re: [Maria-developers] [Commits] Rev 3209: Fix sys_vars test suite for 32bit systems. (MDEV-53 & MDEV-53) in file:///home/bell/maria/bzr/work-maria-5.5-sysvar/
by Sergei Golubchik 09 Jan '12

09 Jan '12

Hi, sanja! On Jan 09, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.5-sysvar/ > > ------------------------------------------------------------ > revno: 3209 > revision-id: sanja(a)montyprogram.com-20120109130202-vqvmsk5eqzo1kgv1 > parent: sergii(a)pisem.net-20120107190155-xral93617o3pzp8u > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.5-sysvar > timestamp: Mon 2012-01-09 15:02:02 +0200 > message: > Fix sys_vars test suite for 32bit systems. (MDEV-53 & MDEV-53) Ok to push! Regards, Sergei

1 0

[Maria-developers] Please review: [Commits] Rev 3377: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate
by Sergei Petrunia 09 Jan '12

09 Jan '12

Hello Igor, Timour Could anybody of please review the below fix? additional details about the problem were posted at https://mariadb.atlassian.net/browse/MDEV-67. ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sun, 8 Jan 2012 14:43:17 +0400 (GST) Subject: [Commits] Rev 3377: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... in file:///home/psergey/dev2/5.3-look46/ At file:///home/psergey/dev2/5.3-look46/ ------------------------------------------------------------ revno: 3377 revision-id: psergey(a)askmonty.org-20120108104314-xa6cxdbfive3elwf parent: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-look46 timestamp: Sun 2012-01-08 14:43:14 +0400 message: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... - Create/use do_copy_nullable_row_to_notnull() function for ref access, which is used when copying from not-NULL field in table that can be NULL-complemented to not-NULL field. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj.result 2012-01-08 10:43:14 +0000 @@ -2152,4 +2152,21 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2012-01-08 10:43:14 +0000 @@ -2166,6 +2166,23 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2011-12-24 16:55:10 +0000 +++ b/mysql-test/t/subselect_sj.test 2012-01-08 10:43:14 +0000 @@ -1997,6 +1997,25 @@ DROP TABLE t1,t2; +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; === modified file 'sql/field_conv.cc' --- a/sql/field_conv.cc 2011-12-11 09:34:44 +0000 +++ b/sql/field_conv.cc 2012-01-08 10:43:14 +0000 @@ -248,6 +248,25 @@ } } +/* + Copy: (not-NULL field in table that can be NULL-complemented) -> (not-NULL + field) +*/ +static void do_copy_nullable_row_to_notnull(Copy_field *copy) +{ + if (*copy->null_row || + (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit))) + { + copy->to_field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, 1); + copy->to_field->reset(); + } + else + { + (copy->do_copy2)(copy); + } + +} /* Copy: (NULL-able field) -> (not NULL-able field) */ static void do_copy_not_null(Copy_field *copy) @@ -638,7 +657,15 @@ else if (to_field == to_field->table->next_number_field) do_copy= do_copy_next_number; else - do_copy= do_copy_not_null; + { + if (!from_null_ptr) + { + null_row= &from->table->null_row; + do_copy= do_copy_nullable_row_to_notnull; + } + else + do_copy= do_copy_not_null; + } } } else if (to_field->real_maybe_null()) === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-12-24 16:55:10 +0000 +++ b/sql/sql_select.cc 2012-01-08 10:43:14 +0000 @@ -646,6 +646,9 @@ aggregate functions and non-aggregate fields, any non-aggregated field may produce a NULL value. Set all fields of each table as nullable before semantic analysis to take into account this change of nullability. + + Note: this loop doesn't touch tables inside merged semi-joins, because + subquery-to-semijoin conversion has not been done yet. This is intended. */ if (mixed_implicit_grouping) tbl->table->maybe_null= 1; _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 3204: Fix some failing tests on Windows in file:///H:/bzr/5.5/
by Sergei Golubchik 06 Jan '12

06 Jan '12

Hi, Vladislav! On Jan 06, Vladislav Vaintroub wrote: > - disable perfschema all_instances test on Windows- there are > legitimate reasons for output to be different on Unix (some > different threads, some different locks), the differences are > expected to grow in the future, e.g with threadpool. 1. Why is it not an issue for vanilla MySQL? 2. please don't forget to close all related issues after you fix them :) Regards, Sergei

2 1

[Maria-developers] 回复: Re: IO statistic in slow log
by jiangchengyao 05 Jan '12

05 Jan '12

Yes, Logical IO means pages from buffer pool, physical IO means pages read from disk 2012-01-05 jiangchengyao 发件人：MARK CALLAGHAN 发送时间：2012-01-05 04:39 主题：Re: [Maria-developers] IO statistic in slow log 收件人：jiangchengyao 抄送：maria-developers What are the difference between logical and physical IO? I assume you are using Oracle's terminology where a logical IO means that a page was read from the InnoDB buffer cache. On Thu, Dec 29, 2011 at 6:41 PM, jiangchengyao <jiangchengyao(a)gmail.com> wrote: > Hi, all > > I write a patch to show physical & logical IO statistics in slow log. Now it > only support XtraDB storage engine, but it is easy to realize in other > engine. You may found it like this in slow log: > > # Time: 111227 16:29:54 > # User@Host: root[root] @ localhost [::1] > # Query_time: 0.310018 Lock_time: 0.203012 Rows_sent: 1 Rows_examined: > 30000 Logical_IO: 30145 Physical_IO: 50 > use tpcc; > SET timestamp=1324974594; > select count(1) from history; > > I think this feature is much helpful for DBA or Developer to know what the > real IO one SQL has. In addition, it is simple, only a few changes should be > made. > > The branch is: https://code.launchpad.net/~jiangchengyao/+junk/mariadb > > Regards, > David > > 2011-12-30 > ________________________________ > jiangchengyao > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : maria-developers(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp > -- Mark Callaghan mdcallag(a)gmail.com

1 0

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 04 Jan '12

04 Jan '12

Start with it in the current development branch. If I am not able to backport that then we can talk again. On Tue, Jan 3, 2012 at 1:33 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Mark, > > There is one thing we didn't specify - in which MariaDB version > do you want the feature? If you want just a patch, then it could > be in any version, but if you'd like it to be in a released version, > then it should be in 5.5. > > If you want it to be both in an older version, and in 5.5, then > there will be slight differences IMO because in 5.3 there are few > new join algorithms, and the related code has changed. > > Timour > > > On 23.12.2011 06:38, MARK CALLAGHAN wrote: >> >> Yes, I don't think it is a lot of work. I started on it myself but >> don't trust myself to modify optimizer code. I think we can find a >> bigger project to collaborate on in 2012 -- probably something related >> to replication. >> >> On Wed, Dec 21, 2011 at 12:57 PM, Michael Widenius<monty(a)askmonty.org> >> wrote: >>> >>> >>> Hi! >>> >>>>>>>> "MARK" == MARK CALLAGHAN<mdcallag(a)gmail.com> writes: >>> >>> >>> MARK> A warning would be excellent. An error would be a problem. >>> Returning >>> MARK> OK is probably not the best result when the examined limit is >>> reached. >>> >>> Ok, we will try to develop this feature (with a ok + warning) this year. >>> (Should not be that many hours of work). >>> >>> Regards, >>> Monty >> >> >> >> > -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] WL#248 New (by Igor): Collect engine independent statistics on table columns and indexes to store it in persistent system tables
by worklog-noreply＠askmonty.org 04 Jan '12

04 Jan '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Collect engine independent statistics on table columns and indexes to store it in persistent system tables CREATION DATE..: Wed, 04 Jan 2012, 04:11 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Knielsen Monty Psergey Sanja Sergei Timour Wlad CATEGORY.......: Server-BackLog TASK ID........: 248 (http://askmonty.org/worklog/?tid=248) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 120 (hours remain) ORIG. ESTIMATE.: 120 PROGRESS NOTES: DESCRIPTION: In a special mode the ANALYZE command will collect statistics on table cardinality, table columns and table indexes. This statistics will be stored in system tables table_stat, column_stat and index_stat. For any database table the system table table_stat will contain the number of rows in the table. For any table column the system table column_stat will contain min and max value, the share of null values, the average length of a column value, and the the average frequency of the column values. For any index defined on a database table the system table index_stat will contain number of records per every key prefix. This task will be implemented in 3 steps: 1. A pure back-port of the WL#4777 from mysql-5.4 code line will be performed. 2. New possibility to collect statistics only on specified columns and/or indexes will be added. 3. A new possibility to scan only a certain percentage of the table records in a pseudo-random manner will be added. The collected statistics is supposed to be used by the optimizer, but this will be an objective of some other task. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 06:44 AM, Timour Katchaounov wrote: > Igor, > >> On 01/03/2012 12:49 AM, Timour Katchaounov wrote: >>> Hi Igor, >>> >>> The reason for removing this specific call to >>> engine->set_thd() was because Monty claimed this is >>> the correct way, and because with the new assert we >>> couldn't find any case where the new assert would fail. >>> >>> Apparently the above assert was incorrect, however, the >>> current commit message doesn't have any explanation what >>> was wrong, why the assert you removed is wrong, and why >>> it is needed to call engine->set_thd(). >> >> Igor, >> >> I would rather say that you patch did not have any explanation >> why you had removed the call. Should it be a reference to Monty's >> opinion?I have to admit it would be pretty pretty lame. > > There is a comment in the patch for bug lp:685411, this is the > relevant diff: > > @@ -183,7 +185,8 @@ bool Item_subselect::fix_fields(THD *thd > bool res; > > DBUG_ASSERT(fixed == 0); > - engine->set_thd((thd= thd_param)); > + /* There is no reason to get a different THD. */ > + DBUG_ASSERT(thd == thd_param); > if (!done_first_fix_fields) > { > done_first_fix_fields= TRUE; > > The reasoning is simple - there is no reason for a subquery to > get a different THD object compared to that of the containing > query. > > So my question to your patch is whether you figured out why the > above assumption is not true. If you did, it would be nice to have > an explanation. I am pretty sure the assumption is true for SELECTs. > If that is the case, then I would refine your fix by testing whether > a statement is a SELECT, or UPDATE (or INSERT?) and updating the THD > only when needed. It's relevant for the scenario when the fixed item is created by a different client: for example, when the item belongs to SP and this SP was created by a different client. I returned the code that you removed. I don't think I have to explain why you were wrong. > > Timour > >> >> Regards, >> Igor. >> >>> >>> Timour >>> >>> On 3.01.2012 06:06, Igor Babaev wrote: >>>> At file:///home/igor/maria/maria-5.3-bug910083/ >>>> >>>> ------------------------------------------------------------ >>>> revno: 3376 >>>> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >>>> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >>>> committer: Igor Babaev<igor(a)askmonty.org> >>>> branch nick: maria-5.3-bug910083 >>>> timestamp: Mon 2012-01-02 20:06:36 -0800 >>>> message: >>>> Fixed LP bug #910083. >>>> The patch for bug 685411 erroneously removed a call of >>>> engine->set_thd() >>>> from Item_subselect::fix_fields(). >>>> >>>> >>>> >>>> _______________________________________________ >>>> commits mailing list >>>> commits(a)mariadb.org >>>> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits >>

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 12:49 AM, Timour Katchaounov wrote: > Hi Igor, > > The reason for removing this specific call to > engine->set_thd() was because Monty claimed this is > the correct way, and because with the new assert we > couldn't find any case where the new assert would fail. > > Apparently the above assert was incorrect, however, the > current commit message doesn't have any explanation what > was wrong, why the assert you removed is wrong, and why > it is needed to call engine->set_thd(). Igor, I would rather say that you patch did not have any explanation why you had removed the call. Should it be a reference to Monty's opinion?I have to admit it would be pretty pretty lame. Regards, Igor. > > Timour > > On 3.01.2012 06:06, Igor Babaev wrote: >> At file:///home/igor/maria/maria-5.3-bug910083/ >> >> ------------------------------------------------------------ >> revno: 3376 >> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >> committer: Igor Babaev<igor(a)askmonty.org> >> branch nick: maria-5.3-bug910083 >> timestamp: Mon 2012-01-02 20:06:36 -0800 >> message: >> Fixed LP bug #910083. >> The patch for bug 685411 erroneously removed a call of >> engine->set_thd() >> from Item_subselect::fix_fields(). >> >> >> >> _______________________________________________ >> commits mailing list >> commits(a)mariadb.org >> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] Rev 3202: Fix compile error in file:///H:/bzr/5.5/
by Sergei Golubchik 03 Jan '12

03 Jan '12

Hi, Vladislav! What was the error that you fixed? On Jan 03, Vladislav Vaintroub wrote: > ------------------------------------------------------------ > revno: 3202 > committer: Vladislav Vaintroub <wlad(a)montyprogram.com> > branch nick: 5.5 > timestamp: Tue 2012-01-03 00:17:36 +0100 > message: > Fix compile error > === modified file 'storage/maria/ma_write.c' > --- a/storage/maria/ma_write.c 2012-01-02 20:56:16 +0000 > +++ b/storage/maria/ma_write.c 2012-01-02 23:17:36 +0000 > @@ -472,10 +472,11 @@ > int error; > my_off_t new_root= *root; > uchar key_buff[MARIA_MAX_KEY_BUFF]; > - MARIA_KEY UNINIT_VAR(org_key); /* Set/used when now_transactional=TRUE */ > + MARIA_KEY org_key; /* Set/used when now_transactional=TRUE */ > + my_bool transactional= share->now_transactional; > DBUG_ENTER("_ma_ck_write_btree_with_log"); > > - if (share->now_transactional) > + if (transactional) > { > /* Save original value as the key may change */ > org_key= *key; > @@ -483,7 +484,7 @@ > } > > error= _ma_ck_real_write_btree(info, key, &new_root, comp_flag); > - if (!error && share->now_transactional) > + if (!error && transactional) > { > /* Log the original value */ > *key= org_key; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits Regards, Sergei

2 1

[Maria-developers] win auth plugin
by sergii＠pisem.net 31 Dec '11

31 Dec '11

Hi. Attached is the patch for the windows authentication plugin. To avoid code duplication I've reused MySQL's windows authentication plugin and only wrote the server part. This plugin is different from MySQL's, because (as all our plugins) it does not do user name mapping and only preforms the authentication - that is, only verifies that the user really is who he says he is. Regards, Sergei

3 3

[Maria-developers] WL#247 New (by Igor): Make the optimizer use extended keys
by worklog-noreply＠askmonty.org 31 Dec '11

31 Dec '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make the optimizer use extended keys CREATION DATE..: Sat, 31 Dec 2011, 05:49 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Monty Psergey Sanja Sergei Timour CATEGORY.......: Server-BackLog TASK ID........: 247 (http://askmonty.org/worklog/?tid=247) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 60 (hours remain) ORIG. ESTIMATE.: 60 PROGRESS NOTES: DESCRIPTION: Currently the optimizer mostly does not use hidden components of InnoDB keys. Meanwhile these components contain fields of the primary key and could be used to to generate more efficient execution plans. In particular using these components in many cases allow to generate execution plans that that employs index only look-ups. This task will support usage of extended keys for: - ref and eq-ref accesses - range scans - index-merge scans - loose scans - min/max optimization ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Problems with packages in 5.3 buildbot
by Kristian Nielsen 30 Dec '11

30 Dec '11

I learned that there were many test failures with packages in 5.3. I think it may be due to the version in configure.in not being increased after last release. I pushed a commit that changes it to 5.3.4-rc, we will see if it helps. - Kristian.

1 0

Re: [Maria-developers] License issues
by Henrik Ingo 30 Dec '11

30 Dec '11

I'm assuming you left out the list in accident and won't mind that I quote you a little: On Thu, Dec 29, 2011 at 10:22 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > On Dec 29, Henrik Ingo wrote: >> (I'm pretty sure readline developers didn't quite realize what they >> were doing here. Their original goal with their choice of license was >> to create an advantage for GPL licensed apps, now they are doing >> exactly the opposite.) > > I think it was intentional. > > readline is a gnu project, hosted on gnu.org. > It looks like an FSF policy to use GPLv3. Other gnu software than I've > checked is also on GPLv3 (in particular, libbfd - that is used by > binutils, gdb, etc - so it's kind of fundamental for development chain). Yes, I'm aware of this. However, most FSF *libraries* are licensed LGPL. Only readline and a few others (apparently then libbfd, if you say so) are GPL. The purpose of this is to make those libraries exclusively available to GPL software, or also compatible FOSS software. (eg. a BSD licensed application could use a GPL licensed library as long as source code of the app is in fact made available.) > I might be wrong, but I think using GPLv3 for all (or most) GNU > projects, including essencial libraries, is quite intentional. Well, it is intentional for FSF in the sense that it is policy. For the libraries that use LGPL it is also quite fine to force everyone to use them under LGPLv3, as it won't have similar bad effects on the apps that use those libraries. However, in the case of readline and other libraries that are GPL, this has the effect of hurting projects that are GPLv2 only, while it in fact improves the situation for non-copyleft projects such as those under Apache license. (Apache license is considered by FSF to be incompatible with GPLv2 but compatible with GPLv3.) I very much doubt that this particular consequence was intentional. To support GPL licensed software, the FSF should in this case have licensed readline as both GPLv2 and GPLv3. henrik -- henrik.ingo(a)avoinelama.fi +358-40-8211286 skype: henrik.ingo irc: hingo www.openlife.cc My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

2 1

[Maria-developers] License issues
by Maarten Vanraes 30 Dec '11

30 Dec '11

Hi, I'm a packager for mageia, and i'm working to get branch 5.5 submitted on our system, replacing mysql. However, we have readline v6 on mageia, which is GPLv3, and serg added the NOT_FOR_DISTRIBUTION variable, which makes it build a bundled readline v5. well, it builds for us too, but later i get a conflict situation on our system between mariadb-common and mariadb-client (i didn't look deep into it). but normally we try to use as much system shared libraries as possible, also, what about security fixes + bug fixes in this bundled readline v5, etc... i donno how long v5 will be maintained by gnu, this is not an ideal situation. i've checked and noted that mariadb server is GPLv2only and mariadb client has a FLOSS exception... which doesn't mention GPLv3 so i'm a bit at a loss here, ideally we want to use system shared readline v6 on our distribution... it's a bit of a PITA for me, tbh, i mean, it's all open source, i don't really get why this is all so difficult... :-( I guess the easiest way for us is if mariadb was GPLv2+ we could go ahead and use readline v6. I guess i'm asking for a way to proceed for us. Regards, AL13N

4 6

Re: [Maria-developers] [Commits] Rev 3077: Fixed LP bug #904345. in file:///home/igor/maria/maria-5.2-bug904345/
by Oleksandr Byelkin 27 Dec '11

27 Dec '11

Hi! 27.12.2011 20:46, Igor Babaev пишет: > At file:///home/igor/maria/maria-5.2-bug904345/ > > ------------------------------------------------------------ > revno: 3077 > revision-id: igor(a)askmonty.org-20111227184559-v7hlo1h98dz8ui32 > parent: wlad(a)montyprogram.com-20111223140257-k34gmwhs5msbc57k > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.2-bug904345 > timestamp: Tue 2011-12-27 10:45:59 -0800 > message: > Fixed LP bug #904345. > > The MIN/MAX optimizer code from the function opt_sum_query erroneously > did not take into account conjunctive conditions that did not depend on > any table, yet were not identified as constant items. These could be > items containing rand() or PS/SP parameters. These items are supposed > to be evaluated at the execution phase. That's why if such conditions > can be extracted from the WHERE condition the MIN/MAX optimization is > not applied as currently it is always done at the optimization phase. > > (In 5.3 expensive subqueries are also evaluated only at the execution > phase. So, if a constant condition with such subquery can be extracted > from the WHERE clause the MIN/MAX optimization should not be applied > in 5.3.) > > IF an IN/ALL/SOME predicate with a constant left part is transformed > into an EXISTS subquery the resulting subquery should not be considered > uncacheable if the right part of the predicate is not uncacheable. > > Backported the function dbug_print_item() from 5.3. The function is used > only for debugging. [skip] > === modified file 'sql/item.cc' > --- a/sql/item.cc 2011-11-24 16:48:58 +0000 > +++ b/sql/item.cc 2011-12-27 18:45:59 +0000 > @@ -7915,6 +7915,22 @@ > ((TABLE_LIST *)data)->hide_view_error(thd); > } > > +/* Debugger help function */ > +static char dbug_item_print_buf[256]; > + > +const char *dbug_print_item(Item *item) > +{ > + char *buf= dbug_item_print_buf; > + String str(buf, sizeof(dbug_item_print_buf),&my_charset_bin); > + str.length(0); > + if (!item) > + return "(Item*)NULL"; > + item->print(&str ,QT_ORDINARY); > + if (str.c_ptr() == buf) > + return buf; > + else > + return "Couldn't fit into buffer"; > +} This should be put under #ifndef DBUG_OFF Everything else looks OK to be pushed. [skip]

1 0

Re: [Maria-developers] [Commits] Rev 3170: After-merge fixes for 5.5 merge. in http://bazaar.launchpad.net/~maria-captains/maria/5.5
by Kristian Nielsen 27 Dec '11

27 Dec '11

Hi Monty, While fixing test failures in the MariaDB 5.5 tree, I encountered problems in sql_string, related to changes you did there in 5.2. I managed to come up with some solution, but please see below patch and see if you can suggest something better. Two issues: 1. You added realloc_with_extra() / realloc_with_extra_if_needed(), and used them to replace calls to realloc(). But realloc() as part of its operation puts a terminating '\0' at the end of the allocation; with your change this is now at a different (and not very useful) position in the buffer. My change tries to put it in the original place, but it is not terribly elegant, and perhaps your original change needs to be rethought. 2. I found a terrible mess (IMHO): We have two distict, subtly differing, sql_string.{h,cc}, one in /sql/ and one in /client/. We even seem to have a requirement for ABI compatibility between the two, since mysql_embedded uses client/sql_string.h with sql/sql_string.cc :-( And in fact your change seems to break such ABI compatibility, so I suspect mysql_embedded might be subtly broken in 5.2. But why do we need to have two sql_string in the first place? Especially given that they are apparently sufficiently close to be ABI compatible ... can't we eliminate on of them to get at least a little sanity into the sql_string story? - Kristian. knielsen(a)knielsen-hq.org writes: > At http://bazaar.launchpad.net/~maria-captains/maria/5.5 > > ------------------------------------------------------------ > revno: 3170 > revision-id: knielsen(a)knielsen-hq.org-20111209144027-yf5as4v9lb6jt3s7 > parent: knielsen(a)knielsen-hq.org-20111208170848-pijq4i8ceqwcl19q > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-5.5 > timestamp: Fri 2011-12-09 15:40:27 +0100 > message: > After-merge fixes for 5.5 merge. > > Fix typo causing too low timeout value for wait_for_slave_param.inc. > Fix wrong/missing '\0' termination in String:realloc() after Monty's > optimisations to reduce reallocation calls. > === modified file 'mysql-test/include/wait_for_slave_param.inc' > --- a/mysql-test/include/wait_for_slave_param.inc 2010-12-20 14:15:01 +0000 > +++ b/mysql-test/include/wait_for_slave_param.inc 2011-12-09 14:40:27 +0000 > @@ -79,7 +79,7 @@ if ($_slave_check_configured == 'No such > > # mysqltest doesn't provide any better way to multiply by 10 > --let $_wait_for_slave_param_zero= 0 > ---let $_slave_timeout_counter= $_slave_timeout$zero > +--let $_slave_timeout_counter= $_slave_timeout$_wait_for_slave_param_zero > --let $_slave_continue= 1 > while ($_slave_continue) > { > > === modified file 'sql/rpl_handler.cc' > --- a/sql/rpl_handler.cc 2011-11-03 18:17:05 +0000 > +++ b/sql/rpl_handler.cc 2011-12-09 14:40:27 +0000 > @@ -397,7 +397,7 @@ int Binlog_transmit_delegate::before_sen > > int ret= 0; > FOREACH_OBSERVER(ret, before_send_event, thd, > - (&param, (uchar *)packet->c_ptr(), > + (&param, (uchar *)packet->c_ptr_safe(), > packet->length(), > log_file+dirname_length(log_file), log_pos)); > return ret; > @@ -411,7 +411,7 @@ int Binlog_transmit_delegate::after_send > > int ret= 0; > FOREACH_OBSERVER(ret, after_send_event, thd, > - (&param, packet->c_ptr(), packet->length())); > + (&param, packet->c_ptr_safe(), packet->length())); > return ret; > } > > > === modified file 'sql/sql_string.cc' > --- a/sql/sql_string.cc 2011-11-03 18:17:05 +0000 > +++ b/sql/sql_string.cc 2011-12-09 14:40:27 +0000 > @@ -80,8 +80,12 @@ bool String::real_alloc(uint32 length) > no allocation occured. > > @retval true An error occured when attempting to allocate memory. > + > + The _internal() version is just to share code between realloc() and > + realloc_with_extra(), so that we can put the NULL termination at the right > + position. > */ > -bool String::realloc(uint32 alloc_length) > +bool String::realloc_internal(uint32 alloc_length) > { > if (Alloced_length <= alloc_length) > { > @@ -109,6 +113,13 @@ bool String::realloc(uint32 alloc_length > Ptr= new_ptr; > Alloced_length= len; > } > + return FALSE; > +} > + > +bool String::realloc(uint32 alloc_length) > +{ > + if (realloc_internal(alloc_length)) > + return TRUE; > Ptr[alloc_length]=0; // This make other funcs shorter > return FALSE; > } > > === modified file 'sql/sql_string.h' > --- a/sql/sql_string.h 2011-11-03 18:17:05 +0000 > +++ b/sql/sql_string.h 2011-12-09 14:40:27 +0000 > @@ -255,12 +255,18 @@ public: > return real_alloc(arg_length); > } > bool real_alloc(uint32 arg_length); // Empties old string > +private: > + bool realloc_internal(uint32 arg_length); > +public: > bool realloc(uint32 arg_length); > bool realloc_with_extra(uint32 arg_length) > { > if (extra_alloc < 4096) > extra_alloc= extra_alloc*2+128; > - return realloc(arg_length + extra_alloc); > + if (realloc_internal(arg_length + extra_alloc)) > + return TRUE; > + Ptr[arg_length]= 0; > + return FALSE; > } > bool realloc_with_extra_if_needed(uint32 arg_length) > { > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

3 2

[Maria-developers] Thread pool (request for review)
by Vladislav Vaintroub 27 Dec '11

27 Dec '11

Hello, Worklog#246 http://askmonty.org/worklog/Server-BackLog/?tid=246 is ready to review. The worklog is implemented in lp:~maria-captains/maria/5.5-threadpool, and if someone prefers to have a one big diff, it is in the attachment(diff.diff) It would be great if Monty, Serg (optionally Kristian if there is interest) could take a some time to review it in the near future. The worklog has extensive description of the implementation, might be helpful to consult it before reviewing. The branch currently has thread-handling=pool-if-threads as default, I found it easier for testing. Based on results of benchmark (see below), it seems to be generally performing well. So maybe we should use it as default, beta version of 5.5 for better testing coverage . Maybe we can keep it as new default after testing default, if there are no complains. Benchmarking: I also run some sysbench on 3 machines (2 Linuxes - fb-maria1 with 16 cores and pitbull with 24 cores, and windows one with 8 cores but good IO), results in https://docs.google.com/spreadsheet/ccc?key=0AqvHLyfRWjmbdDY0U1MzZGlXY3VGdUx xcC1pUlBZeHc I used sysbench 0.4 to run some of "unit" oltp tests (point-select and update-nokey), as well as mere "classic" OLTP-readonly and OLTP-readwrite. All tests are run with number of concurrent clients ranging from 16 to 4096, with warm cache, with sysbench table having 1Mio records. The results seem to be quite different on all of the machines tested (the machines are very different, in terms of cores, IO etc) , yet threadpool has a positive effect on all 3 machines, and the positive effect seems to grow with the number of cores. Some words about how I ran the benchmarks 1. Benchmark client and server used different CPUs - (I used 'taskset' (Linux), or 'start /affinity' (Windows) to run benchmark client on #CPUs/4, the rest of CPUs are used by the server). On Linuxes, I used --thread_pool_size=<N> where N is number of cores I dedicate to server. 2.I used innodb_flush_log_at_trx_commit=0 and innodb_flush_method= ALL_O_DIRECT to avoid extensive fsyncing, which I think is ok for the purposes of the testing for this WL. 3. Every "write" benchmark (oltp_rw and update_nokey) started with new server (i.e I kill mysqld, remove innodb files, and restart mysqld for each test),whereas every "read" benchmark reused the same running server instance. Starting afresh with a new server on write benchmarks is done mailnly to eliminate the effects of the purge lag. I attach config file sysbench.ini is the one I used on fb-maria1 In case I missed to say it before, Happy Xmas and Happy New Year! Wlad PS. Elena, we need to have some plan on testing it. PPS. Kristian, tried to integrate the 5.5-threadpool branch into Buildbot, and stuck. Can you help?

2 2

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 23 Dec '11

23 Dec '11

A warning would be excellent. An error would be a problem. Returning OK is probably not the best result when the examined limit is reached. On Wed, Dec 21, 2011 at 7:17 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: >> >> Hi! >> >>>>>>> "MARK" == MARK CALLAGHAN<mdcallag(a)gmail.com> writes: >> >> >> MARK> I want a variant of the LIMIT clause that limits the number of rows >> MARK> examined during SELECT processing. This should return OK when the >> MARK> limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While >> MARK> LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument >> -- >> MARK> LIMIT_ROWS_EXAMINED x. Are you interested in implementing this >> feature >> MARK> for me? We will sponsor the work. The purpose of the feature is to >> MARK> prevent queries from taking too long when there isn't a great index >> MARK> and many rows might be filtered. >> >> I am a bit afraid of returning 'ok'. Would it not be better to return >> an error? >> >> Another option would be to generate a warning so that the application >> can know that they didn't get all rows. > > > This is what I would prefer to do: > - process the new LIMIT_ROWS_EXAMINED clause in the same way as LIMIT, > just with a different condition to end query execution, > - in addition produce a warning that not all rows were returned. > > Mark, does the above suit you? If you don't want to check for > warnings, you can skip them, however users will have the option > to detect that not all rows were returned. > > Timour > >> >> We can do this and it should be relatively easy to do. >> >> Regards, >> Monty >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-developers >> Post to : maria-developers(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-developers >> More help : https://help.launchpad.net/ListHelp -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-developers] Fixes for PAM plugin in packages
by Kristian Nielsen 21 Dec '11

21 Dec '11

Hi Serg, As promised, here a summary of what I did to fix PAM in linux packages. I managed to fix most issues I think (except for RHEL5 as noted below), but it needs testing, which takes time due to waiting for Buildbot. - I installed libpam0g-dev in all the VMs for .deb and bintar packages. I checked on one of them that this really does cause pam modules to build and install. - I installed pam-devel on the centos5 VMs. I also tried on the rhel5, but failed. These VMs seem to require some sort of license key or something to install packages with yum, which I don't have. Maybe Daniel can sort it out (all that is needed is `sudo yum install pam-devel` in vm-rhel5-amd64-build.qcow2 and vm-rhel5-x86-build.qcow2). - I hacked .deb packaging scripts (ourdelta scripts) so that dialog.so is in the libmariadbclient16 package (it used to be in the mariadb-server-5.[23] package). I did this for 5.2 and 5.3 (not 5.1). - The dialog.so change could cause an upgrade problem I think - installing new libmariadbclient16 package on top of old mariadb-server-5.[23] package will conflict because they both contain /usr/lib/mysql/plugin/dialog.so. I tried to fix this by declaring a conflicts: dependency which should allow Apt to sort it out. I hope it works - .deb dependencies can be tricky, hopefully buildbot Upgrade tests will catch any problems. - I did not change RPM packages wrt. dialog.so. I don't know if it is needed, nor how to do it... What remains is basically testing. First checking Buildbot when it has chewed through all the builds. It might be a good idea also to test manually a couple packages that the PAM plugin is in there and actually works according to instructions on the knowledgebase. - Kristian.

2 1

[Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 21 Dec '11

21 Dec '11

I want a variant of the LIMIT clause that limits the number of rows examined during SELECT processing. This should return OK when the limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature for me? We will sponsor the work. The purpose of the feature is to prevent queries from taking too long when there isn't a great index and many rows might be filtered. -- Mark Callaghan mdcallag(a)gmail.com

2 1

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 18 Dec '11

18 Dec '11

On Sat, Dec 17, 2011 at 3:18 PM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Mark, > > >> I want a variant of the LIMIT clause that limits the number of rows >> examined during SELECT processing. This should return OK when the > > > I had a look at the code, and it seems you need a limit on the value > of Rows_examined that is currently available in the slow query log. > Did I understand correctly that this is what you need? yes > > >> limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While >> LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- >> LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature >> for me? We will sponsor the work. The purpose of the feature is to >> prevent queries from taking too long when there isn't a great index >> and many rows might be filtered. >> > > Have you considered a system variable instead of extending the query > syntax? If you rejected this idea, why? > > It seems quite logical to me that one may want to set the same limit > for more than one query. In this case a system variable would make > life easier, because no queries need to be changed. Personally I > would prefer not to extend the query language with constructs whose > purpose is to compensate for the imperfection of the optimizer. This isn't about limits on the optimizer. We don't have a great index for some queries and never will. We prefer to stop queries early rather than have them examine 1,000,000 rows. Real users might be waiting on the query response so we really need to finish queries fast and in some cases our apps work as expected were queries allowed to terminate early. I almost always prefer to attach the semantics to the statement and that includes this case. Anyone looking at the statement via a log (slow query, general), SHOW PROCESSLIST, tcpdump should be able to understand what the statement does. Making the LIMIT part of a session variable prevents that. I also don't want to risk an additional round trip to the server and for drivers that don't support multi-statement or for limitations on the code that generates the SQL, I don't want to do: set session limit_rows_examined=1000; SELECT /* with rows examined limit */ ... ; set session limit_rows_examined=0; -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] Patch for xtradb compilation on visual studio
by Kristian Nielsen 15 Dec '11

15 Dec '11

Here is a patch for XtraDB (from Percona Server 5.5.17-rel22.1). We needed this in MariaDB 5.5 to compile on Windows. The problem is that this compiler apparently does not like having #ifdef inside macro invocations. I thought Percona might want to include it upstream, in case there are other compilers that also do not like this. - Kristian. === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc 2011-12-15 09:34:14 +0000 +++ storage/innobase/handler/ha_innodb.cc 2011-12-15 12:35:48 +0000 @@ -12369,14 +12369,16 @@ static MYSQL_SYSVAR_ULONG(concurrency_ti "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket", NULL, NULL, 500L, 1L, ~0L, 0); -static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction, - PLUGIN_VAR_RQCMDARG, #ifdef EXTENDED_FOR_KILLIDLE - "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.", +#define TMP_STR "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB." #else - "No effect for this build.", +#define TMP_STR "No effect for this build." #endif +static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction, + PLUGIN_VAR_RQCMDARG, + TMP_STR, NULL, NULL, 0, 0, LONG_MAX, 0); +#undef TMP_STR static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,

2 1

Re: [Maria-developers] Please fix: innodb_plugin.innodb_gis fails on 'gcov' builder
by Sergei Petrunia 14 Dec '11

14 Dec '11

Hi Alexey, We have a repeatable failure like this: https://internal.askmonty.org/buildbot/builders/gcov/builds/830/steps/test_… innodb_plugin.innodb_gis w3 [ fail ] Test ended at 2011-12-14 06:52:31 CURRENT_TEST: innodb_plugin.innodb_gis mysqltest: At line 1: query 'SET storage_engine=innodb' failed: 1286: Unknown table engine 'innodb' The result from queries just before the failure was: SET storage_engine=innodb; - saving '/home/buildbot/buildbot_basedir/perpush-gcov/build/mysql-test/var/3/log/innodb_plugin.innodb_gis/' to '/home/buildbot/buildbot_basedir/perpush-gcov/build/mysql-test/var/log/innodb_plugin.innodb_gis/' It's in GIS, so I assume it is on your turf. Could you please fix this? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 2

Re: [Maria-developers] Please do a test of 5.3 with these new @@optimizer_switch settings
by Sergei Petrunia 14 Dec '11

14 Dec '11

Hi Elena, On Thu, Dec 08, 2011 at 06:29:01AM +0400, Elena Stepanova wrote: > I tried to set them run overnight, but it isn't going too well. Most of > tests are crashing, see https://bugs.launchpad.net/maria/+bug/901506 . > This bug is finally fixed and pushed. Could you please run more tests on the fix asap? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Compilation problem on an old 32-bit machine
by Kristian Nielsen 12 Dec '11

12 Dec '11

Hi Paul, It seems a patch is needed for PBXT to build on 32-bit x86 in some cases: Elena Stepanova <elenst(a)montyprogram.com> writes: > lock_xt.h: Assembler messages: > lock_xt.h:70: Error: bad register name `%sil' > After some re-installation and long digging, I found the ancient bug > report http://gcc.gnu.org/bugzilla/show_bug.cgi?id=10153 which gets > re-filed regularly, the last time was a few months ago. They say it's > a code problem, and suggest replacing the '=r' constraint with '=q'. I > did so in all 4 lines that gcc complained about, and finally got it > work. It seems correct to me that the "q" constraint must be used rather than "r" for the 8-bit case. Below patch should do it (we will apply this in MariaDB). - Kristian. === modified file 'storage/pbxt/src/lock_xt.h' --- storage/pbxt/src/lock_xt.h 2010-05-06 12:57:15 +0000 +++ storage/pbxt/src/lock_xt.h 2011-12-12 11:34:54 +0000 @@ -67,9 +67,9 @@ inline void xt_atomic_inc1(volatile xtWo #elif defined(XT_ATOMIC_GNUC_X86) xtWord1 val; - asm volatile ("movb %1,%0" : "=r" (val) : "m" (*mptr) : "memory"); + asm volatile ("movb %1,%0" : "=q" (val) : "m" (*mptr) : "memory"); val++; - asm volatile ("xchgb %1,%0" : "=r" (val) : "m" (*mptr), "0" (val) : "memory"); + asm volatile ("xchgb %1,%0" : "=q" (val) : "m" (*mptr), "0" (val) : "memory"); #elif defined(XT_ATOMIC_SOLARIS_LIB) atomic_inc_8(mptr); #else @@ -91,9 +91,9 @@ inline xtWord1 xt_atomic_dec1(volatile x #elif defined(XT_ATOMIC_GNUC_X86) xtWord1 val2; - asm volatile ("movb %1, %0" : "=r" (val) : "m" (*mptr) : "memory"); + asm volatile ("movb %1, %0" : "=q" (val) : "m" (*mptr) : "memory"); val--; - asm volatile ("xchgb %1,%0" : "=r" (val2) : "m" (*mptr), "0" (val) : "memory"); + asm volatile ("xchgb %1,%0" : "=q" (val2) : "m" (*mptr), "0" (val) : "memory"); /* Should work, but compiler makes a mistake? * asm volatile ("xchgb %1, %0" : : "r" (val), "m" (*mptr) : "memory"); */

2 1

Re: [Maria-developers] Please fix: gis-precise crashes on solaris
by Alexey Botchkov 12 Dec '11

12 Dec '11

Hi, Adam. Yes i need the access to the machine. Wasn't able to figure out what's wrong by looking at the traces. The crashing query is probably the most trivial of them all. Regards HF 10.12.2011 15:29, Adam M Dutko wrote: > > Let me know if you need access to the machine and I will coordinate > off-list. > > -Adam > >

1 0

[Maria-developers] WL#246 New (by Wlad): Threadpool
by worklog-noreply＠askmonty.org 09 Dec '11

09 Dec '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Threadpool CREATION DATE..: Fri, 09 Dec 2011, 22:23 SUPERVISOR.....: IMPLEMENTOR....: Wlad COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 246 (http://askmonty.org/worklog/?tid=246) VERSION........: Maria-2.0 STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 300 (hours remain) ORIG. ESTIMATE.: 300 PROGRESS NOTES: DESCRIPTION: Implement a dynamic thread pool, which provides good (superior compared to thread-per-connection) performance for short queries and many threads and does not block on on long queries. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Please fix: gis-precise crashes on solaris
by Sergei Petrunia 09 Dec '11

09 Dec '11

Hi Alexey, I'm looking at 5.3 tree and see main.gis-precise to repeatably crash on solaris: http://buildbot.askmonty.org/buildbot/builders/sol-sparc-32/builds/1518/ste… We don't have a solaris machine but the test failure has a stack trace. Could you please look into this? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Please do a test of 5.3 with these new @@optimizer_switch settings
by Sergei Petrunia 08 Dec '11

08 Dec '11

Hi Elena, We're going to make the following enabled by default: @@optimizer_switch='semijoin_with_cache=on,outer_join_with_cache=on' @@join_cache_level=2 Could you please make a test run with these settings? It is important to test revision>=3337 (just pushed it), because it has a change in the optimizer which makes it choose semijoin+join_cache execution plans much more often. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#245 New (by Sanja): EXISTS to IN transformation
by worklog-noreply＠askmonty.org 07 Dec '11

07 Dec '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: EXISTS to IN transformation CREATION DATE..: Wed, 07 Dec 2011, 12:51 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 245 (http://askmonty.org/worklog/?tid=245) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Transform queries like: ... WHERE EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 2*outer_table.field AND maybe_something_else)... and ... WHERE NOT EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 2*outer_table.field AND maybe_something_else)... into ... WHERE 2*outer_table.field IN (SELECT inner_table.field FROM inner_table WHERE 1 = 1 AND maybe_something_else).. ... WHERE 2*outer_table.field IS NULL OR 2*outer_table.field IN (SELECT inner_table.field FROM inner_table WHERE inner_table.field IS NOT NULL AND maybe_something_else).. To allow optimizations made for IN/ALL/ANY subqueries. Conversion is possible only if: 1)real NULL is not important (top element of WHERE/ON AND/OR list, i.e. NULL equal to FALSE) 2)the subquery has the only dependence which we bring out of it 3)the subquery is simple (has no aggregate function, GROUp BY, ORDER BY, LIMIT HAVING and so on) For NOT EXISTS conversion the subquery should be marked that its left part can't be NULL. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] No coverage for cases where materialization failed to create temptable?
by Sergei Petrunia 06 Dec '11

06 Dec '11

Hi Timour, I was working on BUG#868908, which involves having different kind of handling for cases where Materialization is not applicable because temp table had the wrong properties. So, I've set a goal to catch such cases: === modified file 'sql/item_subselect.cc' --- sql/item_subselect.cc 2011-12-06 21:03:00 +0000 +++ sql/item_subselect.cc 2011-12-06 21:11:16 +0000 @@ -4182,6 +4182,8 @@ bool subselect_hash_sj_engine::init(List */ if (tmp_table->s->keys == 0) { + //fprintf(stderr, "Q: %s\n", current_thd->query()); + DBUG_ASSERT(0); DBUG_ASSERT( tmp_table->s->uniques || tmp_table->key_info->key_length >= tmp_table->file->max_key_length() || Surprisingly, running mysql-test-run t/subselect*.test with this didn't produce any failures. Is this poor test coverage? Or, this branch is now deadcode? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Tools/suggestions for MySQL 5.6 merge
by Kristian Nielsen 06 Dec '11

06 Dec '11

I have been doing a bit of preparation/planning for the upcoming merge of MySQL 5.6. I think the 5.6 merge (and 5.5 merge) is very important for MariaDB. There is much stuff in there that we really need to make available to our users (and probably some better left out). If we fail to keep the merges going, it will leave the community in a very sorry situation; I do not see anyone else stepping up to this task. My idea is that we could merge MySQL 5.6 piece-by-piece. Several other developers mentioned this idea at the Athens meeting: - It makes it much easier to selectively merge or not merge stuff. For example there are probably large chunks of optimiser changes that we do not want to merge (or maybe merge only the testcases for). On the other hand we probably want all InnoDB stuff. - I am hopeful it makes the merge much more managable when done in smaller pieces. Large reorganisations of code (header refactoring, copyright changes) should be easier when not intermixed with other changes. Merges from MySQL 5.5 -> MySQL 5.6 are probably also easier to deal with when done in isolation (I assume we can get conflicts for these, though we already have the equivalent changes in MariaDB 5.5). - Easier to share the task between developers. One developer can do a single piece in a few days, then pass the baton when he needs to switch to other tasks (or just gets tired). Complex merges of large feature pushes can be null-merged to allow main merge to progress, and in parallel another developer can work on reworking the patch into the MariaDB code. For some changes I think we will even be able to do the actual merge in parallel. - Easier to plan and understand how far the merge is progressing. Eg. if we merge M weeks worth of 5.6 pushes every N days, that allows to estimate when we will catch up with Oracle development - Easier debugging of test failures; by keeping the trees green between pieces merged, new test failures will be known to be caused by relatively smaller sets of changes, simplifying debugging. I have done some work to facilitate this kind of piecewise merge. As it turns out, it is not trivial to determine suitable pieces to merge. Oracle developers use no discipline for the direction they do merges in, so the sequence of pushes into lp:mysql-server ("trunk") is not immediately apparent. So I took the time to reconstruct the list of pushes from reading through the history; this was not overly hard to do. I next wrote a script that scans the history based on this list of pushes, and further annotates each push with some useful information: - Size of diff for the patch - Number of changesets merged (if a merge) - Mark if this is a merge from MySQL 5.5 - One-line short comment for the push, either hand-crafted for selected items, or else extracted from the commit message. - Revision ID (including developer info) It should be fairly simple to scan this list from the bottom, collecting chunks of related or manageable pushes and merging them as one piece. Eg. A sequence of small pushes could be merged as one, or a sequence of pushes by one developer, or a single large feature push. Here is an example extract of such an annotated list: 0 2* tor.didriksen(a)oracle.com-20111101065343-ha3d79h1ayk9j7x9 [5.5] Null merge from 5.1/5.5 13 andrei.elkin(a)oracle.com-20111031135232-q13pfsaieo8ujekj Minor test adjustment 791 alexander.nozdrin(a)oracle.com-20111031115220-01la8vj7ssmcm0mc Bug#61392 0 2* tor.didriksen(a)oracle.com-20111031091354-8p6s03s1zxerrp8p [5.5] Null merge Bug#12406055 from 5.1/5.5 5380 15 jorgen.loland(a)oracle.com-20111031081122-dnufl9r4li0i3ni6 [opt] Larger merge from optimiser tree 134 2* andrei.elkin(a)oracle.com-20111028212219-cdcuh37i70k9o4ms [5.5] Merge Bug#56299 (?) from 5.5 451 2 andrei.elkin(a)oracle.com-20111028130851-mz4p2mg28y14wb7k merge from trunk to local tree. 43 sergey.glukhov(a)oracle.com-20111028094528-enre87osw9u4qjy7 [opt] Bug#32254 post-merge fix (optimiser) 48 tor.didriksen(a)oracle.com-20111028092548-a3gjubgmwuhdunfj Bug#11747423 post-merge fix 114 tor.didriksen(a)oracle.com-20111028091835-xqavfrxkc50gbsxq "Default value for sort_buffer_size changed from 2M to 256K" The first column is diff size, the second is number of changesets merged (or empty for non-merge changesets). A "*" marks pushes that are only merges from MySQL 5.5. I pushed the script to lp:mariadb-tools, in /mysql-5.6-merge-stuff/. It also includes the full output in 5.6-annotated.txt (it takes 15-20 minutes for bzr to chew through the history on a fast multi-core machine). I hope this will be useful for the merge. Comments/suggestions/questions very welcome! - Kristian.

1 0

[Maria-developers] MariaDB 5.1.60 and 5.2.10
by Daniel Bartholomew 06 Dec '11

06 Dec '11

All, MariaDB 5.1.60 is nearly ready to release. There are only a couple of mirrors which don't have the files. The changelog and release notes pages are here: * http://kb.askmonty.org/en/mariadb-5160-release-notes * http://kb.askmonty.org/en/mariadb-5160-changelog Unless something comes up, I will probably activate the download page for this release and 5.2.10 in a couple of hours. This will be followed by a combined announce email and so forth. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] MariaDB 5.2.10
by Daniel Bartholomew 05 Dec '11

05 Dec '11

All, The MariaDB 5.2.10 release is just about ready to go. Most of the packages have been mirrored. We're still waiting on Windows packages (we're having some issues with the Windows build server), but they will hopefully be available soon. This brings up the question: Should we delay the release until we have Windows packages or add them after the fact? The Release Notes and Changelog for the release are here: - http://kb.askmonty.org/en/mariadb-5210-release-notes - http://kb.askmonty.org/en/mariadb-5210-changelog Please let me know ASAP if there is anything that should be mentioned in the Release Notes that currently is not (or jump in and add it directly to the page yourself). Serg: The PAM note in the release notes is pretty sparse. Can you add some more to it? Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 3120: Added suppressions
by Sergei 03 Dec '11

03 Dec '11

Hi, Monty! Can you please explain how it is possible to have "different number rows in the log" ? On Dec 03, noreply(a)launchpad.net wrote: > ------------------------------------------------------------ > revno: 3120 > committer: Michael Widenius <monty(a)askmonty.org> > branch nick: maria-5.1 > timestamp: Sat 2011-12-03 20:29:15 +0200 > message: > Added suppressions > Fixed feedback_plugin_send to not generate a random number of lines. > modified: > mysql-test/t/feedback_plugin_send.test > mysql-test/valgrind.supp > support-files/compiler_warnings.supp > > === modified file 'mysql-test/t/feedback_plugin_send.test' > --- mysql-test/t/feedback_plugin_send.test 2011-11-13 12:28:35 +0000 > +++ mysql-test/t/feedback_plugin_send.test 2011-12-03 18:29:15 +0000 > @@ -17,7 +17,16 @@ > perl; > $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; > open(LOG, '<', $log_error) or die "open(< $log_error): $!"; > - /feedback plugin:.*/ && print "$&\n" while $_=<LOG>; > + > + # Get the first few rows (as there may be different number rows in the log) > + $i= 0; > + while ($_=<LOG>) > + { > + if (/feedback plugin:.*/) > + { > + print "$&\n"; > + break if ($i++ >= 3); > + } > + } > close LOG; > EOF > - > Regards, Sergei

1 0

[Maria-developers] 5.3: optimizer status update and test requests
by Sergei Petrunia 01 Dec '11

01 Dec '11

Hi Elena, A quick update on what has been happening in 5.3 optimizer recently. The text is FYI, requests for QA are marked with 'REQUEST': == Semi-joins: no non-PS crashes, optimizations refactoring == When I was fixing BUG#887468, I have made some refactorings to semi-join optimization code and pushed them into 5.3-main (revno: 3316, revision-id: psergey(a)askmonty.org-20111126082752-u8s9tv928lc67c8o) This change has grown to be more than just refactoring: - EXPLAIN now shows "Start temporary" at different position (documentation pending) - The optimizer may pick certain plans that were not picked before. REQUEST#1: please re-run semi-join optimization tests for the new server. == Discovery of effects of @@optimizer_prune_level == I have (yesterday) found https://bugs.launchpad.net/maria/+bug/898747. In a nutshell, default value of @@optimizer_prune_level=1 causes the optimizer to prune away some semi-join query plans. There can be different views about how serious BUG#898747 is, but its effect is that certain range of query plans were not tested before. REQUEST#2: re-run semi-join optimization tests with @@optimizer_prune_level=0 This can be joined with REQUEST#1 (however, if you find bugs, we're interested to know whether they have been present before the revno:3316 patch). To be continued... BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

[Maria-developers] Proposal: change SUBQUERY to MATERIALIZE
by Sergei Petrunia 01 Dec '11

01 Dec '11

2 1

Re: [Maria-developers] Proposal: change SUBQUERY to MATERIALIZE
by Sergei Petrunia 01 Dec '11

01 Dec '11

1 0

Re: [Maria-developers] Proposal: change SUBQUERY to MATERIALIZE
by Sergei Petrunia 01 Dec '11

01 Dec '11

1 0

[Maria-developers] MYSQL_THDVAR and SET GLOBAL
by MARK CALLAGHAN 30 Nov '11

30 Nov '11

Is there an easy way to prevent SET GLOBAL ... from being done for plugin variables declared via MYSQL_THDVAR_...? For the following variable I can do SET GLOBAL innodb_fake_changes=1 and that sets a global variable. static MYSQL_THDVAR_BOOL(fake_changes, PLUGIN_VAR_OPCMDARG, "In the transaction after enabled, UPDATE, INSERT and DELETE only move the cursor to the records " "and do nothing other operations (no changes, no ibuf, no undo, no transaction log) in the transaction. " "This is to cause replication prefetch IO. ATTENTION: the transaction started after enabled is affected.", check_fake_changes, NULL, FALSE); -- Mark Callaghan mdcallag(a)gmail.com

3 2

[Maria-developers] MariaDB 5.5 binary tarball packages and libaio
by Kristian Nielsen 30 Nov '11

30 Nov '11

Hi Wlad, I got some progress with Buildbot and producing binary tarball packages for MariaDB 5.5, using your suggestions. But I encountered a problem. I used -DBUILD_CONFIG=mysql-release and installed libaio-dev. But then the server fails like this: ./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory Of course - as libaio.so must be installed by users. I do not think it is appropriate that users need to install libaio.so to use the binary tarballs (as those are supposed to work anywhere - on the other hand, for .deb for example we _should_ use libaio of course, and dependencies will take care that it is installed). So what to do? For now I've tried to build without libaio for the generic tarballs (-DIGNORE_AIO_CHECK=1). So binaries will work; to get aio, users can build themselves or install real packages. Or do you have another idea? Do you know what MySQL does? One option could be statically linking libaio. Now in general, static linking is a can of worms with many pitfalls, however for just libaio it might work (libaio.so is statically linked on my Debian Wheezy). However, I am not sure if this is safe in general, nor do I know how to make cmake link just libaio statically? Any ideas? - Kristian.

4 4

Re: [Maria-developers] [Commits] Rev 3322: Fixed bug lp:825051 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 30 Nov '11

30 Nov '11

29.11.2011 23:06, timour(a)askmonty.org пишет: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3322 > revision-id: timour(a)askmonty.org-20111129210639-bjcn2pfocub6uwba > parent: holyfoot(a)askmonty.org-20111129112752-6cy8wy7h5q58l6je > fixes bug(s): https://launchpad.net/bugs/825051 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-11-29 23:06:39 +0200 > message: > Fixed bug lp:825051 > > The cause of the wrong result was that Item_ref_null_helper::get_date() > didn't use a method of the *_result() family, and fetched the data > for the field from the current row instead of result_field. Changed to > use the correct *_result() method, like to all other similar methods > of Item_ref_null_helper. OK to push. Do not forget that we agreed to fix this typo in 5.2 at least.

1 0

[Maria-developers] Alternate fix for BUG#833777 and BUG#894397
by Sergei Petrunia 28 Nov '11

28 Nov '11

Hi Timour, Please find it below. Any objections? === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2011-11-25 19:54:36 +0000 +++ sql/sql_select.cc 2011-11-28 11:40:42 +0000 @@ -14776,14 +14776,10 @@ do_select(JOIN *join,List<Item> *fields, /* HAVING will be checked after processing aggregate functions, But WHERE should checkd here (we alredy have read tables). - If there is join->exec_const_cond, and all tables are constant, then it - is equivalent to join->conds. exec_const_cond is already checked in the - beginning of JOIN::exec. If it is false, JOIN::exec returns zero - result already there, therefore execution reaches this point only if - exec_const_cond is TRUE. Since it is equvalent to join->conds, then - join->conds is also TRUE. + + (TODO new comment here) */ - if (!join->conds || join->exec_const_cond || join->conds->val_int()) + if (!join->outer_ref_cond || join->outer_ref_cond->val_int()) { error= (*end_select)(join, 0, 0); if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3308: Fixed LP BUG#747278 in file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/
by Sergei Petrunia 28 Nov '11

28 Nov '11

Hi Sanja, Ok to push. On Wed, Nov 23, 2011 at 01:46:22AM +0200, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/ > > ------------------------------------------------------------ > revno: 3308 > revision-id: sanja(a)askmonty.org-20111122234621-b1t47i7xgzsg1hwo > parent: holyfoot(a)askmonty.org-20111122135733-vgsg077x9rbn08r6 > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-lpb747278 > timestamp: Wed 2011-11-23 01:46:21 +0200 > message: > Fixed LP BUG#747278 > > The problem was that when we have single row subquery with no rows > Item_cache(es) which represent result row was not null and being > requested via element_index() returned random value. > > The fix is setting all Item_cache(es) in NULL before executing the > query (reset() method) which guaranty NULL value of whole query > or its elements requested in any way if no rows was found. > > set_null() method was added to Item_cache to guaranty correct NULL > value in case of reseting the cache. > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect.result 2011-11-22 23:46:21 +0000 > @@ -5681,4 +5681,85 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2011-11-22 23:46:21 +0000 > @@ -5680,6 +5680,87 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2011-11-21 20:01:47 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2011-11-22 23:46:21 +0000 > @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2011-11-22 23:46:21 +0000 > @@ -5685,6 +5685,87 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-22 23:46:21 +0000 > @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2011-11-21 20:16:01 +0000 > +++ b/mysql-test/t/subselect.test 2011-11-22 23:46:21 +0000 > @@ -4778,4 +4778,47 @@ set @@optimizer_switch='in_to_exists=on, > select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); > drop table t1; > > +--echo # > +--echo # LP BUG#747278 incorrect values of the NULL (no rows) single > +--echo # row subquery requested via element_index() interface > +--echo # > + > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > + > +# check different IN with switches where the bug was found > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > + > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > + > +set @@session.optimizer_switch=@old_optimizer_switch; > + > +# check different IN with default switches > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > + > +# other row operation with NULL single row subquery also should work > +select (null, null) = (null, null); > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > + > +drop tables t1,t2,t3; > +--echo # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/item.cc' > --- a/sql/item.cc 2011-11-02 08:05:07 +0000 > +++ b/sql/item.cc 2011-11-22 23:46:21 +0000 > @@ -8201,6 +8201,20 @@ void Item_cache::print(String *str, enum > str->append(')'); > } > > +/** > + Assign to this cache NULL value if it is possible > +*/ > + > +void Item_cache::set_null() > +{ > + if (maybe_null) > + { > + null_value= TRUE; > + value_cached= TRUE; > + } > +} > + > + > bool Item_cache_int::cache_value() > { > if (!example) > @@ -8671,6 +8685,20 @@ void Item_cache_row::bring_value() > } > > > +/** > + Assign to this cache NULL value if it is possible > +*/ > + > +void Item_cache_row::set_null() > +{ > + Item_cache::set_null(); > + if (!values) > + return; > + for (uint i= 0; i < item_count; i++) > + values[i]->set_null(); > +}; > + > + > Item_type_holder::Item_type_holder(THD *thd, Item *item) > :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item)) > { > > === modified file 'sql/item.h' > --- a/sql/item.h 2011-11-14 17:24:36 +0000 > +++ b/sql/item.h 2011-11-22 23:46:21 +0000 > @@ -3651,6 +3651,7 @@ public: > return false; > return example->is_expensive_processor(arg); > } > + virtual void set_null(); > }; > > > @@ -3825,6 +3826,7 @@ public: > DBUG_VOID_RETURN; > } > bool cache_value(); > + virtual void set_null(); > }; > > > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2011-11-21 14:56:32 +0000 > +++ b/sql/item_subselect.cc 2011-11-22 23:46:21 +0000 > @@ -893,7 +893,10 @@ void Item_singlerow_subselect::reset() > { > Item_subselect::reset(); > if (value) > - value->null_value= TRUE; > + { > + for(uint i= 0; i < engine->cols(); i++) > + row[i]->set_null(); > + } > } > > > @@ -1004,6 +1007,11 @@ void Item_singlerow_subselect::fix_lengt > */ > if (engine->no_tables()) > maybe_null= engine->may_be_null(); > + else > + { > + for (uint i= 0; i < max_columns; i++) > + row[i]->maybe_null= TRUE; > + } > } > > > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#244 New (by Knielsen): Reduced table/row locking for SELECT statement with read-only stored function
by worklog-noreply＠askmonty.org 25 Nov '11

25 Nov '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Reduced table/row locking for SELECT statement with read-only stored function CREATION DATE..: Thu, 24 Nov 2011, 10:55 SUPERVISOR.....: Monty IMPLEMENTOR....: Monty COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 244 (http://askmonty.org/worklog/?tid=244) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: When statement (or mixed) binlogging is used, the server currently is overly conservative in its use of table/row locks for SELECT statements that call stored functions. For example: delimiter // CREATE FUNCTION `test_f`(x CHAR(8)) RETURNS INT READS SQL DATA BEGIN DECLARE output INT DEFAULT NULL; SELECT id FROM t1 WHERE a = x INTO output; RETURN output; END // delimiter ; SELECT test_f("foo"); In general, it is possible for a SELECT statement that calls a stored function to modify tables, and thus to need to be binlogged. In statement-based binlogging, this requires taking extra read locks to ensure that replication will apply events from concurrent queries on the master in the correct order, just like normal UPDATE statements. However, most uses of stored functions in SELECT do _not_ modify data, as in the above example. Thus there is no need for extra read locks in statement-based binlog mode, as nothing will be logged to the binlog anyway for the SELECT statement. However, the server currently takes the pessimistic safe route and _always_ takes extra read locks in statement-based binlogging when stored functions are used. There is no analysis to detect that stored functions used are read-only, and hence that no extra locking is needed. This worklog is about improving this so that in common cases where no table modification is done in stored functions, no extra locking is done, while still preserving correct operation for statement-based replication when modification _is_ done. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

2 1

Re: [Maria-developers] Fwd: [Commits] Rev 3296: Fix bug lp:833777 in file:///home/tsk/mprog/src/5.3-lpb833777/
by Sergei Petrunia 21 Nov '11

21 Nov '11

Hi Timour, On Fri, Nov 18, 2011 at 11:16:27PM +0200, Timour Katchaounov wrote: > Sergey, > > Please review the following patch. > Do you have any idea about the change in > func_compress.result ? I've seen that before, > but don't remember what was the issue. I think in this case it is fairly clear - a warning is produced every time a constant expression is evaluated. Your patch makes one less evaluation, so one fewer warning is emitted. The patch is ok to push after the below notes are addressed. > ------------------------------------------------------------ > revno: 3296 > revision-id: timour(a)askmonty.org-20111118201920-fac3c9870u7eh898 > parent: igor(a)askmonty.org-20111118173551-78xd3uuu5h25756u > fixes bug(s): https://launchpad.net/bugs/833777 > committer: timour(a)askmonty.org > branch nick: 5.3-lpb833777 > timestamp: Fri 2011-11-18 22:19:20 +0200 > message: > Fix bug lp:833777 > > Analysis: > The optimizer distinguishes two kinds of 'constant' conditions: > expensive ones, and non-expensive ones. The non-expensive conditions > are evaluated inside make_join_select(), and if false, already the > optimizer detects empty query results. > > In order to avoid arbitrarily expensive optimization, the evaluation of > expensive constant conditions is delayed until execution. These conditions > are attached to JOIN::exec_const_cond and evaluated in the beginning of > JOIN::exec. The relevant execution logic is: > > JOIN::exec() > { > if (! join->exec_const_cond->val_int()) > { > produce an empty result; > stop execution > } > continue execution > execute the original WHERE clause (that contains exec_const_cond) > ... > } > > As a result, when an expensive constant condition is > TRUE, it is evaluated twice - once through > JOIN::exec_const_cond, and once through JOIN::cond. > When the expensive constant condition is a subquery, > predicate, the subquery is evaluated twice. If we have > many levels of subqueries, this logic results in a chain > of recursive subquery executions that walk a perfect > binary tree. The result is that for subquries with depth N, > JOIN::exec is executed O(2^N) times. > > Solution: > Notice that the second execution of the constant conditions > happens inside do_select(), in the branch: > if (join->table_count == join->const_tables) { ... } > In this case exec_const_cond is equivalent to the whole WHERE > clause, therefore the WHERE clause has already been checked in > the beginnig of JOIN::exec, and has been found to be true. > The bug is addressed by not evaluating the WHERE clause if there > was exec_const_conds, and it was TRUE. > === modified file 'mysql-test/r/func_compress.result' > --- a/mysql-test/r/func_compress.result 2011-02-03 15:00:28 +0000 > +++ b/mysql-test/r/func_compress.result 2011-11-18 20:19:20 +0000 > @@ -103,7 +103,6 @@ foo > Warnings: > Error 1259 ZLIB: Input data corrupted > Error 1259 ZLIB: Input data corrupted > -Error 1259 ZLIB: Input data corrupted > explain select *, uncompress(a) from t1; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t1 system NULL NULL NULL NULL 1 > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect.result 2011-11-18 20:19:20 +0000 > @@ -5665,4 +5665,14 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 Why use 'benchmark' function? It seems totally redundant. Please change the test to look like: select (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in ... (select a from t1) ))))))))))))))))))))))))))))))))) AS foo; to make it less cluttered. > +drop table t1; > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2011-11-18 20:19:20 +0000 > @@ -5670,6 +5670,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2011-11-18 20:19:20 +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2011-11-18 20:19:20 +0000 > @@ -5669,6 +5669,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-18 20:19:20 +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/t/subselect.test 2011-11-18 20:19:20 +0000 > @@ -4768,4 +4768,14 @@ SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > > +--echo # > +--echo # LP BUG#833777 Performance regression with deeply nested subqueries > +--echo # > + > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +drop table t1; > + > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2011-11-15 21:03:00 +0000 > +++ b/sql/sql_select.cc 2011-11-18 20:19:20 +0000 > @@ -14782,9 +14782,15 @@ do_select(JOIN *join,List<Item> *fields, > { > /* > HAVING will be checked after processing aggregate functions, > - But WHERE should checkd here (we alredy have read tables) > + But WHERE should checkd here (we alredy have read tables). > + If there is join->exec_const_cond, and all tables are constant, then it > + is equivalent to join->conds. exec_const_cond is already checked in the > + beginning of JOIN::exec. If it is false, JOIN::exec returns zero > + result already there, therefore execution reaches this point only if > + exec_const_cond is TRUE. Since it is equvalent to join->conds, then > + join->conds is also TRUE. > */ > - if (!join->conds || join->conds->val_int()) > + if (!join->conds || join->exec_const_cond || join->conds->val_int()) > { > error= (*end_select)(join, 0, 0); > if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] feedback plugin and 5.1 debs
by Kristian Nielsen 16 Nov '11

16 Nov '11

Hi Serg, If you remember, in Athens, we discovered that the feedback plugin is missing from 5.1 .debs. I fixed and pushed this (in the ourdelta scripts). I restarted latest 5.1 buildbot for this, and it seems to work. So just a FYI ... - Kristian.

3 3

Re: [Maria-developers] [Commits] Rev 3276: Fix MySQL BUG#12329653 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 12 Nov '11

12 Nov '11

Hi! 12 нояб. 2011, в 10:24, <timour(a)askmonty.org> написал(а): The patch is OK to push after one small change: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3276 > revision-id: timour(a)askmonty.org-20111112082415-7t6jjzdfg35la7aq > parent: igor(a)askmonty.org-20111111225326-k11e0durn6kvivly > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Sat 2011-11-12 10:24:15 +0200 > message: > Fix MySQL BUG#12329653 > > In MariaDB, when running in ONLY_FULL_GROUP_BY mode, > the server produced in incorrect error message that there > is an aggregate function without GROUP BY, for artificially > created MIN/MAX functions during subquery MIN/MAX optimization. > > The fix introduces a way to distinguish between artifially > created MIN/MAX functions as a result of a rewrite, and normal > ones present in the query. The test for ONLY_FULL_GROUP_BY violation > now tests in addition if a MIN/MAX function was part of a MIN/MAX > subquery rewrite. > > In order to be able to distinguish these MIN/MAX functions, the > patch introduces an additional flag in Item_in_subselect::in_strategy - > SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its > final choice of a subuqery strategy. In order to make the choice > consistent, access to Item_in_subselect::in_strategy is provided > via new class methods. > ****** > Fix MySQL BUG#12329653 > > In MariaDB, when running in ONLY_FULL_GROUP_BY mode, > the server produced in incorrect error message that there > is an aggregate function without GROUP BY, for artificially > created MIN/MAX functions during subquery MIN/MAX optimization. > > The fix introduces a way to distinguish between artifially > created MIN/MAX functions as a result of a rewrite, and normal > ones present in the query. The test for ONLY_FULL_GROUP_BY violation > now tests in addition if a MIN/MAX function was part of a MIN/MAX > subquery rewrite. > > In order to be able to distinguish these MIN/MAX functions, the > patch introduces an additional flag in Item_in_subselect::in_strategy - > SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its > final choice of a subuqery strategy. In order to make the choice > consistent, access to Item_in_subselect::in_strategy is provided > via new class methods. > [skip] > @@ -2606,9 +2606,8 @@ bool > Item_allany_subselect::select_transformer(JOIN *join) > { > DBUG_ENTER("Item_allany_subselect::select_transformer"); > - DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE | > - SUBS_IN_TO_EXISTS)) == 0); > - in_strategy|= SUBS_IN_TO_EXISTS; > +// DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE | > +// SUBS_IN_TO_EXISTS)) == 0); > if (upper_item) > upper_item->show= 1; > DBUG_RETURN(select_in_like_transformer(join)); > @@ -2617,7 +2616,7 @@ Item_allany_subselect::select_transforme Remove the comment, but better change it to: DBUG_ASSERT((in_strategy & ~(SUBS_MAXMIN_INJECTED | SUBS_MAXMIN_ENGINE | SUBS_IN_TO_EXISTS | SUBS_STRATEGY_CHOSEN)) == 0); [skip]

1 0

[Maria-developers] Shared plugin library build question
by Steve Ellcey 12 Nov '11

12 Nov '11

I am looking at a difference in the HP-UX vs. Linux mariadb build and was wondering if someone could help me understand why something is set up the way it is. The problem I see is that on HP-UX, when I do a mariadb build I do not get shared plugin libraries like ha_federated. Looking into why this was happening I found that when the plugin libraries like ha_federated are built they have a dependency on libsqlservices and libsqlservices is built only as an archive library. HP-UX doesn't support building shared libaries that depend on archive libraries and so the build of the shared plugin libraries fails when no shared version of libsqlservices is found. So my main question is: why is libsqlservices built only as an archive library? The other question is: if libsqlservices has to be an archive only library, is there is another way to link it in to executables, such as linking it in explicitly when building an executable instead of counting on the shared plugin libraries to bring it in indirectly by making it a dependent library? Steve Ellcey sje(a)cup.hp.com

3 29

Re: [Maria-developers] Shared plugin library build question
by Sergei Golubchik 09 Nov '11

09 Nov '11

Hi, Michael! On Nov 09, Michael Widenius wrote: > > Hi! > > >>>>> "Vladislav" == Vladislav Vaintroub <wlad(a)montyprogram.com> writes: > > <cut> > > > I think the best option for MySQL, and for MariaDB is to rename own base64.h and function prototypes in a way that it does not conflict with system headers on any system it is meant to be run on. Relying on the include order is fragile, I also do not see how it would not solve the problem with same function name different prototypes - how can we always reliably force use our base64_encode rather than system one ? > > Yes, we should rename it to my_base64.h in MySQL 5.5 > > Serg, can you do that for your next push to 5.5? Yes, but this is just one of the problems that was mentioned in this thread. I'll wait till the list is complete and 5.5 compiles. Regards, Sergei

1 0