MySQL Bugs: #20540: sp with multitable delete causes crash under high concurrency

Bug #20540	sp with multitable delete causes crash under high concurrency
Submitted:	19 Jun 2006 18:10	Modified:	30 Jun 2006 12:38
Reporter:	Shane Bester
Status:	Duplicate
Category:	Server: SP	Severity:	S1 (Critical)
Version:	5.0BK, 5.1BK	OS:	Any (*)
Assigned to:	Bugs System	Target Version:

Description:
Calling a simple sp containing a single multitable DELETE statement, a crash will occur
when run many times by concurrent connections.  Verified on 5.0BK on both windows and
linux, and 5.1 on linux.

I managed to crash windows 5.0 release versions at 50 threads, and linux 5.1.12 took me
150 threads to crash.

Below is stack trace from Windows crash on 5.0BK.  Stack was not always 100% the same
each time on linux/windows.

check_table_access()  Line 5247
multi_delete_precheck()  Line 7077
mysql_execute_command()  Line 3411
sp_instr_stmt::exec_core()  Line 2310
sp_lex_keeper::reset_lex_and_exec_core()  Line 2188
sp_instr_stmt::execute()  Line 2263
sp_head::execute()  Line 1068
sp_head::execute_procedure()  Line 1521
mysql_execute_command()  Line 4436
mysql_parse()  Line 5739
dispatch_command()  Line 1747
do_command()
handle_one_connection()  Line 1174
pthread_start()  Line 63
_threadstart()  Line 196

How to repeat:
drop database if exists db1;
create database db1;
use db1;

DROP TABLE IF EXISTS `g1`;
CREATE TABLE `g1` (
  `id1`int
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `g2`;
CREATE TABLE `g2` (
  `id1` int
) ENGINE=MyISAM;

DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
	DELETE from g1 using g2,g1 where g2.id1=g1.id1;
END
$$
DELIMITER ;

Now, open 50 - 200 threads and run "call p1()" continuously over and over until a crash
occurs.

Suggested fix:
not sure..

If you can't repeat it, increase concurrent threads. I suggest using a C application for
speed in this case.  Leave running for a few minutes if needed.

complete stack on windows

Attachment: forbug.txt (text/plain), 1.63 KiB.

crashes on 5.1BK on linux, resolved

Attachment: 5.1.12-beta-bk.stack (application/octet-stream, text), 10.13 KiB.

crashes on 5.0BK on linux

Attachment: 5.0.23bk.stack (application/octet-stream, text), 30.34 KiB.

This crash is reproducable even if a single thread is run to "call p1()", and another
thread issues a simple DELETE FROM g1 using g1,g2..........  The 2nd thread doesn't need
to call an SP.

Please let me know if you cannot repeat it quick enough

C program to create multiple threads. See top of file for how to compile and run

Attachment: bug20540.c (text/x-csrc), 5.37 KiB.

Bug: http://bugs.mysql.com/bug.php?id=20655 was marked as duplicate
of this one (similar call stack).

This bug is a duplicate of a bug#19399.