MySQL Bugs: #31037: A table when query a DELETE will crashing mysql

Bug #31037	A table when query a DELETE will crashing mysql
Submitted:	15 Sep 2007 2:50	Modified:	22 Sep 2007 8:01
Reporter:	bole chen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.45-log	OS:	Any (Windows & RedHat Linux AS 4)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	5.0.45, crashing, delete

Description:
I have a table below:

CREATE TABLE `syspub` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `classid` int(11) NOT NULL default '0',
  `articleid` int(11) NOT NULL default '0',
  `topath` varchar(255) NOT NULL default '',
  `frompath` varchar(255) NOT NULL default '',
  `stats` smallint(1) default '0',
  `lastupdate` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `frompath` (`frompath`),
  KEY `cid` (`cid`),
  KEY `classid` (`classid`),
  KEY `articleid` (`articleid`)
) TYPE=MyISAM;

In this table, when i query a SQL:

DELETE FROM syspub ORDER BY articleid,id LIMIT 500;

The MySQL server will crashing, the error message like to:

070915 10:39:43 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.45-log'  socket: './mysql.sock'  port: 3306  MySQL Community Server (GPL)
070915 10:40:01 - 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=67108864
read_buffer_size=2093056
max_used_connections=1
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 = 4157528 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xe73cd0
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=0xe85f80, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0xe85f80, stack_bottom=0x40a80000, thread_stack=262144, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xed6aa0 = DELETE FROM fol_admin.syspub ORDER BY articleid,id LIMIT 500
thd->thread_id=1
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
070915 10:40:01  mysqld restarted

I test this on my Laptop runs MySQL 5.0.45-community-nt for Windows,the crashing is meet too.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

mysql> use test
Database changed
mysql> CREATE TABLE `syspub` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `cid` int(11) NOT NULL default '0',
    ->   `classid` int(11) NOT NULL default '0',
    ->   `articleid` int(11) NOT NULL default '0',
    ->   `topath` varchar(255) NOT NULL default '',
    ->   `frompath` varchar(255) NOT NULL default '',
    ->   `stats` smallint(1) default '0',
    ->   `lastupdate` int(11) default NULL,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `frompath` (`frompath`),
    ->   KEY `cid` (`cid`),
    ->   KEY `classid` (`classid`),
    ->   KEY `articleid` (`articleid`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> DELETE FROM syspub ORDER BY articleid,id LIMIT 500;

Suggested fix:
When i deleted the key `articleid`,all thing is ok,but when i add the index,the crashing is also here~~

In Windows XP, the errormsg like this:

---------------------------
mysqld-nt.exe - Application Error
---------------------------
The instruction at "0x0041c787" referenced memory at "0x49525107". The memory could not be "read".

Click on OK to terminate the program
Click on CANCEL to debug the program
---------------------------
OK   Cancel   
---------------------------

Thank you for a bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.44-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE `syspub` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `cid` int(11) NOT NULL default '0',
    ->   `classid` int(11) NOT NULL default '0',
    ->   `articleid` int(11) NOT NULL default '0',
    ->   `topath` varchar(255) NOT NULL default '',
    ->   `frompath` varchar(255) NOT NULL default '',
    ->   `stats` smallint(1) default '0',
    ->   `lastupdate` int(11) default NULL,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `frompath` (`frompath`),
    ->   KEY `cid` (`cid`),
    ->   KEY `classid` (`classid`),
    ->   KEY `articleid` (`articleid`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> DELETE FROM syspub ORDER BY articleid,id LIMIT 500;
ERROR 2013 (HY000): Lost connection to MySQL server during query

There is no crash with latest 5.0.50-debug on Linux though:

openxs@linux:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.50-debug Source distribution

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

mysql> CREATE TABLE `syspub` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `cid` int(11) NOT NULL default '0',
    ->   `classid` int(11) NOT NULL default '0',
    ->   `articleid` int(11) NOT NULL default '0',
    ->   `topath` varchar(255) NOT NULL default '',
    ->   `frompath` varchar(255) NOT NULL default '',
    ->   `stats` smallint(1) default '0',
    ->   `lastupdate` int(11) default NULL,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `frompath` (`frompath`),
    ->   KEY `cid` (`cid`),
    ->   KEY `classid` (`classid`),
    ->   KEY `articleid` (`articleid`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> DELETE FROM syspub ORDER BY articleid,id LIMIT 500;
Query OK, 0 rows affected (0.00 sec)

Anyway, we have to add this test to a test suite, it seems.

This bug duplicates bug #30385 (test case is already provided).