- developers - lists.mariadb.org

[Maria-developers] WL#249 New (by Monty): Locking SQL_MODE against client changes
by worklog-noreply＠askmonty.org 21 Feb '12

21 Feb '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Locking SQL_MODE against client changes CREATION DATE..: Tue, 21 Feb 2012, 19:36 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 249 (http://askmonty.org/worklog/?tid=249) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 4 (hours remain) ORIG. ESTIMATE.: 4 PROGRESS NOTES: DESCRIPTION: Add an option to lock sql_mode against client-side changes. For example: mysqld --lock_sql_mode ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Please review: Rev 3275: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050
by Sergei Petrunia 20 Feb '12

20 Feb '12

Hello Sanja, Igor, Could you please review the below? The new code, Item_cache::walk() passes the main testsuite and fixed the bug, however I'm concerned it may have implications that I am not aware of. ----- 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: Tue, 21 Feb 2012 01:50:51 +0400 (GST) Subject: [Commits] Rev 3275: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 in file:///home/psergey/dev2/5.5-look5/ At file:///home/psergey/dev2/5.5-look5/ ------------------------------------------------------------ revno: 3275 revision-id: psergey(a)askmonty.org-20120220215050-ovuvi8qigyidenum parent: psergey(a)askmonty.org-20120220210822-6yumujmervtemmwy committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.5-look5 timestamp: Tue 2012-02-21 01:50:50 +0400 message: BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 - MySQL 5.5 introduced caching of constant items by means of wrapping them in Item_cache_XXX objects. If a subquery was wrapped in this cache, it could end up being pushed down by ICP. - The fix is to add Item_cache::walk() which lets ICP to see that the cache item has a subquery inside it, and disable pushdown for this case. === modified file 'mysql-test/r/innodb_icp.result' --- a/mysql-test/r/innodb_icp.result 2012-01-13 14:50:02 +0000 +++ b/mysql-test/r/innodb_icp.result 2012-02-20 21:50:50 +0000 @@ -808,5 +808,34 @@ 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# +# BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 +# +CREATE TABLE t1 ( a INT ) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (7),(7); +CREATE TABLE t2 ( b VARCHAR(1) ); +INSERT INTO t2 VALUES ('j'),('v'); +CREATE TABLE t3 ( +c INT, d VARCHAR(1), e VARCHAR(1), +KEY (d,c) +) ENGINE=InnoDB; +INSERT INTO t3 +VALUES (6,'w','w'), +(1,'v','v'),(7,'s','s'),(4,'l','l'), +(7,'y','y'),(0,'c','c'),(2,'i','i'), +(9,'h','h'),(4,'q','q'),(0,'a','a'), +(9,'v','v'),(1,'u','u'),(3,'s','s'), +(8,'z','z'),(1,'h','h'),(8,'p','p'), +(6,'e','e'),(3,'i','i'),(6,'y','y'); +SELECT * +FROM t1 INNER JOIN t2 INNER JOIN t3 +ON d = b +WHERE +NOT EXISTS ( SELECT * FROM t3 ) +OR a = c +ORDER BY e; +a b c d e +DROP TABLE t1,t2,t3; set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; === modified file 'mysql-test/t/innodb_icp.test' --- a/mysql-test/t/innodb_icp.test 2011-07-08 14:46:47 +0000 +++ b/mysql-test/t/innodb_icp.test 2012-02-20 21:50:50 +0000 @@ -12,6 +12,39 @@ --source include/icp_tests.inc +--echo # +--echo # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 +--echo # +CREATE TABLE t1 ( a INT ) + ENGINE=InnoDB; +INSERT INTO t1 VALUES (7),(7); + +CREATE TABLE t2 ( b VARCHAR(1) ); +INSERT INTO t2 VALUES ('j'),('v'); + +CREATE TABLE t3 ( + c INT, d VARCHAR(1), e VARCHAR(1), + KEY (d,c) +) ENGINE=InnoDB; +INSERT INTO t3 + VALUES (6,'w','w'), + (1,'v','v'),(7,'s','s'),(4,'l','l'), + (7,'y','y'),(0,'c','c'),(2,'i','i'), + (9,'h','h'),(4,'q','q'),(0,'a','a'), + (9,'v','v'),(1,'u','u'),(3,'s','s'), + (8,'z','z'),(1,'h','h'),(8,'p','p'), + (6,'e','e'),(3,'i','i'),(6,'y','y'); + +SELECT * +FROM t1 INNER JOIN t2 INNER JOIN t3 + ON d = b +WHERE + NOT EXISTS ( SELECT * FROM t3 ) + OR a = c +ORDER BY e; + +DROP TABLE t1,t2,t3; + set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; === modified file 'sql/item.h' --- a/sql/item.h 2012-02-15 17:08:08 +0000 +++ b/sql/item.h 2012-02-20 21:50:50 +0000 @@ -3901,6 +3901,12 @@ return example->is_expensive_processor(arg); } virtual void set_null(); + bool walk(Item_processor processor, bool walk_subquery, uchar *arg) + { + if (example && example->walk(processor, walk_subquery, arg)) + return TRUE; + return (this->*processor)(arg); + } }; _______________________________________________ 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

[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