MySQL Bugs: #5837: MySQL server crash when executing a multi-table delete

Bug #5837	MySQL server crash when executing a multi-table delete
Submitted:	1 Oct 2004 0:44	Modified:	4 Aug 2005 9:14
Reporter:	Allen Arakaki	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.21, 4.1-bk	OS:	Linux (Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
From either jdbc or mysql client, the mysqld (server) crashes when executing a multi-table delete.

This only happens on the linux version; the windows version works fine.

It produces the following SQLException:
SQLException: Communication link failure: java.io.EOFException, underlying cause: null

How to repeat:
DROP TABLE IF EXISTS sb_acls;
CREATE TABLE sb_acls(
aclId                   BIGINT NOT NULL PRIMARY KEY,
status                  TINYINT(1) NOT NULL
)TYPE = InnoDB;

DROP TABLE IF EXISTS sb_acl_notes;
CREATE TABLE sb_acl_notes(
refId                   BIGINT NOT NULL PRIMARY KEY,
aclId                   BIGINT,
INDEX                   idx_acl(aclId),
FOREIGN KEY (aclId) REFERENCES sb_acls(aclId) ON DELETE RESTRICT
)TYPE = InnoDB;

INSERT INTO sb_acl_notes VALUES(1,NULL);

DELETE sb_acl_notes, sb_acls FROM sb_acl_notes AS a LEFT JOIN sb_acls AS b ON (a.aclId=b.aclId)
WHERE a.refId='1';

Verified with MySQL 4.0.22-debug-log.
On 4.1.6 and 5.0.2 works it works like a charm.

Back trace:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 163851 (LWP 14078)]
0x082361b7 in row_update_for_mysql (mysql_rec=0x8a30e18 "ÿ", prebuilt=0x4118ce68)
    at row0mysql.c:1087
1087            ut_a(node->pcur->rel_pos == BTR_PCUR_ON);
Current language:  auto; currently c
(gdb) Quit
(gdb) bt
#0  0x082361b7 in row_update_for_mysql (mysql_rec=0x8a30e18 "ÿ", prebuilt=0x4118ce68)
    at row0mysql.c:1087
#1  0x08168b6e in ha_innobase::delete_row (this=0x8a30d30, record=0x8a30e18 "ÿ")
    at ha_innodb.cc:2575
#2  0x08138532 in multi_delete::send_data (this=0x8a362f8, values=@0x8a2567c)
    at sql_delete.cc:352
#3  0x0812aa77 in end_send (join=0xbe5ff1bc, join_tab=0x8a36930, end_of_records=false)
    at sql_select.cc:5370
#4  0x081294ed in do_select (join=0xbe5ff1bc, fields=0x8a2567c, table=0x0, procedure=0x0)
    at sql_select.cc:4667
#5  0x08120f33 in mysql_select (thd=0x8a254c8, tables=0x8a35fe8, fields=@0x8a2567c,
    conds=0x8a36248, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2164823296, result=0x8a362f8) at sql_select.cc:1012
#6  0x08106271 in mysql_execute_command () at sql_parse.cc:2134
#7  0x0810839c in mysql_parse (thd=0x8a254c8,
    inBuf=0x8a35e30 "DELETE sb_acl_notes, sb_acls FROM sb_acl_notes AS a LEFT JOIN sb_acls AS b ON\n(a.aclId=b.aclId)\nWHERE a.refId='1'", length=113) at sql_parse.cc:3014
#8  0x081037fc in dispatch_command (command=COM_QUERY, thd=0x8a254c8,
    packet=0x8a31dd1 "DELETE sb_acl_notes, sb_acls FROM sb_acl_notes AS a LEFT JOIN sb_acls AS b ON\n(a.aclId=b.aclId)\nWHERE a.refId='1'", packet_length=114) at sql_parse.cc:1083
#9  0x0810325f in do_command (thd=0x8a254c8) at sql_parse.cc:953
#10 0x0810293e in handle_one_connection (arg=0x8a254c8) at sql_parse.cc:737
#11 0x4016314b in pthread_start_thread () from /lib/libpthread.so.0
#12 0x401631df in pthread_start_thread_event () from /lib/libpthread.so.0
#13 0x402df50a in clone () from /lib/libc.so.6

with just-pulled 4.1-bk the following happens:

DELETE sb_acl_notes, sb_acls FROM sb_acl_notes AS a LEFT JOIN sb_acls AS b ON
(a.aclId=b.aclId)
WHERE a.refId='1';
ERROR 42S02: Unknown table 'sb_acl_notes' in MULTI DELETE
DELETE sb_acl_notes, sb_acls FROM sb_acl_notes  LEFT JOIN sb_acls ON
(sb_acl_notes.aclId=sb_acls.aclId)
WHERE sb_acl_notes.refId='1';
-- and here it crashes.

ChangeSet@1.2032, 2004-10-09 02:09:08+04:00, sergefp@mysql.com

New patch (assuming the previous is discarded):
bk commit - 4.0 tree (sergefp:1.2006) BUG#5837

Still seems to happen in 4.1.7 under Windows Server 2003. We have an application crashing the mysqld-nt.exe process when invoking a certain feature. This is the last statement in the general log:

DELETE mww.BESTANDSKORREKTURANFORDERUNG, mww.BESTANDSKORREKTURANFORDERUNGPOSTEN FROM mww.BESTANDSKORREKTURANFORDERUNG left join mww.BESTANDSKORREKTURANFORDERUNGPOSTEN on (mww.BESTANDSKORREKTURANFORDERUNGPOSTEN.x_bestandskorrekturanforderung_nummer = mww.BESTANDSKORREKTURANFORDERUNG.nummer) WHERE ursprung != 3 and istVollstaendigBearbeitet = false

MySQL is running as a service on the machine.
The following information is written to the event log:
Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x0013205c.

I will upload the Windows crash dump.

... which of course I am not allowed to...

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The final cset is:
ChangeSet@1.2010, 2004-12-11 15:51:52+03:00, sergefp@mysql.com

Fixed in 4.0.23 and 4.1.8.