Bug #36185 Incorrect precedence for warning and exception handlers
| Submitted: | 17 Apr 2008 19:07 | Modified: | 5 Aug 2010 17:32 |
|---|---|---|---|
| Reporter: | Davi Arnaut (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 6.0, 5.0, 5.1 | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[28 Jul 2010 9:46]
Alexander Nozdrin
It'll be fixed by a patch for Bug#23032.
[29 Jul 2010 22:09]
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/114685 3158 Alexander Nozdrin 2010-07-30 This patch fixes the following bugs: - Bug#5889: Exit handler for a warning doesn't hide the warning in trigger - Bug#9857: Stored procedures: handler for sqlwarning ignored - Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP - Bug#36185: Incorrect precedence for warning and exception handlers The problem was in the way warnings/errors during stored routine execution were handled. Prior to this patch the logic was as follows: - when a warning/an error happens: if we're executing a stored routine, and there is a handler for that warning/error, remember the handler, ignore the warning/error and continue execution. - after a stored routine instruction is executed: check for a remembered handler and activate one (if any). This logic caused several problems: - if one instruction generates several warnings (errors) it's impossible to choose the right handler -- a handler for the first generated condition was chosen and remembered for activation. - mess with handling conditions in scopes different from the current one. - not putting generated warnings/errors into Warning Info (Diagnostic Area) is against The Standard. The patch changes the logic as follows: - Diagnostic Area is cleared on the beginning of each statement that either is able to generate warnings, or is able to work with tables. - at the end of a stored routine instruction, Diagnostic Area is left intact. - Diagnostic Area is checked after each stored routine instruction. If an instruction generates several condition, it's now possible to take a look at all of them and determine an appropriate handler.
[30 Jul 2010 11:36]
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/114720 3158 Alexander Nozdrin 2010-07-30 This patch fixes the following bugs: - Bug#5889: Exit handler for a warning doesn't hide the warning in trigger - Bug#9857: Stored procedures: handler for sqlwarning ignored - Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP - Bug#36185: Incorrect precedence for warning and exception handlers The problem was in the way warnings/errors during stored routine execution were handled. Prior to this patch the logic was as follows: - when a warning/an error happens: if we're executing a stored routine, and there is a handler for that warning/error, remember the handler, ignore the warning/error and continue execution. - after a stored routine instruction is executed: check for a remembered handler and activate one (if any). This logic caused several problems: - if one instruction generates several warnings (errors) it's impossible to choose the right handler -- a handler for the first generated condition was chosen and remembered for activation. - mess with handling conditions in scopes different from the current one. - not putting generated warnings/errors into Warning Info (Diagnostic Area) is against The Standard. The patch changes the logic as follows: - Diagnostic Area is cleared on the beginning of each statement that either is able to generate warnings, or is able to work with tables. - at the end of a stored routine instruction, Diagnostic Area is left intact. - Diagnostic Area is checked after each stored routine instruction. If an instruction generates several condition, it's now possible to take a look at all of them and determine an appropriate handler. @ mysql-test/r/signal.result Update result file: 1. handled conditions are not cleared any more; 2. reflect changes in signal.test @ mysql-test/r/signal_demo3.result Update result file: handled conditions are not cleared any more. Due to playing with max_error_count, resulting warning lists have changed. @ mysql-test/r/sp-big.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp-bugs.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp-code.result Update result file: 1. handled conditions are not cleared any more. 2. add result for a new test case in sp-code.test. @ mysql-test/r/sp-error.result Update result file: 1. handled conditions are not cleared any more. 2. add result for a new test case in sp-error.test. @ mysql-test/r/sp.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp_trans.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/strict.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/view.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/innodb_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/memory_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/myisam_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/storedproc.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_sp005.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_trig003.result Update result file: handled conditions are not cleared any more. @ mysql-test/t/signal.test Make a test case more readable in the result file. @ mysql-test/t/sp-code.test Add a test case for Bug#23032 checking that No Data takes precedence on Warning. @ mysql-test/t/sp-error.test Adding test cases for: - Bug#23032 - Bug#36185 - Bug#5889 - Bug#9857 @ mysql-test/t/sp.test Fixing test case to reflect behavioral changes made by the patch. @ sql/sp_head.cc Reset the per-statement warning count before executing a stored procedure instruction. Move to a separate function code which checks the completion status of the executed statement and searches for a handler. Remove redundant code now that search for a handler is done after execution, errors are always pushed. @ sql/sp_pcontext.h Remove unused code. @ sql/sp_rcontext.cc - Polish sp_rcontext::find_handler(): use sp_rcontext::m_hfound instead of an extra local variable; - Remove sp_rcontext::handle_condition(); - Introduce sp_rcontext::activate_handler(), which prepares previously found handler for execution. - Move sp_rcontext::enter_handler() code into activate_handler(), because enter_handler() is used only from there; - Cleanups; - Introduce DBUG_EXECUTE_IF() for a test case in sp-code.test @ sql/sp_rcontext.h - Remove unused code - Cleanups @ sql/sql_class.cc Merge THD::raise_condition_no_handler() into THD::raise_condition(). After the patch raise_condition_no_handler() was called in raise_condition() only. @ sql/sql_class.h Remove raise_condition_no_handler(). @ sql/sql_error.cc Remove Warning_info::reserve_space() -- handled conditions are not cleared any more, so there is no need for RESIGNAL to re-push them. @ sql/sql_error.h Remove Warning_info::reserve_space(). @ sql/sql_signal.cc Handled conditions are not cleared any more, so there is no need for RESIGNAL to re-push them.
[30 Jul 2010 15:30]
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/114769 3158 Alexander Nozdrin 2010-07-30 Auto-merge from mysql-trunk-bugfixing. ****** This patch fixes the following bugs: - Bug#5889: Exit handler for a warning doesn't hide the warning in trigger - Bug#9857: Stored procedures: handler for sqlwarning ignored - Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP - Bug#36185: Incorrect precedence for warning and exception handlers The problem was in the way warnings/errors during stored routine execution were handled. Prior to this patch the logic was as follows: - when a warning/an error happens: if we're executing a stored routine, and there is a handler for that warning/error, remember the handler, ignore the warning/error and continue execution. - after a stored routine instruction is executed: check for a remembered handler and activate one (if any). This logic caused several problems: - if one instruction generates several warnings (errors) it's impossible to choose the right handler -- a handler for the first generated condition was chosen and remembered for activation. - mess with handling conditions in scopes different from the current one. - not putting generated warnings/errors into Warning Info (Diagnostic Area) is against The Standard. The patch changes the logic as follows: - Diagnostic Area is cleared on the beginning of each statement that either is able to generate warnings, or is able to work with tables. - at the end of a stored routine instruction, Diagnostic Area is left intact. - Diagnostic Area is checked after each stored routine instruction. If an instruction generates several condition, it's now possible to take a look at all of them and determine an appropriate handler. @ mysql-test/r/signal.result Update result file: 1. handled conditions are not cleared any more; 2. reflect changes in signal.test @ mysql-test/r/signal_demo3.result Update result file: handled conditions are not cleared any more. Due to playing with max_error_count, resulting warning lists have changed. @ mysql-test/r/sp-big.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp-bugs.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp-code.result Update result file: 1. handled conditions are not cleared any more. 2. add result for a new test case in sp-code.test. @ mysql-test/r/sp-error.result Update result file: 1. handled conditions are not cleared any more. 2. add result for a new test case in sp-error.test. @ mysql-test/r/sp.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/sp_trans.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/strict.result Update result file: handled conditions are not cleared any more. @ mysql-test/r/view.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/innodb_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/memory_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/myisam_storedproc_02.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/funcs_1/r/storedproc.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_sp005.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_sp006_InnoDB.result Update result file: handled conditions are not cleared any more. @ mysql-test/suite/rpl/r/rpl_row_trig003.result Update result file: handled conditions are not cleared any more. @ mysql-test/t/signal.test Make a test case more readable in the result file. @ mysql-test/t/sp-code.test Add a test case for Bug#23032 checking that No Data takes precedence on Warning. @ mysql-test/t/sp-error.test Adding test cases for: - Bug#23032 - Bug#36185 - Bug#5889 - Bug#9857 @ mysql-test/t/sp.test Fixing test case to reflect behavioral changes made by the patch. @ sql/sp_head.cc Reset the per-statement warning count before executing a stored procedure instruction. Move to a separate function code which checks the completion status of the executed statement and searches for a handler. Remove redundant code now that search for a handler is done after execution, errors are always pushed. @ sql/sp_pcontext.h Remove unused code. @ sql/sp_rcontext.cc - Polish sp_rcontext::find_handler(): use sp_rcontext::m_hfound instead of an extra local variable; - Remove sp_rcontext::handle_condition(); - Introduce sp_rcontext::activate_handler(), which prepares previously found handler for execution. - Move sp_rcontext::enter_handler() code into activate_handler(), because enter_handler() is used only from there; - Cleanups; - Introduce DBUG_EXECUTE_IF() for a test case in sp-code.test @ sql/sp_rcontext.h - Remove unused code - Cleanups @ sql/sql_class.cc Merge THD::raise_condition_no_handler() into THD::raise_condition(). After the patch raise_condition_no_handler() was called in raise_condition() only. @ sql/sql_class.h Remove raise_condition_no_handler(). @ sql/sql_error.cc Remove Warning_info::reserve_space() -- handled conditions are not cleared any more, so there is no need for RESIGNAL to re-push them. @ sql/sql_error.h Remove Warning_info::reserve_space(). @ sql/sql_signal.cc Handled conditions are not cleared any more, so there is no need for RESIGNAL to re-push them.
[4 Aug 2010 7:50]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)
[4 Aug 2010 8:02]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:18]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 9:01]
Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[5 Aug 2010 17:32]
Paul DuBois
Noted in 5.5.6 changelog. Handling of warnings and errors during stored program execution was problematic: * If one statement generated several warnings or errors, only the handler for the first was activated, even if another might be ore appropriate. * Warning or error information could be lost.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL calls the handler for the first signaled condition during the execution of a stored procedure statement, meaning that if a warning is signaled first, it will search for a handler at that point and if a handler is found it won't look further for other handlers even if new warning/exception conditions occur. How to repeat: create table t1 (a int, b int not null); delimiter |; create procedure p() begin declare continue handler for sqlwarning select 'warning'; declare continue handler for sqlexception select 'exception'; insert into t1 values (cast('10 ' as signed), NULL); end| delimiter ;| call p(); Although the insert statement throw a exception, only the handler for the warning will be called because the warning generated by the cast is signaled first.