Description:

We've just experienced a crash caused by the following query:

select backorder.* from backorder left join tmp_backorder using(order_id, product_id, product_type) where tmp_backorder.order_id=NULL;

Obviously, the writer of this query intended to use IS NULL, and reported the problem after his first attempt above kept crashing the mysql thread. It's a common enough mistake to make, so I'm sure you'll want to investigate further.

Below is as much information as I could dig up, including a reproducable test case.

Kind regards,

pa...@classical.com

Environment: ============ mysql Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686)

How-To-Repeat: ============== CREATE TABLE `backorder` ( `order_id` char(32) NOT NULL default '', `product_id` char(32) NOT NULL default '', `product_type` int(11) NOT NULL default '0', PRIMARY KEY (`order_id`,`product_id`,`product_type`) ) TYPE=MyISAM;

CREATE TABLE `tmp_backorder` ( `order_id` char(32) NOT NULL default '', `product_id` char(32) NOT NULL default '', `product_type` int(11) NOT NULL default '0', PRIMARY KEY (`order_id`,`product_id`,`product_type`) ) TYPE=MyISAM;

INSERT INTO backorder (order_id, product_id, product_type) VALUES ('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3), ('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3), ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);

INSERT INTO tmp_backorder (order_id, product_id, product_type) VALUES ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);

#this query will crash the mysql thread select backorder.* from backorder left join tmp_backorder using(order_id, product_id, product_type) where tmp_backorder.order_id=NULL;

#the crash produces the follow report in the error log:

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 agaist 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=268431360 record_buffer=1044480 sort_buffer=1048568 max_used_connections=93 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1284136 K bytes of memory Hope that's ok, if not, decrease some variables in the equation

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... Stack range sanity check OK, backtrace follows: 0x807ba8f 0x812c82a 0x80baf11 0x80bc7db 0x80bc416 0x80bb755 0x80bb3c4 0x809d9ab 0x8099758 0x80821c7 0x8086006 0x808153d 0x8080a0c Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/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 0x86884d0 = select backorder.* from backorder left join tmp_backorder using(order_id, product_id, product_type) where tmp_backorder.order_id=NULL thd->thread_id=120

Successfully dumped variables, if you ran with --log, take a look at the details of what thread 120 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash