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:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:6.0, 5.0, 5.1 OS:Any
Assigned to: Alexander Nozdrin
Triage: Triaged: D4 (Minor)

[17 Apr 2008 19:07] Davi Arnaut
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.
[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.