Bug #55850 Trigger warnings not cleared
Submitted: 9 Aug 2010 17:40 Modified: 16 Nov 2010 3:26
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5.6-m3, 5.6.0-m4 OS:Linux (SUSE 64-bit)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[9 Aug 2010 17:40] Peter Gulutzan
Description:
Quoting the SQL standard, at end of "routine invocation",
the DBMS should copy the routine's condition to the caller's
condition "if the [SQLSTATE value in the routine's condition area]
does not represent successful completion".

But for a trigger, the wording is different.
SQL/Foundation Section 15.19 Execution of Triggers says:
"If the execution of TSS [i.e. the trigger SQL statement] is not
successful, then an exception condition is raised: triggered action
exception. The exception condition that caused TSS to fail is raised.
NOTE 409 — Raising the exception condition that caused TSS to fail
enters the exception information into the diagnostics area that was
pushed prior to the execution of TSS."

"Successful execution" is more than "SQLSTATE = successful completion".
SQL/Framework 6.3.3.7 Exceptions says:
"... unless an exception condition is ... raised, the execution of the
SQL-statement is successful."

So the words about triggers in Section 15.19 apply to exceptions,
only. Completion conditions would be destroyed when trigger execution
ends.

MySQL doesn't do that. It preserves warnings. I think that we could
say that's an extension of the standard, rather than a violation.
But I've said I'd be happy if this was changed,
and Konstantin Osipov has said "let's be closer to the standard".

How to repeat:
For example I see a warning with this script:

delimiter //
drop table if exists t1,t2//
create table t1 (s1 smallint)//
create table t2 (s1 smallint)//
create trigger t_bi before insert on t1 for each row
begin
  insert into t2 values (99999);
  end//
insert into t1 values (0)//
show warnings//

Sample run:

mysql> delimiter //
mysql> drop table if exists t1,t2//
Query OK, 0 rows affected (2.05 sec)

mysql> create table t1 (s1 smallint)//
Query OK, 0 rows affected (0.22 sec)  

mysql> create table t2 (s1 smallint)//
Query OK, 0 rows affected (0.57 sec)  

mysql> create trigger t_bi before insert on t1 for each row
    -> begin
    ->   insert into t2 values (99999);
    ->   end//
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (0)//
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings//
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
[9 Aug 2010 17:43] Valeriy Kravchuk
Verified just as described:

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.5.6-m3-debug |
+----------------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> drop table if exists t1,t2//
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t1 (s1 smallint)//
Query OK, 0 rows affected (0.45 sec)

mysql> create table t2 (s1 smallint)//
Query OK, 0 rows affected (0.14 sec)

mysql> create trigger t_bi before insert on t1 for each row
    -> begin
    ->   insert into t2 values (99999);
    ->   end//
Query OK, 0 rows affected (0.36 sec)

mysql> insert into t1 values (0)//
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings//
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
[10 Aug 2010 11:06] Konstantin Osipov
Regression introduced by the patch for Bug#23032
[6 Oct 2010 19:06] 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/120166

3097 Alexander Nozdrin	2010-10-06
      Preliminary patch for Bug#55850 (Trigger warnings not cleared).
      
      The problem was that the warnings risen by a trigger were not cleared.
      The warnings should be cleared if the trigger completes successfully.
      
      The fix is to remove "trigger warnings" after executing the trigger
      in Table_triggers_list::process_triggers().
[14 Oct 2010 13:07] 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/120772

3097 Alexander Nozdrin	2010-10-14
      Patch for Bug#55850 (Trigger warnings not cleared).
      
      The problem was that the warnings risen by a trigger were not cleared.
      The warnings should be cleared if the trigger completes successfully.
      
      The fix is to skip merging warnings into caller's Warning Info for triggers.
[18 Oct 2010 6:05] 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/120920

3247 Alexander Nozdrin	2010-10-18
      Patch for Bug#55850 (Trigger warnings not cleared).
      
      The problem was that the warnings risen by a trigger were not cleared upon
      successful completion. The warnings should be cleared if the trigger completes
      successfully.
      
      The fix is to skip merging warnings into caller's Warning Info for triggers.
[26 Oct 2010 11:50] 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/121921

3098 Alexander Nozdrin	2010-10-26
      Patch for Bug#55850 (Trigger warnings not cleared).
      
      The problem was that the warnings risen by a trigger were not cleared upon
      successful completion. The warnings should be cleared if the trigger completes
      successfully.
      
      The fix is to skip merging warnings into caller's Warning Info for triggers.
[26 Oct 2010 11:52] Alexander Nozdrin
Pushed to 5.5-bugteam.
[27 Oct 2010 7:18] 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/122036

3101 Alexander Nozdrin	2010-10-27
      Follow-up for Bug#55850: update funcs_1 result files.
[13 Nov 2010 16:16] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:27] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 3:26] Paul DuBois
Noted in 5.5.7 changelog.

Warnings raised by a trigger were not cleared upon successful
completion. Now warnings are cleared if the trigger completes
successfully, per the SQL standard.
[5 Dec 2010 22:01] Paul DuBois
Correction: This is in the 5.5.8 changelog (not 5.5.7).
[16 Dec 2010 22:24] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)