MySQL Bugs: #13549: Server crash with nested stored procedures

Bug #13549	Server crash with nested stored procedures
Submitted:	28 Sep 2005 0:44	Modified:	23 Nov 2005 14:29
Reporter:	Ivan Zoratti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.13/BK source	OS:	Windows (Windows XP Pro SP2/Linux)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I am migrating a relatively complex package to MySQL.
In order to handle multiple open cursors I am using a series of nested stored procedures.

As in the mySQL manual (chapter 19.2.11) I am using an INT variable to handle the SQLSTATE '02000' and close the cursor.

If I use the same method after 4 nested procedures, the server crashes.

How to repeat:
At the moment I do not have a repeatable pattern, apart from the fragement of code I am using.

As a general rule, this happens if you create a set of nested procedures handling a set of open cursors. Cursors should be used using this approach:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

REPEAT
    FETCH cur1 INTO a, b;
    IF NOT done THEN
...
    END IF;
  UNTIL done END REPEAT;

Suggested fix:
As a workaround, instead of checking the SQLSTATE, I am checking that a variable containing a fetched columns is NULL.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Could you please feel free for to add a comment for to open
this bug again when you will have a repeatable test case
which can be tested on our side.
Thanks in advance.

If those tables and procedures you have mentioned, permits
to replicate the crash, then please attach them here or
upload a compressed file with the name of this bug 
report at:

ftp://ftp.mysql.com:/pub/mysql/upload

Thanks in advance.

minimal data dir uploaded as Bug #13549 - Server crash with nested stored procedures.zip
the sp call in runThis.sql cause the crash.

i got the same error with just one nested procedure.  i can run my procedure containing the cursor fromthe query browser and it works, but when I call it from inside another stored procedure it crashes the server.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.14-rc-debug

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

mysql> create database axway;
Query OK, 1 row affected (0.02 sec)

mysql> use axway;
Database changed
mysql> source /home/miguel/dbs/5/new.dump
Query OK, 0 rows affected (0.00 sec)

/* create procedures and functions and issue */

mysql> CALL FRM_U_OBJ_VALIDATE_OBJECT( 'f1e413d9-b3ad-11d8-81e8-b89df3085772', '2005-01-01', '999999999','IZ' );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

050928 19:16:57 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.14-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132456880 (LWP 24961)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132456880 (LWP 24961)]
0x0814c913 in Item_splocal::type (this=0x8e99808) at item.cc:896
896         return thd->spcont->get_item(m_offset)->type();
(gdb) bt full
#0  0x0814c913 in Item_splocal::type (this=0x8e99808) at item.cc:896
        thd = (class THD *) 0x8e38248
#1  0x081fa4ea in negate_expression (thd=0x8e38248, expr=0x8e99808) at sql_parse.cc:7412
        negated = (class Item *) 0x820d474
#2  0x082035f5 in yyparse (yythd=0x8e38248) at sql_yacc.yy:4256
<cut>

I have experienced other crashes. I can avoid them replacing the way to leave the course when there are no more data available.

For example, this fragment cause the crash:
...
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN pSec;
	REPEAT
		fetch pSec into ...;

    IF NOT DONE THEN
    	CALL FRM_U_OBJ_REC_INV_PARENTS( ... );
    END IF;
   UNTIL done END REPEAT;
  close pSec;

This does not:
...
   OPEN pSec;
     REPEAT
		fetch pSec into abc...;

    IF abc IS NOT NULL THEN --abc is null if there are no more data
    	CALL FRM_U_OBJ_REC_INV_PARENTS( ... );
    END IF;
   UNTIL done END REPEAT;
  close pSec;

Sanja,
I sent you new dump file. axway.sql

minimal test suite is following:
DELIMITER //;
CREATE PROCEDURE `p2`()
begin
  call p1();
end//
CREATE PROCEDURE `p1`()
begin
  declare done int default 0;
  set done= not done;
end//
DELIMITER ;//
CALL p2();

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/internals/30757

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/internals/30774

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/internals/32560

Thank you for bugreport! Bugfix was pushed to 5.0.17.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.17 changelog.