Bug #6127 Stored procedure handlers within handlers don't work
Submitted: 16 Oct 2004 13:19 Modified: 30 Sep 2005 19:05
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Bugs System CPU Architecture:Any

[16 Oct 2004 13:19] Peter Gulutzan
Description:
In a stored procedure, I define a handler for a condition X. Within the handler, there is a 
handler for another condition Y -- that is, a "nested handler". I execute a statement which 
causes activation of the handler for X. In the body of this handler, I execute a statement 
which should cause activation of the handler for Y. But the handler for Y is not activated. 
The statement fails. 
 

How to repeat:
mysql> create table t7 (s1 int unique)// 
Query OK, 0 rows affected (0.29 sec) 
 
mysql> set sql_mode='traditional'// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p18 () 
    -> begin 
    ->   declare continue handler for sqlstate '23000' begin 
    ->     declare continue handler for sqlstate '22003' insert into t7 values (0); 
    ->     insert into t7 values (1000000000000000); 
    ->     end; 
    ->   insert into t7 values (1); 
    ->   insert into t7 values (1); 
    ->   end; 
    ->   // 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p18()// 
ERROR 1264 (22003): Out of range value adjusted for column 's1' at row 1
[21 Sep 2005 11:45] 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/30146
[26 Sep 2005 16:56] 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/30338
[26 Sep 2005 17:01] Per-Erik Martin
Pushed to bk 5.0.14.

Handlers within handlers now works.
[30 Sep 2005 19:05] Paul Dubois
Noted in 5.0.14 changelog.