Bug #7299 Stored procedures: exception handler catches not-found conditions

• Submitted: 14 Dec 2004 21:55
• Modified: 12 Apr 2005 9:55
• Reporter: Peter Gulutzan
• Status: Closed
• 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

[14 Dec 2004 21:55] Peter Gulutzan

Description:
I have a stored procedure with an SQLEXCEPTION handler -- not a NOT FOUND handler. 
The handler catches a "not found" condition, when I FETCH from an empty table. 
This should not happen, for "not found" only a NOT FOUND handler should handle. 
 
 
 

How to repeat:
mysql> create table t125 (s1 int)// 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> CREATE PROCEDURE p125 () BEGIN DECLARE v INT; DECLARE c CURSOR FOR 
SELECT s1 FROM t125; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'Error!'; 
OPEN c; select 'before fetch'; FETCH c INTO v; select 'after fetch',v; END;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p125()// 
+--------------+ 
| before fetch | 
+--------------+ 
| before fetch | 
+--------------+ 
1 row in set (0.01 sec) 
 
+--------+ 
| Error! | 
+--------+ 
| Error! | 
+--------+ 
1 row in set (0.01 sec) 
 
Query OK, 0 rows affected (0.01 sec)

[14 Dec 2004 22:47] MySQL Verification Team

Thank you for the bug report.

[17 Dec 2004 1:54] Peter Gulutzan

The exception handler also catches "note" conditions. A "note" is so trivial that I'm not even 
sure an SQLWARNING should catch it. Certainly an SQLEXCEPTION shouldn't catch it. 
Example: 
 
mysql> DROP TABLE IF EXISTS t// 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> CREATE PROCEDURE h () 
    -> BEGIN 
    ->   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'exit'; 
    ->   DROP TABLE IF EXISTS t; 
    ->   select 'drop succeeded'; 
    -> END;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call h()// 
+------+ 
| exit | 
+------+ 
| exit | 
+------+ 
1 row in set (0.00 sec)

[8 Apr 2005 14:24] 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/23806