MySQL Bugs: #21186: Crash on DELETE within a stored procedure

Bug #21186	Crash on DELETE within a stored procedure
Submitted:	20 Jul 2006 15:47	Modified:	23 Jul 2006 9:07
Reporter:	Steve Nunes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (Linux (RedHat ES3))
Assigned to:		CPU Architecture:	Any

Description:
I am seeing an intermittent crash in the following stored procedure on the first delete statement.  There may or may not be any rows to delete that match the where clause.  It is a little hard to reproduce by hand, but usually crashes within 5-10 minutes if I use jmeter to throw a significant load on it.

I have run a debug trace of the crash and I will attach the trace data to the bug. 

(tomcat5.5-connector/J-MySQL5.0.22)

DELIMITER $$;

CREATE DEFINER=`vjweb`@`%` PROCEDURE `DeleteEntry`(
IN i_entry_id INT
,IN i_journal_id INT)
    DETERMINISTIC
BEGIN
DELETE me FROM media_entry AS me, media_member AS mm
WHERE i_journal_id = mm.parent_journal_id
AND i_entry_id = mm.parent_entry_id
AND mm.parent_journal_id = mm.media_journal_id
AND mm.media_journal_id = me.journal_id
AND mm.media_entry_id = me.entry_id;
DELETE FROM entry
WHERE i_journal_id = journal_id
AND i_entry_id = entry_id;
END$$

DELIMITER ;$$

thd=0x90f8908
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x3d35fc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81776d8
0xe7df88
0x818eca0
0x818eca0
0x8289324
0x8289071
0x8289204
0x8286303
0x8287296
0x8190fcf
0x8193986
0x818b5bf
0x818af63
0x818a25a
0xe77dd8
0x1edd1a
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 0x90df120 = DELETE me FROM media_entry AS me, media_member AS mm
WHERE  NAME_CONST('i_journal_id',1) = mm.parent_journal_id
AND  NAME_CONST('i_entry_id',141) = mm.parent_entry_id
AND mm.parent_journal_id = mm.media_journal_id
AND mm.media_journal_id = me.journal_id
AND mm.media_entry_id = me.entry_id
thd->thread_id=289

How to repeat:
I don't have an isolated test case but it crashes in my full environment in 5-10 minutes with load test.

This may be a duplicate of bug #19399.
Could you try to resolve the stack trace information from the error log
as documented in http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html
so that we can further investigate this?

resolved stack trace...

0x81776d8 handle_segfault + 368
0xe7df88 (?)
0x818eca0 _Z21mysql_execute_commandP3THD + 9374
0x818eca0 _Z21mysql_execute_commandP3THD + 9374
0x8289324 _ZN13sp_instr_stmt9exec_coreEP3THDPj + 14
0x8289071 _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 337
0x8289204 _ZN13sp_instr_stmt7executeEP3THDPj + 224
0x8286303 _ZN7sp_head7executeEP3THD + 1219
0x8287296 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 1010
0x8190fcf _Z21mysql_execute_commandP3THD + 18381
0x8193986 _Z11mysql_parseP3THDPcj + 312
0x818b5bf _Z16dispatch_command19enum_server_commandP3THDPcj + 1617
0x818af63 _Z10do_commandP3THD + 437
0x818a25a handle_one_connection + 768
0xe77dd8 (?)
0x1edd1a (?)

Please, upload some data for the table and send exact call that fails. I was not able to repeat on empty tables with 5.0.25-BK:

Please, openxs@suse:~/dbs/5.0> bin/mysql -uroot vj_node
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> call DeleteEntry(1,1);
ERROR 1370 (42000): execute command denied to user 'vjweb'@'%' for routine 'vj_n
ode.DeleteEntry'
mysql> grant all on *.* to 'vjweb'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> call DeleteEntry(1,1);
Query OK, 0 rows affected (0.01 sec)

As we have mulitable delete here in SP, it is almost surely a duplicate of bug #19399. At least, should be checked again only after patch for that fix will apper in public tree.