- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 3770: MDEV-4042 & MDEV-4536 fix. in file:///home/bell/maria/bzr/work-maria-5.5-MDEV-4536/
by Sergei Petrunia 24 May '13

24 May '13

Hi Sanja, The patch doesn't take one thing into account. Consider a query: explain select * from t12 where b>3 or exists (select max(t10.b) Z from t10 where t10.a in ( select t11.a from t11) group by t10.c having Z<100); +--+------------+-----------+------+-------------+------------+-------+----+----+-------------------------------+ |id|select_type |table |type |possible_keys|key |key_len|ref |rows|Extra | +--+------------+-----------+------+-------------+------------+-------+----+----+-------------------------------+ |1 |PRIMARY |t12 |ALL |NULL |NULL |NULL |NULL|10 | | |2 |SUBQUERY |t10 |ALL |NULL |NULL |NULL |NULL|6 |Using temporary; Using filesort| |2 |SUBQUERY |<subquery3>|eq_ref|distinct_key |distinct_key|4 |func|1 | | |3 |MATERIALIZED|t11 |ALL |NULL |NULL |NULL |NULL|6 | | +--+------------+-----------+------+-------------+------------+-------+----+----+-------------------------------+ Here, the EXISTS subquery is the one that will have the problem. I have added "in ( select t11.a from t11)" so that EXISTS subquery's join_tab array is not linear. The problem is not visible to the user, but it can be seen in debugger. Put a breakpoint in JOIN::cleanup(), with these commands: p table->alias->Ptr p table->file Run the query and you'll see: Breakpoint 5, st_join_table::cleanup (this=0x7fff280dbea0) at /home/psergey/dev2/5.5/sql/sql_select.cc:10248 $30 = 0x7fff28000af0 "t12" $31 = (ha_innobase *) 0x7fff28047198 ... Breakpoint 5, st_join_table::cleanup (this=0x7fff28053078) at /home/psergey/dev2/5.5/sql/sql_select.cc:10248 $32 = 0xd05418 "" $33 = (ha_heap *) 0x7fff2805ab98 ... Breakpoint 5, st_join_table::cleanup (this=0x7fff28051ef8) at /home/psergey/dev2/5.5/sql/sql_select.cc:10248 $36 = 0x7fff28025720 "t10" $37 = (ha_innobase *) 0x7fff28009558 ... Breakpoint 5, st_join_table::cleanup (this=0x7fff28052218) at /home/psergey/dev2/5.5/sql/sql_select.cc:10248 $38 = 0xd30a69 "sj-materialize" $39 = (ha_heap *) 0x7fff28054398 ... Note that in subquery join_tab->cleanup() was called for t10, and for sj-materialize. For t11 (which is inside the sj-materialize) it was not called. This caused by this loop being flat loop: > @@ -10646,6 +10646,12 @@ void JOIN::cleanup(bool full) > { > tab->cleanup(); > } > + if (join_tab != table_access_tabs) > + { > + for (uint i= 0; i < top_table_access_tabs_count; i++)a ^^^^^^^^^^^ here. > + table_access_tabs[i].cleanup(); > + table_access_tabs= join_tab; // avoid second cleanup() > + } > cleaned= true; > } > else It turns out, I was incorrect with the statement that "table_access_tabs array is always flat". On Tue, May 21, 2013 at 08:30:35AM +0300, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.5-MDEV-4536/ > > ------------------------------------------------------------ > revno: 3770 > revision-id: sanja(a)montyprogram.com-20130521053031-xe34093yhvey0l5o > parent: monty(a)askmonty.org-20130515132812-pnjhza9iugz89356 > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.5-MDEV-4536 > timestamp: Tue 2013-05-21 08:30:31 +0300 > message: > MDEV-4042 & MDEV-4536 fix. > > Optimization JOIN tabs also should be cleaned up (in case of EXPLAIN when we cleaning up everythibng at the end of execution). > === added file 'mysql-test/r/innodb_explain.result' > --- a/mysql-test/r/innodb_explain.result 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/r/innodb_explain.result 2013-05-21 05:30:31 +0000 > @@ -0,0 +1,34 @@ > +drop table if exists t1,t2,t3; > +# > +# MDEV-4536: sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): > +# Assertion `table->key_read == 0' failed. > +# > +set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off"; > +CREATE TABLE t1 (pk int auto_increment primary key, `col_int_key` int(11), > +key `col_int_key` (`col_int_key`),`col_varchar_key` varchar(128), key > +(col_varchar_key)) engine=innodb; > +EXPLAIN SELECT 1 FROM t1 AS alias1 WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) ) GROUP BY SQ2_field1 HAVING SQ2_alias1 . col_int_key >= 7 ); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY SQ2_alias2 index NULL col_int_key 5 NULL 1 Using index; Using temporary; Using filesort > +2 SUBQUERY SQ2_alias1 ref col_int_key col_int_key 5 test.SQ2_alias2.col_int_key 1 Using where; Using index > +set optimizer_switch=default; > +drop table t1; > +# > +# MDEV-4042: Assertion `table->key_read == 0' fails in > +# close_thread_table on EXPLAIN with GROUP BY and HAVING in > +# EXISTS SQ, InnoDB > +# > +CREATE TABLE t1 (a INT) ENGINE=InnoDB; > +CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; > +CREATE TABLE t3 (d INT) ENGINE=InnoDB; > +EXPLAIN > +SELECT * FROM t1 > +WHERE EXISTS ( SELECT b FROM t2, t3 > +GROUP BY b > +HAVING b != 3 ); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using index; Using temporary; Using filesort > +2 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) > +drop table t1,t2,t3; > > === added file 'mysql-test/t/innodb_explain.test' > --- a/mysql-test/t/innodb_explain.test 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/t/innodb_explain.test 2013-05-21 05:30:31 +0000 > @@ -0,0 +1,41 @@ > +# > +# Innodb specific explains > + > +--disable_warnings > +drop table if exists t1,t2,t3; > +--enable_warnings > + > +--source include/have_innodb.inc > + > +--echo # > +--echo # MDEV-4536: sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): > +--echo # Assertion `table->key_read == 0' failed. > +--echo # > +set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off"; > + > +CREATE TABLE t1 (pk int auto_increment primary key, `col_int_key` int(11), > +key `col_int_key` (`col_int_key`),`col_varchar_key` varchar(128), key > +(col_varchar_key)) engine=innodb; > + > +EXPLAIN SELECT 1 FROM t1 AS alias1 WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) ) GROUP BY SQ2_field1 HAVING SQ2_alias1 . col_int_key >= 7 ); > + > + > +set optimizer_switch=default; > +drop table t1; > + > +--echo # > +--echo # MDEV-4042: Assertion `table->key_read == 0' fails in > +--echo # close_thread_table on EXPLAIN with GROUP BY and HAVING in > +--echo # EXISTS SQ, InnoDB > +--echo # > +CREATE TABLE t1 (a INT) ENGINE=InnoDB; > +CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; > +CREATE TABLE t3 (d INT) ENGINE=InnoDB; > + > +EXPLAIN > +SELECT * FROM t1 > +WHERE EXISTS ( SELECT b FROM t2, t3 > +GROUP BY b > +HAVING b != 3 ); > + > +drop table t1,t2,t3; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2013-05-07 11:05:09 +0000 > +++ b/sql/sql_select.cc 2013-05-21 05:30:31 +0000 > @@ -3680,8 +3680,8 @@ make_join_statistics(JOIN *join, List<TA > if (pull_out_semijoin_tables(join)) > DBUG_RETURN(TRUE); > > - join->join_tab=stat; > - join->top_join_tab_count= table_count; > + join->table_access_tabs= join->join_tab=stat; > + join->top_table_access_tabs_count= join->top_join_tab_count= table_count; > join->map2table=stat_ref; > join->table= table_vector; > join->const_tables=const_count; > @@ -10646,6 +10646,12 @@ void JOIN::cleanup(bool full) > { > tab->cleanup(); > } > + if (join_tab != table_access_tabs) > + { > + for (uint i= 0; i < top_table_access_tabs_count; i++) > + table_access_tabs[i].cleanup(); > + table_access_tabs= join_tab; // avoid second cleanup() > + } > cleaned= true; > } > else > > _______________________________________________ > 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

Re: [Maria-developers] Fwd: [Commits] Rev 3549: Fix bug MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1 in file:///home/tsk/mprog/src/10.0-md83/
by Sergei Petrunia 24 May '13

24 May '13

Hi Timour, Ok to push. On Thu, May 23, 2013 at 11:19:36PM +0300, Timour Katchaounov wrote: > Sergey, > > Please approve the following patch we already discussed. > > Timour > > > ------------------------------------------------------------ > revno: 3549 > revision-id: timour(a)askmonty.org-20130523201638-4vj8cx0yjuww29nt > parent: timour(a)askmonty.org-20130520190622-rdrru0c2gbdgtjy5 > fixes bug: https://mariadb.atlassian.net/browse/MDEV-4407 > committer: timour(a)askmonty.org > branch nick: 10.0-md83 > timestamp: Thu 2013-05-23 23:16:38 +0300 > message: > Fix bug MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1 > (Part of MDEV-83) > > Analysis: > MDEV-83 takes into account the cost of subqueries, which results > in a materialized semi-join where the materialized table is on > the right side of the join. At the same time, the WHERE clause > has an EXISTS predicate which is moved to the second table in > the join because of smaller join cardinality. > > The semi-join startup function setup_sj_materialization_part2 > didn't take into account that in addition to the injected > IN-EXISTS conditions, there may be other conditions, such as > ones moved by pushdown of subquery predicates in mdev-83. So > setup_sj_materialization_part2 just set the condition of the > semi-join to be the IN-EXISTS equality, this removing the > subquery moved by the subquery pushdown logic. > > Solution: > And the conditions instead of just setting them. > > > > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2013-05-20 19:06:22 +0000 > +++ b/mysql-test/r/subselect4.result 2013-05-23 20:16:38 +0000 > @@ -2393,5 +2393,31 @@ COUNT(b) > 1 > 2 > drop table t1, t2, t3; > +# > +# MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1 > +# > +SET optimizer_switch='expensive_pred_static_pushdown=on'; > +SET optimizer_use_condition_selectivity=3; > +SET use_stat_tables=PREFERABLY; > +CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0); > +ANALYZE TABLE t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +FLUSH TABLES; > +EXPLAIN > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY outer_t1 ALL b NULL NULL NULL 4 > +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where > +2 MATERIALIZED t1 ALL b NULL NULL NULL 4 Using where > +3 DEPENDENT SUBQUERY t1 index b b 5 NULL 4 Using where; Using index > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +a b > +2 2 > +drop table t1; > +SET optimizer_switch=@@global.optimizer_switch; > +SET optimizer_use_condition_selectivity=default; > +SET use_stat_tables=default; > SET optimizer_switch= @@global.optimizer_switch; > set @@tmp_table_size= @@global.tmp_table_size; > > === modified file 'mysql-test/t/subselect4.test' > --- a/mysql-test/t/subselect4.test 2013-05-20 19:06:22 +0000 > +++ b/mysql-test/t/subselect4.test 2013-05-23 20:16:38 +0000 > @@ -1917,5 +1917,29 @@ GROUP BY a; > > drop table t1, t2, t3; > > +--echo # > +--echo # MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1 > +--echo # > + > +SET optimizer_switch='expensive_pred_static_pushdown=on'; > +SET optimizer_use_condition_selectivity=3; > +SET use_stat_tables=PREFERABLY; > + > +CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0); > +ANALYZE TABLE t1; > +FLUSH TABLES; > + > +EXPLAIN > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > + > +drop table t1; > + > +SET optimizer_switch=@@global.optimizer_switch; > +SET optimizer_use_condition_selectivity=default; > +SET use_stat_tables=default; > + > + > SET optimizer_switch= @@global.optimizer_switch; > set @@tmp_table_size= @@global.tmp_table_size; > > === modified file 'sql/opt_subselect.cc' > --- a/sql/opt_subselect.cc 2013-04-01 10:36:05 +0000 > +++ b/sql/opt_subselect.cc 2013-05-23 20:16:38 +0000 > @@ -3620,7 +3620,10 @@ bool setup_sj_materialization_part2(JOIN > emb_sj_nest->sj_subq_pred))) > DBUG_RETURN(TRUE); /* purecov: inspected */ > sjm_tab->type= JT_EQ_REF; > - sjm_tab->select_cond= sjm->in_equality; > + remove_sj_conds(&sjm_tab->select_cond); > + sjm_tab->select_cond= and_items(sjm_tab->select_cond, sjm->in_equality); > + if (!sjm_tab->select_cond->fixed) > + sjm_tab->select_cond->fix_fields(thd, &sjm_tab->select_cond); > } > else > { > > > _______________________________________________ > 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

Re: [Maria-developers] [Commits] Rev 3769: MDEV-4520 fix
by Sergei Golubchik 23 May '13

23 May '13

Hi, Oleksandr! On May 23, Oleksandr Byelkin wrote: > 22.05.2013 20:27, Sergei Golubchik пишет: > > On May 22, sanja(a)montyprogram.com wrote: > >> ------------------------------------------------------------ > >> revno: 3769 > >> revision-id: sanja(a)montyprogram.com-20130522125526-aw778iepj1yceskd > >> parent: holyfoot(a)askmonty.org-20130514213637-3uvcda98gemgiquf > >> committer: sanja(a)montyprogram.com > >> branch nick: work-maria-5.5-MDEV-4520 > >> timestamp: Wed 2013-05-22 15:55:26 +0300 > >> message: > >> MDEV-4520 fix. > > First, please, fix the changeset comment. The first line should be > > MDEV-<number><space><complete issue summary> > > > > For example: > > > > MDEV-4520 Assertion `0' fails in Query_cache::end_of_result on concurrent drop event and event execution > > > > Then, after an empty line, you add whatever explanations you think are > > necessary. Like the one below: > The only question is how happened that I did not know standard of commit > comment (where that standard is/was described)? This is a de facto standard. It wasn't formally stated as a rule to follow, but everyone is doing it anyway, and it's really convenient to have changeset comments structured this way. But now I don't mind making it a written rule - because it practically won't change anything :) Where do you suggest it should be written? > >> If there is no net.vio then query cache cant't get data via > >> net_real_write() so it is better just do not try to cache such > >> query. > > Also, it'd be good to record the fixed bug in the changeset metadata. > > This can be done either with "bzr gcommit" and my patches to it (see the > > article in KB), or from the command line with "bzr commit --fixes". > > Again previous question... It's not at all a standard. It's nice to have, but very few are doing it. Feel free not to. > >> === modified file 'sql/sql_cache.cc' > >> --- a/sql/sql_cache.cc 2013-05-07 11:05:09 +0000 > >> +++ b/sql/sql_cache.cc 2013-05-22 12:55:26 +0000 > >> @@ -3977,7 +3977,8 @@ Query_cache::is_cacheable(THD *thd, LEX > >> if (thd->lex->safe_to_cache_query && > >> (thd->variables.query_cache_type == 1 || > >> (thd->variables.query_cache_type == 2 && (lex->select_lex.options & > >> - OPTION_TO_QUERY_CACHE)))) > >> + OPTION_TO_QUERY_CACHE))) && > >> + thd->net.vio) > >> { > >> DBUG_PRINT("qcache", ("options: %lx %lx type: %u", > >> (long) OPTION_TO_QUERY_CACHE, > > The fix itself looks ok. But please add a test case. > > > > And think if there can be more cases (besides events) where SELECT is > > executed with thd->net.vio == 0. E.g. init-file? or init-connect? > > Or INSERT (in the replication thread) that invokes a stored function or > > a trigger? Anything you can think of. > I spent more then half a day with this and here is result: > > - vio is definitely unset for replication and/or events (I have no idea > how that tests work, probably it will require several days to figure out) Right, that's why I suggested a test with events. And a test with a SELECT in the replication thread. > - one bug found in mysql-test-run.pl and options files (MDEV-4567) (the > bug really slows down any investigation about any test with options). Sanja, if you want your submitted bug to have any chance of ever being fixed, please at least specify the affected version and the fix version. > - putting SELECT statement in --init_connect does not work. What does it mean? How exactly it "does not work" ? > I was trying to catch it with putting assertion in my_net_write. there > is no great success so far. > > Should I continue or left it as is taking into account that bug was > reported against our standard test suite? But that's the point - it's not against our standard test suite. It's a test from the test suite run with the non-default command line options. Nobody runs the test suite this way, so if this will ever be broken again, it won't be noticed, no test will fail. That's why we need a test for this bug. Regards, Sergei

1 0

[Maria-developers] Proposed changes to GTID syntax
by Kristian Nielsen 23 May '13

23 May '13

I am planning some changes to the user interface of Global Transaction ID. Testing by Elena has shown usability issues that I want to address with these changes, but I wanted to give everyone a change to voice their opinion on the changes. The problem: The issue concerns when a slave server initially connects to the master using GTID. The slave needs to send to the master the GTID position it wants to start replicating from (the GTID of the last transaction executed within each replication domain). If the connecting server was already a slave to another master before, then the position is the last replicated event(s), stored in table mysql.rpl_slave_state. But if we server is the prior master that is now to become a slave, we need to connect at the position of the last event(s) logged by the prior master in the binlog. The problem occurs when users do manual updates on the slave and those updates get into the binlog of the slave. Of course, in principle one should not do that, as it creates differences between the master and the slave. But it is a very common thing to do among users no doubt. Maybe they need to fix some replication problem, maybe add an account only on the slave, whatever, and they forget to set @@sql_log_bin=0 while doing the change, so it gets into the slave binlog. With current code, if the slave connects to a new master right after doing the manual changes to the binlog on the slave, it will connect at the GTID position of those changes, not at the last replicated transaction. Because doing changes directly on the slave server in effect makes that server temporarily a "master". And besides, this is user error anyway, creating divergence betheen the binlogs on slave and master server. But I agree with Elena that this will be a nasty surprise to many users trying out GTID for the first time. It is inconsistent with old-style replication, where direct updates on the slave never change the slave replication position. And many users will not read the complete documentation on GTID before trying to use it, and will not understand the issues with manual changes on a slave in effect turning it into a master temporarily. Note that this affects usability when users abuse the replication, using it against the recommended way, not taking care to keep master and slaves in sync. For disciplined usage, eg. running with the "strict GTID" mode that we plan to implement, these kind of problems will never occur in the first place. But I want to do as much as possible to also make things work well for "undisciplined" users. The proposed solution: There will be two ways to configure a slave to use GTID. One will be for "disciplined" usage, same as the current code, where slave connects with whatever is most recent in mysql.rpl_slave_state or binlog. The other will be for "sloppy" usage, where the user will need to explicitly state whether to use the position from the last replicated transaction, or to use what is in the binlog. I hope this will solve the problem. Since new users need to learn new syntax to enable GTID anyway (CHANGE MASTER ... master_use_gtid=...), they will at least have made a conscious choice about whether to use one syntax or the other. And for "disciplined" users there is not really any change, they will just use the syntax for the current behaviour. I also think this improves things on the conceptual level. Currently, there is too much magic about the value of the variable @@GLOBAL.gtid_pos. You can set this manually (and that updates mysql.rpl_slave_state), but it also changes value whenever something is added to the binlog, and you cannot manually set it to something that conflicts with the binlog. This magic all becomes unnecessary with the proposed changes. So there will be some changes to the syntax for CHANGE MASTER, and some stuff will be renamed to make things clearer. Details: CHANGE MASTER syntax will be modified to this: 1. CHANGE MASTER TO master_use_gtid = current_gtid_pos This is the "disciplined" mode, the same as master_use_gtid=1 in current code. It will use as replication start position whatever is most recent, last replicated GTID or last binlogged GTID. 2. CHANGE MASTER TO master_use_gtid = slave_gtid_pos This is "sloppy" mode, for pointing an existing slave to a new master. It will only look at the last replicated GTID for the start position, not at any transactions logged directly to the slave binlog. 3. CHANGE MASTER TO master_use_gtid = off For completeness, to turn of using GTID to connect, go back to using old-style replication. There will be three new system variables introduced, replacing the current @@GLOBAL.gtid_pos: 1. @@GLOBAL.slave_gtid_pos. Read-write. This is the set of last replicated GTID, per replication domain. 2. @@GLOBAL.binlog_gtid_pos. Read only. This is the set of last binlogged GTID, per domain. 3. @@GLOBAL.current_gtid_pos. Read only. This is a combination of @@GLOBAL.slave_gtid_pos and @@GLOBAL.binlog_gtid_pos, per-domain it holds the most recent GTID either replicated or binlogged. The current mysql.rpl_slave_state table will be renamed to mysql.slave_gtid_pos. Note how the naming is now more consistent. slave_gtid_pos means the most recently replicated GTIDs, both for table, variable, and CHANGE MASTER syntax. Similarly, current_gtid_pos is used for both the variable and CHANGE MASTER to mean the most recent GTID either replicated or binlogged. The user can see the GTID position the slave will start replicating at by inspecting the value of the corresponding variable. When using master_use_gtid=slave_gtid_pos, the following statement can be used on a server that used to be the master but should now become a slave: SET GLOBAL slave_gtid_pos = @@binlog_gtid_pos; This will load whatever was logged to the binlog into the slave position. When using master_use_gtid=current_gtid_position, there is no need to do anything special, the binlog will have the most recent GTID on a prior master so it will be used automatically. I think these changes will be a good improvement, justifying doing the change this late during 10.0 release state. But I very much welcome input and suggestions for doing things differently. - Kristian.

1 2

[Maria-developers] Ubuntu Hardy and Oneiric EOL
by Daniel Bartholomew 23 May '13

23 May '13

FYI: As of the end of April, Ubuntu 8.04 "Hardy" and 11.10 "Oneiric" have reached EOL and are no longer supported by Canonical. Sometime in the next week or so I plan to turn off these builders in buildbot. The VMs will be archived, in case we need them, but no regular builds will be made. This includes Debian packages for Hardy and Oneiric and the binary tarballs which are built on Hardy (binary tarballs are also built on Ubuntu Precise, and they are not going away). The MariaDB 5.5.31 release will be the last MariaDB release to feature packages for these two distros. The repos for them will remain online until the 5.5.32 release. At that time they will go away. This also affects the Mint 9 and Mint 12 as they use the hardy and oneiric repos, respectively. Let me know if you have any questions or concerns. Thanks! -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] MariaDB 5.5.31 Release Prep
by Daniel Bartholomew 22 May '13

22 May '13

All, I've uploaded almost all of the packages for the release (working on a fix for an error with the sol-10 package) and the mirrors will be picking them up soon. Draft Changelog and Release Notes are here: - https://kb.askmonty.org/en/mariadb-5531-release-notes/ - https://kb.askmonty.org/en/mariadb-5531-changelog/ One of the big items in this release is TokuDB. We didn't have a proper category for TokuDB yet, so I created a stub TokuDB category with some links to the Tokutek website: - https://kb.askmonty.org/en/tokudb/ If there is anything I should be sure to mention in the release notes, please let me know ASAP. My current plan is to activate the release tomorrow, 23 May 2013. Thanks! -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org

3 4

Re: [Maria-developers] [Commits] Rev 3769: MDEV-4520 fix
by Sergei Golubchik 22 May '13

22 May '13

Hi, Sanja! On May 22, sanja(a)montyprogram.com wrote: > ------------------------------------------------------------ > revno: 3769 > revision-id: sanja(a)montyprogram.com-20130522125526-aw778iepj1yceskd > parent: holyfoot(a)askmonty.org-20130514213637-3uvcda98gemgiquf > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.5-MDEV-4520 > timestamp: Wed 2013-05-22 15:55:26 +0300 > message: > MDEV-4520 fix. First, please, fix the changeset comment. The first line should be MDEV-<number><space><complete issue summary> For example: MDEV-4520 Assertion `0' fails in Query_cache::end_of_result on concurrent drop event and event execution Then, after an empty line, you add whatever explanations you think are necessary. Like the one below: > If there is no net.vio then query cache cant't get data via > net_real_write() so it is better just do not try to cache such > query. Also, it'd be good to record the fixed bug in the changeset metadata. This can be done either with "bzr gcommit" and my patches to it (see the article in KB), or from the command line with "bzr commit --fixes". > === modified file 'sql/sql_cache.cc' > --- a/sql/sql_cache.cc 2013-05-07 11:05:09 +0000 > +++ b/sql/sql_cache.cc 2013-05-22 12:55:26 +0000 > @@ -3977,7 +3977,8 @@ Query_cache::is_cacheable(THD *thd, LEX > if (thd->lex->safe_to_cache_query && > (thd->variables.query_cache_type == 1 || > (thd->variables.query_cache_type == 2 && (lex->select_lex.options & > - OPTION_TO_QUERY_CACHE)))) > + OPTION_TO_QUERY_CACHE))) && > + thd->net.vio) > { > DBUG_PRINT("qcache", ("options: %lx %lx type: %u", > (long) OPTION_TO_QUERY_CACHE, The fix itself looks ok. But please add a test case. And think if there can be more cases (besides events) where SELECT is executed with thd->net.vio == 0. E.g. init-file? or init-connect? Or INSERT (in the replication thread) that invokes a stored function or a trigger? Anything you can think of. Regards, Sergei

1 0

[Maria-developers] History of table changes
by Roberto Spadim 22 May '13

22 May '13

Hi guys, i was looking git and i don't know if there's something similar to mariadb alter table command could we develop something like git for create database,alter tables, create table, drop table, drop database? a log that could be useful to update databases table definition and read changes done and 'commits' done by dba or developer or a tool to read my table and read remote table and show the create command to make they in sync (ok here history is lost) thanks guys -- Roberto Spadim

1 0

[Maria-developers] unexpected "Duplicate entry" error
by Kazuhiko Shiozaki 21 May '13

21 May '13

Hello, We sometimes (not always, not often but sometimes) have unexpected "Duplicate entry" error on the following table and with the following query : * table CREATE TABLE `category` ( `uid` bigint(20) unsigned NOT NULL, `category_uid` bigint(20) unsigned NOT NULL DEFAULT '0', `base_category_uid` bigint(20) unsigned NOT NULL DEFAULT '0', `category_strict_membership` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`uid`,`category_uid`,`base_category_uid`,`category_strict_membership`), KEY `Membership` (`category_uid`,`base_category_uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci * query BEGIN; (some queries for other tables) DELETE FROM category WHERE uid=14026268; INSERT INTO category VALUES (14026268, 3131502, 2074, 1),(14026268, 2992, 2992, 0),(14026268, 3133077, 191, 1),(14026268, 1212264, 2992, 1); (some queries for other tables) COMMIT; * error Duplicate entry '14026268-3131502-2074-1' for key 'PRIMARY' But this duplicate entry error should not happen because DELETE is called in the same transaction, even if similar queries are sent from another connection at the same time. We confirmed that this kind of issue happens on the following environments using MySQL_python-1.2.3 : * MariaDB 5.5.25, XtraDB * MariaDB 5.5.29, XtraDB * MariaDB 5.5.29, InnoDB plugin Since it happens SOMETIMES, I cannot reproduce this issue by small query. Do you have any idea so that we can investigate this issue further ? Thanks in advance ! -- Kazuhiko Shiozaki Nexedi: Consulting and Development of Free / Open Source Software http://www.nexedi.com/ ERP5: Full Featured High End Open Source ERP http://www.erp5.com/

4 12

Re: [Maria-developers] MariaDB and MySQL replication
by Kristian Nielsen 21 May '13

21 May '13

Jan Lindström <jplindst(a)mariadb.org> writes: > My question is how compatible replication between > > 1) MySQL (master) -> MariaDB (slave) > 2) MariaDB (master) -> MySQL (slave) In general, it should work, though there are some limitations. It also depends on versions. I always try to make MariaDB (master) -> MySQL (slave) work. For example, when new binlog events are introduced to MariaDB, the MariaDB master will not send them to slaves that do not understand the new events (whether MySQL slave, or older version of MariaDB), it will replace them with something compatible. This is not the best tested part of the code though, so there may be bugs. MySQL (master) -> MariaDB (slave) generally has limitations. This is the same as MySQL (high version master) -> MySQL (low version master). This is due to the MySQL replication teams approach, where you need to disable new features on the master for things to work with old slaves. For example, MySQL 5.6 (master) -> MySQL 5.5 (slave) does not work out-of-the box, so MariaDB 5.5 slave is the same. Generally, when incompatibilities are introduced in new MySQL versions used as a master, we will backport to MariaDB at least to the extent that new MariaDB versions will be able to replicate as slaves from a MySQL master without needing any special configuration. Though it depends on time available and priority against other work, for example there are known incompatibilities that have not yet been fixed in 10.0 alpha but will hopefully be fixed by 10.0 GA. Officially, replicating from new master to old slave is not supported (you should upgrade slaves first). This applies mostly the same whether MySQL or MariaDB or both are involved. Unofficially, as explained above, we do try to make it work as well as reasonably possible on the MariaDB side. > Before you say they are fully compatible, I point out at least > following types at mysql_com.h that do not exist on MariaDB mysql_com.h: > > MYSQL_TYPE_TIMESTAMP2, > MYSQL_TYPE_DATETIME2, > MYSQL_TYPE_TIME2, I do not know the details of this particular point (also you did not mention any version info). Best is probably to test it and see, and then report your findings and if needed we can file a bug for fixing things. - Kristian.

2 1