March 2012 - developers - lists.mariadb.org

[Maria-developers] MariaDB 5.1.61 and 5.2.11 releases
by Daniel Bartholomew 31 Mar '12

31 Mar '12

All, I've uploaded the packages for MariaDB 5.1.61 and 5.2.11 to the primary mirror. I've also created initial Release Notes and Changelog pages for the releases. MariaDB 5.1.61: * http://kb.askmonty.org/en/mariadb-5161-release-notes * http://kb.askmonty.org/en/mariadb-5161-changelog MariaDB 5.2.11: * http://kb.askmonty.org/en/mariadb-5211-release-notes * http://kb.askmonty.org/en/mariadb-5211-changelog Please let me know ASAP about anything which should be mentioned in the release notes. My current plan is to activate the releases on Monday morning (US Eastern time). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] Analysing MariaDB 5.5 sysbench performance regression
by Kristian Nielsen 31 Mar '12

31 Mar '12

I have been analysing the performance difference between MariaDB 5.5.21 and MySQL 5.5.21 using oltp.lua in sysbench 0.5. It turns out that MySQL has about 8% higher tps in this benchmark when running CPU bound. (See below for links to blog posts that started this). I took the base sysbench run and tweaked it to get stable numbers - basically put the datadir in ramdisk and adjusted my.cnf a bit (more details below). Then I used OProfile to analyse where the difference comes from. Oprofile is a really great tool for this kind of analysis, where CPU time spent is the measurement. It is very easy to use, and it gives detailed and accurate measurements on how much time is spent in each function. It is then easy to compare this against MySQL and find where we have performance regressions. Then further details is available by adding call graph to the reports, which shows from where the expensive parts are called; and by using the annotation facility which annotates every function and puts execution times on the individual statement and assembler functions. Here is an overview of where the extra time is spent. Time is in units of samples, which is 1/100000 of a clock cycle. Total was around 4.15M samples. Here are the top functions I found used more time in MariaDB, and the amount of extra samples spent in MariaDB: 39k mysql_select 20k alloc_root 16k JOIN::choose_subquery_plan 10k thd_ha_data 10k make_sortkey() 9k get_best_combination 9k handler::read_range_next 7k handler::ha_thd 7k update_ref_and_keys 7k Item::cmp_type 6k Item::operator delete 5k ha_maria::implicit_commit 4k end_write 4k sort_and_filter_keyuse 4k my_interval_timer 4k st_select_lex::master_unit 4k st_select_lex::save_leaf_tables 4k sel_arg_range_seq_next 4k thd_test_options 4k Item_func::walk 4k TABLE::init 4k sub_select 3k strnmov 3k ut_free 3k vio_write 3k ut_malloc_low 2k setup_tables 2k create_ref_for_key Some care is needed to interpret these data. GCC -O3 does heavy inlining. Oprofile is a statistical profiler, so numbers are somewhat uncertain, more so for smaller numbers. And modern CPUs are superscalar, out-of-order, and hyper-threaded, which makes interpreting individual instruction costs higly challenging. But the list can be used to drill down into the actual code; opannotate --source --assembler is very enlightening, in my experience (few examples below). >From this list, it seems that - not surprising - the 5.3 optimiser features seem to have added a bit of overhead. Much of this is spread out over many places in the code, though it could be that the root cause comes from few inlined functions. I checked mysql_select(), and most of the overhead is from lp:941889, expensive constructors in JOIN, which was inlined by GCC. I believe Sergey plans to fix this. More analysis of the individual functions is needed to find the cause there. It seems we are also using alloc_root() somewhat more. thd_ha_data and maybe handler_thd are likely due to using dynamically loaded innodb_plugin rather than built-in. I would like to do more analysis (and would probably need help from optimiser-knowledgable people for most of it) - I think it is feasible to remove most of the overhead in a modest time frame. But my main point is to present these results, to show that we have a very useful tool to pin-point these performance issues, and to start discussion in more detail (and I have plenty more of those). ----------------------------------------------------------------------- More background: These benchmarks started from these blog posts: http://www.mysqlperformanceblog.com/2012/02/18/mariadb-5-3-4-benchmarks/ http://blog.montyprogram.com/benchmarking-mariadb-5-3-4/ The main difference in these tests seem to be caused by different XtraDB buffer pool flush algorithm. So Axel tried using innodb_pluing in MariaDB instead, and got much smaller differences - but a difference was still there. So for 5.5 I took Axel's benchmark, adjusted until I got stable, fully cpu-bound numbers, and analysed the results, as above. I have the actual scripts and my.cnf available if anyone is interested. Main point is datadir in ramdisk, reducing redo log size to 300MB total, and running for 30 seconds. As examples, I analysed a handful of cases, and came up with two simple patches for specific issues. One is update_virtual_fields(), which originally accounted for 0.5% of total time in sysbench. This seems to be called for every row, so just the push/pop of registers in function prologue became significant. I fixed this with below patch; it puts a NULL pointer into the table instead of an empty list of virtual fields, and guards a few frequent calls with an if (NULL) check. Another is my_hash_sort_simple(). This is not a regression, but Oprofile shows we spend 10% of total time in this function. opannotate of the assembler showed the problem. Below simple patch speeds it up by 33%, by reducing 4 memory loads per loop to 2. Still, it would be nice to understand why this is called that much - here is the call stack: my_hash_sort_simple() ha_heap::write_row(). evaluate_join_record() sub_select() do_select() JOIN::exec() mysql_select() It would be nice to understand why this is needed - this is an expensive function, it needs to lookup every character in the collation tables. If this could be eliminated, that alone would probably be enough to win over MySQL, even without fixing any of the regressions. Some other top contenders in the profile are innodb functions. For example buf_calc_page_new_checksum(). This is expensive, as it needs to loop over every byte in each buffer pool page (16K), computing a 7-long dependency chain for each byte. This could be much faster if the checksum was done on 8-byte longwords, but AFAIK, it's not trivial to change the checksum algorithm due to the migration issues. Facebook may have done some work on this. Another top contender is row_sel_store_mysql_rec() and similar - they show high costs in the opannotate on loads from the records, this is probably cache misses from accessing pages in the buffer pool - not too much to do about that I think. Hope this helps, - Kristian. ----------------------------------------------------------------------- Patch for my_hash_sort_simple(): === modified file 'strings/ctype-simple.c' --- strings/ctype-simple.c 2012-01-13 14:50:02 +0000 +++ strings/ctype-simple.c 2012-03-16 16:34:31 +0000 @@ -306,19 +306,24 @@ void my_hash_sort_simple(CHARSET_INFO *c { register const uchar *sort_order=cs->sort_order; const uchar *end; + ulong n1, n2; /* Remove end space. We have to do this to be able to compare 'A ' and 'A' as identical */ end= skip_trailing_space(key, len); - + + n1= *nr1; + n2= *nr2; for (; key < (uchar*) end ; key++) { - nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * - ((uint) sort_order[(uint) *key])) + (nr1[0] << 8); - nr2[0]+=3; + n1^=(ulong) ((((uint) n1 & 63)+n2) * + ((uint) sort_order[(uint) *key])) + (n1 << 8); + n2+=3; } + *nr1= n1; + *nr2= n2; } ----------------------------------------------------------------------- Patch for update_virtual_fields(): === modified file 'sql/filesort.cc' --- sql/filesort.cc 2012-03-11 22:45:18 +0000 +++ sql/filesort.cc 2012-03-16 19:17:58 +0000 @@ -589,7 +589,7 @@ static ha_rows find_all_keys(SORTPARAM * { if ((error= select->quick->get_next())) break; - if (!error) + if (!error && sort_form->vfield) update_virtual_fields(thd, sort_form); file->position(sort_form->record[0]); DBUG_EXECUTE_IF("debug_filesort", dbug_print_record(sort_form, TRUE);); @@ -608,7 +608,7 @@ static ha_rows find_all_keys(SORTPARAM * else { error=file->ha_rnd_next(sort_form->record[0]); - if (!error) + if (!error && sort_form->vfield) update_virtual_fields(thd, sort_form); if (!flag) { === modified file 'sql/records.cc' --- sql/records.cc 2012-01-13 14:50:02 +0000 +++ sql/records.cc 2012-03-16 19:15:51 +0000 @@ -344,7 +344,8 @@ static int rr_quick(READ_RECORD *info) break; } } - update_virtual_fields(info->thd, info->table); + if (info->table->vfield) + update_virtual_fields(info->thd, info->table); return tmp; } === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-03-11 22:45:18 +0000 +++ sql/sql_select.cc 2012-03-16 19:15:05 +0000 @@ -15824,7 +15824,8 @@ evaluate_join_record(JOIN *join, JOIN_TA DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ } - update_virtual_fields(join->thd, join_tab->table); + if (join_tab->table->vfield) + update_virtual_fields(join->thd, join_tab->table); if (select_cond) { === modified file 'sql/table.cc' --- sql/table.cc 2012-03-02 23:03:20 +0000 +++ sql/table.cc 2012-03-18 16:10:18 +0000 @@ -2466,30 +2466,35 @@ int open_table_from_share(THD *thd, TABL /* Process virtual columns, if any. */ - if (!(vfield_ptr = (Field **) alloc_root(&outparam->mem_root, - (uint) ((share->vfields+1)* - sizeof(Field*))))) - goto err; - - outparam->vfield= vfield_ptr; - - for (field_ptr= outparam->field; *field_ptr; field_ptr++) + if (!share->vfields) + outparam->vfield= NULL; + else { - if ((*field_ptr)->vcol_info) + if (!(vfield_ptr = (Field **) alloc_root(&outparam->mem_root, + (uint) ((share->vfields+1)* + sizeof(Field*))))) + goto err; + + outparam->vfield= vfield_ptr; + + for (field_ptr= outparam->field; *field_ptr; field_ptr++) { - if (unpack_vcol_info_from_frm(thd, - outparam, - *field_ptr, - &(*field_ptr)->vcol_info->expr_str, - &error_reported)) + if ((*field_ptr)->vcol_info) { - error= 4; // in case no error is reported - goto err; + if (unpack_vcol_info_from_frm(thd, + outparam, + *field_ptr, + &(*field_ptr)->vcol_info->expr_str, + &error_reported)) + { + error= 4; // in case no error is reported + goto err; + } + *(vfield_ptr++)= *field_ptr; } - *(vfield_ptr++)= *field_ptr; } + *vfield_ptr= 0; // End marker } - *vfield_ptr= 0; // End marker #ifdef WITH_PARTITION_STORAGE_ENGINE if (share->partition_info_str_len && outparam->file) -----------------------------------------------------------------------

4 5

[Maria-developers] MariaDB 5.5.22-rc release
by Daniel Bartholomew 29 Mar '12

29 Mar '12

All, The MariaDB 5.5.22-rc release has been uploaded to the primary mirror and will be replicated to the other mirrors over the next few hours. The release notes and changelog pages for this release are at: https://kb.askmonty.org/en/mariadb-5522-release-notes https://kb.askmonty.org/en/mariadb-5522-changelog As soon as the release files are mirrored I will activate the downloads page, update various banners and links, and then I or Colin will send out an announce email and promote the release in the usual manner. In the mean time, please let me know if there is anything which should be mentioned in the release notes that currently is not. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] WL#250 New (by Igor): Use the statistics from persistent statistical tables instead of the statistics provided by engine
by worklog-noreply＠askmonty.org 25 Mar '12

25 Mar '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Use the statistics from persistent statistical tables instead of the statistics provided by engine CREATION DATE..: Sun, 25 Mar 2012, 17:36 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Monty Psergey Sergei Timour CATEGORY.......: Server-Sprint TASK ID........: 250 (http://askmonty.org/worklog/?tid=250) VERSION........: Server-5.6 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This task adds the functionality that allows optionally to use use the statistics from persistent statistical tables (see MWL #248) instead the statistics provided by engine. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

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