November 2013 - developers - lists.mariadb.org

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

11 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

Re: [Maria-developers] metadata_lock_info plugin
by Sergey Vojtovich 13 Dec '13

13 Dec '13

Hi Kentoku, thanks for your contribution and sorry for this long delay. I finally managed to go through your code. The most important thing I'm worried about is that there is no protection of ticket lists: lists may be updated while we iterate them and it may cause server crash in concurrent environment. From what I can see MDL_context is mostly supposed to be accessed only by thread which owns this context. As such it is not protected by any locks. I believe we should use global lists instead, something like: mysql_mutex_lock(&mdl_locks.m_mutex); iterate mdl_locks.m_locks (hash of MDL_lock) { mysql_prlock_rdlock(&lock->m_rwlock); iterate lock->m_granted (list of MDL_ticket) { // store data } mysql_prlock_unlock(&lock->m_rwlock); } mysql_mutex_lock(&mdl_locks.m_mutex); A few more suggestions in no particular order: - instead of making m_tickets public I'd suggest to try to make i_s_metadata_lock_info_fill_table() a friend function (shouldn't be relevant anymore) - why check for thd->killed? (shouldn't be relevant anymore) - metadata_lock_info_lock_mode_length[] includes ending zero, and then we store e.g. "MDL_SHARED\0" whereas we should store "MDL_SHARED" I'd suggest to declare these arrays as following: static const LEX_STRING metadata_lock_info_lock_mode_str[]= { STRING_WITH_LEN("MDL_INTENTION_EXCLUSIVE"), // or C_STRING_WITH_LEN() ... }; - Roberto suggests to rename ID to THREAD_ID and I agree - Roberto suggests to add QUERY_ID column and I agree - Roberto suggests to add LOCK_DURATION column and I agree - Roberto suggests to use m_namespace_to_wait_state_name instead of metadata_lock_info_lock_names, but I tend to disagree because the former says "Waiting for...". Though I'd suggest to make array of LEX_STRING's to avoid calling strdup(). - Roberto suggests to replace DB with KEY and NAME with SUB_KEY, but I tend to disagree since in most cases it is db and table name. I'd vote for TABLE_SCHEMA and TABLE_NAME to stay in line with the rest I_S tables. Regards, Sergey On Mon, Jul 01, 2013 at 03:46:22AM +0900, kentoku wrote: > Hi Sergey, > Sorry. I think this plugin needs more locks "mdl_locks.m_mutex", > "mdl_locks.m_global_lock" and "mdl_locks.m_commit_lock". Is it right? Would > you please teach me how can I use it? > Thanks, > Kentoku > > > 2013/7/1 kentoku <kentokushiba(a)gmail.com> > > > Hi Sergey, > > > > I made new information schema plugin which named "metadata_lock_info" > > plugin. This plugin makes it possible knowing "who has metadata locks". In > > development stage, sometimes DBAs meet metadata locks when using alter > > table statement. This metadata locks are sometimes caused by GUI tools. In > > service stage, sometimes DBAs meet metadata locks when using alter table > > statement. This metadata locks are sometimes caused by long batch > > processing. In many cases, the DBAs need to judge immediately. So I made it > > for all DBAs. > > Would you please review this plugin? > > lp:~kentokushiba/maria/10.0.3-metadata_lock_info > > Note: This plugin needs a change of sql/mdl.h > > > > Thanks, > > Kentoku > >

3 15

[Maria-developers] MDEV-5345 - Deadlock between mysql_change_user(), SHOW VARIABLES and INSTALL PLUGIN
by Sergey Vojtovich 04 Dec '13

04 Dec '13

Hi Sergei, I'm afraid I couldn't find easy fix for this deadlock. The question is if it is worth to spend time for more complex (and possibly less stable) solution. Details below. Locking profile --------------- INSTALL PLUGIN: LOCK_plugin -> LOCK_system_variables_hash (w) SHOW VARIABLES: LOCK_system_variables_hash (r) -> LOCK_global_system_variables change_user(): LOCK_global_system_variables -> LOCK_plugin INSTALL PLUGIN -------------- Needs LOCK_plugin to register new plugin. Needs LOCK_system_variables_hash to register plugin variables. Doesn't seem to need both locks at the same time. Similar lock order is used in a few places. SHOW VARIABLES -------------- Needs LOCK_system_variables_hash to iterate system_variable_hash. Needs LOCK_global_system_variables to read variable value. Does seem to need both locks at the same time. This lock order is not used in other places. change_user() ------------- Needs LOCK_global_system_variables to read global_system_variable.table_plugin. Needs LOCK_plugin to my_plugin_lock(table_plugin). Does seem to need both locks at the same time. Similar lock order is used in a few places. "SHOW VARIABLES" looks correct. So I have only two ideas: - Fix INSTALL PLUGIN so it releases LOCK_plugin before registering variables. Sounds like the best solution, but there are a few more things to fix, e.g. UNINTALL PLUGIN. - Store default storage engine as a string, not Sys_var_plugin. Thanks, Sergey

2 3

Re: [Maria-developers] MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables
by Sergei Golubchik 03 Dec '13

03 Dec '13

Hi, Sergey! On Aug 27, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3807 > revision-id: svoj(a)mariadb.org-20130827121233-xh1uyhgfwbhedqyf > parent: jplindst(a)mariadb.org-20130823060357-pww92qxla7o8iir7 > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Tue 2013-08-27 16:12:33 +0400 > message: > MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables > > - tc_acquire_table and tc_release_table do not access > TABLE_SHARE::tdc.used_tables anymore > - in tc_acquire_table(): release LOCK_tdc after we relase LOCK_open > (saves a few CPU cycles in critical section) > - in tc_release_table(): if we reached table cache threshold, evict > to-be-released table without moving it to unused_tables. unused_tables > must be empty at this point (added assertion). I don't understand what you're doing here, could you elaborate? E.g. explain in a changeset comment what you've done, why you introduced a new list for tables (all_share), what is its semantics, etc. Regards, Sergei

3 12

[Maria-developers] Movable conditions and semi-joins
by Sergei Petrunia 27 Nov '13

27 Nov '13

Hi! The below is an extension of the skype discussion. I wrote down the limitation imposed by FirstMatch, and also covered other Semi-join strategies. <contents> 1. SJ-Materialization 2. FirstMatch 3. Duplicate Elimination 4. Loose Scan </contents> == 1. SJ-Materialization == The general rule is: If the condition is originally attached inside an SJM nest, one should not move it outside. Conditions that are originally outside should not be moved inisde the SJM nest. The reason for the rule is that field values for inner tables are only available inside the SJM nest. Correspondingly, values of fields of outer tables are not available at materialization step. The only possible exception is when the condition depends only on columns that are in the IN-equality. Then, it could be moved. I don't think this is worth handling for the first milestone. == 2. FirstMatch == Consider a query select ... from ot1, nt1, nt2 where expr(ot1) in (select ... from it1, it2, ... where ...) and a join order ot1 FirstMatch(it1, it2) nt1 nt2 It is possible to move cond(otX) to table itX or ntX. There is a problem with moving condition attached to itX (will call it SUBQ_COND. this ocndition depends on table itX and maybe on ot1) to table ntX. Consider join execution execution: ot1.row0 ot1.row1 -+- it1.row1 -- it2.row1 | +- it1.row2 -- it2.row2 ->- (F1)-->-- nt1.row1 (F2)--<-- FirstMatch check is at point (F2). At that point, we know that ot1.row1 has had a match in the subquery, and the execution returns to table ot1. If SUBQ_COND is moved to table ntX, then there may be a situation where we will get to point (F2) and assume that the subquery had a match, while actually it hasn't. A suggestion by Igor: at point (F2) we should check the last return value of SUBQ_COND (actually, of all conditions that were moved like SUBQ_COND was) It is actually more complicated: what if there is COND(ot1, nt1) such that COND(ot1.ro1, nt1.row1) = FALSE? Then, we will never evaluate SUBQ_COND(ot1.row1, ...). What if never evaluate SUBQ_COND(ot1.row1, ...) but before we have evaluated SUBQ_COND(ot1.row0, ...)? We will have to track what we have evaluated for the current row of ot1.row1. == 3. Duplicate Elimination == Duplicate Elimination removes duplicate matches using a temporary table. In order to work correctly, filtering needs to be performed before the weedout is done. Let's again use an example. select ... from ot1, nt1, nt2 where expr(ot1) in (select ... from it1, it2, ... where ...) and a join order (DUPS-W-START) it1 ot1 it2 (DUPS-W-WEEDOUT) nt1 nt2 at DUPS-W-START, all rows are deleted from the temp. table. at DUPS-W-WEEDOUT, we take ot1.rowid and attempt to write it into the temp table. Basic idea: If a condition SUBQ_COND(itX, ...) is moved to the right across the (DUPS-W-WEEDOUT) point, wrong query results will occur. Example: Suppose, SUBQ_COND(it2) is moved to nt1, and join execution proceeds as follows: it1.row1 --- ot1.row1 --- it2.row1 -- DUPS-W-WEEDOUT -- nt1 | - We reach DUPS-W-WEEDOUT with (it1.row1, ot1.row1, it2.row1). - The temp. table is empty, so rowid(ot1.row1) is written into temp.table, and Weedout doesn't weed it out. - At nt1, SUBQ_COND(it2.row1) is evaluated and found to be false. join execution returns back. - Suppose, tables it2 and ot1 have no more matching rows, so execution returns to it1. (the same diagram with a few more steps) it1.row1 --- ot1.row1 --- it2.row1 -- DUPS-W-WEEDOUT -- nt1 | it1.row2 --- ot1.row1 --- it2.row2 -- DUPS-W-WEEDOUT - We reach DUPS-W-WEEDOUT with (it1.row2, ot1.tow1, it2.rowX). - The temptable already has rowid(ot1.row1). The record combination is filtered out. if SUBQ_COND(it2.row2)=TRUE, then the result will miss a row. == 4. Loose Scan == Loose Scan is similar to First Match - it does short-cutting (but also makes forward jumps on the driving table). Short-cutting imposes a limitation similar to FirstMatch - conditions that depend on subquery's tables cannot be moved out of LooseScan's range. BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] maria-tokudb-engine-10.0 deb package (raring) - if files are empty where are sources?
by Paul Hewson 27 Nov '13

27 Nov '13

Hello, I'm trying to follow the instructions on https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/ which imply there is more tuned version of tokudb for mariadb than I might get by following instructions at tokutek. However, I have ubuntu 13.04 (raring), and despite some mismatch in numbers (I have maria 10.0 rather than 5.5) I am getting an empty file when I try to apt-get install mariadb-tokudb-engine-10.0 I found a bit of discussion about this point, but got the impression it had been fixed. Does it mean it was fixed and the debs were removed? If so, do you mind if I ask where I get the mariadb tokutek sources. Again the webpage https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/ implies only some parts (most parts) of totudb have been merged into mariadb. Thanks, sorry for troubling you with naive issues like this. Paul ________________________________ [http://www.plymouth.ac.uk/images/email_footer.gif]<http://www.plymouth.ac.uk/worldclass> This email and any files with it are confidential and intended solely for the use of the recipient to whom it is addressed. If you are not the intended recipient then copying, distribution or other use of the information contained is strictly prohibited and you should not rely on it. If you have received this email in error please let the sender know immediately and delete it from your system(s). Internet emails are not necessarily secure. While we take every care, Plymouth University accepts no responsibility for viruses and it is your responsibility to scan emails and their attachments. Plymouth University does not accept responsibility for any changes made after it was sent. Nothing in this email or its attachments constitutes an order for goods or services unless accompanied by an official order form.

2 2

[Maria-developers] Malformed XML in mysql-test/std_data/Index.xml added in revision 3873
by Jeremy Cole 26 Nov '13

26 Nov '13

Bar, Regarding your change: http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3873/mysql-… I was wondering if it's intentional that mysql-test/std_data/Index.xml now contains malformed XML. It wasn't mentioned in your commit message and the test nor XML files do not mention it specifically. Although utf8_5624_5_bad is supposed to be "bad" I didn't think it was supposed to be *that* "bad" (mismatched "p" vs "a" tags): $ xmllint --noout mysql-test/std_data/Index.xml mysql-test/std_data/Index.xml:126: parser error : Opening and ending tag mismatch: p line 126 and a <reset>a-a4</reset>xxx04</a> ^ mysql-test/std_data/Index.xml:127: parser error : Opening and ending tag mismatch: p line 127 and a <reset>a-aa5</reset>xxx05</a> ^ mysql-test/std_data/Index.xml:128: parser error : Opening and ending tag mismatch: p line 128 and a <reset>a-aaa6</reset>xxx06</a> ^ mysql-test/std_data/Index.xml:129: parser error : Opening and ending tag mismatch: p line 129 and a <reset>a-aaaa7</reset>xxx07</a> ^ mysql-test/std_data/Index.xml:130: parser error : Opening and ending tag mismatch: p line 130 and a <reset>a-aaaaa8</reset>xxx08</a> ^ mysql-test/std_data/Index.xml:131: parser error : Opening and ending tag mismatch: p line 131 and a <reset>a-aaaaaa9</reset>xxx09</a> ^ mysql-test/std_data/Index.xml:132: parser error : Opening and ending tag mismatch: p line 132 and a <reset>a-aaaaaa10</reset>xxx10</a> ^ Regards, Jeremy

2 1

[Maria-developers] Force PK option and global lock for DDLs
by erkan yanar 25 Nov '13

25 Nov '13

Ahoi, I would like to propagate two ideas (did it on #irc some times but still some love is missing) 1. Force PK option. I would like to have an option to force PK for (innodb) tables. Forcing is quite easy (a poc from 2011): http://linsenraum.de/erkules/2011/03/this-table-type-requires-a-primary-key… Why? RBR and so Galera would benefit from such an option. 2. Global DDL Lock Without Global DDL Lock i.e. ALTER TABLE, DROP TABLE etc. brings you in trouble while doing a --single-transaction dump. You can use flush tables with read lock, but then you need not to do a single-transaction dump anyway. Even xtrabackup got troubles: https://bugs.launchpad.net/percona-xtrabackup/+bug/722638 I got to confess I think ignoring tablespace ids is the right soltution. Even I think this are simple options. I believe they would be quite welcome to a lot of dba's out there. Regards Erkan -- über den grenzen muß die freiheit wohl wolkenlos sein

1 1

Re: [Maria-developers] [Commits] Rev 3908: MDEV-5277 - Ensure that all MySQL 5.6 options are supported
by Sergei Golubchik 25 Nov '13

25 Nov '13

Hi, Sergey! On Nov 25, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3908 > revision-id: svoj(a)mariadb.org-20131125144940-nhdqjt2eisqaa7pb > parent: svoj(a)mariadb.org-20131114140000-5eq8gopqu5giwvqq > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0-mdev5277 > timestamp: Mon 2013-11-25 18:49:40 +0400 > message: > MDEV-5277 - Ensure that all MySQL 5.6 options are supported by the > MariaDB 10.0 server Looks ok to me, thanks! Regards, Sergei

1 0

Re: [Maria-developers] Rev 3985: MDEV-5321 Calling mysql_library_end accesses freed memory; dumps memory to display
by Sergei Golubchik 25 Nov '13

25 Nov '13

Hi, Holyfoot! On Nov 24, holyfoot(a)askmonty.org wrote: > ------------------------------------------------------------ > revno: 3985 > revision-id: holyfoot(a)askmonty.org-20131124184915-spkpy6qt8vdbluaz > parent: igor(a)askmonty.org-20131123023813-0mgl217lyyyysv5b > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5321 > timestamp: Sun 2013-11-24 22:49:15 +0400 > message: > MDEV-5321 Calling mysql_library_end accesses freed memory; dumps memory to display. > Don't call the vio_end() in the clean_up() in EMBEDDED mode. > Call vio_end() before the end_embedded_server(). ok to push Regards, Sergei

1 0