Bug #12379 PROCEDURE with HANDLER calling FUNCTION with error get strange result
Submitted: 4 Aug 2005 12:48 Modified: 15 Sep 2005 17:20
Reporter: Anders Karlsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.10 OS:Linux (Linux)
Assigned to: Bugs System CPU Architecture:Any

[4 Aug 2005 12:48] Anders Karlsson
Description:
A procedure with a handler for SQLEXCEPTION that calls a STORED FUNCTION (not a UDF) that returns an error, will return strange data or get into a loop in some cases. Might be related to 7049, I don't know, they seem to deal with similar issues.

How to repeat:
Create these objects:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(1));

DROP FUNCTION IF EXISTS myfunc;
delimiter //
CREATE FUNCTION myfunc()
RETURNS INTEGER
BEGIN
   DECLARE v1 INTEGER;

   INSERT INTO t1 VALUES('X', 'Y');
   RETURN 0;
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc1;
delimiter //
CREATE PROCEDURE myproc1()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 1;

   SELECT myfunc();
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc2;
delimiter //
CREATE PROCEDURE myproc2()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

   SELECT myfunc();
END;
//
delimiter ;

Now, to see the errors:
mysql> call myproc1();
ERROR 2027 (HY000): Malformed packet
mysql> call myproc2();
-- The above will loop and loop and loop....
[4 Aug 2005 12:50] Anders Karlsson
Or rather, the call to myproc2() will hang, I guess... Anyway it doesn't return.
[4 Aug 2005 13:05] MySQL Verification Team
Below the current behavior with BK source server:

mysql> call myproc1();
ERROR 2027 (HY000): Malformed packet
mysql> call myproc2();
Packets out of order (Found: 5, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[9 Aug 2005 4:48] David Axmark
Changed priority since the dropping a connection is more than non-critical
[6 Sep 2005 15:08] 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/29375
[8 Sep 2005 23:41] Michael Widenius
Proposed a more general solutuion in review email. Need more discussion to decide which solution is better
[15 Sep 2005 17:20] Paul DuBois
Noted in 5.0.13 changelog.