- developers - lists.mariadb.org

[Maria-developers] Please review: Rev 3430: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN in file:///home/psergey/dev2/5.3-look55/
by Sergei Petrunia 20 Feb '12

20 Feb '12

Hi Monty, 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: Mon, 20 Feb 2012 20:04:54 +0400 (GST) Subject: [Commits] Rev 3430: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN in file:///home/psergey/dev2/5.3-look55/ At file:///home/psergey/dev2/5.3-look55/ ------------------------------------------------------------ revno: 3430 revision-id: psergey(a)askmonty.org-20120220160453-jtj27fub5fxl9re4 parent: psergey(a)askmonty.org-20120220113450-0foyjou7b1h0bid6 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-look55 timestamp: Mon 2012-02-20 20:04:53 +0400 message: BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN - In mi_rkey(), do correct handling of case where mi_yield_and_check_if_killed() detects that the thread was killed (all other similar functions in MyISAM/Aria have slightly different code and do not have this problem). - Also fixed assignment in DBUG_ASSERT. === modified file 'mysql-test/r/myisam_icp.result' --- a/mysql-test/r/myisam_icp.result 2012-02-16 06:49:10 +0000 +++ b/mysql-test/r/myisam_icp.result 2012-02-20 16:04:53 +0000 @@ -909,4 +909,98 @@ c NULL NULL SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; +# +# BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN +# +CREATE TABLE t1 ( +b INT, +c VARCHAR(1) NOT NULL, +d DATETIME, +KEY (c, b) +) ENGINE=MyISAM; +INSERT INTO t1 ( b, d, c ) VALUES +(4,'2005-01-08 00:00:00','f'), +(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'), +(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'), +(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'), +(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'), +(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'), +(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'), +(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'), +(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'), +(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'), +(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'), +(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'), +(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'), +(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'), +(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'), +(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'), +(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'), +(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'), +(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'), +(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'), +(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'), +(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'), +(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'), +(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'), +(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'), +(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'), +(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'), +(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'), +(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'), +(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'), +(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'), +(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'), +(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'), +(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'), +(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'), +(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'), +(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'), +(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'), +(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'), +(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'), +(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'), +(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'), +(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'), +(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'), +(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'), +(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'), +(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'), +(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'), +(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'), +(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'), +(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'), +(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'), +(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'), +(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'), +(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'), +(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'), +(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'), +(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'), +(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'), +(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'), +(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'), +(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'), +(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'), +(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'), +(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'), +(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'), +(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'), +(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'), +(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'), +(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'), +(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'), +(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'), +(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'), +(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'), +(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'), +(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'), +(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'), +(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'), +(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r'); +CREATE TABLE t2 ( a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(7),(3),(7),(3); +# Now run a number of ICP queries while trying to kill them +DROP TABLE t1,t2; set optimizer_switch=@myisam_icp_tmp; === modified file 'mysql-test/t/myisam_icp.test' --- a/mysql-test/t/myisam_icp.test 2011-12-15 08:21:15 +0000 +++ b/mysql-test/t/myisam_icp.test 2012-02-20 16:04:53 +0000 @@ -262,4 +262,126 @@ DROP TABLE t1,t2,t3,t4; +--echo # +--echo # BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN +--echo # +CREATE TABLE t1 ( + b INT, + c VARCHAR(1) NOT NULL, + d DATETIME, + KEY (c, b) +) ENGINE=MyISAM; +INSERT INTO t1 ( b, d, c ) VALUES +(4,'2005-01-08 00:00:00','f'), +(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'), +(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'), +(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'), +(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'), +(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'), +(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'), +(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'), +(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'), +(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'), +(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'), +(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'), +(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'), +(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'), +(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'), +(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'), +(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'), +(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'), +(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'), +(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'), +(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'), +(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'), +(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'), +(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'), +(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'), +(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'), +(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'), +(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'), +(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'), +(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'), +(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'), +(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'), +(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'), +(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'), +(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'), +(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'), +(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'), +(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'), +(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'), +(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'), +(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'), +(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'), +(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'), +(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'), +(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'), +(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'), +(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'), +(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'), +(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'), +(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'), +(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'), +(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'), +(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'), +(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'), +(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'), +(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'), +(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'), +(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'), +(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'), +(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'), +(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'), +(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'), +(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'), +(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'), +(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'), +(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'), +(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'), +(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'), +(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'), +(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'), +(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'), +(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'), +(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'), +(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'), +(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'), +(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'), +(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'), +(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'), +(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r'); + +CREATE TABLE t2 ( a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES + (7),(3),(7),(3); + +--let $run = 300 +--let $con_id = `SELECT CONNECTION_ID()` + +--echo # Now run a number of ICP queries while trying to kill them +--disable_query_log +--disable_result_log +while ($run) +{ + --send + SELECT * FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 + ON alias2.c = alias1.c + WHERE alias2.b >= 9; + + --connect (con1,127.0.0.1,root,,test) + --eval KILL QUERY $con_id + --disconnect con1 + + --dec $run + + --connection default + --error 0,ER_QUERY_INTERRUPTED + --reap +} +--enable_result_log +--enable_query_log +DROP TABLE t1,t2; + set optimizer_switch=@myisam_icp_tmp; + === modified file 'storage/myisam/mi_rkey.c' --- a/storage/myisam/mi_rkey.c 2011-02-18 19:45:32 +0000 +++ b/storage/myisam/mi_rkey.c 2012-02-20 16:04:53 +0000 @@ -157,7 +157,11 @@ mi_yield_and_check_if_killed(info, inx)) { /* Aborted by user */ + DBUG_ASSERT(info->lastpos == HA_OFFSET_ERROR && + my_errno == HA_ERR_ABORTED_BY_USER); + res= ICP_ERROR; buf= 0; /* Fast abort */ + break; } } if (res == ICP_OUT_OF_RANGE) @@ -179,7 +183,7 @@ } else { - DBUG_ASSERT(info->lastpos= HA_OFFSET_ERROR); + DBUG_ASSERT(info->lastpos == HA_OFFSET_ERROR); } } if (share->concurrent_insert) _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- 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 3335: Fix bug lp:825075 in file:///home/tsk/mprog/src/5.3/
by Sergei Petrunia 17 Feb '12

17 Feb '12

Hi Timour, Feedback follows skype discussion: First, here's a general comment Consider a query: SELECT b, min(a) FROM t1 GROUP BY b; Suppose there is an index (b,a). Loose scan would execute it as follows: S00 h->index_first(); S01 produce an output record of (b, a) // first value of 'a' is the minimum S02 S03 h->read_next_different_b(); // Jump to the next GROUP-BY group. S04 ... S05 If we modify the query by adding a WHERE: SELECT b, min(a) FROM t1 WHERE arbitrary_cond(a,b) GROUP BY b; then LooseScan (in its current form) will be unable to execute it. The problem is here: S00 h->index_first(); S01 produce an output record of (b, a) // first value of 'a' is the minimum ^^^ what if this record doesn't match the WHERE? We'll have to scan further within the record having b=first_record.b until we find a match. LooseScan executor doesn't support such "walk forward" action currently. If the WHERE clause has been converted into a range, such that there are no records that would be contained in the range but not match the WHERE clause (***) then LooseScan is able to handle it. Example: SELECT b, min(a) FROM t1 WHERE a>10 GROUP BY b; Execution would proceed as follows: S00 h->index_first(); S00 h->index_read(HA_READ_AFTER_KEY, {current_row.b, 'a>10'}) S01 produce an output record of (b, a) // first value of 'a>10' is the minimum Step S01 will produce correct results as long as the following holds: "The first record we've got in a range will satisfy the WHERE condition" This is not always the case. Here's a counterexample: SELECT b, min(a) FROM t1 WHERE a>10 GROUP BY b; SELECT b, min(a) FROM t1 WHERE a>10 and ((a MOD 2) = 0) GROUP BY b; -- (C1) Consider the second query and this dataset: b , a 1, 5 1, 7 1, 11 1, 13 1, 14 range optimizer would give a range of "a > 10", and the first matching record is (1,11). It doesn't satisfy the "(a MOD 2)=0" condition, neither does the next record of (1,13), and only the record (1,14) will satisfy the MOD part of the condition. The problem: current code doesn't seem to have the logic to detect such cases. The submitted patch disables LooseScan for apparently invalid cases where the WHERE condition covers a column while range condition does not, which means that condition (***) is certainly unmet. However, cases like (C1) are still not handled. Further comments are inline. On Wed, Dec 07, 2011 at 02:18:02PM +0200, Timour Katchaounov wrote: > Could you please review the below patch. > > Timour > > ----------------------------------------------------------- > revno: 3335 > revision-id: timour(a)askmonty.org-20111207115332-3oa536gbt20r22po > parent: igor(a)askmonty.org-20111206214218-lev0vyidyt8h3431 > fixes bug(s): https://launchpad.net/bugs/825075 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Wed 2011-12-07 13:53:32 +0200 > message: > Fix bug lp:825075 > > Analys: > The cause for the wrong result was that the optimizer > incorrectly chose min/max loose scan when it is not > applicable. The applicability test missed the case when > a condition on the MIN/MAX argument was OR-ed with a > condition on some other field. In this case, the MIN/MAX > condition cannot be used for loose scan. > > Solution: > The function check_group_min_max_predicates() duplicated > functionality performed by the range optimizer, but this > duplication was incomplete. The solution is to reuse instead > the analys and the ranges produced by the range optimizer. > If a suitable range could not be extracted for the MIN/MAX > argument, then loose scan is not applicable. > === modified file 'mysql-test/r/group_min_max.result' > --- a/mysql-test/r/group_min_max.result 2011-12-04 21:31:42 +0000 > +++ b/mysql-test/r/group_min_max.result 2011-12-07 11:53:32 +0000 > @@ -2103,7 +2103,7 @@ id select_type table type possible_keys > explain select a1,a2,b,min(c),max(c) from t2 > where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; > id select_type table type possible_keys key key_len ref rows Extra > -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index > +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 33 Using where; Using index for group-by > explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort Why there is no testcase from the bug report? I think it's better to have a testcase that - will explicitly mention BUG# - will show the wrong query result. A changed EXPLAIN in some irrelevant test is a too poor coverage. > === modified file 'sql/opt_range.cc' > --- a/sql/opt_range.cc 2011-11-18 17:35:51 +0000 > +++ b/sql/opt_range.cc 2011-12-07 11:53:32 +0000 > @@ -11984,164 +11996,41 @@ get_best_group_min_max(PARAM *param, SEL > } > > > -/* > - Check that the MIN/MAX attribute participates only in range predicates > - with constants. > - > - SYNOPSIS > - check_group_min_max_predicates() > - cond tree (or subtree) describing all or part of the WHERE > - clause being analyzed > - min_max_arg_item the field referenced by the MIN/MAX function(s) > - min_max_arg_part the keypart of the MIN/MAX argument if any > - > - DESCRIPTION > - The function walks recursively over the cond tree representing a WHERE > - clause, and checks condition (SA3) - if a field is referenced by a MIN/MAX > - aggregate function, it is referenced only by one of the following > - predicates: {=, !=, <, <=, >, >=, between, is null, is not null}. > +/** > + Find the range tree for the field that is an argument of MIN/MAX. > > - RETURN > - TRUE if cond passes the test > - FALSE o/w > + @param cond The WHERE clause of the query > + @param min_max_arg_item The field referenced by the MIN/MAX function(s) > + @param index_tree Range tree for the chosen index > + > + @note > + The function iterates over the range trees for each keypart of the > + chosen index until it finds the range for the MIN/MAX keypart. > + > + @retval > + If there is a range for the MIN/MAX argument > + @retval > + NULL if there is no range > */ > > -static bool > -check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, > - Field::imagetype image_type) > +static SEL_ARG * > +get_min_max_range(Item *cond, Item_field *min_max_arg_item, SEL_ARG *index_tree) > { > - DBUG_ENTER("check_group_min_max_predicates"); > - DBUG_ASSERT(cond && min_max_arg_item); > - > + if (!cond || !min_max_arg_item) > + return NULL; > cond= cond->real_item(); So we've got: get_min_max_range(Item *cond, Item_field *min_max_arg_item, SEL_ARG *index_tree) { if (!cond || !min_max_arg_item) return NULL; cond= cond->real_item(); 'cond' is passed as parameter, checked, modified, and .. then never used in the function. Please fix this. > - Item::Type cond_type= cond->real_type(); > - if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */ > - { > - DBUG_PRINT("info", ("Analyzing: %s", ((Item_func*) cond)->func_name())); > - List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list()); > - Item *and_or_arg; > - while ((and_or_arg= li++)) > - { > - if (!check_group_min_max_predicates(and_or_arg, min_max_arg_item, > - image_type)) > - DBUG_RETURN(FALSE); > - } > - DBUG_RETURN(TRUE); > - } > - > - /* > - Disallow loose index scan if the MIN/MAX argument field is referenced by > - a subquery in the WHERE clause. > - */ > - > - if (cond_type == Item::SUBSELECT_ITEM) > - { > - Item_subselect *subs_cond= (Item_subselect*) cond; > - if (subs_cond->is_correlated) > - { > - DBUG_ASSERT(subs_cond->upper_refs.elements > 0); > - List_iterator_fast<Item_subselect::Ref_to_outside> > - li(subs_cond->upper_refs); > - Item_subselect::Ref_to_outside *dep; > - while ((dep= li++)) > - { > - if (dep->item->eq(min_max_arg_item, FALSE)) > - DBUG_RETURN(FALSE); > - } > - } > - DBUG_RETURN(TRUE); > - } > - > /* > - Condition of the form 'field' is equivalent to 'field <> 0' and thus > - satisfies the SA3 condition. > + Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX > + attribute. > */ > - if (cond_type == Item::FIELD_ITEM) > + SEL_ARG *min_max_range= index_tree; > + while (min_max_range) /* Find the tree for the MIN/MAX key part. */ > { > - DBUG_PRINT("info", ("Analyzing: %s", cond->full_name())); > - DBUG_RETURN(TRUE); > - } > - > - /* We presume that at this point there are no other Items than functions. */ > - DBUG_ASSERT(cond_type == Item::FUNC_ITEM); > - > - /* Test if cond references only group-by or non-group fields. */ > - Item_func *pred= (Item_func*) cond; > - Item **arguments= pred->arguments(); > - Item *cur_arg; > - DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); > - for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++) > - { > - cur_arg= arguments[arg_idx]->real_item(); > - DBUG_PRINT("info", ("cur_arg: %s", cur_arg->full_name())); > - if (cur_arg->type() == Item::FIELD_ITEM) > - { > - if (min_max_arg_item->eq(cur_arg, 1)) > - { > - /* > - If pred references the MIN/MAX argument, check whether pred is a range > - condition that compares the MIN/MAX argument with a constant. > - */ > - Item_func::Functype pred_type= pred->functype(); > - if (pred_type != Item_func::EQUAL_FUNC && > - pred_type != Item_func::LT_FUNC && > - pred_type != Item_func::LE_FUNC && > - pred_type != Item_func::GT_FUNC && > - pred_type != Item_func::GE_FUNC && > - pred_type != Item_func::BETWEEN && > - pred_type != Item_func::ISNULL_FUNC && > - pred_type != Item_func::ISNOTNULL_FUNC && > - pred_type != Item_func::EQ_FUNC && > - pred_type != Item_func::NE_FUNC) > - DBUG_RETURN(FALSE); > - > - /* Check that pred compares min_max_arg_item with a constant. */ > - Item *args[3]; > - bzero(args, 3 * sizeof(Item*)); > - bool inv; > - /* Test if this is a comparison of a field and a constant. */ > - if (!simple_pred(pred, args, &inv)) > - DBUG_RETURN(FALSE); > - > - /* Check for compatible string comparisons - similar to get_mm_leaf. */ > - if (args[0] && args[1] && !args[2] && // this is a binary function > - min_max_arg_item->result_type() == STRING_RESULT && > - /* > - Don't use an index when comparing strings of different collations. > - */ > - ((args[1]->result_type() == STRING_RESULT && > - image_type == Field::itRAW && > - ((Field_str*) min_max_arg_item->field)->charset() != > - pred->compare_collation()) > - || > - /* > - We can't always use indexes when comparing a string index to a > - number. > - */ > - (args[1]->result_type() != STRING_RESULT && > - min_max_arg_item->field->cmp_type() != args[1]->result_type()))) > - DBUG_RETURN(FALSE); > - } > - } > - else if (cur_arg->type() == Item::FUNC_ITEM) > - { > - if (!check_group_min_max_predicates(cur_arg, min_max_arg_item, > - image_type)) > - DBUG_RETURN(FALSE); > - } > - else if (cur_arg->const_item()) > - { > - /* > - For predicates of the form "const OP expr" we also have to check 'expr' > - to make a decision. > - */ > - continue; > - } > - else > - DBUG_RETURN(FALSE); > + if (min_max_range->field->eq(min_max_arg_item->field)) > + break; > + min_max_range= min_max_range->next_key_part; > } > - > - DBUG_RETURN(TRUE); > + return min_max_range; > } > > BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

[Maria-developers] MariaDB 5.3.4 release status
by Sergei Petrunia 15 Feb '12

15 Feb '12

Hi, I've pushed the last bug fix slated for MariaDB 5.3.4, so revno:3419 has a chance to become the 5.3.4 release. It is currently building in buildbot. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 7

Re: [Maria-developers] [Commits] Rev 3421: Counters for Index Condition Pushdown added (MDEV-130). in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-130/
by Sergei Petrunia 15 Feb '12

15 Feb '12

Hi Sanja, On Wed, Feb 15, 2012 at 04:13:54PM +0200, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-130/ > > ------------------------------------------------------------ > revno: 3421 > revision-id: sanja(a)montyprogram.com-20120215141351-rk4pi025utdztzt8 > parent: holyfoot(a)askmonty.org-20120214120406-02q2mv2t6xb37dno > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.3-MDEV-130 > timestamp: Wed 2012-02-15 16:13:51 +0200 > message: > Counters for Index Condition Pushdown added (MDEV-130). OK to push. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Failure in plugins.feedback_plugin_send
by Kristian Nielsen 14 Feb '12

14 Feb '12

Hi Serg, 5.5 Buildbot is getting greener, but there are still a couple of random failures left. One of them is in plugins.feedback_plugin_send: ----------------------------------------------------------------------- plugins.feedback_plugin_send w1 [ fail ] Test ended at 2012-02-12 00:41:06 CURRENT_TEST: plugins.feedback_plugin_send --- /home/buildbot/build/mysql-test/suite/plugins/r/feedback_plugin_send.result 2012-02-11 21:14:54.336622000 +0200 +++ /home/buildbot/build/mysql-test/suite/plugins/r/feedback_plugin_send.reject 2012-02-12 00:41:05.785412054 +0200 @@ -9,7 +9,4 @@ FEEDBACK_SEND_TIMEOUT 60 FEEDBACK_URL http://mariadb.org/feedback_plugin/post FEEDBACK_USER_INFO mysql-test -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' -feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent -feedback plugin: server replied 'ok' +feedback plugin: failed to send report to 'http://mariadb.org/feedback_plugin/post' mysqltest: Result length mismatch ----------------------------------------------------------------------- Any ideas? The test has been failing for a while, though rarely (but maybe some of the past failures have been fixed): http://buildbot.askmonty.org/buildbot/reports/cross_reference#branch=&revis… - Kristian.

2 1

[Maria-developers] Please try new fulltext storage engine "mroonga"
by Kazuhiko Shiozaki 07 Feb '12

07 Feb '12

Hello ! I would like to introduce a new fulltext storage engine "mroonga", that will be included in the future MariaDB release. Mroonga is a fulltext storage engine having the following characteristics: * CJK ready fulltext search * no read lock * faster than MyISAM for both indexing and searching * geolocalazation search is also supported Current development branch is : https://code.launchpad.net/~maria-captains/maria/mariadb-5.3-monty Further information about mroonga can be found : https://kb.askmonty.org/en/about-mroonga You can also see some presentations : http://mroonga.github.com/publication/ I sincerely ask you to try mroonga, at least compile and run tests, on various platforms, like the following procedure. (get source tree) $ ./BUILD/compile-pentium-max (wait for a while) $ cd mysql-test $ ./mtr --suite=mroonga_storage,mroonga_wrapper --force If you find any problem like compilation error, test failure, or whatever, please let me know. Thanks in advance ! -- Kazuhiko Shiozaki, Nexedi SA Senior Consultant Nexedi: Consulting and Development of Free / Open Source Software http://www.nexedi.co.jp/ ERP5: Full Featured High End Open Source ERP http://www.erp5.com/

1 0

[Maria-developers] New KB Question: SSL with other Clients than the original MariaDB-client?
by Daniel Bartholomew 03 Feb '12

03 Feb '12

A new question has been posted to the Knowledgebase. http://kb.askmonty.org/en/ssl-with-other-clients-than-the-original-mariadb-… ==================== It seems to be that there is a difference in handling the SSL handshake at the MariaDB-Server (5.2 or below) in contrast to the Mysql-Server 5.1. All JDBC-Clients can sucessfully connect to the Mysql-Server 5.1 via SSL, but with MariaDB-Server not. Without SSL all JDBC-Clients can sucessfully connect to MariaDB. Both server run with the same CA-, Server- and Client-Certificates and I have made the following tests (all with MariaDB Server 5.2.10 and also tested with 5.1.60 from the Deb-Repository): * Mysql-Client (5.1) cannot connect to MariaDB Server: "ERROR 2026 (HY000): SSL connection error" * Mysql-connector/J cannot connect to MariaDB Server: TLSv1 Handshake fails with "unexpected message" after ClientHello * Drizzle JDBC cannot connect to MariaDB-Server: TLSv1 Handshake fails also with "unexpected message" after ClientHello * Original MariaDB-Client (5.2) can connect easily via SSL with the CA-Certificate to MariaDB-Server. It seems to be a general communication problem in the SSL-Handshake after ClientHello. Is it a bug or a feature? Debug-Log of Java: <<fixed>> trigger seeding of SecureRandom done seeding SecureRandom Allow unsafe renegotiation: false Allow legacy hello messages: true Is initial handshake: true Is secure renegotiation: false %% No cached client session ClientHello, TLSv1 RandomCookie: GMT: 1328138424 bytes Session ID: {} Cipher Suites: [SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA, \\TLS_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, \\TLS_DHE_DSS_WITH_AES_128_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA, \\SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA, \\SSL_RSA_WITH_DES_CBC_SHA, SSL_DHE_RSA_WITH_DES_CBC_SHA, \\ SSL_DHE_DSS_WITH_DES_CBC_SHA, SSL_RSA_EXPORT_WITH_RC4_40_MD5, \\SSL_RSA_EXPORT_WITH_DES40_CBC_SHA, SSL_DHE_RSA_EXPORT_WITH_DES40_CBC_SHA, \\SSL_DHE_DSS_EXPORT_WITH_DES40_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV] \\ Compression Methods: { 0 } \\ [write] MD5 and SHA1 hashes: len = 75 \\ main, WRITE: TLSv1 Handshake, length = 75 \\ [Raw write]: length = 80 \\ [Raw read]: length = 5 \\ 0000: 16 00 00 02 FF .....\\ main, handling exception: javax.net.ssl.SSLException: Unsupported record version Unknown-0.0\\ main, SEND TLSv1 ALERT: fatal, description = unexpected_message\\ main, WRITE: TLSv1 Alert, length = 2\\ <</fixed>> ==================== Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] New KB Question: How can I install the pam_authentication plugin in deb based system?
by Daniel Bartholomew 01 Feb '12

01 Feb '12

A new question has been posted in the Knowledgebase: How can I install the pam_authentication plugin in deb based system? http://kb.askmonty.org/en/how-can-i-install-the-pam_authentication-plugin-i… ========== Hi all, I've been reading the pam_auth plugin howto: https://kb.askmonty.org/en/pam-authentication-plugin MariaDB [test]> INSTALL PLUGIN pam SONAME 'auth_pam.so'; ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/auth_pam.so' (errno: 2 cannot open shared object file: No such file or directory) Indeed that file does not exist on the system. Nor does the pam_mariadb_mtr.so file. I installed mariadb-server-5.2 using the deb repos on Ubuntu 10.04 64bit (Lucid). Is there any deb archive to include those files in binary? Or do I have to resort to source compilation? ========== Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] Preparing to release MariaDB 5.3.4-rc
by Daniel Bartholomew 31 Jan '12

31 Jan '12

All, Monty has informed me that we are ready to release MariaDB 5.3.4-rc. I'm uploading the packages to the primary mirror now and if all goes well the general release announcement will go out tomorrow morning (my time). The source tree has not been tagged yet but the release is from revision 3402: http://bazaar.launchpad.net/~maria-captains/maria/5.3/changes To all the devs: If there is anything notable which I should make sure to include in the release notes, please let me know ASAP. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] Is our annotate_rows_log_event same as mysql-5.6's?
by Kristian Nielsen 31 Jan '12

31 Jan '12

Sergei Petrunia <psergey(a)askmonty.org> writes: > Also, it seems that right now binary logs are not compatible between mysql-5.6 > and mariadb-5.3. When I try read one file with mysqlbinlog of the other I get > this: > > > mariadb-5.3 reading binlog from mysql-5.6 > #120131 2:43:14 server id 1 end_log_pos 295 Query thread_id=2 exec_time=0 error_code=0 > SET TIMESTAMP=1327963394/*!*/; > BEGIN > /*!*/; > ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 62, event_type: 29 > ERROR: Could not read entry at offset 295: Error in log format or read error. Yes, 29 is ROWS_QUERY_LOG_EVENT, their version of annotate_rows_log_event. I think you will find the same problem if you try to read with MySQL 5.1 or 5.5. I believe this only happens if you enable row annotation events on the master; it seems a general thing that if one enables a new feature on a MySQL master, it breaks old slaves. However, when we merge 5.6, we get the new IGNORABLE_LOG_EVENT flag, which allows a newer master (eg. 5.7) to inform an old slave that it is ok to ignore a specific event. When we merge 5.6, I suppose we will just make sure MariaDB 5.6 writes the MySQL event code on the master, and understands the MariaDB event code on the slave, so this will be solved. > Mysql 5.6 reading mariadb-5.3 > ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 48, event_type: -96 > ERROR: Could not read entry at offset 503: Error in log format or read error. > # at 434 > #120131 2:01:47 server id 1 end_log_pos 503 Query thread_id=1 exec_time=0 error_code=0 > SET TIMESTAMP=1327960907/*!*/; > BEGIN And this is the reverse situation, -96 is our ANNOTATE_ROWS_EVENT, which is not understood by MySQL. Again, this only happens when row annotation is explicitly enabled on the master, so one should not do this when having old slaves connected (whether MySQL or MariaDB). There is a deeper issue here. It is not possible on the master to simply filter out the events that an old slave does not understand, as would seem natural. Because the current slave code gets confused when there are "holes" in the binlog positions it receives from the master. In MariaDB 5.5 this will be fixed (if I'm allowed to push MWL#234 ...), allowing a MariaDB slave to adjust binlog positions correctly if the master filters out events. But this does not help MySQL slaves of course. Also, global transaction IDs, which MySQL@Oracle says they are working on, should fix this, but this is not in 5.6 currently. Thanks for taking up these subjects, it's good to get them more clearly understood! - Kristian.

1 0