MySQL Bugs: #44810: index merge and order by with low sort_buffer

Bug #44810	index merge and order by with low sort_buffer_size crashes server!
Submitted:	12 May 2009 2:23	Modified:	21 Jul 2009 18:00
Reporter:	Xindong Su	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S1 (Critical)
Version:	5.0.81, 5.1.35, 6.0.12	OS:	Any (Server 2003 with SP2)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	regression

Description:
This statement:
select cyd.IndexNO,InRange,PTStatus,STStatus1,STStatus2,DateDeadLine,DateST1DeadLine,DateST2DeadLine,Priority
from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
   and 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus1="已审核" or STStatus1="已改正" or STStatus1="已批准" or STStatus1="有错误") or 
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000

crashed the server. Those string constants are in Chinese. 

Here is the explain of the sql statement:===================================================
+----+-------------+-------+-------------+-----------------------------------------------------+------------------------------+---------+------+------+-----------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                       | key                          | key_len | ref  | rows | Extra                                                                       |
+----+-------------+-------+-------------+-----------------------------------------------------+------------------------------+---------+------+------+-----------------------------------------------------------------------------+
|  1 | SIMPLE      | cyd   | index_merge | PTROOM,PTStatus,STStatus1,STStatus2,STRoom1,STRoom2 | PTStatus,STStatus1,STStatus2 | 9,9,9   | NULL | 3470 | Using sort_union(PTStatus,STStatus1,STStatus2); Using where; Using filesort |
+----+-------------+-------+-------------+-----------------------------------------------------+------------------------------+---------+------+------+-----------------------------------------------------------------------------+

Here is the error log:===================================================
090512  9:40:01 - mysqld got exception 0xc0000005 ;
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=33554432
read_buffer_size=524288
max_used_connections=1
max_connections=20
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 53248 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=011729B8
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...
008D955C    mysqld-debug.exe!UnwindUpVec()[memcpy.asm:303]
00757666    mysqld-debug.exe!_mi_rec_unpack()[mi_dynrec.c:1127]
00758BF0    mysqld-debug.exe!_mi_read_rnd_dynamic_record()[mi_dynrec.c:1669]
00744481    mysqld-debug.exe!mi_scan()[mi_scan.c:44]
0045C3BA    mysqld-debug.exe!ha_myisam::rnd_next()[ha_myisam.cc:1629]
0057D3A9    mysqld-debug.exe!rr_sequential()[records.cc:332]
00674DA2    mysqld-debug.exe!join_init_read_record()[sql_select.cc:11378]
0067366D    mysqld-debug.exe!sub_select()[sql_select.cc:10714]
006731C5    mysqld-debug.exe!do_select()[sql_select.cc:10478]
0065F43A    mysqld-debug.exe!JOIN::exec()[sql_select.cc:2124]
0065FB08    mysqld-debug.exe!mysql_select()[sql_select.cc:2305]
006597B2    mysqld-debug.exe!handle_select()[sql_select.cc:256]
00638CC5    mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2876]
00641B16    mysqld-debug.exe!mysql_parse()[sql_parse.cc:6404]
0063697B    mysqld-debug.exe!dispatch_command()[sql_parse.cc:1961]
0063607F    mysqld-debug.exe!do_command()[sql_parse.cc:1642]
0063443B    mysqld-debug.exe!handle_one_connection()[sql_parse.cc:1234]
007992E6    mysqld-debug.exe!pthread_start()[my_winthread.c:85]
008E1B67    mysqld-debug.exe!_threadstart()[thread.c:196]
7C82482F    kernel32.dll!GetModuleHandleA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0119ED58=select cyd.IndexNO,InRange,PTStatus,STStatus1,STStatus2,DateDeadLine,DateST1DeadLine,DateST2DeadLine,Priority
from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
   and 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus1="已审核" or STStatus1="已改正" or STStatus1="已批准" or STStatus1="有错误") or 
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000
thd->thread_id=1
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Here is the my.ini:===================================================
[client]
port=3306
default-character-set=latin1

[mysql]
default-character-set=latin1

[mysqld]
port=3306
old_passwords=1
max_allowed_packet=4M
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="D:/MySQLData/Data/"
default-character-set=latin1
default-storage-engine=MYISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE"
max_connections=20
query_cache_size=32M
open_files_limit=8192
table_cache=8192
tmp_table_size=18M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=35M
bulk_insert_buffer_size=4M
key_buffer_size=32M
read_buffer_size=512K
read_rnd_buffer_size=1M
sort_buffer_size=512K
skip-innodb
skip-name-resolve
old-passwords

How to repeat:
Just run it and the server crashes again and again. I have ever tried to downgrade the server. With version 5.0.67 this statement runs ok, but crashed with version 5.0.77. Since 5.0.75 have no offical windows build released, I have not test it. 

There are about 77k rows in each of these tables. I have tried deleting some rows, and found out that with some rows deleted the server goes stable. But I can't find out which row causes this bug (It's a long work I have no so much time). I also tried different combination of the elements of the select statment, like reducing the selected fields or simplifing the where condition,  some of them crashed the server but some did not. 

Which do crash the server:
select cyd.IndexNO,InRange,DateDeadLine,DateST1DeadLine,DateST2DeadLine
from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
   and 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus1="已审核" or STStatus1="已改正" or STStatus1="已批准" or STStatus1="有错误") or 
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000

select cyd.IndexNO,InRange,PTStatus,STStatus1,STStatus2,DateDeadLine,DateST1DeadLine,DateST2DeadLine,Priority
from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
   and 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000

The backtrace part of the error log is the same as the above.

Which do not crash the server:
select cyd.IndexNO,InRange,PTStatus,STStatus1,STStatus2,DateDeadLine,DateST1DeadLine,DateST2DeadLine,Priority
from cyd where 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus1="已审核" or STStatus1="已改正" or STStatus1="已批准" or STStatus1="有错误") or 
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000

select cyd.IndexNO,InRange,PTStatus,STStatus1,STStatus2,DateDeadLine,DateST1DeadLine,DateST2DeadLine,Priority
from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
order by cyd.indexno desc limit 4000

select cyd.IndexNO from cyd where 
  (PTRoom="食品检验部" or STRoom1="食品检验部" or STRoom2="食品检验部") 
   and 
  ((PTStatus="已审核" or PTStatus="已改正" or PTStatus="已批准" or PTStatus="有错误") or    
  (STStatus1="已审核" or STStatus1="已改正" or STStatus1="已批准" or STStatus1="有错误") or 
  (STStatus2="已审核" or STStatus2="已改正" or STStatus2="已批准" or STStatus2="有错误"))   
order by cyd.indexno desc limit 4000

Here is the table structure:===================================================
CREATE TABLE `cyd` (
  `IndexNO` varchar(16) character set latin1 collate latin1_bin NOT NULL,
  `LicenseNO` varchar(17) default NULL,
  `SINO` varchar(13) character set latin1 collate latin1_bin default NULL,
  `ProductName` varchar(60) default NULL,
  `ProductTM` varchar(30) default NULL,
  `ProductSpec` varchar(30) default NULL,
  `ProductionDate1` varchar(30) character set latin1 collate latin1_bin default NULL,
  `ProductionDate2` varchar(30) character set latin1 collate latin1_bin default NULL,
  `ProductionDate3` varchar(30) character set latin1 collate latin1_bin default NULL,
  `NationalCode` varchar(5) character set latin1 collate latin1_bin default NULL,
  `ProductCode` varchar(17) character set latin1 collate latin1_bin default NULL,
  `FoodCode` char(7) character set latin1 collate latin1_bin NOT NULL,
  `BOAProductCode` int(10) unsigned NOT NULL default '0',
  `SamplingSheetNO` varchar(16) default NULL,
  `AltSamplingSheetNO` varchar(30) default NULL,
  `SamplerName` varchar(45) character set latin1 collate latin1_bin default NULL,
  `SampleQuantity` varchar(20) default NULL,
  `SampleBase` varchar(20) default NULL,
  `SampleSpot` varchar(50) default NULL,
  `SampleSpotProperty` varchar(10) default NULL,
  `SamplingWay` varchar(40) default NULL,
  `SamplingDate` date default NULL,
  `SampleSituation` varchar(48) default NULL,
  `SampleNeedReturn` char(2) default NULL,
  `SampleAcceptDate` date default NULL,
  `SampleAccepter` varchar(8) default NULL,
  `BICorp` varchar(50) default NULL,
  `BICorpAddress` varchar(50) default NULL,
  `BICorpMan` varchar(8) default NULL,
  `BICorpPhone` varchar(30) default NULL,
  `BICorpZipCode` varchar(6) default NULL,
  `BICorpProperty` varchar(24) default NULL,
  `BICorpCode` varchar(12) default NULL,
  `BICorpLicenseNO` varchar(80) NOT NULL,
  `BICorpFax` varchar(16) default NULL,
  `BICorpLegalPerson` varchar(25) default NULL,
  `ProductionCorp` varchar(50) default NULL,
  `ProductionCorpCode` varchar(12) character set latin1 collate latin1_bin default NULL,
  `ProductionCorpAddress` varchar(50) default NULL,
  `ProductionCorpPhone` varchar(30) default NULL,
  `ProductionCorpZipCode` varchar(6) default NULL,
  `SubmitCorp` varchar(50) default NULL,
  `DateOrder` datetime default NULL,
  `DateDemand` date default NULL,
  `DateDeadLine` datetime default NULL,
  `DateST1DeadLine` datetime default NULL,
  `DateST2DeadLine` datetime default NULL,
  `DateTested` date default NULL,
  `DateFinished` date default NULL,
  `InspectionKindNO` char(2) default NULL,
  `InspectionKindShort` char(2) default NULL,
  `InspectionKindCode` char(1) default NULL,
  `InspectionKindName` varchar(40) default NULL,
  `InspectionStandard` varchar(200) default NULL,
  `InspectionPrice` double NOT NULL default '0',
  `Payday` varchar(20) default NULL,
  `InspectionEnviroment` varchar(40) default NULL,
  `InspectionResult` varchar(400) default NULL,
  `ReportHeader` varchar(60) default NULL,
  `ReportColumns` char(3) default NULL,
  `ReportMemo` text,
  `PTStatus` varchar(6) default NULL,
  `STStatus1` varchar(6) default NULL,
  `STStatus2` varchar(6) default NULL,
  `PTRoom` varchar(20) default NULL,
  `STRoom1` varchar(20) default NULL,
  `STRoom2` varchar(20) default NULL,
  `ReAuthorizingCount` tinyint(3) unsigned NOT NULL default '0',
  `PageCount` char(2) default NULL,
  `NeedPrint` char(2) character set latin1 collate latin1_bin NOT NULL default '是',
  `PrintCopies` int(11) unsigned NOT NULL default '4',
  `CoverToPrint` tinyint(3) unsigned NOT NULL default '0',
  `FirstPageToPrint` tinyint(3) unsigned NOT NULL default '0',
  `FirstPageShowTester` tinyint(3) unsigned NOT NULL default '0',
  `InnerPageToPrint` tinyint(3) unsigned NOT NULL default '0',
  `InnerPageShowTester` tinyint(3) unsigned NOT NULL default '0',
  `ReportTitle` varchar(30) NOT NULL,
  `ReportTableTitles` varchar(60) NOT NULL default '',
  `FooterCaptions` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
  `IsTestReport` tinyint(3) unsigned NOT NULL default '0',
  `LeftModified` char(2) NOT NULL default '否',
  `IsVIP` char(2) character set latin1 collate latin1_bin NOT NULL default '否',
  `EMailAddress` varchar(50) default NULL,
  `MissionSource` varchar(50) default NULL,
  `SampleLevel` varchar(30) default NULL,
  `InRange` varchar(6) character set latin1 collate latin1_bin NOT NULL default '已认可',
  `ClientAccountID` int(10) unsigned NOT NULL default '0',
  `ClientAccountIDCC` int(10) unsigned NOT NULL default '0',
  `UseCCOnSubmitCorp` char(2) NOT NULL default '否',
  `UseCCOnProductionCorp` char(2) NOT NULL default '否',
  `CCCorpName` varchar(50) NOT NULL default '',
  `Priority` int(10) unsigned NOT NULL default '0',
  `ReportIsPass` char(2) character set latin1 collate latin1_bin NOT NULL default '',
  `ReportIsPassIgnoreLabel` char(2) NOT NULL,
  `ReportIsPassCriticalLevelA` char(2) NOT NULL,
  `JudgeType` char(2) NOT NULL default '是',
  `GUIDKey` char(36) NOT NULL default '',
  `NeedEnglishReport` char(2) character set latin1 collate latin1_bin NOT NULL default '否',
  PRIMARY KEY  (`IndexNO`),
  KEY `PTROOM` (`PTRoom`),
  KEY `PTStatus` (`PTStatus`),
  KEY `STStatus1` (`STStatus1`),
  KEY `STStatus2` (`STStatus2`),
  KEY `STRoom1` (`STRoom1`),
  KEY `STRoom2` (`STRoom2`),
  KEY `SINO` (`SINO`),
  KEY `SamplingSheetNO` (`SamplingSheetNO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have also tried 5.0.81 on CentOS, same thing happened. But since the machine running CentOS is a production server, I couldn't do any test on it.

Hi Xindong, 

Can you please show us results of this:

CHECK TABLE `cyd`;
SHOW TABLE STATUS LIKE 'cyd';

Thanks!

mysql> check table `cyd`;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| jwxt.cyd | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (2.19 sec)

mysql> SHOW TABLE STATUS LIKE 'cyd';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| cyd  | MyISAM |      10 | Dynamic    | 77511 |            838 |    64985696 | 281474976710655 |      5349376 |         0 |           NULL | 2009-04-18 22:02:35 | 2009-05-12 10:32:01 | 2009-05-12 14:39:43 | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set, 1 warning (0.00 sec)

Thank you for the feedback.

Please also provide output of  CHECK TABLE `cyd` FOR UPGRADE; : it looks like you created the table in older version, then upgraded.

I have done a little more:

mysql> CHECK TABLE `cyd` FOR UPGRADE;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| jwxt.cyd | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> CHECK TABLE `cyd` EXTENDED;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| jwxt.cyd | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (50.48 sec)

mysql> REPAIR TABLE `cyd` EXTENDED;
+----------+--------+----------+----------+
| Table    | Op     | Msg_type | Msg_text |
+----------+--------+----------+----------+
| jwxt.cyd | repair | status   | OK       |
+----------+--------+----------+----------+
1 row in set (1 min 2.41 sec)

mysql> SHOW TABLE STATUS LIKE 'cyd';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| cyd  | MyISAM |      10 | Dynamic    | 77511 |            838 |    64985400 | 281474976710655 |      8666112 |         0 |           NULL | 2009-04-18 22:02:35 | 2009-05-12 15:40:46 | 2009-05-12 15:40:46 | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set, 1 warning (0.02 sec)

After doing all these, I try my sql statment and the server still crashed. Error log is the same.

I have cleared out sensitive data from the table (mysql still crashes) and compressed it into a 7z file around 2MB. How ever this bug report system has a limitation of 500KB. I can't upload it.

Thank you for the feedback.

You can upload the file into our FTP server as described in "Files" section:

If the data you need to attach is more than 500KB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (example: bug-data-44810.zip), and use FTP to upload the archive to ftp://ftp.mysql.com/pub/mysql/upload/. Once you have uploaded the file, add a comment to this bug to notify us about it. Note: This directory is unlistable, which means that once you have uploaded your file, you will not be able to see it.

All entries become the exclusive editorial property of Sun Microsystems, Inc.

File is uploaded to ftp. 
Compressed as zip format and file name is bug-data-44810.zip. 
File size is 3.18MB.

Please change default-character-set=utf8 in your my.cnf then start the server again and let us know if this will solve your problem.

the sql statment in DBCS.

Attachment: sql.txt (text/plain), 531 bytes.

After applying "default-character-set=utf8", the sql statement results in:

Error Code : 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I use latin1 character set to store DBCS Chinese characters. I know this is not a right choice, but for historical reason I have to. 

I upload the sql statement as a file to avoid any possible charset conversion.

Thanks for your detailed comments. How ever, still not everything can handle utf8 these days. To correct the history, it's a costly task...

Xindong if you set a huge sort_buffer_size for the session, does this query still crash?

testcase for 5.0.82:

drop table if exists t1;
create table t1(a varchar(128),b varchar(128),key(a),key(b))engine=myisam;
insert into t1 values (repeat('a',128),repeat('b',128));
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
set session sort_buffer_size=1;
select * from t1 force index(a,b) where a like 'a%' or b like 'b%' order by a,b;

At first I increased the value of sort_buffer_size to 64M, MySQL had not crashed. Therefore I tried different values, and found that when the value below 528K MySQL will crash.

Then I tried the test case which provided by Shane Bester, it crashed. The error log is shown below:

090531 13:09:12 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.81-community-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)
090531 13:09:44 [Warning] option 'sort_buffer_size': unsigned value 1 adjusted to 32776
090531 13:09:53 [Warning] option 'sort_buffer_size': unsigned value 1 adjusted to 32776
090531 13:10:01 [Warning] option 'sort_buffer_size': unsigned value 1 adjusted to 32776
090531 13:10:01 - mysqld got exception 0xc0000005 ;
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=33554432
read_buffer_size=524288
max_used_connections=2
max_connections=20
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 53248 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=00EE9648
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...
005D1CBC    mysqld-nt.exe!_mi_rec_unpack()[mi_dynrec.c:1127]
005D2700    mysqld-nt.exe!_mi_read_rnd_dynamic_record()[mi_dynrec.c:1669]
005C94D4    mysqld-nt.exe!mi_scan()[mi_scan.c:44]
0043B527    mysqld-nt.exe!ha_myisam::rnd_next()[ha_myisam.cc:1630]
004D2CE5    mysqld-nt.exe!rr_sequential()[records.cc:332]
0054E64B    mysqld-nt.exe!join_init_read_record()[sql_select.cc:11378]
005543DF    mysqld-nt.exe!sub_select()[sql_select.cc:10715]
005674A7    mysqld-nt.exe!do_select()[sql_select.cc:10478]
005685BF    mysqld-nt.exe!JOIN::exec()[sql_select.cc:2125]
00568C14    mysqld-nt.exe!mysql_select()[sql_select.cc:2305]
0056905B    mysqld-nt.exe!handle_select()[sql_select.cc:256]
0053AC0C    mysqld-nt.exe!mysql_execute_command()[sql_parse.cc:2876]
00541B31    mysqld-nt.exe!mysql_parse()[sql_parse.cc:6405]
00542B3E    mysqld-nt.exe!dispatch_command()[sql_parse.cc:1963]
00543E26    mysqld-nt.exe!do_command()[sql_parse.cc:1646]
00544145    mysqld-nt.exe!handle_one_connection()[sql_parse.cc:1234]
005F579B    mysqld-nt.exe!pthread_start()[my_winthread.c:85]
006E0B7F    mysqld-nt.exe!_threadstart()[thread.c:196]
7C82482F    kernel32.dll!GetModuleHandleA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00EF1BB0=select * from t1 force index(a,b) where a like 'a%' or b like 'b%' order by a,b
thd->thread_id=3
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

5.0.74 also crashed. I have not mentioned this just because it's outdated.

bug #45257 is probably a duplicate of this.

This is a regression from the fix for bug #40974.

The problem appears when a index merge is followed by a filesort and the filesort data don't fit in the memory (and thus a temp file for the output is created).
After the storage of the data into the filesort table and before the retrieval the code decides (correctly) to  free the quick select (as it's already used to get the data when storing into the filesort table). 
On doing this the fix for bug #40974 fires and frees the temp file created by filesort.

There're two ways I can see to fix this : 
1. preserve the temp table before calling select->cleanup() in create_sort_index(), set it to 0 (so the deallocation doesn't happen) and then restore it

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc   2009-05-15 09:29:41 +0000
+++ sql/sql_select.cc   2009-06-02 15:49:16 +0000
@@ -12865,8 +12865,11 @@ create_sort_index(THD *thd, JOIN *join, 
   tab->records= table->sort.found_records;     // For SQL_CALC_ROWS
   if (select)
   {
+    IO_CACHE *io_cache= table->sort.io_cache;
+    table->sort.io_cache= 0;
     select->cleanup();                         // filesort did select
     tab->select= 0;
+    table->sort.io_cache= io_cache;
   }
   tab->select_cond=0;
   tab->last_inner= 0;

2. Move the deallocation out of the index merge destructor

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/75520

2759 Georgi Kodinov	2009-06-03
      Bug #44810: index merge and order by with low sort_buffer_size
      crashes server!
      
      The problem affects the scenario when index merge is followed by a filesort
      and the sort buffer is not big enough for all the sort keys.
      In this case the filesort function will read the data to the end through the 
      index merge quick access method (and thus closing the cursor etc), 
      but will leave the pointer to the quick select method in place.
      It will then create a temporary file to hold the results of the filesort and
      will add it as a sort output file (in sort.io_cache).
      Note that filesort will copy the original 'sort' structure in an automatic
      variable and restore it after it's done.
      As a result at exiting filesort() we have a sort.io_cache filled in and 
      nothing else (as a result of close of the cursors at end of reading data 
      through index merge).
      Now create_sort_index() will note that there is a select and will clean it up
      (as it's been used already by filesort() reading the data in). While doing that
      a special case in the index merge destructor will clean up the sort.io_cache,
      assuming it's an output of the index merge method and is not needed anymore.
      As a result the code that tries to read the data back from the filesort output 
      will get no data in both memory and disk and will crash.
      
      Fixed similarly to how filesort() does it : by copying the sort.io_cache structure
      to a local variable, removing the pointer to the io_cache (so that it's not freed 
      by the index merge destructor) and restoring the original structure (together with
      the valid pointer) after the cleanup is done.
      This is a safe thing to do because all the structures are already cleaned up by
      hitting the end of the index merge's read method (get_next()) and the cleanup 
      code being written in a way that tolerates consecutive cleanups.
     @ mysql-test/r/index_merge.result
        Bug #44810: test case
     @ mysql-test/t/index_merge.test
        Bug #44810: test case
     @ sql/sql_select.cc
        Bug #44810: preserve the io_cache produced by filesort while cleaning up
        the index merge quick access method.

Bug #45257 is marked as a duplicate of this one.

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/76280

2772 Georgi Kodinov	2009-06-15
      Bug #44810: index merge and order by with low sort_buffer_size
      crashes server!
      
      The problem affects the scenario when index merge is followed by a filesort
      and the sort buffer is not big enough for all the sort keys.
      In this case the filesort function will read the data to the end through the 
      index merge quick access method (and thus closing the cursor etc), 
      but will leave the pointer to the quick select method in place.
      It will then create a temporary file to hold the results of the filesort and
      will add it as a sort output file (in sort.io_cache).
      Note that filesort will copy the original 'sort' structure in an automatic
      variable and restore it after it's done.
      As a result at exiting filesort() we have a sort.io_cache filled in and 
      nothing else (as a result of close of the cursors at end of reading data 
      through index merge).
      Now create_sort_index() will note that there is a select and will clean it up
      (as it's been used already by filesort() reading the data in). While doing that
      a special case in the index merge destructor will clean up the sort.io_cache,
      assuming it's an output of the index merge method and is not needed anymore.
      As a result the code that tries to read the data back from the filesort output 
      will get no data in both memory and disk and will crash.
            
      Fixed similarly to how filesort() does it : by copying the sort.io_cache structure
      to a local variable, removing the pointer to the io_cache (so that it's not freed 
      by QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT) and restoring the original 
      structure (together with the valid pointer) after the cleanup is done.
      This is a safe thing to do because all the structures are already cleaned up by
      hitting the end of the index merge's read method (QUICK_INDEX_MERGE_SELECT::get_next()) 
      and the cleanup code being written in a way that tolerates repeating cleanups.
     @ mysql-test/r/index_merge.result
        Bug #44810: test case
     @ mysql-test/t/index_merge.test
        Bug #44810: test case
     @ sql/sql_select.cc
        Bug #44810: preserve the io_cache produced by filesort while cleaning up
        the index merge quick access method (QUICK_INDEX_MERGE_SELECT).

Pushed into 5.0.84 (revid:gkodinov@mysql.com-20090616082753-kwe0l8uoictxhojf) (version source revid:joro@sun.com-20090615133815-eb007p5793in33p5) (merge vers: 5.0.83) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:joro@sun.com-20090615143651-bnkzgvfyc6uqs4mm) (merge vers: 5.1.36) (pib:6)

bug #45400 is a duplicate of this

Noted in 5.0.84, 5.1.36 changelogs.

Index Merge followed by a filesort could result in a server crash if
sort_buffer_size was not large enough for all sort keys.

Setting report to NDI pending push into 5.4.x.

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:joro@sun.com-20090615145920-6jfjry3wzjutp8z1) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

Georgi, would setting sort_buffer_size to the lowest of max_heap_table_size and tmp_table_size be sufficient to diagnose this is a possible crash cause without upgrading the server? Is there any likely lower value based on server settings (not trying to estimate total key size) that would work? People are often reluctant to upgrade without a clear test result.

Noted in 5.0.82sp1 changelog.

Pushed into 5.0.85 (revid:build@mysql.com-20090810185326-yr4orhpwq09e3y50) (version source revid:build@mysql.com-20090810185326-yr4orhpwq09e3y50) (merge vers: 5.0.85) (pib:11)

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39 (revid:jperkin@sun.com-20090824091334-6ktgrhq218vl7zq1) (version source revid:joerg@mysql.com-20090813203300-nnskc3aofxydzi85) (merge vers: 5.1.39) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed to 5.4.2.