| Bug #55852 | Possibly inappropriate handler activation | ||
|---|---|---|---|
| Submitted: | 9 Aug 2010 17:45 | Modified: | 16 Oct 2011 22:27 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.1, 5.6.0-m4 | OS: | Linux (SUSE 64-bit) |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[9 Aug 2010 18:22]
MySQL Verification Team
Thank you for the bug report.
[14 Oct 2010 12:35]
Alexander Nozdrin
Related to Bug#38806.
[18 Nov 2010 11:56]
Alexander Nozdrin
This bug is related to Bug#38806 and will be fixed by the patch for that bug.
[16 Oct 2011 22:27]
Paul DuBois
Noted in 5.6.4 changelog. If an error occurred in a context where different handlers were present at different levels of nesting, an outer handler could be chosen rather than the innermost one.
Description: I'm using mysql-trunk-bugfixing. The SQL standard document says: "8) If there is a general<handler declaration> and a specific <handler declaration> for the same<condition value> in the same scope, then only the specific<handler declaration> is associated with that<condition value>." A general handler declaration has SQLWARNING + SQLEXCEPTION + NOT FOUND. A specific handler declaration has SQLSTATE, condition name, or (for us) an errno. So when there are multiple handlers in the same scope, for example DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'sqlwarning'; DECLARE EXIT HANDLER FOR SQLSTATE '22003' SELECT 'sqlstate'; DECLARE EXIT HANDLER FOR 1264 SELECT '1264'; INSERT INTO t (smallint_column) VALUES (99999); They're all applicable, but MySQL very rightly activates the most specific handler, that is, '1264'. But notice the standard's exact words: "in the same scope". MySQL is activating a more specific handler even if it's in a different, containing, scope. How to repeat: For example, this script will say '22003': delimiter // drop procedure if exists p// drop table if exists t// set @@sql_mode=''// create table t (s1 smallint)// create procedure p () begin declare continue handler for sqlstate '22003' select '22003'; begin declare continue handler for sqlwarning select 'sqlwarning'; insert into t values (99999); end; end// call p()// That appears wrong to me. I'm sure we all know that, when there are multiple handlers in different scopes, the "more appropriate" handler is the one in the innermost compound statement. So I'd expect the script to display 'sqlwarning'.