| 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: | |
| 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: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)
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)