- developers - lists.mariadb.org

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

Re: [Maria-developers] Getting community to write missing test cases
by Kristian Nielsen 21 May '13

21 May '13

Elena Stepanova <elenst(a)montyprogram.com> writes: > If we start doing it, Oracle will soon realize that this activity is > based at least as much on commit comments as on the code. As a result, > they will probably start making much vaguer comments than they do now, > which in turn might make merge and related activities even more > complicated than they are now. I wouldn't worry about that. First, I do not believe day-to-day developers spend any effort to actively conceal things for outside developers. The ban on bug test cases seems to have come from a generic security team mandate, which has roots some years back where Oracle were hit quite hard with countless vulnerabilities after their "can't break it - can't break in" campain. Second, even if they did, anything that makes Oracle close down more benefits the adoption of the forks. But most importantly, we must not get into the mentality that we let what Oracle might or might not do influence how we work. We need to go in whatever direction we believe is best for the project. Oracle has made it plain that they do not want to participate in the community, and we should just ignore them until such time as they might decide otherwise. - Kristian.

1 0

[Maria-developers] Feature Request - Working auto_increment on BlackHole Engine
by Gordan Bobic 20 May '13

20 May '13

How difficult would be be to implement working auto-increment functionality on the BlackHole engine? This would be really useful for what I'm trying to do at the moment. Since it is not available and there are no sequences in MariaDB and MySQL, I have to resort to a hack such as a single-int-field table doing the auto-increment, and an insert trigger on the blackhole table to insert a null into the auto-increment tracking table, get LAST_INSERT_ID() to set the value in the auto-increment table, and then delete the record. Even with InnoDB log files and tablespace on tmpfs this makes the performance nosedive by a factor of 4 if the trigger doesn't bother cleaning up the auto-increment table, and a factor of 13 if it deletes the record immediately after inserting it. The actual times I tested with 24 inserter threads on a 24-thread server are 8m vs. 30m vs. 105m, i.e. a massive drop. Is there an better place than this list for a feature request? Gordan

3 6

[Maria-developers] Ogg vorbis based table
by Roberto Spadim 18 May '13

18 May '13

Hi guys, i was listening some musics, and i had a idea... let's me explain Some systems are based in time and value, an example of scada systems. In this kind of system we have an 'channel' (like right or left sound channel) a timestamp and a value, normally a table like CREATE TABLE history( Channel int not null default 0, Datetimestamp decimal(22,7) not null default 0, Value decimal(22,5) default NULL, Primary key (channel,datetimestamp) ) Some explanations and features that scada systems use... Value can be NULL, in this case we could think like MUTE function on audio files Datetime normally are gmt, or unixtimestamp values with microsecond precision, they could be fixed interval or not, example... 1 2 3 4 5... (q second interval), in audio it's fixed sample rate, or not fixed interval (variable sample rate), like 1 3 4 9... Well what i'm thinking... today mariadb 10.0.2 started the CONNECT table type (please correct me if i'm wrong), that can 'connect' to a xml,txt,csv and others kinds of files to read and write it's values... Could we implement mp3, wave, ogg vorbis, file type type? This is a really nice 'table compression' for scada systems, and allow a new era of table type, think about... Select datetimestamp from someaudiofile where someniceaudiofunction()>1 In this example we could check date time where some scada value get over a fixed level, or where a frequency get higher and start a alarm condition, it could be used to get more information using statistics functions... i'm talking now about a new table type and new functions... but thats the idea... Anyone intested in this? About table compression think about compressing tax values in nasdaq for example, and execute a statistic analyse with this information... Well just some uses... Thanks for the space to show my idea and your time to read it Maybe a video table in future, or image based... just to use jpeg compreesion (x,y,color values) and others kinds of files... Bye

1 0

[Maria-developers] Empty_queries vs Com_empty_queries
by Ian Gilfillan 18 May '13

18 May '13

I'm documenting the status variables at the moment, and have a question about Empty_queries vs Com_empty_queries. Com_empty_queries is not incremented when an empty result is returned, and appears to be incremented only when an empty query is sent to the server, eg: mysql --comments -e '-- sql comment' Empty_queries appears to match the empty queries returned with SHOW USER_STATISTICS. It increments each time a query is performed that returns an empty result. Currently the documentation on the user statistics page at https://kb.askmonty.org/en/user-statistics/ says that EMPTY_QUERIES (and therefore the Empty_queries status variable) is "The number of times this user's connections sent empty queries to the server", which would describe Com_empty_queries rather than Empty_queries, and would need to be corrected. The documentation on https://kb.askmonty.org/en/server-status-variables/ is what I've just written based on the above. Have I understood this correctly, or is there something else to it? thanks, ian

3 2

[Maria-developers] To Sergei:
by Samarth Mathur 16 May '13

16 May '13

Hi Sergei.. just a few questions. Hope not to bother too much! does the .so file of two_questions exist in the mariadb 10.0.2 source code or was that just an example? Also, if I'm looking to implement pluggable authentication for LDAP, does the version of mariadb, i'm doing this on matter? if it does, which version would you recommend? I'm currently on v10.0.2.. What is the advantage of going for a pluggable authentication over say a module made for the PAM plugin in mariadb? Regards Sam

3 6

Re: [Maria-developers] Feature Request - Working auto_inc rement on BlackHole Engine
by Gordan Bobic 15 May '13

15 May '13

On 05/15/2013 11:27 PM, Jocelyn Fournier wrote: > Hi Gordan, > > > Le 15/05/13 22:18, Gordan Bobic a écrit : >> On 05/15/2013 09:06 PM, Michael Widenius wrote: >>> >>> Hi! >>> >>>>>>>> "Gordan" == Gordan Bobic <gordan(a)bobich.net> writes: >>> >>> Gordan> How difficult would be be to implement working >>> Gordan> auto-increment functionality on the BlackHole engine? >>> >>> Gordan> This would be really useful for what I'm trying to do at >>> Gordan> the moment. Since it is not available and there are no >>> Gordan> sequences in MariaDB and MySQL, I have to resort to a >>> Gordan> hack such as a single-int-field table doing the >>> Gordan> auto-increment, and an insert trigger on the blackhole >>> Gordan> table to insert a null into the auto-increment >>> Gordan> tracking table, get LAST_INSERT_ID() to set the >>> Gordan> value in the auto-increment table, and then delete >>> Gordan> the record. >>> >>> What problem is that you wanted to solve? >>> Normally blackhole is used just for creating a slave of a slave that >>> doesn't have to hold any data (only the logs). >> >> The problem is a huge volume logging application that needs to have >> uniquely enumerated records for auditing purposes. > > Have you tried TokuDB engine ? It isn't suitable for what I am doing in this case - note that the blackhole engine in this case suits all of my requirements except for the working auto_increment functionality. I don't need to store the data on these nodes, merely binlog it up for further processing downstream. Gordan

1 0

[Maria-developers] MariaDB GTID first impression
by Giuseppe Maxia 15 May '13

15 May '13

Kristian, Thanks for the docs. It's a very good start. Compared to MySQL 5.6, GTID in MariaDB has some advantages: it is enabled by default and it is easy to read. There are a few items, though, that should need some more thought: 1) rpl_slave_state is probably ill-named. In this architecture, it is only written by the slaves, and thus the name matches, but I wonder if the master should write this table (or a similar one) to keep track of its progress in the binary log. Rationale: all the info related to replication should be in tables and easy to query. 2) rpl_slave_state does not have enough metadata. What is missing here, and would be extremely useful, is the corresponding binlog+position for each GTID, a timestamp of when the transaction was saved to the binary log, and the timestamp of when it was applied. (The host name of the origin would also be useful). Also very important (when you will implement parallel replication) is a shard identifier. 3) Multiple domains do not integrate well with GTID. In multi-source replication, we use "connection names", but for GTID we use "domain numbers". They do not play well together : there is no correspondence between the gtid_domain_id and the connection name, and a "SHOW ALL SLAVES STATUS" lists all the GTID for every connection. For example: mysql -e 'show all slaves status\G' |grep 'Connection_name\|Running\|Master_Host\|Master_Port\|Gtid_Pos\|Master_Log' Connection_name: Bear Master_Host: 127.0.0.1 Master_Port: 8203 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1020 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 1020 Gtid_Pos: 100-101-21,400-104-23,200-102-8,300-103-12,500-105-20 Connection_name: Wolf Master_Host: 127.0.0.1 Master_Port: 8204 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 896 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 896 Gtid_Pos: 100-101-21,400-104-23,200-102-8,300-103-12,500-105-20 Connection_name: Fox Master_Host: 127.0.0.1 Master_Port: 8205 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 365 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 365 Gtid_Pos: 100-101-21,400-104-23,200-102-8,300-103-12,500-105-20 Connection_name: Deer Master_Host: 127.0.0.1 Master_Port: 8206 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1144 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 1144 Gtid_Pos: 100-101-21,400-104-23,200-102-8,300-103-12,500-105-20 Which connection is running which gtid number? Not easy to tell, as the correspondence between domain and connections is only in the DBA's head. 4) gtid_pos is text that includes all sequences from all domains. The docs say that I can modify it if needed, but in a case like the one above, modifying this string can be very error prone. For example, if server_ids are assigned using inet_aton(@IP), you could have a gtid_pos like this '1001-170062692-230000,1002-170062593-12893445,1003-170062700-672009,1003-170062699-700002' Instead of editing a string, and risk breaking replication in all domains, I would expect the availability of commands like: CHANGE GTID for domain 'Wolf' set gtid_pos=1900 That's it for now. Hope there is still room for improvements. Cheers Giuseppe

3 16

Re: [Maria-developers] MariaDB build logs
by Kristian Nielsen 14 May '13

14 May '13

Marcin Kulisz <debian(a)kulisz.net> writes: > as promised I'm attaching build log for maria DB, it's much smaller then > anticipated. Thanks! > From what I can tell it was just one test which failed and rendered whole build as a > failure. > My build env is based on cowbuilder on fully patched SID chroot. Ah, I see what is going on: > plugins.unix_socket [ fail ] > Test ended at 2013-05-11 00:12:52 > --- /tmp/buildd/mariadb-5.5-5.5.29/builddir/mysql-test/suite/plugins/r/unix_socket.result 2013-05-10 21:49:20.510929790 +0000 > +++ /tmp/buildd/mariadb-5.5-5.5.29/builddir/mysql-test/suite/plugins/r/unix_socket.reject 2013-05-11 00:12:52.562350485 +0000 > @@ -8,7 +8,7 @@ > # > select user(), current_user(), database(); > user() current_user() database() > -USER@localhost USER@% test > +USER@localhost USER@localhost test > # > # name does not match = failure > # Right, so the problem is that this test is poorly written. Its output depends on the name of unix username of the account running the test! I am guessing that in your case, you build as user root. Since the MariaDB server running in the test happens to also have a (database) account named root, we get an authenticated user USER@localhost instead of an anonymous user. I have now pushed a fix for this problem to the MariaDB tree: http://lists.askmonty.org/pipermail/commits/2013-May/004715.html So this fix will appear in MariaDB 5.5.31 or possibly 5.5.32 (not sure if it made it in 5.5.31, which I think is due for release). Until that fix propagates to the MariaDB packaging, some options for a work-around are: - Run the build as a non-root user, with fakeroot. - Skip the test suite during build (DEB_BUILD_OPTIONS=nocheck) (this will also greatly speed up the build). - Apply the linked patch manually Hope this helps, - Kristian.

1 0

Re: [Maria-developers] Forked mytop - conflicts, issues | fullqueries overflow bug patch
by Jean Weisbuch 14 May '13

14 May '13

Hi, I attached to this message a patch to fix the overflow when fullqueries is activated and full queries are taking more than a line that might be resulting in a page overflow (the informations shown on mytop will exceed the terminal height). As for the latest commit upstream (21, mar 2013 https://github.com/jzawodn/mytop/commit/437f2ef8d3fce02eafe935ddbf860d1dfbc…) its only a small fix that seems to already have been fixed on the MariaDB version. Regards.

1 0

Re: [Maria-developers] [Commits] Rev 3654: MDEV-4290: in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-4290/
by Sergei Golubchik 13 May '13

13 May '13

Hi, Sanja! On May 03, sanja(a)askmonty.org wrote: > revno: 3654 > revision-id: sanja(a)askmonty.org-20130503130713-0n5lgrniay7n3jua > parent: igor(a)askmonty.org-20130430033140-qhx9wsgc5w2dx14r > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-MDEV-4290 > timestamp: Fri 2013-05-03 16:07:13 +0300 > message: > MDEV-4290: > Fix agregate function resolution in derived tables (no name > resolution over a derived table border) ok to push Regards, Sergei

1 0

[Maria-developers] An invalid memory access bug in HandlerSocket
by ahiguti100＠gmail.com 10 May '13

10 May '13

Hi MariaDB developers, I found an invalid memory access bug of HandlerSocket is not yet fixed in MariaDB. https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/issues/78 This bug can be reproduced by running the following script: https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/regtest/… You may need to run mysqld with valgrind to observe the invalid memory access, because hitting this bug may not cause mysqld to crash. I've filed a merge request. https://code.launchpad.net/~ahiguti100/maria/handlersocket-fix-78/+merge/16… Rev #3750 fixes the bug, and #3751 updates documents and tests for HandlerSocket. I want docs to be updated because my github url for HandlerSocket has been changed. All the changes are copied from my github repo, and are provided under the BSD license. Please let me know if something is wrong with the merge request. Thanks, Akira

3 3

[Maria-developers] Updated myisamchk to handle LARGE files
by Michael Widenius 10 May '13

10 May '13

Hi! Here is a patch that should fix your myisamchk issues. This is to be applied to MariaDB 5.5.30 source. Can you verify that this works so I can add it to MariaDB 10.0 ? Some notes: - If you run repair with two --force options, like this: myisamchk --repair --force --force ... Then myisamchk will use the full myisam_sort_buffer_size for keys. Other buffers will be allocated as needed. This means that with this option one will never get the "myisam_sort_buffer_size is too small" error. I also added the option --create-missing-keys, which will create keys just for any disabled keys. Other things: - Better error message for 'myisam_sort_buffer_size is too small' errors. - Somewhat larger buffers as default. - Changed a lot of variables from uint -> ulonglong to try to ensure that big files will not cause any problems. - Removed some checks that limited the sort_buffer_size to 4G. - Fixed code that would cause problems if sort_buffer_size would be > 8G - Fixed printing of longlong variables. - Fixed possible core dump if 'alloc_dynamic()' would fail. - Fixed some possible problems on system where uint would be <= ulong. Hope this helps! Regards, Monty

2 2

Re: [Maria-developers] GSoC projects related on data replication.
by Kristian Nielsen 09 May '13

09 May '13

QIU Shuang <qiush.summer(a)gmail.com> writes: > A quick question: are the codes related to replication in the path `sql/rpl_*' and `sql/repl_*'? Much of the replication code is in: sql/log_event.* sql/slave.* sql/log.* sql/sql_repl.* sql/rpl_* sql/repl* Hope this helps, - Kristian.

1 0

[Maria-developers] how to use TC_LOG_MMAP in MySQL
by nanyi607rao 09 May '13

09 May '13

hi , In http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev452/revision/3434 you make some change to the class TC_LOG_MMAP,and I want to debug those codes to see how it works.but i don't understand in what conditions will MySQL use the TC_LOG_MMAP.it only use TC_LOG_DUMMY or MYSQL_BIN_LOG depends on the value of opt_bin_log.so how will it use the TC_LOG_MMAP in MySQL. 2013-05-09 nanyi607rao

3 2

[Maria-developers] GTID and failovers with multi-domain replication
by Pavel Ivanov 08 May '13

08 May '13

Kristian, You've mentioned that in future implementation of parallel replication MariaDB will use multiple domains to replicate from one master to a slave. It means for me that I need to understand how multiple domains will work. And I realized that I don't know how the following situation can be handled. I don't know how parallel replication will work, so I'll explain situation in terms of several masters. Let's assume we have server S1 that is master working with domain_id=0, server S2 is master working with domain_id=1, servers S3 and S4 are slaves and replicate from both of these masters, i.e. they have both domains in their databases. Now let's say S1 has last GTID 0-1-100, S2 has last GTID 1-2-100. Before S3 and S4 were able to fully catch up with S1 and S2 power got cut out from S1 and S2. As replication from two masters goes independently it's possible that S3 will have last transactions 0-1-100, 1-2-99 while S4 will have last transactions 0-1-99, 1-2-100. As my masters are out I want either S3 or S4 to temporarily become master. But it looks like I won't be able to do so: S3 won't connect to S4 because S4 doesn't have 0-1-100 and S4 won't connect to S3 because S3 doesn't have 1-2-100. Ideally I'd want for S3 to replicate from S4 in domain 1 and S4 to replicate from S3 in domain 0, and when they are equal in their position I can declare one of them master for both domains. But it looks like there are no tools to do such operation. How would you suggest to resolve such situation? Thank you, Pavel

2 3

[Maria-developers] Documentation about GTID
by Giuseppe Maxia 08 May '13

08 May '13

Hi! I am testing MariaDB 10.0.2, and I am interested in GTID. The only documentation I found is the worklog. Without docs at hand, I tried something: slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO master_gtid_pos=''; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'master_gtid_pos=''' at line 1 slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO master_gtid_pos=auto; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'master_gtid_pos=auto' at line 1 This is supposed to work, according to a blog post and some worklogs, but it doesn't. As for GTID in practice, it seems to be active. slave1 [localhost] {msandbox} ((none)) > show variables like '%gtid%'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | gtid_domain_id | 0 | | gtid_pos | 0-1-11 | | gtid_seq_no | 0 | +----------------+--------+ 3 rows in set (0.00 sec) slave1 [localhost] {msandbox} ((none)) > use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed slave1 [localhost] {msandbox} (mysql) > select * from rpl_slave_state; +-----------+--------+-----------+--------+ | domain_id | sub_id | server_id | seq_no | +-----------+--------+-----------+--------+ | 0 | 12 | 1 | 11 | +-----------+--------+-----------+--------+ However, when I run "show slave status", I get: Using_Gtid: 0 Could you provide a pointer to the docs of the expected behavior at this stage? Thanks Giuseppe

9 35