MySQL Bugs: #7049: Stored procedure CALL errors are ignored

Bug #7049	Stored procedure CALL errors are ignored
Submitted:	6 Dec 2004 19:40	Modified:	20 Oct 2005 7:31
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
If stored procedure #1 calls stored procedure #2, and stored procedure #2 has an 
unhandled exception, then stored procedure #1 should catch the error. 
But stored procedure #1 ignores the error. 
One way to show this is: although stored procedure #1 has an EXIT handler 
for the exception, the statement following the CALL gets executed. 
That wouldn't happen if the EXIT handler was activated. 
 

How to repeat:
 
mysql> delimiter // 
mysql> drop table if exists t// 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> create procedure p1 () drop table t// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p2 () begin declare exit handler for sqlexception set @x = 1; call p1
(); set @x =2; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p2()// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select @x// 
+------+ 
| @x   | 
+------+ 
| 2    | 
+------+ 
1 row in set (0.01 sec)

Thank you for the bug report.

This bug still exists, sot of, but the original case isn't really reproducible in 5.0.10. I just assume that as errors related to tables not existing are handled differently, as they are handled as part of pre-statement locking, at least that is my guess.

But the problem in this bug still exists. And it is only apparent if the calling procedure (p2 in the case above) has a handler. If not, the appropriate error is returned to the caller of p2. To reproduce:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(1));

DROP PROCEDURE IF EXISTS myproc1;
delimiter //
CREATE PROCEDURE myproc1()
BEGIN
   INSERT INTO t1 VALUES('X', 'X');
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc2a;
delimiter //
CREATE PROCEDURE myproc2a()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @err = 1;

   CALL myproc1();
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc2b;
delimiter //
CREATE PROCEDURE myproc2b()
BEGIN
   CALL myproc1();
END;
//
delimiter ;

mysql> call myproc2a();
Query OK, 0 rows affected (0.00 sec)

mysql> call myproc2b();
ERROR 1136 (21S01): Column count doesn't match value count at row 1

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/29745

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/30339

Question answered in email.

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/31176

Pushed to bk 5.0.16

It was pushed on Monday October 17 (about 17:00 CET) and the version in configure.in was 5.0.16.

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 bugfix in 5.0.16 changelog. Closed.