- developers - lists.mariadb.org

[Maria-developers] MariaDB 5.5.20 Alpha
by Daniel Bartholomew 26 Feb '12

26 Feb '12

All, Nearly all of the mirrors have the MariaDB 5.5.20 Alpha packages and source tarball now, so I've gone ahead and activated the various pages for the release. The release notes are pretty sparse at the moment. If there is anything that should be added to them, please let me know ASAP, or jump in and add it directly. http://kb.askmonty.org/en/mariadb-5520-release-notes http://kb.askmonty.org/en/mariadb-5520-changelog http://downloads.askmonty.org/mariadb/5.5.20/ Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] BUG#939009: Making sj-materialized fields non-nullable doesn't remove the crash
by Sergei Petrunia 23 Feb '12

23 Feb '12

Hello Igor, I've made a patch that makes temporary table's fields non-nullable. It still has one part missing, but I have discovered that it will not remove the crash. I was able to construct an example with inner join and a non-nullable key field for which best_access_path() will still access rec_per_key[...]. <testcase> create table t21 (a int NOT NULL, b int, key(a)); # note the NOT NULL key column... # we can make the key unique: create table t21a (a int NOT NULL, b int, unique key(a)); insert into t21 select a,a from test.one_k; explain select * from t21a where a=b and (b=10 or b is null); </testcase> When I debug the above EXPLAIN, I get this execution in best_access_path(): if (found_part == PREV_BITS(uint,keyinfo->key_parts) && !ref_or_null_part) //(branch not taken) if ((found_part & 1) && (!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) || found_part == PREV_BITS(uint,keyinfo->key_parts))) { //(branch taken) if (table->quick_keys.is_set(key) && !found_ref && //(C1) table->quick_key_parts[key] == max_key_part && //(C2) table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3) { //(branch not taken) tmp= records= (double) table->quick_rows[key]; } else { //(we end up here, where the crash is) /* Check if we have statistic about the distribution */ if ((records= keyinfo->rec_per_key[max_key_part-1])) Conclusion: marking temporary table's field as NOT NULL will not cause best_access_path to avoid accessing the " rec_per_key[max_key_part-1] " part. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] On whether BUG#920132 is repeatable in mysql-5.6
by Sergei Petrunia 22 Feb '12

22 Feb '12

<igor> spetrunia: so does mysql-5.6 contains a fix for that ICP bug #920132? It doesn't have the fix. They don't have the bug either, though. mysql-5.6 will execute the subquery at optimization phase and remove it from the WHERE. The stack trace will look like this: (gdb) wher 20 #0 JOIN::exec (this=0x977cf40) at /home/psergey/dev2/mysql-server-trunk/sql/sql_executor.cc:129 #1 0x0851962c in subselect_single_select_engine::exec (this=0x97227d0) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:2517 #2 0x0851fee2 in Item_subselect::exec (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:340 #3 0x08515902 in Item_exists_subselect::val_bool (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:1005 #4 0x0822a144 in Item_func_not::val_int (this=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_cmpfunc.cc:290 #5 0x0825abff in eval_const_cond (cond=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_func.cc:79 #6 0x0839c385 in internal_remove_eq_conds (thd=0x964a390, cond=0x97227f0, cond_value=0xa95a9fb4) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8127 #7 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722870, cond_value=0xa95aa054) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #8 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #9 0x0839c71b in remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8220 #10 0x083ad51f in optimize_cond (join=0x977cbc8, conds=0x9722918, join_list=0x964bb18, build_equalities=true, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7947 #11 0x083adde9 in JOIN::optimize (this=0x977cbc8) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:246 #12 0x083f7efa in mysql_select (thd=0x964a390, tables=0x9721298, wild_num=1, fields=..., conds=0x9722918, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x9722a38, unit=0x964b5ec, select_lex=0x964ba48) at /home/psergey/dev2/mysql-server-trunk/sql/sql_select.cc:1074 so by the time we get to push_index_cond(), subquery will no longer be in the WHERE clause. As far as I udnerstand, we don't want this behavior, all subuqueries are expensive and should not be executed in EXPLAIN. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[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