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

[19 Jun 2006 16:10] Shane Bester
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..
[19 Jun 2006 16:10] MySQL Verification Team
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.
[19 Jun 2006 16:11] MySQL Verification Team
complete stack on windows

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

[19 Jun 2006 17:34] MySQL Verification Team
crashes on 5.1BK on linux, resolved

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

[19 Jun 2006 17:55] MySQL Verification Team
crashes on 5.0BK on linux

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

[20 Jun 2006 20:21] MySQL Verification Team
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
[23 Jun 2006 0:56] MySQL Verification Team
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.

[26 Jun 2006 16:06] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=20655 was marked as duplicate
of this one (similar call stack).
[30 Jun 2006 10:38] Tomash Brechko
This bug is a duplicate of a bug#19399.