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:Microsoft Windows (Windows XP Pro SP2/Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[28 Sep 2005 0:44] Ivan Zoratti
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.
[28 Sep 2005 2:31] Miguel Solorzano
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.
[28 Sep 2005 2:46] Miguel Solorzano
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.
[28 Sep 2005 8:00] Ivan Zoratti
minimal data dir uploaded as Bug #13549 - Server crash with nested stored procedures.zip
the sp call in runThis.sql cause the crash.
[28 Sep 2005 19:59] Roscoe P. Coletrain
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.
[28 Sep 2005 22:23] Miguel Solorzano
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>
[30 Sep 2005 10:53] Ivan Zoratti
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;
[4 Oct 2005 12:51] Miguel Solorzano
Sanja,
I sent you new dump file. axway.sql
[6 Oct 2005 8:17] Oleksandr Byelkin
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();
[6 Oct 2005 13:12] Bugs System
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
[6 Oct 2005 16:06] Bugs System
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
[22 Nov 2005 22:50] Bugs System
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
[23 Nov 2005 4:49] Oleksandr Byelkin
Thank you for bugreport! Bugfix was pushed to 5.0.17.
[23 Nov 2005 14:29] Jon Stephens
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.