| Bug #37627 | Killing query with sum(exists()) or avg(exists()) reproducibly crashes server | ||
|---|---|---|---|
| Submitted: | 25 Jun 2008 11:44 | Modified: | 24 Jul 2008 1:16 |
| Reporter: | Randall Farmer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.1.22-rc-community x86_64, 5.1 BZR | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | avg, crash, DoS, KILL, subquery, sum | ||
[25 Jun 2008 12:27]
Sveta Smirnova
Thank you for the report. But version 5.1.22 is a bit old. Please upgrade to current version 5.1.25, try with it and inform us if problem still exists.
[25 Jun 2008 12:40]
MySQL Verification Team
crashed my 5.1.26 ..
[25 Jun 2008 12:42]
MySQL Verification Team
my testcase: drop table if exists t1; create table t1(id int)engine=myisam; insert into t1 values (1),(2),(3),(4); insert into t1 select a.id from t1 a,t1 b,t1 c,t1 d; insert into t1 select a.id from t1 a,t1 b,t1 c; select sum(exists(select rand() from t1)) from t1; wait between 1 and 5 second then kill it from another session. try it multiple times, it's obviously timing problem. stack trace from 5.1.26: mysqld.exe!decimal_add()[decimal.c:1948] mysqld.exe!my_decimal_add()[my_decimal.h:363] mysqld.exe!Item_sum_sum::add()[item_sum.cc:808] mysqld.exe!update_sum_func()[sql_select.cc:15234] mysqld.exe!end_send_group()[sql_select.cc:12064] mysqld.exe!evaluate_join_record()[sql_select.cc:11159] mysqld.exe!sub_select()[sql_select.cc:11050] mysqld.exe!do_select()[sql_select.cc:10800] mysqld.exe!JOIN::exec()[sql_select.cc:2179] mysqld.exe!mysql_select()[sql_select.cc:2360] mysqld.exe!handle_select()[sql_select.cc:269] mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4765] mysqld.exe!mysql_execute_command()[sql_parse.cc:2073] mysqld.exe!mysql_parse()[sql_parse.cc:5649] mysqld.exe!dispatch_command()[sql_parse.cc:1139] mysqld.exe!do_command()[sql_parse.cc:794] mysqld.exe!handle_one_connection()[sql_connect.cc:1115] mysqld.exe!pthread_start()[my_winthread.c:85] mysqld.exe!_callthreadstart()[thread.c:293]
[25 Jun 2008 13:32]
Sveta Smirnova
Shane, thank you for the feedback. Verified as described.
[4 Jul 2008 13:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49013 2686 Georgi Kodinov 2008-07-04 Bug#37627: Killing query with sum(exists()) or avg(exists()) reproducibly crashes server On error executing an IN Subquery Item_in_exists::val_str and Item_in_exists::val_decimal were returning a NULL pointer without setting the NULL flag. Since IN subquery predicates are not supposed to return NULL values we must return 0 in such cases (and count on the subquery code actually setting the error).
[4 Jul 2008 14:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49016 2686 Georgi Kodinov 2008-07-04 Bug#37627: Killing query with sum(exists()) or avg(exists()) reproducibly crashes server When there is an error executing EXISTS predicates they return NULL as their string or decimal value but don't set the NULL value flag. Fixed by returning 0 (as a decimal or a string) on error exectuting the subquery. Note that we can't return NULL as EXISTS is not supposed to return NULL.
[4 Jul 2008 15:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49020 2687 Georgi Kodinov 2008-07-04 [merge] atutomatic merge of 5.1-bugteam into bug37627
[4 Jul 2008 15:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49021 2687 Georgi Kodinov 2008-07-04 [merge] atutomatic merge of 5.1-bugteam into bug37627
[4 Jul 2008 19:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49032 2696 Chad MILLER 2008-07-04 [merge] Merge from 5.1. Add files new to 6.0.
[7 Jul 2008 8:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49064 2689 Georgi Kodinov 2008-07-07 Bug#37627: addendum : - moved the test into a separate file to check for presence of the test variable
[7 Jul 2008 8:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49065 2689 Georgi Kodinov 2008-07-07 Bug#37627: addendum : - moved the test into a separate file to check for presence of the test variable
[7 Jul 2008 9:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49067 2697 Georgi Kodinov 2008-07-07 [merge] merge of the addendum fix for bug 37627 5.1-bugteam->6.0-bugteam
[7 Jul 2008 9:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49068 2697 Georgi Kodinov 2008-07-07 [merge] merge of the addendum fix for bug 37627 5.1-bugteam->6.0-bugteam
[16 Jul 2008 21:11]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49893 2718 Andrei Elkin 2008-07-16 [merge] merging with 6.0-merge-rpl
[17 Jul 2008 10:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49907 2718 Andrei Elkin 2008-07-16 [merge] merging with 6.0-merge-rpl
[23 Jul 2008 9:09]
Georgi Kodinov
Pushed in 5.1.28 and 6.0.7-alpha
[24 Jul 2008 1:16]
Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs. Killing a query that used an EXISTS subquery as the argument to SUM() or AVG() caused a server crash.
[28 Jul 2008 16:47]
Bugs System
Pushed into 5.1.28 (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)
Description: It usually kills MySQL with a segfault when I start running this query and kill it a few seconds into execution (clickthroughs is a large MyISAM table): select sum(exists(select rand() from clickthroughs)) from clickthroughs; (Any dependent or uncacheable subquery that touches a table can be put in the exists() and will trigger the bug. "select rand() from clickthroughs" is just a convenient example.) Many queries similar to that execute fine; sum(exists()) seems to be the problem. Specifically: (1) The server doesn't crash if I add 0 to the output of exists() select sum(0+exists(select rand() from clickthroughs)) from clickthroughs; (2) The server doesn't crash if I use an aggregate function that accepts non-number arguments, like max() or group_concat() select max(exists(select rand() from clickthroughs)) from clickthroughs; (3) The server doesn't crash if I use a cacheable subquery, or a subquery which does not touch any tables select sum(exists(select 1 from clickthroughs)) from clickthroughs; select sum(exists(select rand())) from clickthroughs; (4) The server doesn't crash if I use a subquery other than exists() select sum((select rand()>0.5)) from clickthroughs; select sum((select rand()>0.5 from clickthroughs limit 1)) from clickthroughs; select sum((select rand())>0.5) from clickthroughs; select sum((select rand() from clickthroughs limit 1)>0.5) from clickthroughs; (5) The server doesn't crash if I put the exists() in a WHERE clause and SELECT a different aggregate value. select sum(rand()) from clickthroughs where exists(select rand() from clickthroughs); How to repeat: I tested these queries on a server running 5.0.46-enterprise-gpl. I filed this bug as affecting 5.1.22-rc-community after reproducing it on a 5.1.22-rc-community server. Below is MySQL's crash information for a typical crash. Sometimes the backtrace shows 0x74006f00746f6f72 instead of 0x746f6f72. Sometimes, instead of "Stack trace seems successful - bottom reached", the message includes "New value of fp=0x26f7fd30 failed sanity check, terminating stack trace!" Again, this crash report is from a 5.0.46 mysqld: mysqld Ver 5.0.46-enterprise-gpl for redhat-linux-gnu on x86_64 (MySQL Enterprise Server (GPL)) 080625 3:54:25 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=200704 max_used_connections=4 max_connections=1600 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3655923 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x2ffbc4c0d0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x4510af78, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x746f6f72 Stack trace seems successful - bottom reached Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xd4cb20 = select sum(exists(select rand() from clickthroughs limit 1)) from clickthroughs thd->thread_id=4 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. My client is mysql Ver 14.12 Distrib 5.0.46, for redhat-linux-gnu (i686) using readline 5.0 Although any large table should work to reproduce this bug, my table's SHOW CREATE TABLE and SHOW TABLE STATUS follow, as well as the EXPLAIN for the query. CREATE TABLE `clickthroughs` ( `ct_id` int(11) NOT NULL auto_increment, `url_id` int(11) NOT NULL default '0', `user_id` int(11) NOT NULL default '0', `mailing_id` int(11) NOT NULL default '0', `link_id` int(11) NOT NULL default '1', `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`ct_id`), KEY `i_1` (`mailing_id`,`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=41053062 DEFAULT CHARSET=latin1 *************************** 1. row *************************** Name: clickthroughs Engine: MyISAM Version: 10 Row_format: Fixed Rows: 41053061 Avg_row_length: 29 Data_length: 1190538769 Max_data_length: 8162774324609023 Index_length: 1070072832 Data_free: 0 Auto_increment: 41053062 Create_time: 2007-08-23 19:02:45 Update_time: 2008-06-25 02:47:33 Check_time: 2007-08-24 09:57:58 Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: Suggested fix: Because sum(0+exists()) was not affected, and sum() but not max() was affected, and non-exists() subqueries were not affected, a coworker and I thought it could be that the result of exists() is stored internally as a boolean at some time that some other numeric type is expected. Obviously, we're making a wild guess.