| Bug #30889 | filesort and order by with float/numeric crashes server | ||
|---|---|---|---|
| Submitted: | 7 Sep 2007 9:59 | Modified: | 1 Feb 2008 17:57 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 6.0.3, 5.0.50, 5.1.23 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | crash, filesort, make_set, round, truncate | ||
[7 Sep 2007 9:59]
Shane Bester
[7 Sep 2007 10:10]
MySQL Verification Team
another variant for the testsuite: drop table if exists t1; create table t1(`col8` DECIMAL,`col47` BIGINT)engine=myisam; insert into t1 values (1675480,4.28711e+001); insert into t1 values (2805623,2.96037e+006); select round(col8,col47) as a from t1 order by a
[7 Sep 2007 10:42]
MySQL Verification Team
Version: '6.0.3-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 yes mysqld: filesort.cc:737: void make_sortkey(SORTPARAM*, uchar*, uchar*): Assertion `0' failed. 070907 12:23:58 - mysqld got signal 6;
[22 Sep 2007 13:59]
Martin Hansson
Shane, Can you please show me the exact steps you took to get this failed assertion? Thanks, Martin
[22 Sep 2007 14:17]
MySQL Verification Team
Martin, I suggest using a debug binary. Here's another example:
drop table if exists t1;
drop table if exists t2;
create table `t1` (a float primary key auto_increment,`col000` geometrycollection not null ,key(`col000` (1)),`col001` timestamp not null ,key(`col001` ),`col002` varbinary (245) ,key(`col002` ))engine=myisam;
create table `t2` (a float primary key auto_increment,`col000` time not null ,key(`col000` ),`col001` tinyblob ,key(`col001` (1)),`col002` int (3) not null ,key(`col002` ),`col003` int (7) zerofill not null ,key(`col003` ),`col004` polygon ,key(`col004` (1)),`col005` set('sweet','sour','bitter','salty') charset gb2312 collate gb2312_chinese_ci ,key(`col005` ),`col006` varbinary (88) not null ,key(`col006` ),`col007` longtext charset latin5 collate latin5_bin ,key(`col007` (1)),`col008` tinyint (2) zerofill ,key(`col008` ))engine=myisam;
insert into t1 values (),(),();
insert into t2 values (),(),();
select * from t1 left join t2 on t1.col000=t2.col000 order by (maketime(`t1`.`col000`,`t1`.`a`,`t1`.`col001`));
produced this error:
Version: '6.0.3-alpha-debug' socket: '/tmp/mysql.sock' port: 3308 yes
mysqld: filesort.cc:737: void make_sortkey(SORTPARAM*, uchar*, uchar*): Assertion `0' failed.
070922 15:58:02 - mysqld got signal 6;
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=8388572
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337622 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x8e6f758
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=0x42a5d804, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8215bbb handle_segfault + 541
0xffffe410 _end + -142886944
0x40167b75 _end + 932335429
0x4015f903 _end + 932302035
0x82ff2dd _Z12make_sortkeyP13st_sort_paramPhS1_ + 1361
0x82fe897 _Z13find_all_keysP13st_sort_paramP10SQL_SELECTPPhP11st_io_cacheS6_S6_ + 1913
0x82fd921 _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTybPy + 1201
0x828bab7 _Z17create_sort_indexP3THDP4JOINP8st_orderyyb + 881
0x827557a _ZN4JOIN4execEv + 6472
0x8275d1e _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 638
0x827095d _Z13handle_selectP3THDP6st_lexP13select_resultm + 365
0x8229c3a _Z21execute_sqlcom_selectP3THDP10TABLE_LIST + 772
0x8222958 _Z21mysql_execute_commandP3THD + 1648
0x822b6b0 _Z11mysql_parseP3THDPKcjPS2_ + 372
0x8220dad _Z16dispatch_command19enum_server_commandP3THDPcj + 2369
0x8220460 _Z10do_commandP3THD + 600
0x821ee51 handle_one_connection + 255
0x40038aa7 _end + 931094135
0x401f8c2e _end + 932929534
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.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 0x8e9dc98 = select * from t1 left join t2 on t1.col000=t2.col000 order by (maketime(`t1`.`col000`,`t1`.`a`,`t1`.`col001`))
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
[22 Sep 2007 15:07]
Martin Hansson
Hi Shane, Thank you for your quick reply, now I'm able to repeat the signal 6.
[23 Sep 2007 9:27]
Martin Hansson
The problem with MAKETIME() above is actually a different bug which should be dealt with separately (this is why I asked for this info). The MAKETIME() problem deals with NULL resulted functions not being handled properly, whereas the problem with ROUND(<NUMERIC>, x) and TRUNCATE(<NUMERIC>, x) (they are, in fact, synonyms when used in this way) and filesort deal with display length vs. number of decimals.
[23 Sep 2007 16:53]
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/34498 ChangeSet@1.2490, 2007-09-23 18:55:59+02:00, mhansson@linux-st28.site +3 -0 Bug #30889: filesort and order by with float/numeric crashes server There are two problems with ROUND() on an exact numeric (DECIMAL, NUMERIC type) field of a table: 1) If we issue a ROUND( column1, column2 ), this means that the number of digits is set to that of column1 during setup_fields phase, however as soon as a value for column2 is found, the number of digits after the decimal point(aka scale) will be changed, but not the total number of digits (aka precision). This can lead to a state where scale > precision, causing the conversion to binary representation to crash the server. (called when filesort creates sort key values) Fixed by setting the precision to its maximum possible value as soon as a non-constant is seen as the 2nd argument of ROUND() 2) If we issuse a ROUND( a, b ), and b is greater than the maximum scale, or greater than the precision, we get a failed assertion. Fixed by not allowing a scale greater than the maximum scale for exact numeric types.
[23 Sep 2007 17:16]
Martin Hansson
See also Bug#31160
[8 Oct 2007 9:08]
Sergey Petrunya
Review feedbaack has been provided by both reviewers via email (which didn't reach commits@ for some reason). Setting back to In Progress since the ball is on the fixer's side now.
[8 Oct 2007 12:28]
Martin Hansson
Not quite. After discussion with second reviewer on Oct 5th, he said he will get back to me. (unless there was another email that didn't reach neither bugs@ nor me)
[15 Oct 2007 9: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/35547 ChangeSet@1.2490, 2007-10-15 11:19:47+02:00, mhansson@linux-st28.site +4 -0 Bug #30889: filesort and order by with float/numeric crashes server There are two problems with ROUND(X, D) on an exact numeric (DECIMAL, NUMERIC type) field of a table: 1) The implementation of the ROUND function would change the number of decimal places regardless of the value decided upon in fix_length_and_dec. When the number of decimal places is not constant, this would cause an inconsistent state where the number of digits was less than the number of decimal places, which crashes filesort. Fixed by not allowing the ROUND operation to add any more decimal places than was decided in fix_length_and_dec. 2) fix_length_and_dec would allow the number of decimals to be greater than the maximium configured value for constant values of D. This led to the same crash as in (1). Fixed by not allowing the above in fix_length_and_dec.
[15 Oct 2007 9:19]
Martin Hansson
Thank you for a good review, holyfoot! The problem was elsewhere, so the new fix is different. I agree that ROUND(1.23, 50) should be 1.23, since this would be more consistent with ROUND(1.23, column), but IMHO it is beyond a simple bugfix.
[22 Oct 2007 15:38]
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/36048 ChangeSet@1.2573, 2007-10-22 17:42:50+02:00, mhansson@linux-st28.site +1 -0 Bug#30889: Post-merge fix.
[29 Oct 2007 11: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/36541 ChangeSet@1.2556, 2007-10-29 15:39:56+04:00, gluh@mysql.com +4 -0 Bug#30889: filesort and order by with float/numeric crashes server There are two problems with ROUND(X, D) on an exact numeric (DECIMAL, NUMERIC type) field of a table: 1) The implementation of the ROUND function would change the number of decimal places regardless of the value decided upon in fix_length_and_dec. When the number of decimal places is not constant, this would cause an inconsistent state where the number of digits was less than the number of decimal places, which crashes filesort. Fixed by not allowing the ROUND operation to add any more decimal places than was decided in fix_length_and_dec. 2) fix_length_and_dec would allow the number of decimals to be greater than the maximium configured value for constant values of D. This led to the same crash as in (1). Fixed by not allowing the above in fix_length_and_dec.
[16 Nov 2007 9:31]
Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:32]
Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:34]
Bugs System
Pushed into 6.0.4-alpha
[24 Jan 2008 11:56]
Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01]
Bugs System
Pushed into 5.1.24-rc
[24 Jan 2008 12:01]
Bugs System
Pushed into 5.0.56
[1 Feb 2008 17:57]
Paul DuBois
Noted in 5.0.56, 5.1.24, 6.0.5 changelogs. ROUND(X,D) and TRUNCATE(X,D) for non-constant values of D could crash the server if these functions were used in an ORDER BY that was resolved using filesort.
[6 Mar 2008 9:42]
Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 19:44]
Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.
