Bug #3297 Stored procedure handler scope outside of begin/end
Submitted: 25 Mar 2004 18:11 Modified: 5 Apr 2004 8:06
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Bugs System CPU Architecture:Any

[25 Mar 2004 18:11] Peter Gulutzan
A stored procedure handler should affect the begin/end block that it's in, but when there 
are nested begin/end blocks, a stored procedure handler should not affect outer begin/end 

How to repeat:
delimiter // 
mysql> create table t (s1 int, primary key (s1)) engine=innodb// 
Query OK, 0 rows affected (0.31 sec) 
mysql> insert into t values (5),(6)// 
Query OK, 2 rows affected (0.28 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
mysql> create procedure p10 () 
    -> begin 
    ->   begin 
    ->     declare exit handler for sqlexception select '!'; 
    ->     insert into t values (5); 
    ->   end; 
    -> insert into t values (6); 
    -> end; 
    -> // 
Query OK, 0 rows affected (0.58 sec) 
call p10()// 
... The "SELECT !" will repeat forever. 
What should happen is: the first INSERT fails, the SELECT '!' happens, 
the exit happens (now the handler is out of scope), the second INSERT 
fails, the exception is unhandled. 
An interesting side effect: watch the "1 row in set (... sec)" display. 
The time will increase, it is not reset after each select.
[5 Apr 2004 8:06] Per-Erik Martin
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