| 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: | |
| 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 | ||
[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.
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();