- developers - lists.mariadb.org

Re: [Maria-developers] RedHat Buildslaves
by Kristian Nielsen 07 Mar '11

07 Mar '11

Daniel Bartholomew <dbart(a)montyprogram.com> writes: > Daniel> I'm creating the build-slaves based on the KVM setup > Daniel> instructions you have here: > Daniel> > Daniel> http://askmonty.org/wiki/BuildBot:vm-setup#Centos_5_i386 > I now have a rhel6-amd64 vm setup on terrier with base, serial, build, > and install vms. The steps I went through to set it up are here: > > http://kb.askmonty.org/v/buildbot-setup-for-red-hat > I'm not sure what the next step should be. Probably copying over the > correct OurDelta file. Can you copy one out After that, do you want to > take a look at the VMs and see if they're ok for building and install > testing? I did this, and set up new builders in Buildbot. They fail, but the failure looks related to the .spec file and build scripts, rather than the VMs. So I would say the VMs are now ok, and the next step is to find someone to modify the ourdelta build scripts and/or spec files to work with RHEL. > Once this VM is certifed as good to use as a buildbot buildslave I'll > move on to rhel4 and rhel5. As I said, the VMs look reasonable. So if you want, you can start doing the rhel4/5 ones the same way, or if you prefer, you can wait until the .spec files are done for rhel6, in case that work turns up something else that is needed. > One thing I just realized is that > the /kvm/MySQL-OurDelta-shared-5.0.87.d10-65.el5.i386.rpm file that I > placed at /srv/shared/yum/CentOS/5/i386/RPMS/ is not the right > architecture for an amd64 system, and it's probably not at the right > path, either (what with CentOS being in the path). Yeah. I grabbed the amd64 one and copied it in there with my best guess for the path (and also put it on terrier:/kvm/). The thing is, it probably needs more than this, someone who understands (or takes the time to learn) .rpm will have to develop the right solution. The full story is like this: The Redhat rpms (original, not ours) put the `mysql` binary in the same package as libmysqlclient. Then it has other packages depend on this package to get libmysqlclient.so.15 (old version of client library). So when we replace the package with a new version of the mysql binary (from MariaDB), we also have to provide a copy of the _old_ client library (as well as a copy of the new one of course, libmysqlclient.so.16). (In contrast, Debian does this correctly, placing the client library in package libmysqlclient15, which can co-exist with new MySQL/MariaDB versions). The question is how to get this old library? The MySQL build team told me they grab it from an old binary package from MySQL 4.1 (or whatever), so the OurDelta stuff does something similar. Admittedly not a nice solution. Maybe it is not needed for RHEL 6, if it does not have dependencies on libmysqlclient15. Or maybe we need to find an old RHEL MySQL .rpm to use instead. I do not know rpm well enough to have a meaningful opinion. > I built this directly on terrier, since my local desktop can only run > with "-cpu qemu32". I tried to build an x86 version on terrier, but I > kept getting stuck in a VM reboot loop. So I'll have to upload the Yeah, there is something wierd/different with the qemu-kvm I built on terrier to fix another problem. But it works with `-cpu qemu64` (also for a 32-bit VM). Doing it on your local machine should be fine as well. Thanks, - Kristian.

1 0

[Maria-developers] Igor please review: Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/]
by Sergey Petrunya 05 Mar '11

05 Mar '11

Hello Igor, Could you please review the below? The bug page https://bugs.launchpad.net/maria/+bug/727667 has verbose explanations of how we ended up having this bug, as well as justifications for fixing it in this particular way. ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sat, 5 Mar 2011 12:56:26 +0300 (MSK) Subject: [Commits] Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/ At file:///home/psergey/dev2/5.3/ ------------------------------------------------------------ revno: 2933 revision-id: psergey(a)askmonty.org-20110305095622-wzlnwr9srfbsu3pv parent: psergey(a)askmonty.org-20110304091446-qmsezak3c0cfm9b1 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3 timestamp: Sat 2011-03-05 12:56:22 +0300 message: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 - put the code that sets HA_NULL_PART bit back - Fix test_if_ref/part_of_refkey() so that = NULL-ability of lookup columns does not prevent the equality from being removed (we now have early/late NULLs filtering which will filter out NULL values) = equality is not removed if it is ref_or_null access, and the value of the lookup column can alternate between the lookup value and NULL. === modified file 'mysql-test/r/null.result' --- a/mysql-test/r/null.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/null.result 2011-03-05 09:56:22 +0000 @@ -170,7 +170,7 @@ insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null i i 5 const 9 Using index +1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index select count(*) from t1 where i=2 or i is null; count(*) 10 === modified file 'mysql-test/r/null_key.result' --- a/mysql-test/r/null_key.result 2010-10-06 20:27:12 +0000 +++ b/mysql-test/r/null_key.result 2011-03-05 09:56:22 +0000 @@ -21,10 +21,10 @@ 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -226,7 +226,7 @@ explain select * from t2,t1 where t1.a=t2.a or t1.a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 @@ -447,3 +447,15 @@ 3 12 0 12 drop table t1, t2; End of 5.0 tests +# +# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +# +CREATE TABLE t1 ( +f3 int(11), +f10 varchar(1), +KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +f3 f10 +DROP TABLE t1; === modified file 'mysql-test/r/order_by.result' --- a/mysql-test/r/order_by.result 2011-01-27 04:45:23 +0000 +++ b/mysql-test/r/order_by.result 2011-03-05 09:56:22 +0000 @@ -643,7 +643,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -652,7 +652,7 @@ 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect.result 2011-03-05 09:56:22 +0000 @@ -906,7 +906,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2011-03-05 09:56:22 +0000 @@ -910,7 +910,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2011-03-05 09:56:22 +0000 @@ -907,7 +907,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2011-03-05 09:56:22 +0000 @@ -907,7 +907,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/t/null_key.test' --- a/mysql-test/t/null_key.test 2008-03-03 17:35:44 +0000 +++ b/mysql-test/t/null_key.test 2011-03-05 09:56:22 +0000 @@ -263,3 +263,16 @@ drop table t1, t2; -- echo End of 5.0 tests +--echo # +--echo # BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +--echo # + +CREATE TABLE t1 ( + f3 int(11), + f10 varchar(1), + KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +DROP TABLE t1; + === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-02-28 21:29:59 +0000 +++ b/sql/sql_select.cc 2011-03-05 09:56:22 +0000 @@ -6564,10 +6564,12 @@ j->ref.has_record= FALSE; j->ref.null_rejecting= 0; j->ref.disable_cache= FALSE; + j->ref.null_ref_part= (uint)-1; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; uchar *key_buff=j->ref.key_buff, *null_ref_key= 0; + uint null_ref_part= (uint)-1; bool keyuse_uses_no_tables= TRUE; if (ftkey) { @@ -6621,7 +6623,10 @@ instead of JT_REF_OR_NULL in case if field can't be null */ if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null) + { null_ref_key= key_buff; + null_ref_part= i; + } key_buff+= keyinfo->key_part[i].store_length; } } /* not ftkey */ @@ -6636,6 +6641,7 @@ /* Must read with repeat */ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; j->ref.null_ref_key= null_ref_key; + j->ref.null_ref_part= null_ref_part; } else if (keyuse_uses_no_tables) { @@ -15287,8 +15293,12 @@ *****************************************************************************/ /** + Check if "left_item=right_item" equality is guaranteed to be true by use of + [eq]ref access on left_item->field->table. + @return - 1 if right_item is used removable reference key on left_item + TRUE if right_item is used removable reference key on left_item + FALSE otherwise */ bool test_if_ref(Item *root_cond, Item_field *left_item,Item *right_item) @@ -15301,7 +15311,11 @@ (!join_tab->first_inner || *join_tab->first_inner->on_expr_ref == root_cond)) { - // Cond guards + /* + If ref access uses "Full scan on NULL key" (i.e. it actually alternates + between ref access and full table scan), then no equality can be + guaranteed to be true. + */ for (uint i = 0; i < join_tab->ref.key_parts; i++) { if (join_tab->ref.cond_guards[i]) @@ -15309,7 +15323,7 @@ return FALSE; } } - // + Item *ref_item=part_of_refkey(field->table,field); if (ref_item && ref_item->eq(right_item,1)) { @@ -15611,6 +15625,29 @@ } +/* + @brief + + Check if + - @table uses "ref"-like access + - it is based on "@field=certain_item" equality + - the equality will be true for any record returned by the access method + and return the certain_item if yes. + + @detail + + Equality won't necessarily hold if: + - the used index covers only part of the @field. + Suppose, we have a CHAR(5) field and INDEX(field(3)). if you make a lookup + for 'abc', you will get both record with 'abc' and with 'abcde'. + - The type of access is actually ref_or_null, and so @field can be either + a value or NULL. + + @return + Item that the field will be equal to + NULL if no such item +*/ + static Item * part_of_refkey(TABLE *table,Field *field) { @@ -15619,17 +15656,30 @@ return (Item*) 0; // field from outer non-select (UPDATE,...) uint ref_parts= join_tab->ref.key_parts; - if (ref_parts) + if (ref_parts) /* if it's ref/eq_ref/ref_or_null */ { - uint key= join_tab->ref.key; KEY *key_info= join_tab->get_keyinfo_by_key_no(key); KEY_PART_INFO *key_part= key_info->key_part; for (uint part=0 ; part < ref_parts ; part++,key_part++) - if (field->eq(key_part->field) && - !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART))) - return join_tab->ref.items[part]; + { + if (field->eq(key_part->field)) + { + /* + Found the field in the key. Check that + 1. ref_or_null doesn't alternate this component between a value and + a NULL + 2. index fully covers the key + */ + if (part != join_tab->ref.null_ref_part && // (1) + !(key_part->key_part_flag & HA_PART_KEY_SEG)) // (2) + { + return join_tab->ref.items[part]; + } + break; + } + } } return (Item*) 0; } === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2011-02-24 06:23:12 +0000 +++ b/sql/sql_select.h 2011-03-05 09:56:22 +0000 @@ -109,8 +109,15 @@ */ key_part_map null_rejecting; table_map depend_map; ///< Table depends on these tables. + /* null byte position in the key_buf. Used for REF_OR_NULL optimization */ uchar *null_ref_key; + /* + ref_or_null optimization: number of key part can be NULL (there's only one) + uint(-1) if no part can be NULL. + */ + uint null_ref_part; + /* The number of times the record associated with this key was used in the join. === modified file 'sql/table.cc' --- a/sql/table.cc 2011-02-22 09:15:47 +0000 +++ b/sql/table.cc 2011-03-05 09:56:22 +0000 @@ -1581,6 +1581,8 @@ #endif key_part->key_part_flag|= HA_PART_KEY_SEG; } + if (field->real_maybe_null()) + key_part->key_part_flag|= HA_NULL_PART; /* Sometimes we can compare key parts for equality with memcmp. But not always. _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB 5.2.5
by Daniel Bartholomew 03 Mar '11

03 Mar '11

Here are the download, release notes, and changelog pages for 5.2.5: * http://downloads.askmonty.org/mariadb/5.2.5 * http://kb.askmonty.org/v/mariadb-525-release-notes * http://kb.askmonty.org/v/mariadb-525-changelog If any of you have a moment, please check over the above pages to see if there are any mistakes or omissions and let me know (or jump in and fix if you have editing rights). Community team: I tested a sample of the mirrors and they all had the files so please announce the release on the announce list, blogs, and so on. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 2

Re: [Maria-developers] [Commits] Rev 2929: Fix LP BUG#718763 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 03 Mar '11

03 Mar '11

Hi! Everything looks OK, except one small style error. Also it would be nice to put somewhere 'TODO:' to bring in order 'uncachable' flags. On 03.03.2011 14:07, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 2929 > revision-id: timour(a)askmonty.org-20110303120726-la5o40rldim2t93y > parent: psergey(a)askmonty.org-20110301072222-1rmapkelx9l1kw8u > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Thu 2011-03-03 14:07:26 +0200 > message: > Fix LP BUG#718763 > > Analysis: > The reason for the crash was that the inner subquery was executed > via a scan on a final temporary table applied after all other > operations. This final operation is implemented by changing the > contents of the JOIN object of the subquery to represent a table > scan over the temp table. At the same time query optimization of > the outer subquery required evaluation of the inner subquery, which > happened before the actual EXPLAIN. The evaluation left the JOIN > object of the inner subquery in the changed state, where it represented > a table scan over a temp table, and EXPLAIN crashed because the temp > table is not associated with any table reference (TABLE_LIST object). > The reason the JOIN was not restored was because its saving/restoration > was controlled by the join->select_lex->uncacheable flag, which was > not set in the case of materialization. > > Solution: > In the methods Item_in_subselect::[single | row]_value_transformer() set: > select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; > In addition, for symmetry, change: > master_unit->uncacheable|= UNCACHEABLE_EXPLAIN; > instead of UNCACHEABLE_DEPENDENT because if a subquery was not > dependent initially, the changed methods do not change this > fact. The subquery may later become correlated if it is transformed > to an EXISTS query, but it may stay uncorrelated if executed via > materialization. [skip] > > > @@ -1742,7 +1752,15 @@ Item_in_subselect::row_value_transformer > optimizer->keep_top_level_cache(); > > thd->lex->current_select= current; > - master_unit->uncacheable|= UNCACHEABLE_DEPENDENT; > + /* > + The uncacheable property controls a number of actions, e.g. whether to > + save/restore (via init_save_join_tab/restore_tmp) the original JOIN for > + plans with a temp table where the original JOIN was overriden by > + make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus > + non-correlated subqueries will not appear as such to EXPLAIN. > + */ > + master_unit->uncacheable|=UNCACHEABLE_EXPLAIN; > + select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; > > if (!abort_on_null&& left_expr->maybe_null&& !pushed_cond_guards) > { put space (' ') after '=' [skip] > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] MariaDB 5.2.5 packages ready for release
by Kristian Nielsen 02 Mar '11

02 Mar '11

From: Kristian Nielsen <knielsen(a)knielsen-hq.org> Subject: Release of MariaDB 5.1.55 To: Daniel Bartholomew <dbart(a)askmonty.org> Cc: Internal Dev List <dev(a)askmonty.org> Date: Mon, 28 Feb 2011 12:40:13 +0100 Hi Daniel, The packages for release of MariaDB 5.2.5 are ready. The main changes I know of are the same changes as in the MariaDB 5.1.55 release: merges of MySQL 5.1.55 and of XtraDB from Percona Server 5.1.54-12.5. This version was released off the main "5.2" branch: lp:maria/5.2 I added the mariadb-5.2.5 tag. The Buildbot build number, for locating packages in /archive/pack, is 829. As for the 5.1.55 release, I prepared apt repositories and signed .rpm files. The repositories are here: hasky:/home/knielsen/repo The signed .rpms are here: hasky:/home/knielsen/build-829 - Kristian.

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 2901: BNLH algorithm always used a full table scan over the joined table in file:///home/igor/maria/maria-5.3-mwl128-hashrange/
by Sergey Petrunya 02 Mar '11

02 Mar '11

Hello Igor, Here's the promised proof of deadcode: On Fri, Feb 11, 2011 at 08:49:37PM -0800, Igor Babaev wrote: > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2011-02-06 04:57:03 +0000 > +++ b/sql/sql_select.cc 2011-02-12 04:41:22 +0000 > @@ -8419,6 +8438,11 @@ > sort_by_tab->type= JT_ALL; > sort_by_tab->read_first_record= join_init_read_record; > } > + else if (sort_by_tab->type == JT_HASH_NEXT) > + { > + sort_by_tab->type= JT_HASH; > + sort_by_tab->read_first_record= join_init_read_record; > + } > } > break; > } Statement: the above else-if branch can never be taken, i.e. equality sort_by_tab->type == JT_HASH_NEXT (1) can never be true. Proof: First, let's see what JOIN_TABs can be in sort_by_tab. sort_by_tab is assigned in this statement: JOIN_TAB *sort_by_tab= join->group && join->simple_group && join->group_list ? join->join_tab+join->const_tables : join->get_sort_by_join_tab(); i.e. possible values are 1. join->join_tab+join->const_tables 2. join->get_sort_by_join_tab() get_sort_by_join_tab is defined in sql_select.h as: JOIN_TAB *get_sort_by_join_tab() { return (need_tmp || !sort_by_table || skip_sort_order || ((group || tmp_table_param.sum_func_count) && !group_list)) ? NULL : join_tab+const_tables; } i.e. it can return: 1. NULL (which is not interesting) 2. join->join_tab + const_tables. this draws us to conclusion that sort_by_tab can be - NULL - join->join_tab + const_tables. so equality (1) can be rewritten as join->join_tab[const_tables].type == JT_HASH_NEXT (2) Now, let's explore the question, what join_tab elements can have type==JT_HASH_NEXT. The value JT_HASH_NEXT is assigned in only one place: sql_select.cc|8399| tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT; this line is inside the case block of code: break; case JT_ALL: case JT_HASH: .... tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT; which means that tab->type can become JT_HASH_NEXT only if it was JT_HASH before. Let's see when a join_tab can get type==JT_HASH. There's only one assignment that can assign type=JT_HASH. It is at sql_select.cc:8279 : if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG) tab->type= JT_HASH; i.e. tab->type can become JT_HASH only if tab->cache!=NULL. tab->cache can be set to non-NULL value only inside check_join_cache_usage(). However, the first lines in check_join_cache_usage() have this code: uint i= tab - join->join_tab; join->return_tab= 0; if (cache_level == 0 || i == join->const_tables || !prev_tab) return 0; from which we can conclude that join->join_tab[join->const_tables].cache == NULL will always hold. Going back on our chain of reasoning, we get that join->join_tab[join->const_tables].cache == NULL at all times=> join->join_tab[join->const_tables].type != JT_HASH at all times => join->join_tab[join->const_tables].type != JT_HASH_NEXT at all times => equality (2) is false always => original equality (1) is false always, q.e.d. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Igor please review: BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) in file:///home/psergey/dev2/5.3/]
by Sergey Petrunya 02 Mar '11

02 Mar '11

On Wed, Mar 02, 2011 at 08:04:39AM -0800, Igor Babaev wrote: > > +SELECT alias2.f1 AS field1 , alias1.f2 AS field2 > > +FROM t1 AS alias1 > > +LEFT OUTER JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 > > +WHERE alias2.f3 > 'k' > > +AND alias2.f3 <= 'z' > > +OR alias2.f4 IN ('o', 'y') > > +AND alias2.f4 >= 'g' AND alias2.f4 < 'z'; > > +field1 field2 > > +Warnings: > > +Warning 1292 Truncated incorrect DOUBLE value: 'k' > > Why do we need comparison of a string with an integer that generates the > above warning? Can we do without it? Why not to use char(4) instead > if integer? Got rid of string vs integer comparison, as well as simplified the testcase a bit (further simplification is difficult). Please find the result below: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Wed, 2 Mar 2011 17:06:15 +0300 (MSK) Subject: [Commits] Rev 2929: BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) in file:///home/psergey/dev2/5.3/ At file:///home/psergey/dev2/5.3/ ------------------------------------------------------------ revno: 2929 revision-id: psergey(a)askmonty.org-20110302140613-4qt8xvdvxs4t4m98 parent: psergey(a)askmonty.org-20110301072222-1rmapkelx9l1kw8u committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3 timestamp: Wed 2011-03-02 17:06:13 +0300 message: BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) - Add debugging aid which maps MRR's range_ids (which are pointers so are different on every execution and hard to track) to range numbers (which allow much easier debugging). The mapper is not compiled unless the developer puts a #define into code to request it. - Fix the bug: the problem was that Mrr_ordered_index_reader's interrupt_read() and resume_read() would save and restore 1) index tuple 2) the rowid (as bytes returned by handler->position()). Clustered primary key columns were not saved/restored. They are not explicitly present in the index tuple (i.e. table->key_info[secondary_key].key_parts doesn't list them), but they are actually there, in particular table->field[clustered_primary_key_member].part_of_key(secondary_key) == 1. Index condition pushdown code [correctly] uses the latter as inidication that pushed index condition can refer to clustered PK members. The fix was to make interrupt_read()/resume_read() to save/restore clustered primary key members as well, so that we get correct values for them when evaluating pushed index condition. [Second attempt, make the code compile without the debugging aid] === modified file 'mysql-test/r/innodb_mrr.result' --- a/mysql-test/r/innodb_mrr.result 2011-01-17 21:26:04 +0000 +++ b/mysql-test/r/innodb_mrr.result 2011-03-02 14:06:13 +0000 @@ -736,3 +736,31 @@ c1 c2 c3 08:29:45 NULL 2009-02-01 drop table `t1`; +# +# BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) +# +set @_save_join_cache_level= @@join_cache_level; +set join_cache_level = 6; +CREATE TABLE t1 ( +f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1), +PRIMARY KEY (f1), +KEY (f3), +KEY (f2) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('11','8','f','f'),('12','5','v','v'),('13','8','s','s'), +('14','8','a','a'),('15','6','p','p'),('16','7','z','z'),('17','2','a','a'), +('18','5','h','h'),('19','7','h','h'),('20','2','v','v'),('21','9','v','v'), +('22','142','b','b'),('23','3','y','y'),('24','0','v','v'),('25','3','m','m'), +('26','5','z','z'),('27','9','n','n'),('28','1','d','d'),('29','107','a','a'); +select count(*) from ( +SELECT alias1.f2 +FROM +t1 AS alias1 JOIN ( +t1 AS alias2 FORCE KEY (f3) JOIN +t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3 +) ON alias3.f1 <= alias2.f1 +) X; +count(*) +361 +set join_cache_level=@_save_join_cache_level; +drop table t1; === modified file 'mysql-test/t/innodb_mrr.test' --- a/mysql-test/t/innodb_mrr.test 2011-01-17 21:26:04 +0000 +++ b/mysql-test/t/innodb_mrr.test 2011-03-02 14:06:13 +0000 @@ -426,3 +426,31 @@ SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; drop table `t1`; +--echo # +--echo # BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) +--echo # +set @_save_join_cache_level= @@join_cache_level; +set join_cache_level = 6; +CREATE TABLE t1 ( + f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1), + PRIMARY KEY (f1), + KEY (f3), + KEY (f2) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('11','8','f','f'),('12','5','v','v'),('13','8','s','s'), +('14','8','a','a'),('15','6','p','p'),('16','7','z','z'),('17','2','a','a'), +('18','5','h','h'),('19','7','h','h'),('20','2','v','v'),('21','9','v','v'), +('22','142','b','b'),('23','3','y','y'),('24','0','v','v'),('25','3','m','m'), +('26','5','z','z'),('27','9','n','n'),('28','1','d','d'),('29','107','a','a'); + +select count(*) from ( + SELECT alias1.f2 + FROM + t1 AS alias1 JOIN ( + t1 AS alias2 FORCE KEY (f3) JOIN + t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3 + ) ON alias3.f1 <= alias2.f1 +) X; + +set join_cache_level=@_save_join_cache_level; +drop table t1; === modified file 'sql/multi_range_read.cc' --- a/sql/multi_range_read.cc 2011-02-18 22:31:01 +0000 +++ b/sql/multi_range_read.cc 2011-03-02 14:06:13 +0000 @@ -387,15 +387,25 @@ bool Mrr_ordered_index_reader::set_interruption_temp_buffer(uint rowid_length, uint key_len, + uint saved_pk_len, uchar **space_start, uchar *space_end) { - if (space_end - *space_start <= (ptrdiff_t)(rowid_length + key_len)) + if (space_end - *space_start <= (ptrdiff_t)(rowid_length + key_len + saved_pk_len)) return TRUE; support_scan_interruptions= TRUE; saved_rowid= *space_start; *space_start += rowid_length; + + if (saved_pk_len) + { + saved_primary_key= *space_start; + *space_start += saved_pk_len; + } + else + saved_primary_key= NULL; + saved_key_tuple= *space_start; *space_start += key_len; @@ -406,17 +416,25 @@ void Mrr_ordered_index_reader::set_no_interruption_temp_buffer() { support_scan_interruptions= FALSE; - saved_key_tuple= saved_rowid= NULL; /* safety */ + saved_key_tuple= saved_rowid= saved_primary_key= NULL; /* safety */ have_saved_rowid= FALSE; } void Mrr_ordered_index_reader::interrupt_read() { DBUG_ASSERT(support_scan_interruptions); + TABLE *table= file->get_table(); /* Save the current key value */ - key_copy(saved_key_tuple, file->get_table()->record[0], - &file->get_table()->key_info[file->active_index], + key_copy(saved_key_tuple, table->record[0], + &table->key_info[file->active_index], keypar.key_tuple_length); + + if (saved_primary_key) + { + key_copy(saved_primary_key, table->record[0], + &table->key_info[table->s->primary_key], + table->key_info[table->s->primary_key].key_length); + } /* Save the last rowid */ memcpy(saved_rowid, file->ref, file->ref_length); @@ -433,9 +451,16 @@ void Mrr_ordered_index_reader::resume_read() { - key_restore(file->get_table()->record[0], saved_key_tuple, - &file->get_table()->key_info[file->active_index], + TABLE *table= file->get_table(); + key_restore(table->record[0], saved_key_tuple, + &table->key_info[file->active_index], keypar.key_tuple_length); + if (saved_primary_key) + { + key_restore(table->record[0], saved_primary_key, + &table->key_info[table->s->primary_key], + table->key_info[table->s->primary_key].key_length); + } } @@ -738,8 +763,8 @@ @retval other Error */ -int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, - void *seq_init_param, uint n_ranges, uint mode, +int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs_arg, + void *seq_init_param_arg, uint n_ranges, uint mode, HANDLER_BUFFER *buf) { THD *thd= current_thd; @@ -748,6 +773,8 @@ uint key_buff_elem_size; handler *h_idx; Mrr_ordered_rndpos_reader *disk_strategy= NULL; + RANGE_SEQ_IF seq_funcs= *seq_funcs_arg; + void *seq_init_param= seq_init_param_arg; bool do_sort_keys= FALSE; DBUG_ENTER("DsMrr_impl::dsmrr_init"); LINT_INIT(key_buff_elem_size); /* set/used when do_sort_keys==TRUE */ @@ -810,8 +837,8 @@ { /* Pre-calculate some parameters of key sorting */ keypar.use_key_pointers= test(mode & HA_MRR_MATERIALIZED_KEYS); - seq_funcs->get_key_info(seq_init_param, &keypar.key_tuple_length, - &keypar.key_tuple_map); + seq_funcs.get_key_info(seq_init_param, &keypar.key_tuple_length, + &keypar.key_tuple_map); keypar.key_size_in_keybuf= keypar.use_key_pointers? sizeof(char*) : keypar.key_tuple_length; key_buff_elem_size= keypar.key_size_in_keybuf + (int)is_mrr_assoc * sizeof(void*); @@ -819,9 +846,18 @@ /* Ordered index reader needs some space to store an index tuple */ if (strategy != index_strategy) { + /* Normally we don't need to save/restore PK columns*/ + uint saved_pk_length=0; + if (h_idx->primary_key_is_clustered()) + { + uint pk= h_idx->get_table()->s->primary_key; + saved_pk_length= h_idx->get_table()->key_info[pk].key_length; + } + if (reader_factory.ordered_index_reader. set_interruption_temp_buffer(primary_file->ref_length, keypar.key_tuple_length, + saved_pk_length, &full_buf, full_buf_end)) goto use_default_impl; } @@ -845,7 +881,9 @@ if (do_sort_keys && !key_buffer->have_space_for(key_buff_elem_size)) goto use_default_impl; - if ((res= index_strategy->init(primary_file, seq_funcs, seq_init_param, n_ranges, + MRR_DEBUG(setup_range_id_mapping(&seq_funcs, &seq_init_param, n_ranges)); + + if ((res= index_strategy->init(primary_file, &seq_funcs, seq_init_param, n_ranges, mode, &keypar, key_buffer, &buf_manager))) goto error; } @@ -873,7 +911,9 @@ if ((res= setup_two_handlers())) goto error; - if ((res= index_strategy->init(secondary_file, seq_funcs, seq_init_param, + MRR_DEBUG(setup_range_id_mapping(&seq_funcs, &seq_init_param, n_ranges)); + + if ((res= index_strategy->init(secondary_file, &seq_funcs, seq_init_param, n_ranges, mode, &keypar, key_buffer, &buf_manager)) || (res= disk_strategy->init(primary_file, index_strategy, mode, @@ -883,6 +923,7 @@ } } + res= strategy->refill_buffer(TRUE); if (res) { @@ -910,7 +951,7 @@ DBUG_ASSERT(primary_file->inited == handler::INDEX); /* Call correct init function and assign to top level object */ Mrr_simple_index_reader *s= &reader_factory.simple_index_reader; - res= s->init(primary_file, seq_funcs, seq_init_param, n_ranges, mode, NULL, + res= s->init(primary_file, &seq_funcs, seq_init_param, n_ranges, mode, NULL, NULL, NULL); strategy= s; DBUG_RETURN(res); @@ -1033,6 +1074,7 @@ DBUG_ENTER("DsMrr_impl::dsmrr_close"); close_second_handler(); strategy= NULL; + MRR_DEBUG(clear_range_id_mapping()); DBUG_VOID_RETURN; } @@ -1690,7 +1732,110 @@ DBUG_VOID_RETURN; } - +#ifdef DBUG_MRR +range_seq_t DsMrr_impl::hook_init(void *init_params, uint n_ranges, uint flags) +{ + DsMrr_impl *mrr= (DsMrr_impl*)init_params; + mrr->backend_seq= mrr->backend_init(mrr->backend_init_params, n_ranges, flags); + return (range_seq_t)mrr; +} + +bool DsMrr_impl::hook_next(range_seq_t seq, KEY_MULTI_RANGE *range) +{ + DsMrr_impl *mrr= (DsMrr_impl*)seq; + int res; + if (!(res= mrr->backend_next(mrr->backend_seq, range))) + { + DBUG_ASSERT(mrr->cur_range_no < mrr->n_range_ids); + mrr->range_ids[mrr->cur_range_no++]= range->ptr; + } + return res; +} + +void DsMrr_impl::hook_get_key_info(void *init_params, uint *length, key_part_map *map) +{ + DsMrr_impl *mrr= (DsMrr_impl*)init_params; + mrr->backend_get_key_info(mrr->backend_init_params, length, map); +} + +bool DsMrr_impl::hook_skip_index_tuple(range_seq_t seq, char *range_info) +{ + DsMrr_impl *mrr= (DsMrr_impl*)seq; + int rangeno= mrr->range_id_to_number(range_info); + bool res= mrr->backend_skip_index_tuple(mrr->backend_seq, range_info); + fprintf(stderr, "checking index tuple %d -> %d\n", + rangeno, + (int)res); + return res; +} + +bool DsMrr_impl::hook_skip_record(range_seq_t seq, char *range_info, uchar *rowid) +{ + DsMrr_impl *mrr= (DsMrr_impl*)seq; + bool res= mrr->backend_skip_record(mrr->backend_seq, range_info, rowid); + return res; +} + +void DsMrr_impl::setup_range_id_mapping(RANGE_SEQ_IF *rseq, void **init_params, uint n_ranges) +{ + /* + Hook ourselves into RANGE_SEQ_IF so that we can watch ranges as they are + enumerated + */ + backend_init= rseq->init; + rseq->init= hook_init; + + backend_next= rseq->next; + rseq->next= hook_next; + + backend_init_params= *init_params; + *init_params= (void*)this; + + if (rseq->get_key_info) + { + backend_get_key_info= rseq->get_key_info; + rseq->get_key_info= hook_get_key_info; + } + + if (rseq->skip_index_tuple) + { + backend_skip_index_tuple= rseq->skip_index_tuple; + rseq->skip_index_tuple= hook_skip_index_tuple; + } + + if (rseq->skip_record) + { + backend_skip_record= rseq->skip_record; + rseq->skip_record= hook_skip_record; + } + + /* Alloc space for the mapping */ + n_range_ids= n_ranges; + if (n_ranges) + range_ids= (char**)malloc(sizeof(char*) * n_ranges); + else + range_ids= NULL; + cur_range_no= 0; +} + +void DsMrr_impl::clear_range_id_mapping() +{ + if (range_ids) + free(range_ids); + range_ids= NULL; + n_range_ids= 0; +} + +int DsMrr_impl::range_id_to_number(char *range_id) +{ + for (int i=0; i < n_range_ids; i++) + { + if (range_ids[i] == range_id) + return i; + } + return -1; +} +#endif /* ************************************************************************** * DS-MRR implementation ends ***************************************************************************/ === modified file 'sql/multi_range_read.h' --- a/sql/multi_range_read.h 2010-12-20 11:40:12 +0000 +++ b/sql/multi_range_read.h 2011-03-02 14:06:13 +0000 @@ -271,7 +271,8 @@ mrr_funcs.skip_index_tuple(mrr_iter, range_info)); } - bool set_interruption_temp_buffer(uint rowid_length, uint key_len, + bool set_interruption_temp_buffer(uint rowid_length, uint key_len, + uint saved_pk_len, uchar **space_start, uchar *space_end); void set_no_interruption_temp_buffer(); @@ -314,6 +315,7 @@ bool have_saved_rowid; uchar *saved_key_tuple; + uchar *saved_primary_key; static int compare_keys(void* arg, uchar* key1, uchar* key2); static int compare_keys_reverse(void* arg, uchar* key1, uchar* key2); @@ -520,7 +522,12 @@ typedef void (handler::*range_check_toggle_func_t)(bool on); DsMrr_impl() - : secondary_file(NULL) {}; + : secondary_file(NULL) + { +#ifdef DBUG_MRR + range_id_mapping_ctor(); +#endif + } void init(handler *h_arg, TABLE *table_arg) { @@ -615,6 +622,52 @@ int setup_two_handlers(); void close_second_handler(); + +#ifdef DBUG_MRR +#define MRR_DEBUG(x) x + /* + Debugging aid: support for converting range_ids (which are pointers) to + range numbers. Range's number specifies when the range was returned by + RANGE_SEQ_IF::next(). First returned range gets number 0, second gets + number 1, and so on. + */ + char **range_ids; /* range_ids[N] == range_id of range with number N */ + int n_range_ids; /* Total number of range ids */ + + /* "current" range number (makes sense when walking the ranges) */ + int cur_range_no; + + + /* RANGE_SEQ_IF override: "backend" functions */ + range_seq_t (*backend_init)(void *init_params, uint n_ranges, uint flags); + bool (*backend_next)(range_seq_t seq, KEY_MULTI_RANGE *range); + void (*backend_get_key_info)(void *init_params, uint *length, key_part_map *map); + bool (*backend_skip_index_tuple)(range_seq_t seq, char *range_info); + bool (*backend_skip_record)(range_seq_t seq, char *range_info, uchar *rowid); + void *backend_init_params; + range_seq_t backend_seq; + + /* RANGE_SEQ_IF override: our hook-functions */ + static range_seq_t hook_init(void *init_params, uint n_ranges, uint flags); + static bool hook_next(range_seq_t seq, KEY_MULTI_RANGE *range); + static void hook_get_key_info(void *init_params, uint *length, key_part_map *map); + static bool hook_skip_index_tuple(range_seq_t seq, char *range_info); + static bool hook_skip_record(range_seq_t seq, char *range_info, uchar *rowid); + + /* Mapper [de]initialization functions*/ + void range_id_mapping_ctor() + { + range_ids= NULL; + n_range_ids= 0; + } + void setup_range_id_mapping(RANGE_SEQ_IF *rseq, void **init_params, uint n_ranges); + void clear_range_id_mapping(); + + /* The reason of all of the above: function to map range ids to range numbers */ + int range_id_to_number(char *range_id); +#else +#define MRR_DEBUG(x) +#endif }; /** _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 2905: Merge Percona patch MWL#47 into mariadb 5.2-percona. in http://bazaar.launchpad.net/~maria-captains/maria/5.2
by Kristian Nielsen 02 Mar '11

02 Mar '11

knielsen(a)knielsen-hq.org writes: > message: > Merge Percona patch MWL#47 into mariadb 5.2-percona. > > This patch adds options to annotate the binlog (and the mysqlbinlog > output) with the original SQL query for queries that are logged > using row-based replication. Serg raised the very relevant issue about what to do about the similar feature in MySQL 5.6: http://forge.mysql.com/worklog/task.php?id=4033 http://forge.mysql.com/worklog/task.php?id=5404 The Oracle team has worked more on this since last I looked, and the two features are now very similar. Here are the main differences, as I see it: - The MySQL 5.6 feature is enabled by --binlog_rows_query_log_events, while the MWL#47 option is called --binlog-annotate-rows-events. - MWL#47 has --replicate-annotate-rows-events which makes the slave receive and binlog the annotations. The MySQL feature always receives annotation events if present in the binlog (so replication 5.6->pre-5.6 will break if annotations are enabled). The MySQL feature binlogs the annotations based on the same --binlog-annotate-rows-events option as the master. - MWL#47 has a mysqlbinlog option --skip-annotate-rows-events, MySQL 5.6 does not. - MySQL 5.6 mysqlbinlog can log the annotations with BINLOG statements so that they are preserved with `mysqlbinlog | mysql`; MWL#47 does not have this. - MWL#47 uses binlog event type 160, MySQL 5.6 uses an ignorable event of type 29. As far as I can tell, the actual event format is identical, except for the LOG_EVENT_IGNORABLE_F flag (which says it's ok for a slave to ignore this event if it doesn't know about it, however there is no server version that knows the LOG_EVENT_IGNORABLE_F but not the MySQL annotation event). - I think there are a couple of issues with the MySQL patch (newlines in queries breaking mysqlbinlog output, duplicated annotations for INSERT DELAYED). There might be issues with the MWL#47 patch also, of course. So the question is how to prepare for when we merge MySQL 5.6. An easy option could be to just ditch MWL#47 (and possibly backport the MySQL 5.6 patch). However, as I understand it, the original sponsor of MWL#47 is already using it, and so we may have to remain compatible with the existing MWL#47 implementation. Can we get some definite information on that? Another option might be to simply change the MWL#47 to use the same event type as MySQL (the event format is the same, and we can add the LOG_EVENT_IGNORABLE_F). And replace the MWL#47 options --binlog-annotate-rows-events and --replicate-annotate-rows-events with the MySQL --binlog_rows_query_log_events option (though I think the MySQL option name is not good). But again, I am not sure if this is a valid option given possible existing usage of MWL#47. The third option I can think of is to leave MWL#47 as it is. Then when we get to merge MySQL 5.6, we will basically drop the MySQL part, doing the following to remain backwards compatible with MySQL: - Make the --binlog_rows_query_log_events option set --binlog-annotate-rows-events and --replicate-annotate-rows-events. - Make a slave read a MySQL rows_query_log_event as a MWL#47 annotation event. - Merge over the bit of the MySQL patch that makes mysqlbinlog output annotation events using a BINLOG '...' statement. This should make upgrade from MySQL 5.6 -> MariaDB fully work. Using MySQL5.6+ with MariaDB will also work, only MySQL slaves will not receive MariaDB master annotation events. The third option seems to preserve the existing MWL#47 most faithfully and does not seem to need much work in terms of the eventual merge, so this is what I suggest as the best way forward. But I am open to other (justified) opinions. - Kristian.

2 1

Re: [Maria-developers] [Commits] Rev 3014: New table creation/renaming block added if old encoded table present. in file:///home/bell/maria/bzr/work-maria-5.1-CREATE/
by Sergei Golubchik 02 Mar '11

02 Mar '11

Hi, sanja! On Jan 13, sanja(a)askmonty.org wrote: > ------------------------------------------------------------ > revno: 3014 > revision-id: sanja(a)askmonty.org-20110113180607-e5hc8zuugxxjtn6k > parent: psergey(a)askmonty.org-20110112083018-90rw058f00uslvoz > committer: sanja(a)askmonty.org > branch nick: work-maria-5.1-CREATE > timestamp: Thu 2011-01-13 20:06:07 +0200 > message: > New table creation/renaming block added if old encoded table present. you've extended the fix, but not the test case. please add a test case for the new code and ok to push Regards, Sergei

1 0

[Maria-developers] Igor please review: Rev 2929: BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init in file:///home/psergey/dev2/5.3-2/]
by Sergey Petrunya 02 Mar '11

02 Mar '11

Hello Igor, Could you please review the below: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Wed, 2 Mar 2011 00:17:53 +0300 (MSK) Subject: [Commits] Rev 2929: BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init in file:///home/psergey/dev2/5.3-2/ At file:///home/psergey/dev2/5.3-2/ ------------------------------------------------------------ revno: 2929 revision-id: psergey(a)askmonty.org-20110301211747-8wvngodwxzr9i2e9 parent: psergey(a)askmonty.org-20110301072222-1rmapkelx9l1kw8u committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-2 timestamp: Wed 2011-03-02 00:17:47 +0300 message: BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init - Make DsMrr_impl::dsmrr_init() handle the case of 1. 1st MRR scan using DS-MRR strategy (i.e. doing key sorting and rowid sorting) 2. 2nd MRR scan getting a buffer that's too small to fit one key element and one rowid element, and so falling back to default MRR implementation In this case, dsmrr_init() is invoked with {primary_handler, secondary_handler} initialized for DS-MRR scan and have to reset them to be initialized for the default MRR scan. === modified file 'mysql-test/r/maria_mrr.result' --- a/mysql-test/r/maria_mrr.result 2010-12-02 11:10:52 +0000 +++ b/mysql-test/r/maria_mrr.result 2011-03-01 21:17:47 +0000 @@ -403,3 +403,30 @@ count(*) 0 drop table t1, t2; +# +# BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init( +# +set @_save_join_cache_level= @@join_cache_level; +set @_save_join_buffer_size= @@join_buffer_size; +set join_cache_level=8; +set join_buffer_size=10000; +CREATE TABLE t1 ( +f1 int(11), f2 varchar(32) COLLATE latin1_swedish_ci, +f3 int(11), f4 varchar(1024) COLLATE utf8_bin, +f5 varchar(1024) COLLATE latin1_bin, +KEY (f5) +) ENGINE=Aria TRANSACTIONAL=0 ; +# Fill the table with some data +SELECT alias2.f1 AS field1 , alias1.f2 AS field2 +FROM t1 AS alias1 +LEFT OUTER JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 +WHERE alias2.f3 > 'k' +AND alias2.f3 <= 'z' +OR alias2.f4 IN ('o', 'y') +AND alias2.f4 >= 'g' AND alias2.f4 < 'z'; +field1 field2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'k' +set join_cache_level=@_save_join_cache_level; +set join_buffer_size=@_save_join_buffer_size; +drop table t1; === modified file 'mysql-test/t/maria_mrr.test' --- a/mysql-test/t/maria_mrr.test 2010-11-09 16:02:08 +0000 +++ b/mysql-test/t/maria_mrr.test 2011-03-01 21:17:47 +0000 @@ -125,3 +125,81 @@ table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; drop table t1, t2; + +--echo # +--echo # BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init( +--echo # +set @_save_join_cache_level= @@join_cache_level; +set @_save_join_buffer_size= @@join_buffer_size; + +set join_cache_level=8; +set join_buffer_size=10000; + +CREATE TABLE t1 ( + f1 int(11), f2 varchar(32) COLLATE latin1_swedish_ci, + f3 int(11), f4 varchar(1024) COLLATE utf8_bin, + f5 varchar(1024) COLLATE latin1_bin, + KEY (f5) +) ENGINE=Aria TRANSACTIONAL=0 ; + +--echo # Fill the table with some data +--disable_query_log +INSERT IGNORE INTO t1 VALUES +('0','cueikuirqr','0','okay','hcueikuirqrzflno'),('2009','her','0','ULWAH','ehcueikuirqrzfln'), +('0','YKAOE','0','of','qieehcueikuirqrz'),('2009','you\'re','0','k','nkqieehcueikuirq'), +('2003','b','0','bgsnkqieehcueiku','the'),('0','MGUDG','0','p','m'), +('0','UXAGU','0','elfhjawbwbgsnkqi','HZXVA'),('2004','bwbgsnkqie','0','x','something'), +('2007','s','0','okay','slelfhjawbwbgsnk'),('2007','the','0','or','if'), +('2008','TDLKE','0','mean','MGWNJ'),('2006','do','0','KGFVV','see'), +('2001','why','0','a','mean'),(NULL,'THKCG','0','no','YFLDY'), +(NULL,'x','0','mean','e'),('2007','yncitaeysb','0','WDOSC','tgyncitaeysbgucs'), +(NULL,'ZEOXX','0','awbwbgsnkqieehcu','jawbwbgsnkqieehc'),('2006','hjawbwbgsn','0','just','fhjawbwbgsnkqiee'), +('0','all','0','k','sbgucsgqslelfhja'),('2009','the','0','f','would'), +('2005','mtgyncitae','0','w','ISNQQ'),('2003','KNCUI','0','r','want'), +('2006','is','0','j','i'),('2000','out','0','o','jvcmjlmtgyncitae'), +('2001','it','0','txyjvcmjlmtgynci','you'),('2008','LHDIH','0','ZKFJK','txmtxyjvcmjlmtgy'), +('0','z','0','the','ntxmtxyjvcmjlmtg'),(NULL,'vyhnmvgmcn','0','PWEYL','AIGQK'), +('0','ytvyhnmvgm','0','gbytvyhnmvgmcntx','z'),('2002','t','0','just','on'), +('2002','xqegbytvyh','0','to','ixqegbytvyhnmvgm'),('2004','WGVRU','0','GHBZB','h'), +('0','b','0','yhnmvgmcntxmtxyj','z'),('2003','who','0','ddixqegbytvyhnmv','gddixqegbytvy'), +('0','PMLFL','0','n','vgmcntxmtxyjvcmj'),(NULL,'back','0','FIXPA','n'), +('2005','i','0','I\'ll','PZGUB'),('0','f','0','t','the'), +(NULL,'PNXVP','0','t','v'),('2004','MAKKL','0','u','CGCWF'), +('0','RMDAV','0','noypgddixqegbytv','v'),('0','l','0','ESAVW','n'), +('2008','rhnoypgddi','0','s','VIZNE'),('2007','t','0','XZECS','a'), +(NULL,'like','0','k','JSHPZ'),('2009','pskeywslmk','0','epskeywslmkdrhno','q'), +(NULL,'QZZJJ','0','LOTFB','c'),('0','atlxepskey','0','b','YJRMA'), +(NULL,'YUVOU','0','wslmkdrhnoypgddi','eywslmkdrhnoypgd'),('2006','some','0','can','r'), +('2002','c','0','k','her'),(NULL,'o','0','ALBVZ','EMURT'), +('0','if','0','okay','had'),('2006','when','0','e','CLVWT'), +(NULL,'blfufrcdjm','0','ZAKUQ','IZCZN'),('2007','vutblfufrc','0','MPKMI','how'), +(NULL,'why','0','UBUAG','I'),('2004','IXLYQ','0','ILPBE','weuwuvutblfufrcd'), +('0','here','0','your','m'),(NULL,'ZOCTJ','0','GTPOO','IDSFD'), +('0','kqsweuwuvu','0','CXQLW','oh'),('0','ykqsweuwuv','0','CLJWP','zykqsweuwuvutblf'), +('0','zezykqsweu','0','had','t'),(NULL,'q','0','jisuzezykqsweuwu','o'), +('2007','IBKAU','0','t','oh'),('0','ivjisuzezy','0','y','XHXKE'), +('0','xsivjisuze','0','right','plxsivjisuzezykq'),('2009','have','0','so','uvplxsivjisuzezy'), +('0','on','0','b','me'),('0','ijkfuvplxs','0','RZXDY','OGEHV'), +('0','u','0','all','okay'),('0','i','0','s','pajzbbojshnijkfu'), +('2001','of','0','xpajzbbojshnijkf','g'),('0','for','0','ITBAQ','I\'m'), +('2006','or','0','now','ZOJHX'),('0','n','0','k','you'), +(NULL,'that','0','suzezykqsweuwuvu','just'),('0','bbojshnijk','0','QGYOA','JYGSJ'), +('2009','k','0','ajzbbojshnijkfuv','y'),('2007','k','0','affpegvavdgxpajz','y'), +('2007','be','0','RCTIT','m'),('2000','fnbmxwicrk','0','lcfnbmxwicrkdyma','t'), +('0','yaffpegvav','0','v','have'),('0','crkdymahya','0','icrkdymahyaffpeg','QQWQI'), +('0','t','0','nijkfuvplxsivjis','hnijkfuvplxsivji'),('0','dgxpajzbbo','0','vdgxpajzbbojshni','vavdgxpajzbbojsh'), +('2006','g','0','gvavdgxpajzbbojs','pegvavdgxpajzbbo'),('2007','I\'m','0','g','ffpegvavdgxpajzb'); +--enable_query_log + + +SELECT alias2.f1 AS field1 , alias1.f2 AS field2 +FROM t1 AS alias1 +LEFT OUTER JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 +WHERE alias2.f3 > 'k' +AND alias2.f3 <= 'z' +OR alias2.f4 IN ('o', 'y') +AND alias2.f4 >= 'g' AND alias2.f4 < 'z'; + +set join_cache_level=@_save_join_cache_level; +set join_buffer_size=@_save_join_buffer_size; +drop table t1; === modified file 'sql/multi_range_read.cc' --- a/sql/multi_range_read.cc 2011-02-18 22:31:01 +0000 +++ b/sql/multi_range_read.cc 2011-03-01 21:17:47 +0000 @@ -907,7 +907,26 @@ DBUG_RETURN(res); use_default_impl: - DBUG_ASSERT(primary_file->inited == handler::INDEX); + if (primary_file->inited != handler::INDEX) + { + /* We can get here when + - we've previously successfully done a DS-MRR scan (and so have + secondary_file!= NULL, secondary_file->inited= INDEX, + primary_file->inited=RND) + - for this invocation, we haven't got enough buffer space, and so we + have to use the default MRR implementation. + + note: primary_file->ha_index_end() will call dsmrr_close() which will + close/destroy the secondary_file, this is intentional. + (Yes this is slow, but one can't expect performance with join buffer + so small that it can accomodate one rowid and one index tuple) + */ + if ((res= primary_file->ha_rnd_end()) || + (res= primary_file->ha_index_init(keyno, test(mode & HA_MRR_SORTED)))) + { + DBUG_RETURN(res); + } + } /* Call correct init function and assign to top level object */ Mrr_simple_index_reader *s= &reader_factory.simple_index_reader; res= s->init(primary_file, seq_funcs, seq_init_param, n_ranges, mode, NULL, _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1