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: Bugs System CPU Architecture:Any

[6 Dec 2004 19:40] Peter Gulutzan
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)
[6 Dec 2004 19:49] MySQL Verification Team
Thank you for the bug report.
[4 Aug 2005 13:30] Anders Karlsson
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
[13 Sep 2005 14:05] 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/29745
[26 Sep 2005 17:21] 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/30339
[13 Oct 2005 10:34] Per-Erik Martin
Question answered in email.
[17 Oct 2005 13:44] 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/31176
[17 Oct 2005 14:59] Per-Erik Martin
Pushed to bk 5.0.16
[19 Oct 2005 15:26] Per-Erik Martin
It was pushed on Monday October 17 (about 17:00 CET) and the version in configure.in was 5.0.16.
[20 Oct 2005 7:31] 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 bugfix in 5.0.16 changelog. Closed.