Bug #9857 Stored procedures: handler for sqlwarning ignored
Submitted: 12 Apr 2005 20:17 Modified: 6 Aug 2010 10:45
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0 OS:Linux (SUSE 9.2)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[12 Apr 2005 20:17] Peter Gulutzan 
Description:
Even though "division by zero" is classed as a warning when sql_mode=traditional, a stored procedure handler treats it as an error. That is, it is caught by an sqlexception handler rather than by an sqlwarning handler.

How to repeat:
mysql> set sql_mode='traditional'//
Query OK, 0 rows affected (0.00 sec)

mysql> select 5 / 0//
+-------+
| 5 / 0 |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings//
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.00 sec)

mysql> create procedure p17 () begin declare continue handler for sqlwarning select 'warning'; select 5 / 0; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p17()//
+-------+
| 5 / 0 |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure p18 () begin declare continue handler for sqlexception select 'exception'; select 5 / 0; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p18()//
+-------+
| 5 / 0 |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)

+-----------+
| exception |
+-----------+
| exception |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

/* Incidentally, the SELECT should have failed in the last example, but I think there's a different bug report for that. */
[13 Apr 2005 15:24] Per-Erik Martin 
The problem is a combination of a limitation in
  error handling: we can't interrupt "select" on error, so it's sending
  what should be an error as a warning, but with level "error".
  The handler is thus correct (sqlstate '22012' is an error). Changing the
  level from "error" to "warning" would make the sqlwarning handler catch,
  but this instead breaks a number of tests in strict mode.
  The actual bug is in the discrepancy between internal error handling
  in the server, and the requirements of the SQL standard.
[23 Aug 2005 17:32] Per-Erik Martin 
The behaviour has change here. The second procedure now give the same result as the first one.
[20 Jan 2006 9:16] Per-Erik Martin 
The bahaviour is now back to the original one, i.e. the sqlexception handler is triggered in the second example.
[23 Jan 2006 9:51] Per-Erik Martin 
Bug#10976 was marked as a duplicate of this bug.
[1 Jun 2006 20:58] Peter Gulutzan 
Here's another case. For this, both SQLWARNING nor SQLEXCEPTION handlers are ignored.

mysql> create procedure pm () begin declare continue handler for sqlexception select 'exception'; declare continue handler for sqlwarning select 'warning'; drop table if exists tt; end//
Query OK, 0 rows affected (0.01 sec)

mysql> call pm()//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings//
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 'tt' |
+-------+------+--------------------+
1 row in set (0.02 sec)
[8 Apr 2008 2:01] Davi Arnaut 
For the last comment example, the error level is a NOTE, not  WARNING, hence the warning handler is not called. As for the division by zero under "traditional" mode, the manual describes that the TRADITIONAL mode is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, hence division by zero is threaded as a error.

What needs to be determined and confirmed in this bug is whether division by zero is a error or a warning under the TRADITIONAL sql_mode and that MySQL notes are not caught by warning handlers.
[8 Apr 2008 2:10] Davi Arnaut 
Lousily related to Bug#13817, Bug#8423, Bug#6105.
[8 Apr 2008 16:17] Davi Arnaut 
After discussing with Konstantin Osipov, we observed that this is actually a bug in the TRADITIONAL mode and once that is fixed SP handlers should work properly. His reasoning follows:

Currently if the "traditional" mode is set, the server produces more warnings and if "strict" mode is also set, warnings become errors depending on where in the query the warning occurred and on the combinations of sql_mode.

The problem is that this is a naive interpretation of the standard, for example, a division by zero is always a error no matter where it occurs. The server wrongly upgrades *all* warnings to errors and it wrongly downgrades some errors to warnings in sql_mode="traditional".
[22 Apr 2008 16:09] Peter Gulutzan 
On 2008-04-08 Davi Arnaut commented:
"For the last comment example, the error level is a NOTE, not  WARNING,
hence the warning handler is not called."

Look at the MySQL Reference Manual:
http://dev.mysql.com/doc/refman/5.1/en/set-option.html
"SQL_NOTES = {0 | 1}
 If set to 1 (the default), warnings of Note level are recorded.
 If set to 0, Note warnings are suppressed."
That means a note is a warning, hence the warning handler should be called.
[28 Jul 2010 9:45] 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:29] 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 7:55] 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 7:56] 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 7:57] 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 7:58] 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 7:59] 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:00] 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:05] 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:13] 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:14] 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:15] 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:16] 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:17] 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:21] 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:00] 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:20)
[4 Aug 2010 9:02] Bugs System 
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[6 Aug 2010 10:45] 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.