Bug #21801 SQL exception handlers and warnings
Submitted: 24 Aug 2006 0:19 Modified: 12 Feb 2008 20:50
Reporter: Marc Alff Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Davi Arnaut
Tags: rt_q1_2007, unscheduled
Triage: D2 (Serious)

[24 Aug 2006 0:19] Marc Alff
Description:
Calling a stored procedure that :
- has an exception handler for warnings
- generates a warning
results in a query that reports the warning, where it should be silenced
by the exception handler.

See the email attachments for a precise use case and discussion about this.

How to repeat:

See attachments
[24 Aug 2006 0:21] Marc Alff
Hi Konstantin,

On Tue, 2006-08-22 at 11:31 +0400, Konstantin Osipov wrote:
> > * Peter Gulutzan <*****> [06/08/22 04:10]:
>> > > We were talking on IRC about exceptions in stored procedures.
>> > > I said I knew of a problem with 'notes', but could
>> > > not see it searching bugs.mysql.com. Perhaps you'll remember.
>> > > 
>> > > How to repeat:
>> > > 
>> > > mysql> delimiter //
>> > > 
>> > > mysql> create procedure p57 () begin declare continue handler for
>> > > sqlwarning select 'warning'; declare continue handler for sqlexception
>> > > select 'exception'; create table t57 (s1 int) type=innodb; insert into
>> > > t57 values (5); end//
>> > > Query OK, 0 rows affected, 1 warning (0.00 sec)
>> > > 
>> > > mysql> call p57()//
>> > > Query OK, 0 rows affected, 1 warning (0.02 sec)
>> > > 
>> > > It says '1 warning' because I said 'type=' instead of 'engine='.
>> > > But the handler ignores it; I can't catch it with a handler.
>> > > And the later INSERT statement doesn't clear it. It's immortal.
> > 
> > I tried and failed to repeat it. The continue handler is invoked,
> > a subsequent insert statement resets warnings.
> > 

Actually it appears that you did repeat it.
I meant "the INSERT statement within the stored procedure".

> > But on the second execution the continue handler is not called, and I think
> > it's a bug:
> > 
> > mysql> create procedure p57 () begin declare continue handler for sqlwarning select 'warning'; declare continue handler for sqlexception select 'exception'; create table t57 (s1 int) type=innodb; insert into t57 values (5); end//
> > Query OK, 0 rows affected, 1 warning (0.00 sec)
> > 
> > mysql> call p57()//
> > +---------+
> > | warning |
> > +---------+
> > | warning | 
> > +---------+
> > 1 row in set (0.00 sec)
> > 
> > Query OK, 1 row affected, 1 warning (0.00 sec)
> > 

See -- it sees "1 warning". There was no reset.

> > mysql> show warnings//
> > +---------+------+--------------------------------------------------------------------------+
> > | Level   | Code | Message                                                                  |
> > +---------+------+--------------------------------------------------------------------------+
> > | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | 
> > +---------+------+--------------------------------------------------------------------------+
> > 1 row in set (0.00 sec)
> > 
> > mysql> insert into t57 (s1) values (2)//
> > Query OK, 1 row affected (0.00 sec)
> > 
> > mysql> show warnings//
> > Empty set (0.00 sec)
> > 
> > mysql> drop table t57//
> > Query OK, 0 rows affected (0.00 sec)
> > 
> > mysql> call p57()//
> > Query OK, 1 row affected (0.01 sec)
> > 
> > mysql> show warnings//
> > Empty set (0.00 sec)
> > 
> > 
> > Is it correct that the warning is added to the stack even though
> > there was a handler found for it?
> > 

No. Now, another example:

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

mysql> create procedure p58 () begin declare continue handler for
sqlwarning select 'warning'; insert into t58 values
(99999999999999999999); end//
Query OK, 0 rows affected (0.41 sec)

mysql> call p58()//
+---------+
| warning |
+---------+
| warning |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Notice it doesn't say "Query OK, 1 row affected, 1 warning (0.00 sec)".

I guess, then, that you didn't find this is a duplicate of an
earlier bug. Please flip a coin, and then tell me whether you
will report it or I should report it.

-- Peter Gulutzan, Senior Software Architect
[19 Dec 2007 18:05] Marc Alff
Analysis

The following warning:
Warning        1287    'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
is raised by the parser, when executing the CREATE PROCEDURE statement,
so it's expected that this warning is not caught by any exception handler inside the stored procedure.

Investigating this report, however, exposed the following:
when a statement raise both a warning and an exception,
- the exception handler for the warning is found, and recorded in
sp_rcontext::m_hfound
- the exception handler for the error is *not* looked up, because of

bool
sp_rcontext::find_handler(THD *thd, uint sql_errno,
                          MYSQL_ERROR::enum_warning_level level)
{
  if (m_hfound >= 0)
    return 1;                   // Already got one

...

Later, the handler for the warning is executed, but the handler for the exception is not executed.
[4 Feb 2008 18:40] 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/41664

ChangeSet@1.2516, 2008-02-04 16:39:55-02:00, davi@mysql.com +3 -0
  Bug#21801 SQL exception handlers and warnings
  
  The problem is that deprecated syntax warnings were not being
  suppressed when the stored routine is being parsed for the first
  execution. It's doesn't make sense to print out deprecated
  syntax warnings when the routine is being executed because this
  kind of warning only matters when the routine is being created.
  
  The solution is to suppress deprecated syntax warnings when
  parsing the stored routine for loading into the cache (might
  mean that the routine is being executed for the first time).
[6 Feb 2008 14:55] Davi Arnaut
Pushed in 5.1-runtime
[7 Feb 2008 19:11] Davi Arnaut
Note that there are no deprecated warnings in 6.0, pushing with test case disabled.
[11 Feb 2008 16:23] Bugs System
Pushed into 5.1.24-rc
[11 Feb 2008 16:26] Bugs System
Pushed into 6.0.5-alpha
[12 Feb 2008 20:50] Paul Dubois
Noted in 5.1.24 changelog.

Warnings for deprecated syntax constructs used in stored routines
make sense to report only when the routine is being created, but they
were also being reported when the routine was parsed for loading into
the execution cache. Now they are reported only at routine creation
time.
[12 Feb 2008 20:58] Paul Dubois
Also noted in 6.0.5 changelog.
[6 Mar 2008 5:18] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[29 Mar 2008 23:53] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.