Bug #23032 Handlers declared in a SP do not handle warnings generated in sub-SP
Submitted: 6 Oct 2006 1:00 Modified: 11 Dec 2008 18:07
Reporter: Harrison Fisk
Status: Verified
Category:Server: SP Severity:S3 (Non-critical)
Version:5.0.24a OS:Mac OS X (Mac OS X.4)
Assigned to: Davi Arnaut Target Version:
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[6 Oct 2006 1:00] Harrison Fisk 
Description:
If you have a stored procedure which has a handler for a warning condition, and this
stored procedure invokes another stored procedure which causes that warning, the warning
is not handled by the outer stored procedure handler.

If a warning is generated directly by the stored procedure it is handled.  Also errors
will be propagated to the outer handler, but warnings are not propagated back to the
invoking SP.

Bug#21801 also indicates problems with warnings and handlers, but appears to be a
different issue.

How to repeat:
Test case illustrating the problem:

=====

SET @old_sql_mode = @@sql_mode;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;

CREATE TABLE t1 (col CHAR(3));

SET sql_mode = '';
delimiter //

CREATE PROCEDURE p1 () INSERT INTO t1 VALUES ('aaaaa')//

CREATE PROCEDURE p2 () 
BEGIN
DECLARE CONTINUE HANDLER FOR 1265 BEGIN END;
CALL p1();
END;
//
delimiter ;

-- produces warning
CALL p1();

-- produces warning, but shouldn't?
CALL p2();

DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;

SET @@sql_mode = @old_sql_mode;

======

A test case showing that errors are propagated properly:

======

SET @old_sql_mode = @@sql_mode;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;

CREATE TABLE t1 (col CHAR(3));

SET sql_mode = 'strict_all_tables';
delimiter //

CREATE PROCEDURE p1 () INSERT INTO t1 VALUES ('aaaaa')//

CREATE PROCEDURE p2 () 
BEGIN
DECLARE CONTINUE HANDLER FOR 1406 BEGIN END;
CALL p1();
END;
//
delimiter ;

-- produces error
CALL p1();

-- produces no error
CALL p2();

DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;

SET @@sql_mode = @old_sql_mode;

Suggested fix:
Send the warnings back to the original invoking SP to handle if there is no local
handler.

As a work around, you need to declare all warning handlers local to each SP.
[7 Apr 2008 19: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/45010

ChangeSet@1.2622, 2008-04-07 14:29:44-03:00, davi@mysql.com +22 -0
  Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
  
  The problem is that warnings generated by a statement executed
  inside a stored procedure should only invoke handlers in the
  calling context. Also, the diagnostics and condition areas must
  be cleaned before the execution of stored procedure statements.
  
  The solution is to prepare the enviorement before the execution
  of procedure statements by clearing the diagnostics area (and
  thus condition area). Also handlers now are only called for
  handling warnings generated in the calling context of the executed
  statement.
[15 Apr 2008 19:25] 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/45444

ChangeSet@1.2643, 2008-04-15 14:25:01-03:00, davi@mysql.com +19 -0
  Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
  
  The problem is that warnings generated by a statement executed
  inside a stored procedure should only invoke handlers in the
  calling context. Also, the diagnostics and condition areas must
  be cleaned before the execution of stored procedure statements.
    
  The solution is to prepare the enviorement before the execution
  of procedure statements by clearing the diagnostics area (and
  thus condition area). Also handlers now are only called for
  handling warnings generated in the calling context of the executed
  statement.
[18 Apr 2008 6:04] 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/45587

ChangeSet@1.2618, 2008-04-18 01:03:47-03:00, davi@mysql.com +18 -0
  Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
  
  The problem is that warnings generated by a statement executed
  inside a stored procedure should only invoke handlers in the
  calling context, unless the warning is generated by the last
  statement of the stored procedure.
  
  The solution is to prepare the environment before the execution
  of procedure statements and tightly control if warning were generated
  during the execution of the statement. Also handlers now are only
  called for handling warnings generated in the calling context of the
  executed statement, unless it's the last statement of the stored
  procedure.
  
  Now statements executed inside stored procedure fallow the same
  rules as non-sp statements with respect to effects on the message
  list (refer to SHOW WARNINGS manual entry for more details).
  
  Bug#36185: Incorrect precedence for warning and exception handlers
  
  The problem is that 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.
  
  The solution is to look for handler only at the end of the execution
  of a statement. At the end, it looks first for error and not found
  conditions. If the execution completed successfully (no errors)
  but warnings were generated, it searches for a single handler in a
  first generated first served schema.
[18 Apr 2008 15:20] 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/45606

ChangeSet@1.2618, 2008-04-18 08:39:09-03:00, davi@mysql.com +18 -0
  Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
  
  The problem is that warnings generated by a statement executed
  inside a stored procedure should only invoke handlers in the
  calling context, unless the warning is generated by the last
  statement of the stored procedure.
  
  The solution is to prepare the environment before the execution
  of procedure statements and tightly control if warning were generated
  during the execution of the statement. Also handlers now are only
  called for handling warnings generated in the calling context of the
  executed statement, unless it's the last statement of the stored
  procedure.
  
  Now statements executed inside stored procedure fallow the same
  rules as non-sp statements with respect to effects on the message
  list (refer to SHOW WARNINGS manual entry for more details).
  
  Bug#36185: Incorrect precedence for warning and exception handlers
  
  The problem is that 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.
  
  The solution is to look for handler only at the end of the execution
  of a statement. At the end, it looks first for error and not found
  conditions. If the execution completed successfully (no errors)
  but warnings were generated, it searches for a single handler in a
  first generated first served schema.
[24 Apr 2008 0:53] 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/45925

ChangeSet@1.2628, 2008-04-23 19:52:42-03:00, davi@mysql.com +22 -0
  Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
  
  The problem is that warnings generated by a statement executed
  inside a stored procedure should only invoke handlers in the
  calling context, unless the warning is generated by the last
  statement of the stored procedure.
  
  The solution is to prepare the environment before the execution
  of procedure statements and tightly control if warning were generated
  during the execution of the statement. Also handlers now are only
  called for handling warnings generated in the calling context of the
  executed statement, unless it's the last statement of the stored
  procedure.
  
  This is a incompatible change, now statements executed inside stored
  procedure fallow the same rules as non-sp statements with respect to
  effects on the message list (refer to SHOW WARNINGS manual entry for
  more details).
  
  Bug#36185: Incorrect precedence for warning and exception handlers
  
  The problem is that 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.
  
  The solution is to look for handler only at the end of the execution
  of a statement. At the end, it looks first for error and not found
  conditions. If the execution completed successfully (no errors)
  but warnings were generated, it searches for a single handler in a
  first generated first served schema.
[27 Apr 2008 9:16] Sveta Smirnova 
Bug #36348 was marked as duplicate of this one.