MySQL Bugs: #21009: mysql crashing on signal 11

Bug #21009	mysql crashing on signal 11
Submitted:	12 Jul 2006 19:38	Modified:	17 Jul 2006 20:12
Reporter:	Koen Martens
Status:	Can't repeat
Category:	Server	Severity:	S1 (Critical)
Version:	5.0.22	OS:	FreeBSD (FreeBSD)
Assigned to:	Sveta Smirnova	Target Version:
Tags:	source distributen, freebsd package, binary distribution

Description:
Recently installed mysql-5.0.22 from FreeBSD package on a FreeBSD 6.1 jail (on HP
DL140R02 w/ 1GB memory and software raid). On the same server, in another jail, is a
mysql-4.1 running hapilly without problems. The mysql-5.0.22 crashed regularly with this
log info:

Here's the log snippet:

--------------------------------------------------------------
060712 15:49:22  InnoDB: Started; log sequence number 0 0
060712 15:49:22 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.22-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
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=16777216
read_buffer_size=258048
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

060712 16:06:38  mysqld restarted
060712 16:06:38  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
--------------------------------------------------------------

Some more details:

it is a wordpress database

tried 5.0.22 from FreeBSD package, tried it from official mysql binary, tried it by
compiling from source. Also tried 4.1 on the same jail with the same database and the
same wordpress and that worked fine, also the current 5.1.11 beta works fine on this
jail.

The offending query seems to be:
SELECT DISTINCT * FROM wp_posts  WHERE 1=1 AND (post_date_gmt <= '2006-07-12 17:21:59'
AND id NOT IN
(-1,9,26,39,43,50,55,179,190,201,204,222,226,258,267,302,303,304,305,306,321,357,363,364,379,380,382,383,407,415,419,447,460,461,462,465,500)
) AND (post_status = "publish") AND post_status != "attachment" GROUP BY  wp_posts.ID 
ORDER BY post_date DESC LIMIT 0, 1

Furthermore, i have a gdb backtrace, which is included below. Finally, i mentioned this
on the freebsd-ports list, the thread can be viewed via pipermail at
http://lists.freebsd.org/pipermail/freebsd-ports/2006-July/033954.html

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x9ccb600 (LWP 100216)]
0x0817618b in SEL_ARG::last (this=0x0) at opt_range.cc:1295
1295      if (!next_arg->right)
(gdb) 
(gdb) 
(gdb) bt
#0  0x0817618b in SEL_ARG::last (this=0x0) at opt_range.cc:1295
#1  0x081795fe in get_func_mm_tree (param=0xbf0c2290, cond_func=0x9d00c58,
field=0x9d10318, value=0x0, 
    cmp_type=INT_RESULT, inv=true) at opt_range.cc:3556
#2  0x0817a0a6 in get_mm_tree (param=0xbf0c2290, cond=0x9d00c58) at opt_range.cc:3792
#3  0x08179928 in get_mm_tree (param=0xbf0c2290, cond=0x9d07208) at opt_range.cc:3652
#4  0x08176df7 in SQL_SELECT::test_quick_select (this=0x9d0e3c0, thd=0x9cf0000,
keys_to_use={map = 5},
    prev_tables=0, limit=4294967295, force_quick_range=false) at opt_range.cc:1848
#5  0x08124cad in get_quick_record_count (thd=0x9cf0000, select=0x9d0e3c0,
table=0x9d0f000, keys=0x9d0e0fc,
    limit=4294967295) at sql_select.cc:1944
#6  0x08125c83 in make_join_statistics (join=0x9d07568, tables=0x0, conds=0x9d07208,
keyuse_array=0x9d0826c)
    at sql_select.cc:2262
#7  0x081218da in JOIN::optimize (this=0x9d07568) at sql_select.cc:690
#8  0x08124afb in mysql_select (thd=0x9cf0000, rref_pointer_array=0x9cf0390,
tables=0x9cff280, wild_num=1, 
    fields=@0x9cf02f4, conds=0x9d07208, og_num=2, order=0x9d07480, group=0x9d07388,
having=0x0,
    proc_param=0x0, select_options=2156153345, result=0x9d07558, unit=0x9cf004c,
select_lex=0x9cf026c)
    at sql_select.cc:1887
#9  0x08120888 in handle_select (thd=0x9cf0000, lex=0x9cf003c, result=0x9d07558,
setup_tables_done_option=0)
    at sql_select.cc:238
#10 0x080ed382 in mysql_execute_command (thd=0x9cf0000) at sql_parse.cc:2499
#11 0x080f465f in mysql_parse (thd=0x9cf0000, 
    inBuf=0x9cff010 "SELECT DISTINCT * FROM wp_posts  WHERE 1=1 AND (post_date_gmt <=
'2006-07-12 17:21:59' AND id NOT IN
(-1,9,26,39,43,50,55,179,190,201,204,222,226,258,267,302,303,304,305,306,321,357,363,364,379,380,38"...,
length=365) at sql_parse.cc:5695
#12 0x080eb936 in dispatch_command (command=COM_QUERY, thd=0x9cf0000,
    packet=0x9cf4001 " SELECT DISTINCT * FROM wp_posts  WHERE 1=1 AND (post_date_gmt <=
'2006-07-12 17:21:59' AND id NOT IN
(-1,9,26,39,43,50,55,179,190,201,204,222,226,258,267,302,303,304,305,306,321,357,363,364,379,380,3"...,
packet_length=367) at sql_parse.cc:1736
#13 0x080eb238 in do_command (thd=0x9cf0000) at sql_parse.cc:1522
#14 0x080ea70a in handle_one_connection (arg=0x9cf0000) at sql_parse.cc:1165
#15 0x28436e3c in pthread_mutexattr_init () from /usr/lib/libpthread.so.2
#16 0x2860fc50 in ?? ()

How to repeat:
I do not have a clearcut reproducable case not using the particular wordpress database,
which i can't make public without my customers approval.

Thank you for the report. Could you please provide structure of wp_posts table: output of
SHOW CREATE TABLE wp_posts statement and approximate quantity of rows in wp_posts table?

Sure thing:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` int(4) NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_category` int(4) NOT NULL default '0',
  `post_excerpt` text NOT NULL,
  `post_status` enum('publish','draft','private','static','object') NOT NULL default
'publish',
  `comment_status` enum('open','closed','registered_only') NOT NULL default 'open',
  `ping_status` enum('open','closed') NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` int(11) NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `post_status` (`post_status`),
  KEY `post_author` (`post_author`),
  KEY `post_author_status` (`post_author`,`post_status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and

mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
|      416 |
+----------+
1 row in set (0.00 sec)

thanks for looking into this!

Thank you for your bug report.

I can not repeat it using random generated data and last MySQL sources. But I can repeat
it using MySQL 5.0.21. It looks like the bug has already fixed.

If necessary, you can access the source repository and build the latest available
version. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html