Bug #15192 "fatal errors" are caught by handlers in stored procedures
Submitted: 23 Nov 2005 16:14 Modified: 6 Mar 2010 19:13
Reporter: Per-Erik Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0 OS:Any (Any)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: stored procedure
Triage: D2 (Serious)

[23 Nov 2005 16:14] Per-Erik Martin
Description:
Some errors are "fatal" (out of memory, some errors from handlers) and should not be caught by exception handlers. Instead execution should stop immediately, but it doesn't:

With a mysqld (patched as described below, so it always returns a fatal "unknown error" in stored procedures:
----
mysql> delimiter //
mysql> create procedure foo()
    -> begin
    ->   select 'Zip';
    ->   select 'Zap';
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create procedure bar()
    -> begin
    ->   declare continue handler for sqlexception select 'Foo';
    -> 
    ->   select 'Zip';
    ->   select 'Zap';
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call foo();
+-----+
| Zip |
+-----+
| Zip |
+-----+
1 row in set (0.01 sec)

ERROR 1105 (HY000): Unknown error
mysql> call bar();
+-----+
| Foo |
+-----+
| Foo |
+-----+
1 row in set (0.02 sec)

mysql> drop procedure foo;
Packets out of order (Found: 6, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> quit
----

foo() and bar() should behave the same way in this case.

How to repeat:
Unfortunately, it's not easy to reproduce a "fatal" error deliberately, so I "faked" it by applying this to sql/sp_head.cc:
----
===== sp_head.cc 1.200 vs edited =====
--- 1.200/sql/sp_head.cc        2005-11-22 14:25:37 +01:00
+++ edited/sp_head.cc   2005-11-23 16:45:02 +01:00
@@ -1026,6 +1026,8 @@
       thd->user_var_events_alloc= thd->mem_root;
     
     ret= i->execute(thd, &ip);
+    thd->fatal_error();
+    my_error(ER_UNKNOWN_ERROR, MYF(0));
 
     /*
       If this SP instruction have sent eof, it has caused no_send_error to be
----

Then doing this in 'mysql':

delimiter //
create procedure foo()
begin
  select 'Zip';
  select 'Zap';
end//

create procedure bar()
begin
  declare continue handler for sqlexception select 'Foo';

  select 'Zip';
  select 'Zap';
end//
delimiter ;

call foo();
call bar();
[9 May 2006 20:14] Peter Gulutzan
On the other hand, some non-fatal errors are not caught by handlers.
Using SUSE 10.0, MySQL 5.0.19, I tried this:

mysql> create procedure pstar () begin declare v int; declare exit handler for sqlexception select 'SQLEXCEPTION'; drop table if exists tstar; create table tstar (s1 int, s2 char(1)); set v = 0; while TRUE do insert into tstar values (null,null); end while; end//
Query OK, 0 rows affected (0.00 sec)

I call this routine, then I stop it by shutting down the server
(using mysqladmin --shutdown elsewhere). So I see:

mysql> call pstar()//
+--------------+
| SQLEXCEPTION |
+--------------+
| SQLEXCEPTION |
+--------------+
1 row in set (3.64 sec)

And that's fine. But then I start mysqld again, and call
the procedure again, and say mysqladmin --shutdown
again, and I see:

mysql> call pstar()//
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: db18

ERROR 1053 (08S01): Server shutdown in progress

So this time the handler failed to catch the exception.
Failures are very common if the procedure has SELECTs
in it.
[26 Mar 2008 14:01] 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/commits/44453

ChangeSet@1.2609, 2008-03-26 11:01:01-03:00, davi@mysql.com +13 -0
  Bug#15192 "fatal errors" are caught by handlers in stored procedures
  
  The problem is that fatal errors (e.g.: out of memory) were being
  caught by stored procedure exception handlers which could cause
  the execution to not be stopped (continue handler).
  
  The solution is to not call any exception handler if the error is
  fatal and send the fatal error to the client.
[28 Mar 2008 2:18] 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/commits/44543

ChangeSet@1.2610, 2008-03-27 23:18:20-03:00, davi@mysql.com +16 -0
  Bug#15192 "fatal errors" are caught by handlers in stored procedures
  
  The problem is that fatal errors (e.g.: out of memory) were being
  caught by stored procedure exception handlers which could cause
  the execution to not be stopped due to a continue handler.
  
  The solution is to not call any exception handler if the error is
  fatal and send the fatal error to the client.
[28 Mar 2008 15:15] 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/commits/44594

ChangeSet@1.2611, 2008-03-28 12:14:20-03:00, davi@mysql.com +21 -0
  Bug#15192 "fatal errors" are caught by handlers in stored procedures
  
  The problem is that fatal errors (e.g.: out of memory) were being
  caught by stored procedure exception handlers which could cause
  the execution to not be stopped due to a continue handler.
  
  The solution is to not call any exception handler if the error is
  fatal and send the fatal error to the client.
[28 Mar 2008 17:48] 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/commits/44610

ChangeSet@1.2614, 2008-03-28 14:47:53-03:00, davi@mysql.com +23 -0
  Bug#15192 "fatal errors" are caught by handlers in stored procedures
  
  The problem is that fatal errors (e.g.: out of memory) were being
  caught by stored procedure exception handlers which could cause
  the execution to not be stopped due to a continue handler.
  
  The solution is to not call any exception handler if the error is
  fatal and send the fatal error to the client.
[28 Mar 2008 17:49] Davi Arnaut
Queued to 6.0-runtime
[20 Apr 2008 13:01] Bugs System
Pushed into 6.0.6-alpha
[21 Apr 2008 17:14] Paul Dubois
Noted in 6.0.6 changelog.
[10 Nov 2009 20:32] 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/commits/90005

2922 Davi Arnaut	2009-11-10
      Backport of Bug#15192 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2597.4.17
      revision-id: sp1r-davi@mysql.com/endora.local-20080328174753-24337
      parent: sp1r-anozdrin/alik@quad.opbmk-20080328140038-16479
      committer: davi@mysql.com/endora.local
      timestamp: Fri 2008-03-28 14:47:53 -0300
      message:
        Bug#15192 "fatal errors" are caught by handlers in stored procedures
      
        The problem is that fatal errors (e.g.: out of memory) were being
        caught by stored procedure exception handlers which could cause
        the execution to not be stopped due to a continue handler.
      
        The solution is to not call any exception handler if the error is
        fatal and send the fatal error to the client.
     @ mysql-test/r/sp-error.result
        Add test case result for Bug#15192
     @ mysql-test/t/sp-error.test
        Add test case for Bug#15192
     @ mysys/my_alloc.c
        Pass flag to signal fatal error in memory root allocations.
     @ sql/event_data_objects.cc
        Use init_sql_alloc to initialize memory roots, which uses
        the sql error handler to push errors.
     @ sql/ha_partition.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/item_func.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/item_subselect.cc
        Remove redundant fatal error, memory root already pushes error.
     @ sql/opt_sum.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sp_head.cc
        Allocator already sets fatal error.
     @ sql/sql_class.h
        A error must exist for it to be fatal. Pass flag to signal fatal
        error instead of calling fatal_error.
     @ sql/sql_insert.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_list.h
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_parse.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_partition.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_select.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_servers.cc
        Use init_sql_alloc to initialize memory roots, which uses
        the sql error handler to push errors.
     @ sql/sql_show.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/sql_trigger.cc
        Use init_sql_alloc to initialize memory roots, which uses
        the sql error handler to push errors.
     @ sql/sql_update.cc
        Pass flag to signal fatal error instead of calling fatal_error.
     @ sql/tztime.cc
        Use init_sql_alloc to initialize memory roots, which uses
        the sql error handler to push errors.
[10 Nov 2009 20:33] Davi Arnaut
Queued to mysql-next-mr-runtime
[20 Nov 2009 12:55] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:13)
[20 Nov 2009 12:58] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:13)
[22 Nov 2009 0:14] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 10:51] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:16)
[6 Mar 2010 19:13] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.