MySQL Bugs: #5889: Exit handler for a warning doesn't hide the warning in trigger

Bug #5889	Exit handler for a warning doesn't hide the warning in trigger
Submitted:	5 Oct 2004 0:57	Modified:	6 Aug 2010 10:45
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
If a trigger causes a warning, I see "Warnings: 1" 
after executing the statement that activates the 
triger. But the warning is handled (by a handler) 
within the trigger. So "Warnings: 1" is confusing. 

How to repeat:
mysql> create trigger x4_bu before update on x4 for each row begin declare exit handler 
for sqlwarning set new.col1=7; set new.col1 = 77777777777; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> update x4 set col2 = 1;// 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 1 
 
mysql> show warnings// 
Empty set (0.00 sec)

Verified with 5.0.2-alpha-debug-log.

Test case:

create table x4(col1 int,col2 int);
insert into x4 values (7,5);
delimiter //
create trigger x4_bu before update on x4 for each row
begin
declare exit handler 
for sqlwarning set new.col1=7; set new.col1 = 77777777777;
end //
update x4 set col2 = 1// 
show warnings//

This bug is still repeatable in MySQL 5.0.17

Please note that number of "warnings" in "Rows matched: 1  Changed: 1  Warnings: 1" string is not really number of warnings produced by the update statement. It is rather number of new values for fields which were adjusted to fit in these fields (to correspond restrictions of column types).
So it is OK that this number does not correspond to number of 'real' warnings. But it is still open question if number of fields adjusted for update statement should include number of fields adjusted in trigger that this statement has invoked.

Here is another test case.

mysql> delimiter //
mysql> create table t (t_1 int)//
Query OK, 0 rows affected (0.00 sec)

mysql> create table u (u_1 char(1))//
Query OK, 0 rows affected (0.28 sec)

mysql> create trigger t_bi before insert on t for each row begin insert into u values ('ab'); insert into u values ('a'); end//
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (0)//
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings//
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'u_1' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

The first "insert into u" statement causes the warning.
The second "insert into u" statement should clear the warning.
But it doesn't, so the user sees it.

This bug will be fixed by a patch for Bug#23032.

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.

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.

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.

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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:20)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)

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.