Bug #20590 Signal 11 after SELECT statement
Submitted: 21 Jun 2006 0:17 Modified: 21 Jun 2006 11:52
Reporter: Ramon de la Fuente Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:version 5.0.22-standard OS:Linux (Fedora FC4smp)
Assigned to: CPU Architecture:Any

[21 Jun 2006 0:17] Ramon de la Fuente
Description:
Signal 11 after a specific SELECT statement.

From SHOW VARIABLES:
version 5.0.22-standard 
version_comment MySQL Community Edition - Standard (GPL) 
version_compile_machine i686 
version_compile_os pc-linux-gnu 

From My.cnf:
[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
port=3306
ft_min_word_len=3
long_query_time = 1
skip-name-resolve
set-variable=max_connections=1000
max_allowed_packet=64M
key_buffer = 256M

[mysqldump]
quick

[mysql.server]
user=mysql
socket=/tmp/mysql.sock

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to repeat:
on any database:
 CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `userid` int(11) unsigned NOT NULL default '0',
  `isactive` tinyint(1) unsigned NOT NULL default '0',
  `title` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `displayurl` varchar(50) NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `costpc` int(11) unsigned NOT NULL default '1',
  `destinationurl` text NOT NULL,
  `isvalid` tinyint(1) unsigned NOT NULL default '0',
  `campaignid` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `userid` (`userid`,`isactive`,`isvalid`),
  KEY `campaignid` (`campaignid`),
  FULLTEXT KEY `search` (`title`,`description`,`keywords`),
  FULLTEXT KEY `displayurl` (`displayurl`,`destinationurl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And then:
SELECT id FROM test WHERE id NOT IN (-1,1);
[21 Jun 2006 0:19] Ramon de la Fuente
From ErrorLog:
Version: '5.0.22-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
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=268435456
read_buffer_size=131072
max_used_connections=2
max_connections=1000
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2438136 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa72e090
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=0xbf91c948, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80a22d7
0x83002a8
0x8124ff6
0x8127e9f
0x8128754
0x81259ff
0x80e3885
0x80e495a
0x80e0c27
0x80e373e
0x80dfee1
0x80b43b5
0x80babfa
0x80b2916
0x80b21a4
0x80b16b4
0x82fda5c
0x83417fa
New value of fp=(nil) failed sanity check, terminating stack trace!
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 0xa74c670 = select id from ads where id not in (-1,1)
thd->thread_id=6
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.

Number of processes running now: 0
060621 01:53:27  mysqld restarted
060621  1:53:27 [Warning] Asked for 196608 thread stack, but got 126976
060621  1:53:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060621  1:53:27  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2864991286.
InnoDB: Doing recovery: scanned up to log sequence number 0 2864991286
060621  1:53:27  InnoDB: Started; log sequence number 0 2864991286
060621  1:53:27 [Warning] 'user' entry 'root@jail4.siteview.nl' ignored in --skip-name-resolve mode.
060621  1:53:27 [Note] /usr/sbin/mysqld: ready for connections.
[21 Jun 2006 0:22] Ramon de la Fuente
Resolved stacktrace:
resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack
0x80a22d7 _init + 358143
0x83002a8 _init + 2840272
0x8124ff6 _init + 893982
0x8127e9f _init + 905927
0x8128754 _init + 908156
0x81259ff _init + 896551
0x80e3885 _init + 625837
0x80e495a _init + 630146
0x80e0c27 _init + 614479
0x80e373e _init + 625510
0x80dfee1 _init + 611081
0x80b43b5 _init + 432093
0x80babfa _init + 458786
0x80b2916 _init + 425278
0x80b21a4 _init + 423372
0x80b16b4 _init + 420572
0x82fda5c _init + 2829956
0x83417fa strstr + 10438
[21 Jun 2006 1:00] Miguel Solorzano
Thank you for the bug report. I was unable to repeat with current source
server:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbj
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbj
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test` (
    ->   `id` int(11) unsigned NOT NULL auto_increment,
    ->   `userid` int(11) unsigned NOT NULL default '0',
    ->   `isactive` tinyint(1) unsigned NOT NULL default '0',
    ->   `title` varchar(50) NOT NULL,
    ->   `description` varchar(255) NOT NULL,
    ->   `displayurl` varchar(50) NOT NULL,
    ->   `keywords` varchar(255) NOT NULL,
    ->   `costpc` int(11) unsigned NOT NULL default '1',
    ->   `destinationurl` text NOT NULL,
    ->   `isvalid` tinyint(1) unsigned NOT NULL default '0',
    ->   `campaignid` int(11) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `userid` (`userid`,`isactive`,`isvalid`),
    ->   KEY `campaignid` (`campaignid`),
    ->   FULLTEXT KEY `search` (`title`,`description`,`keywords`),
    ->   FULLTEXT KEY `displayurl` (`displayurl`,`destinationurl`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT id FROM test WHERE id NOT IN (-1,1);
Empty set (0.06 sec)

mysql>
[21 Jun 2006 11:52] Ramon de la Fuente
Reproduced on:

OS:
    FreeBSD 6.1-PRERELEASE
MYSQL:
    version: 5.0.22-log
    version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006)
    version_comment: FreeBSD port: mysql-server-5.0.22
    version_compile_machine: i386
    version_compile_os: portbld-freebsd6.1

NOT Reproduced on:

OS:
    FreeBSD 5.4-RELEASE-p8
MYSQL:
    version: 4.1.16
    version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (November 30, 2005)
    version_comment: FreeBSD port: mysql-server-4.1.16
    version_compile_machine: i386
    version_compile_os: portbld-freebsd5.4

################ MISSING ACTION FOR REPRODUCING ERROR #########
After creating the database, insert the following 2 records before executing the SELECT query:

INSERT INTO test 
(id,userid,isactive,title,description,displayurl,keywords,costpc,destinationurl,isvalid,campaignid) VALUES
(1,1,1,'string','string','string','string',3,'string',1,1),
(13,10,1,'string','string','string','string',25,'string',1,5)
###################################################