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 16:10	Modified:	30 Jun 2006 10:38
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0BK, 5.1BK	OS:	Any (*)
Assigned to:	Assigned Account	CPU Architecture:	Any

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.12 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.