May 2013 - developers - lists.mariadb.org

[Maria-developers] Extendet FederatedX storage engine to support updating Sphinx RT indexes
by Markus Lidel 12 Jun '14

12 Jun '14

Hello, the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL: http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index: index rt { type = rt path = @CONFDIR@/data/rt rt_field = title rt_field = content rt_attr_string = title rt_attr_string = content rt_attr_uint = gid } you just have to call: CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt'; It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me. Best regards, Markus Lidel

4 8

[Maria-developers] Flashback Feature patch review
by Lixun Peng 22 Aug '13

22 Aug '13

Hi Monty, I patched my flashback feature to 10.0-base, and this is the diff file, please help me to review it. How to use this feature? I added several new arguments for mysqlbinlog: --flashback (-B), it will let mysqlbinlog working on FLASHBACK mode. --table (-T), it likes -d, but this argument is for table. --review, it will le mysqlbinlog print the SQL for reviewing. Reviewing feature will create a new "review" table to record the data that will be changed by FLASHBACK feature. If you aren't special the --review-dbname/tablename, "review" table will created on current DB, and the table name will be "__"+original_table_name. And for getting the original table struct, mysqlbinlog need to connect mysql, so --user/--host/--password is needed if necessary. --review-dbname, the DB that you want to store the review table. --review-tablename the TABLE that you want to store the changed data. Only if you set -T, this argument is useful. For example: I have a table "t" in database "test", CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `info` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9043880 DEFAULT CHARSET=utf8; And I done this operation, root@localhost : (none) 14:52:50> reset master; root@localhost : (none) 14:53:02> use test root@localhost : test 14:53:08> delete from t limit 10; root@localhost : test 14:53:25> insert into t (info) select "你好"; root@localhost : test 14:54:01> update t set info ='hello你好' limit 5; We can compare the output with --flashback or not. #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" > /tmp/1.sql #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -uroot -d test -T t --start-datetime="2013-03-27 14:54:00" -B --review > /tmp/2.sql How to implement Flashback: 1. mysqlbinlog tool can parse the binlog files and print them, so we can use the most of code in mysqlbinlog. 2. Changing the event type (INSERT -> DELETE, DELETE -> INSERT) 3. Exchanging the SET/WHERE part in UPDATE (UPDATE table SET x=1 WHERE y=2 -> UPDATE table SET y=2 WHERE x=1) 4. Invert the event output, from end to beginning (binlog: e1,e2,e3... -> flashback_log: en...e3,e2,e1) Then, importing the flashback output can flashback database/table to the special time. But binlog_format should be ROW. Thanks, Lixun -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com

2 3

[Maria-developers] Partition prune and lock
by Roberto Spadim 02 Jun '13

02 Jun '13

Hi guys i'm studing mariadb code and reading user manuals to know more about features. My last study was about query cache with many ideas in some MDEV and some patchs sent Now i'm reading about partition My questions are 1)when a update or a delete is done on a expecific partition like: Update table set field=2 where partition=1 How lock is done? Lock is done at table level or partition level? 2)Today we have vertical partition, could we implement 'virtual' horizontal partition update and delete in some group of fields (horizontal partition) only lock the horizontal partition being changed and used in where clausule? Any idea and problems on how to develop it? 3) can query cache be partition pruned? instead of removing entire table, just remove the partition that was changed It will consume more memory but will do a better work in cache Maybe a new key like SQL_CACHE_PARTITON or a new variable, query_cache_min_unit=PARTITION or TABLE could change how the query will be cached and pruned Thanks guys

1 1

[Maria-developers] Open Questions in the knowledgebase
by Ian Gilfillan 31 May '13

31 May '13

I've been cleaning up the questions on the knowledgebase, but there are still a few open ones that need a response. If anyone can help, please pay a visit. Here are the ones still needing feedback: Multiple error logs: https://kb.askmonty.org/en/multiple-err-logs/ Query optimizer: https://kb.askmonty.org/en/does-the-query-optimize-a-low-cardinality-index-… Strange SSL messages: https://kb.askmonty.org/en/strange-ssl-messages-in-error-log/ Multi-source replication and time zones: https://kb.askmonty.org/en/time-zones-multi-source-replication/ And of course, the old faithful, when will it be released (10.x as stable) - https://kb.askmonty.org/en/release-date-for-10x-stable-version/ Also, a comment asking for specifics on JSON implementation going forward: https://kb.askmonty.org/en/column_json/ thanks, ian

1 0

[Maria-developers] a bug also in mariadb
by xiaobing jiang 30 May '13

30 May '13

hi all: I submit a bug: http://bugs.mysql.com/bug.php?id=69352 . I review the mariadb code, I think it is also in mariadb. I have read the page. https://kb.askmonty.org/en/reporting-bugs/ should I report this bug to mariadb? Thanks!

1 0

[Maria-developers] GSoC 2013 - Interested Student - Roles Project
by Vicentiu Ciorbaru 28 May '13

28 May '13

Hello, My name is Vicențiu Ciorbaru. I am 3'rd year undergraduate student at the Polytechnic University of Bucharest, Romania and I am very interested in contributing to MariaDB as part of Google Summer of Code. I have not used MariaDB previously but I have taken it for a spin just now. I have participated in GSoC once before and would like to do so again. I have contributed to other open source organisations (Mozilla, Samba, ROSEdu) and I submit patches when time permits. The project that interests me the most is MDEV-4397: Roles. I want to ask you what would be a good place to start in the codebase to help me get an idea what I need to do for this project? Looking forward to hearing from you, Vicențiu

2 7

Re: [Maria-developers] GTID work priorities
by Kristian Nielsen 28 May '13

28 May '13

Pavel Ivanov <pivanof(a)google.com> writes: > we would really love to see "strict gtid mode" and related stuff next. I've now pushed strict mode to 10.0-base, and documented it in the knowledgebase. However, one thing was not clear from your description in MDEV-4478: > 4) Probably with a separate flag we want to prohibit any writes to binlog on > the slave except from the slave thread(s), i.e. whenever MASTER_HOST is > configured server works as if sql_log_bin = 0 globally except for the slave > thread(s) which work as if sql_log_bin = 1 and SELECT @@global.sql_log_bin > still returns 1. Can you elaborate exactly what semantics you have in mind for this new option? And why a new option is needed/how it differs from the already existing read-only and sql-log-bin options? It sounds to me what you want is simply to be able to start the server with --sql-log-bin=0 and still have the slave sql thread log its events to the binlog (if --log-slave-updates=1)? I would expect that to work already, but maybe there is a bug (that we could fix), or maybe you had something else in mind? - Kristian.

2 1

[Maria-developers] Query cache information plugin - MDEV-4516
by Roberto Spadim 28 May '13

28 May '13

Hi guys i'm reading query cache code, and i have some doubts... mariadb-10.0.1 source: 1)PROTOCOL_LOCAL: at line: 1412 (Query_cache::store_query) we have this comment: /* PROTOCOL_LOCAL results are not cached. */ what's PROTOCOL_LOCAL ? it's the unix socket? windows pipe? embedded? memcache? mysql? another thing like this? or it's the internal mariadb source running queries? 2)the query_size=0 (MDEV-4516) i was reading at line 1680 if (is_disabled() || thd->locked_tables_mode || thd->variables.query_cache_type == 0) goto err; and line : 1694 DBUG_ASSERT(query_cache_size != 0); // otherwise cache would be disabled why it's isn't used in query_cache_information plugin? *check that in line 1680 it test the "query_cache_type" too, * *in MDEV-4516 report about size=0 we didn't tested this case!!!* 3) i was at line 1886: DBUG_PRINT("qcache", ("\ long %d, 4.1: %d, bin_proto: %d, more results %d, pkt_nr: %d, \ CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \ sql mode: 0x%llx, sort len: %lu, conncat len: %lu, div_precision: %lu, \ def_week_frmt: %lu, in_trans: %d, autocommit: %d", is this the "primary key" of the query cache!?!?!?!?!?!?! could we use a sha1 or another "primary key" method (index [] of the query in cache + '';'+ 5 bytes of a hash key to compare if the index still with the same query) to easly show query entry "number", and allow a "delete from query_cache_information where key=xxx" function? at line 1938 we have this maybe the query position on cache ("primary key of query cache")?: DBUG_PRINT("qcache", ("Query have result 0x%lx", (ulong) query)); 4) there's some more ideas about query cache information plugin at MDEV-4571, if anyone could read it =) well that's all =] -- Roberto Spadim

2 19

[Maria-developers] GTID replication and relay_log.info
by Pavel Ivanov 28 May '13

28 May '13

Kristian, There's a well-known problem in MySQL that relay_log.info is not always in sync with the database state, and killing and restarting server at an unfortunate time will cause it to re-execute last statement and potentially break replication. Did you fix this situation with GTIDs and rpl_slave_state table? It looks like with GTIDs it's really easy to solve the problem -- rpl_slave_state has GTID of the last executed transaction, so on restart one needs just to ignore all relay logs and re-connect to master with the last GTID. Did you do that? If not are you planning to do that? Thank you, Pavel

2 11

Re: [Maria-developers] MariaDB 5.5 EOL plans
by Honza Horak 27 May '13

27 May '13

On 05/24/2013 11:04 PM, Rasmus Johansson wrote: > Hi, > > On Tue, May 14, 2013 at 5:57 PM, Honza Horak <hhorak(a)redhat.com > <mailto:hhorak@redhat.com>> wrote: > > Hi guys, > > I'm not able to find any info about life cycle of MariaDB 5.5 or > even other versions. Particularly if the MariaDB 5.5 life cycle will > somehow follow the MySQL's 5.5 life cycle or if there is some > another vision/rough plans when supporting of 5.5 will end? > > > Thanks for bringing this up! It has been on the table quite some times, > but now finally I think we got it documented. So here it is: > Thank you very much, that looks really great. Honza

1 0

[Maria-developers] Syncs server status variable
by Ian Gilfillan 26 May '13

26 May '13

What does the Syncs server status variable record, and when was it introduced? I can't seem to find this documented anywhere. thanks, ian gilfillan

2 1

[Maria-developers] MariaDB 5.5 EOL plans
by Honza Horak 25 May '13

25 May '13

Hi guys, I'm not able to find any info about life cycle of MariaDB 5.5 or even other versions. Particularly if the MariaDB 5.5 life cycle will somehow follow the MySQL's 5.5 life cycle or if there is some another vision/rough plans when supporting of 5.5 will end? Cheers, Honza

3 3

[Maria-developers] query cache restrictions
by Roberto Spadim 25 May '13

25 May '13

hi guys, why we can't cache "union" queries with query cache? http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ *Caching full queries only *– Meaning it does not work for subselects, inline views, parts of the UNION. This is also common missunderstanding. -- Roberto Spadim

1 1

Re: [Maria-developers] To Sergei:
by Sergei Golubchik 24 May '13

24 May '13

Hi, Samarth! On May 24, Samarth Mathur wrote: > I put the <plugin>.c file and the CmakeLists.txt in a folder on my desktop > and ran > *cmake .* > > CMake Warning (dev) in CMakeLists.txt: > No cmake_minimum_required command is present. A line of code such as > > cmake_minimum_required(VERSION 2.8) > > should be added at the top of the file. The version specified may be > lower > if you wish to support older CMake versions for this project. For more > information run "cmake --help-policy CMP0000". > This warning is for project developers. Use -Wno-dev to suppress it. > > then i ran > *cmake . -Wno-dev* > > CMake Error at CMakeLists.txt:1 (MYSQL_ADD_PLUGIN): > Unknown CMake command "MYSQL_ADD_PLUGIN". You need to do that from the MariaDB source tree. Put your plugin in the plugin/ directory, where other plugins are. Regards, Sergei

1 0

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

24 May '13

I put the <plugin>.c file and the CmakeLists.txt in a folder on my desktop and ran *cmake .* CMake Warning (dev) in CMakeLists.txt: No cmake_minimum_required command is present. A line of code such as cmake_minimum_required(VERSION 2.8) should be added at the top of the file. The version specified may be lower if you wish to support older CMake versions for this project. For more information run "cmake --help-policy CMP0000". This warning is for project developers. Use -Wno-dev to suppress it. then i ran *cmake . -Wno-dev* CMake Error at CMakeLists.txt:1 (MYSQL_ADD_PLUGIN): Unknown CMake command "MYSQL_ADD_PLUGIN". -- Configuring incomplete, errors occurred! Thanks On Fri, May 24, 2013 at 10:40 PM, Sergei Golubchik <serg(a)askmonty.org>wrote: > Hi, Samarth! > > On May 24, Samarth Mathur wrote: > > Hi, > > > > How do I go about compiling the plugin in mariadb? I searched online, but > > only found the mysql method : > > https://dev.mysql.com/doc/refman/5.5/en/compiling-plugin-libraries.html > > I'm getting some errors there, but, I wanna know, if it's done > differently > > or like this? > > Like in the manual, at the link above. > What errors are you getting? > > Regards, > Sergei >

1 0

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

24 May '13

Hi, How do I go about compiling the plugin in mariadb? I searched online, but only found the mysql method : https://dev.mysql.com/doc/refman/5.5/en/compiling-plugin-libraries.html I'm getting some errors there, but, I wanna know, if it's done differently or like this? Thanks On Thu, May 23, 2013 at 6:28 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Samarth! > > On May 23, Samarth Mathur wrote: > > 2 questions: > > > > to make .so file from .c, is it necessary for me to compile mariadb > > after each change? Or is there a shorter way as far as plugins are > > concerned? > > You only need to compile the plugin, you don't need to compile MariaDB > at all, you can use a binary from our site or as provided by your OS. > > > Is there any specific tip you would have as far as coding is > > concerned? Or does the normal convention of good code, good comments > > and indentation work alright? > > Just normal convention. The usual - see how it's done elsewhere in > MariaDB and do the same. > > Regards, > Sergei > >

2 1

[Maria-developers] Fusion-io atomic writes patch comments
by Laurynas Biveinis 24 May '13

24 May '13

Hi - We ported your patch to Percona Server, meanwhile we collected the following comments which perhaps are useful for you. These are addressed in our XtraDB port, so you will pick them up, but since you patched InnoDB as well, you might consider addressing them there as well. I tried to order them roughly in the order of importance. The comments below refer to rev 3720 of 5.5 tree (and one comment to MariaDB 10 trunk). - Where are the testcases? sys_vars test doesn't count :) - fil_extend_space_to_desired_ size() fails to consider that it might be extending a single node on a multi-node tablespace. It will calculate the desired size as if the last node was the only one, overextending the tablespace by the size of all the previous nodes. Fix is replacing offset_high = size_after_extend * page_size / (4ULL*1024*1024*1024); offset_low = size_after_extend * page_size % (4ULL*1024*1024*1024); with offset_high = (size_after_extend - file_start_page_no) * page_size / (4ULL * 1024 * 1024 * 1024); offset_low = (size_after_extend - file_start_page_no) * page_size % (4ULL * 1024 * 1024 * 1024); - In the same function in InnoDB 5.6 / MariaDB 10.0 it is necessary to set node->being_extended = FALSE; before jumping to complete_io. - In the same function, complete_io: label may skip over the fil_node_complete_io() call, which is required for os_aio() tablespace-extending call only. - os_file_set_atomic_writes() should return not an int, but bool or ibool instead, also its return value currently is raw ioctl() result, which is used as a bool at callers anyway and reversed from the InnoDB convention of true/non-zero meaning success. While at that, it's also nice to add __attribute__((warn_unused_result)) to it. - If ioctl() failed, then it's customary for the failed I/O call handling to call one of the os_file_handle_error... functions, here os_file_handle_error_no_exit() looks like a good fit. - If atomic writes are unsupported, then I'm not sure why set errno / LastError. No syscall has failed, the atomic writes unsupported-ness is communicated by the os_file_set_atomic_writes() return value. - os_file_set_atomic_writes() declaration should not depend on __linux__ etc, thus we pushed the #ifdef to function body. - InnoDB uses UNIV_LINUX instead of __linux__, the build sys checks for the presence of sys/ioctl.h and defines corresponding define, thus we replaced #ifdef __linux__ #include <sys/ioctl.h> #ifndef DFS_IOCTL_ATOMIC_WRITE_SET #define DFS_IOCTL_ATOMIC_WRITE_SET _IOW(0x95, 2, uint) #endif with #if defined(UNIV_LINUX) && defined(HAVE_SYS_IOCTL_H) # include <sys/ioctl.h> # ifndef DFS_IOCTL_ATOMIC_WRITE_SET # define DFS_IOCTL_ATOMIC_WRITE_SET _IOW(0x95, 2, uint) # endif #endif at the top of the file, and the #ifdef inside the function is #ifdef DFS_IOCTL... - Is this bit inside __WIN__ in os_file_create_func() actually needed? if (srv_use_atomic_writes && type == OS_DATA_FILE && os_file_set_atomic_writes(file, name)) { CloseHandle(file); *success = FALSE; file = INVALID_HANDLE_VALUE; } - os_file_set_size() could call os_file_handle_error_no_exit() for failed posix_fallocate() instead of printing the errno itself. - Comments don't follow InnoDB style, missing header function comments, too long lines, spurious whitespace, etc. Our code is at https://bazaar.launchpad.net/~percona-core/percona-server/5.5/revision/518 https://bazaar.launchpad.net/~percona-core/percona-server/5.6/revision/353 We haven't addressed the testcase issue yet neither. Regards, -- Laurynas

2 2

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

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 16 May '13

16 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

[Maria-developers] MYSQL_TYPE_DECIMAL type
by Zardosht Kasheff 08 May '13

08 May '13

Hello all, Under what circumstances can a table be created with MYSQL_TYPE_DECIMAL as being one of the columns? I thought MYSQL_TYPE_DECIMAL was deprecated pre 5.1. I thought all new tables use MYSQL_TYPE_NEWDECIMAL. We have a user reporting a table he wants to alter has that type. However, when I create that table, I don't get that type. As an aside, we don't support that type, mainly because I thought it was impossible to have a table created with it (I am apparently wrong). Is there a way to ensure that tables undergoing an alter convert that column to MYSQL_TYPE_NEWDECIMAL? Thanks -Zardosht

3 3

[Maria-developers] is this a bug?
by jhx1008 08 May '13

08 May '13

hi all: I have a problem about the type cast when I have read the source of Maria & MySQL I notice that when cast from string into int, Maria & MySQL do it like the C function atoi code: for (ul= 0 ; str < end9 && (ch= (uchar) (*str - '0')) < 10; str++) { ul= ul * 10 + ch; } but cast from hex into int, the code is: code: char *end=(char*) str_value.ptr()+str_value.length(), *ptr=end-min(str_value.length(),sizeof(longlong)); ulonglong value=0; for (; ptr != end ; ptr++) value=(value << 8)+ (ulonglong) (uchar) *ptr; so I do the test blew: create table t(id int auto_increment primary key, a int)engine=innodb; insert into t(a) values('1'); insert into t(a) values(0x31); everything is ok, I get the result: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 1 | | 2 | 49 | +----+------+ the value 0x31 is the ascii of the string '1', but we got the different result after inserting maybe it's not a problem, but in the replication environment, it maybe cause the data of slave not the same as master In the master, when we set the connection characterset into multi-bytes characterset and this characterset may escape with backslash is dangerous (like gbk, cp932) and then we use the prepared statement to do the insert, the string value in the insert must be cast into hex before write into the binlog, so we do the insert(insert into t(a) values('1')) on the master, but in the slave it does the another(insert into t(a) values(0x31)) , the data is not the same. how to reappear: master(binlog format=statement): create table t(id int auto_increment primay key, a int) engine=innodb; set names gbk; prepare stmt from 'insert into t(a) values(?)'; set @a='1'; execute stmt using @a; the result in master is: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 1 | +----+------+ and result in slave is: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 49 | +----+------+ I test the newest release version mariadb-10.0.2 and MySQL-5.6.10, the problem is still exist, is this a bug? 2013-04-28 jhx1008 @netease

4 7

[Maria-developers] MariaDB allows for slave to connect with non-existent GTID
by Pavel Ivanov 07 May '13

07 May '13

Kristian, I've realized that the way slaves are processed now on the master allows them to connect even if they request non-existent GTID. Is it "works as intended" and will be different in the "strict mode" or you didn't want for such things to happen even in non-strict mode? I've attached a test reproducing the problem. It sets up replication 1->2->3, executes transaction on 1, executes transaction on 2, disconnects 3, executes one more transaction on 1, fails over to 2, executes one more transaction on 2. Then when it tries to connect 3 to 1 it should fail because 1 doesn't have the last transaction that 3 has. I agree that this scenario is artificial, but in reality scenario can be e.g. like this: 2 is slave of 1, then it gets disconnected and executes one extra transaction, then backup is taken from 2, then 2 gets restarted and restored to the state of 1, then failover happens and 2 becomes master, then 3 restores from the backup taken earlier from 2 and if all this time 1 and then 2 as masters executed some transactions 3 will be able to connect to 2 even though its last transaction doesn't exist on 2... Pavel

2 3

[Maria-developers] Missing locking around THD::set_db() ?
by Kristian Nielsen 07 May '13

07 May '13

Hi, I was looking at a Valgrind warning in Buildbot (appended below). What happens seems to be this: Thread 1 is running SHOW PROCESSLIST, it grabs the pointer THD::db to the current database of thread 2. Thread 2 then does THD::set_db(), freeing the old THD::db pointer and allocating a new one with the new data. Thread 1 then resumes, doing strdup() of the _old_, now invalid, THD::db pointer, which reads garbage data (or could even segfault if we get really unlucky). This seems like a genuine bug. I see absolutely no locking protecting against this race :-( Any suggestions for how to deal with this? The only locking I see in mysqld_list_processes() is holding LOCK_thread_count, but taking that for every THD::set_db() sounds quite bad for scalability (for example, set_db() is run by every query replicated by a slave thread). Should we use LOCK_thd_data also for THD::db ? Or maybe we could have a fixed buffer in THD for the db? Seems we would need 193 bytes. Then SHOW PROCESSLIST could sometimes show a random mix of old and new db name if we race, but at least we would not read invalid memory or crash due to mummap() ... Any better ideas? - Kristian. ----------------------------------------------------------------------- Original valgrind warning, for reference: rpl.rpl_stop_slave 'mix,xtradb' w5 [ fail ] Found warnings/errors in server log file! Test ended at 2013-04-22 22:05:12 line ==9897== Thread 20: ==9897== Invalid read of size 1 ==9897== at 0x4C2826B: memcpy (mc_replace_strmem.c:878) ==9897== by 0xBAF9E8: strmake_root (my_alloc.c:424) ==9897== by 0x63C74E: mysqld_list_processes(THD*, char const*, bool) (sql_class.h:771) ==9897== by 0x5C8574: mysql_execute_command(THD*) (sql_parse.cc:3149) ==9897== by 0x5CC844: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5759) ==9897== by 0x5CDDC9: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1068) ==9897== by 0x5CE4BD: do_command(THD*) (sql_parse.cc:794) ==9897== by 0x6A4E23: do_handle_one_connection(THD*) (sql_connect.cc:1266) ==9897== by 0x6A4F23: handle_one_connection (sql_connect.cc:1181) ==9897== by 0x8F0027: pfs_spawn_thread (pfs.cc:1015) ==9897== by 0x4E3506F: start_thread (in /lib64/libpthread-2.9.so) ==9897== by 0x62F713C: clone (in /lib64/libc-2.9.so) ==9897== Address 0x1a06a973 is 3 bytes inside a block of size 5 free'd ==9897== at 0x4C257D8: free (vg_replace_malloc.c:446) ==9897== by 0xBB96BB: my_free (my_malloc.c:115) ==9897== by 0x838C86: Query_log_event::do_apply_event(Relay_log_info const*, char const*, unsigned int) (sql_class.h:2846) ==9897== by 0x53904C: apply_event_and_update_pos(Log_event*, THD*, Relay_log_info*) (log_event.h:1230) ==9897== by 0x5446DD: exec_relay_log_event(THD*, Relay_log_info*) (slave.cc:2766) ==9897== by 0x545BD9: handle_slave_sql (slave.cc:3627) ==9897== by 0x8F0027: pfs_spawn_thread (pfs.cc:1015) ==9897== by 0x4E3506F: start_thread (in /lib64/libpthread-2.9.so) ==9897== by 0x62F713C: clone (in /lib64/libc-2.9.so)

4 4

Re: [Maria-developers] [Gsoc] Regex enhancements Project
by Sergei Golubchik 04 May '13

04 May '13

Hi, Sudheera! On Apr 19, Sudheera Palihakkara wrote: > Hi, > I went through other threads on this topic. In one thread you mentioned to > choose a suitable regex library. > > *( Preliminary research - only about chosing a regex library to use in > MariaDB. You should be able to explain why we should use this library and > not some other one.) > > * > What do you mean by "choosing"? don't we have to enhance the exiting regex > library? Or choose from exiting already implemented libraries which are > free to use? sorry if it's a stupid question, but I'm confused. :O Enhancing our old regex library to support all modern features and multiple charsets is complex and bug-prone work. I don't see why we should bother doing it, when there are plenty of regex libraries available. There's PHP's mb_regex, there's prce, and many others too. We'd better just pick one that works better for MariaDB, and put it instead of Henry Spencer's library. Regards, Sergei P.S. Please, don't reply to me only, use reply-to-all, so that your mails appear on the mailing list.

3 9

[Maria-developers] Fwd: MariaDB release numbering
by Jorge Manuel B. S. Vicetto 03 May '13

03 May '13

Hi. I've emailed Kristian about this subject and he suggested I sent this to your mailing list to get a more complete picture. On our work packaging MariaDB for Gentoo, me and Robin need to decide how to deal with the packaging of MySQL vs MariaDB and to that it would be really helpful to have an idea of how you plan to number your future releases. AFAIK, currently we can compare MySQL vs MariaDB as: MySQL MariaDB 5.1 5.1 / 5.2 / 5.3 5.5 5.5 (including MariaDB features from 5.2/5.3) 5.6 10.0 (based on MySQL 5.5 with backports from MySQL 5.6 and features from previous MariaDB versions) 5.7 ??? The first question for us is what version number will you use for a newer version based on MySQL-5.7. Another question is if you plan to port all your work to the 5.7 codebase or if you'll keep working on the current 10.0 codebase and do backports from 5.7 (like you did for MySQL 5.6). A second question is whether you still claim binary compatibility with MySQL[1] and how would you compare the latest versions of MariaDB and MySQL (10.0 vs 5.6, 5.7). [1] - https://kb.askmonty.org/en/mariadb-versus-mysql-compatibility/ Any info you can share on the release versioning plans would be really helpful. If this is still too early for any definitions or comments, is there any way we can follow or learn about such decisions? Thank you and regards, Jorge Manuel B. S. Vicetto Gentoo Developer

2 1

[Maria-developers] Forked mytop - conflicts, issues
by Honza Horak 03 May '13

03 May '13

Hi guys, MariaDB forked mytop utility source some time back, but it causes some issues for us in Fedora for example (and similarly in other distributions I guess) -- particularly the MariaDB's binary mytop conflicts with the same utility from separate package mytop. So, I'd like to take a look at possible solutions. Since there was a small commit in mytop upstream few weeks back, it doesn't really look like totally dead upstream. So the ideal solution from my POV would be including enhancements that MariaDB did to mytop back to upstream and not including mytop utility in MariaDB any more. Would something like that be possible from the perspective of MariaDB? Cheers, Honza

4 4

[Maria-developers] xa transaction cause replication broken
by jhx1008 02 May '13

02 May '13

hello I have another problem witch seems like a bug(Server version: 5.5.5-10.0.2-MariaDB-log MariaDB Server). the problem is about the replication for the xa transactions when the master crashed and then restarted. I know when we do a transaction, the queries will be stored in the binlog cache, after commit, flush the binlog cache into the binlog file, any normal un-committed transactions will be rollbacked when the server crashed then restarted, but the prepared xa transactions are not. Prepared xa transactions can be recovered use the command 'xa recover', then committed use 'xa commit xid', but the binlog cache is empty when the master restart, so if we do the 'xa commit xid', nothing to be recorded into the binlog files, the replication will be broken. test: master mysql> create table t(id int auto_increment primary key, a int) engine=innodb; mysql> xa start '111'; mysql> insert into t(a) values(1); mysql> xa end '111'; mysql> xa prepare '111'; kill -9 master_pid restart the master mysql> xa recover; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 3 | 0 | 111 | +----------+--------------+--------------+------+ mysql> xa commit '111'; mysql> select * from t; Empty set (0.00 sec) why? I did the xa commit '111', but no result return, maybe another bug? do the test continue mysql> xa start '222'; mysql> insert into t(a) values(2); mysql> xa end '222'; mysql> prepare '222'; mysql> xa commit '111'; mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ value(1,1) return at this time, why? what's hanppen on the slave? mysql> select * from t; +----+------+ | id | a | +----+------+ | 2 | 2 | +----+------+ the replication is broken now!! by the way, when the xa transactions preapred, if the client exit, the transactions will be rollbacked in the THD::cleanup function, maybe the server rollback all the xa prepared transcations when restart is a good method? 2013-05-02 jhx1008 @netease

2 2

[Maria-developers] GTIDs, events sequence and alternate futures
by Pavel Ivanov 01 May '13

01 May '13

Kristian, I can't find the answer to my questions in the code and don't know if there's some documentation I can use (is there such thing?), so I'm writing my questions here. As I understand GTID is constructed as <domain id>-<server id>-<sequence number>. Is it guaranteed that sequence numbers are always increasing inside the domain id? So let's say two servers have GTID 0-1-100 and if they have alternate futures then one server will have GTIDs 0-1-101, 0-1-102 and so on, while second server will have 0-2-101, 0-2-102 etc. Is that correct? BTW, where in the code is sequence number increased exactly? Thank you, Pavel

2 4