| 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.
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.