MySQL Bugs: #19225: MySQL server crashes in multi delete with subquery

Bug #19225	MySQL server crashes in multi delete with subquery
Submitted:	20 Apr 2006 16:08	Modified:	22 Jun 2006 15:37
Reporter:	Anton Worshevsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	4.1.19-BK, 4.1.18	OS:	Linux (LInux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
This bug was reproduced with mysql 4.1.18 and 4.1.14
It was tested on two systems with Linux 2.4.32 (Slackware 10.0, Slackware 10.2)

Folowing query leads to mysqld crash  
mysqld got signal 11;

DELETE FROM general, history, todo USING general, history, todo 
WHERE general.host = history.host AND
general.host IN ( SELECT host FROM history WHERE history.date = '2005-01-11' )

Here is resolved stack backtrace:

# resolve_stack_dump -s mysqld.sym -n mysqld.stack
0x813d144 handle_segfault + 676
0x40197715 _end + 935475237
0x816fb6c _Z13free_io_cacheP8st_table + 76
0x81a1d8e _ZN12multi_deleteD0Ev + 110
0x8155590 _Z21mysql_execute_commandP3THD + 17088
0x8157511 _Z11mysql_parseP3THDPcj + 273
0x814ff88 _Z16dispatch_command19enum_server_commandP3THDPcj + 1032
0x814fa47 _Z10do_commandP3THD + 247
0x814f02b handle_one_connection + 811
0x401924eb _end + 935454203
0x402fbb0a _end + 936934426

How to repeat:
Import mysqldump from attachment to some database
and use this query to crash mysql server:

DELETE FROM general, history, todo USING general, history, todo 
WHERE general.host = history.host AND
general.host IN ( SELECT host FROM history WHERE history.date = '2005-01-11' )

Suggested fix:
I think this due to try to free already released memory.

Thank you for a problem report. You had not attached that mysqldump yet... So, please, do.

mysql dump for bug reproduction

Attachment: dump.sql (application/octet-stream, text), 17.02 KiB.

Verified on 4.1-BK (ChangeSet@1.2469.1.1, 2006-04-23 19:26:56-05:00) on Linux with the the dump uploaded (dump.sql) and your query:

mysql> DELETE FROM general, history, todo USING general, history, todo
    -> WHERE general.host = history.host AND
    -> general.host IN ( SELECT host FROM history WHERE history.date = '2005-01-11' );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
060424 12:31:52  mysqld restarted

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: db19225

+-----------+
| version() |
+-----------+
| 4.1.19    |
+-----------+
1 row in set (0.02 sec)

In the error log I've got:

Cannot determine thread, fp=0x4275035c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8144926
0xffffe420
0x8a33ef0
0x815fdc4
0x8160bc1
0x8162933
0x8163c54
0x4004eaa7
0x40249c2e
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 0x8a4aa98 = DELETE FROM general, history, todo USING general, history, todo
WHERE general.host = history.host AND
general.host IN ( SELECT host FROM history WHERE history.date = '2005-01-11' )

Resolved stack trace:

openxs@suse:~/dbs/4.1> bin/resolve_stack_dump -s /tmp/mysqld41.sym -n 19225.stack
0x8144926 handle_segfault + 566
0xffffe420 _end + -139770292
0x8a33ef0 _end + 5153052
0x815fdc4 _Z21mysql_execute_commandP3THD + 27556
0x8160bc1 _Z11mysql_parseP3THDPcj + 305
0x8162933 _Z16dispatch_command19enum_server_commandP3THDPcj + 2707
0x8163c54 handle_one_connection + 2244
0x4004eaa7 _end + 934300883
0x40249c2e _end + 936377946

In multi-table delete a table for delete can't be used for selecting in
subselects. Appropriate error was raised but wasn't checked which leads to a
crash on execute phase.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6885

Fixed in 4.1.21, 5.0.23, 5.1.12

Noted in 4.1.21, 5.0.23, 5.1.12 changelogs.

Multiple-table DELETE statements containing a subquery that selected
from one of the tables being modified caused a server crash.