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: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.1 | OS: | Any |
Assigned to: | Davi Arnaut | CPU Architecture: | Any |
Tags: | rt_q1_2007, unscheduled |
[24 Aug 2006 0:19]
Marc ALFF
[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.