#At file:///home/alik/MySQL/bzr/00/bug55850/mysql-5.5-bugteam-bug55850/ based on revid:li-bing.song@stripped
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.
modified:
mysql-test/r/sp-error.result
mysql-test/t/sp-error.test
sql/sp_head.cc
sql/sp_head.h
=== modified file 'mysql-test/r/sp-error.result'
--- a/mysql-test/r/sp-error.result 2010-07-30 15:28:36 +0000
+++ b/mysql-test/r/sp-error.result 2010-10-18 06:05:35 +0000
@@ -1877,9 +1877,6 @@ DROP PROCEDURE p1;
#
# Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
#
-
-# - Case 1
-
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
@@ -1889,40 +1886,13 @@ SET NEW.a = 10;
SET NEW.a = 99999999999;
END|
UPDATE t1 SET b = 20;
-Warnings:
-Warning 1264 Out of range value for column 'a' at row 1
SHOW WARNINGS;
Level Code Message
-Warning 1264 Out of range value for column 'a' at row 1
SELECT * FROM t1;
a b
10 20
DROP TRIGGER t1_bu;
DROP TABLE t1;
-
-# - Case 2
-
-CREATE TABLE t1(a INT);
-CREATE TABLE t2(b CHAR(1));
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-BEGIN
-INSERT INTO t2 VALUES('ab'); # Produces a warning.
-INSERT INTO t2 VALUES('b'); # Does not produce a warning,
-# previous warning should be cleared.
-END|
-INSERT INTO t1 VALUES(0);
-SHOW WARNINGS;
-Level Code Message
-SELECT * FROM t1;
-a
-0
-SELECT * FROM t2;
-b
-a
-b
-DROP TRIGGER t1_bi;
-DROP TABLE t1;
-DROP TABLE t2;
#
# Bug#9857: Stored procedures: handler for sqlwarning ignored
#
@@ -1961,3 +1931,64 @@ Warning 1365 Division by 0
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET sql_mode = @sql_mode_saved;
+#
+# Bug#55850: Trigger warnings not cleared.
+#
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
+CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT,
+d SMALLINT, e SMALLINT, f SMALLINT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT INTO t2(a, b, c) VALUES(99999, 99999, 99999);
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+INSERT INTO t2(d, e, f) VALUES(99999, 99999, 99999);
+CREATE PROCEDURE p1()
+INSERT INTO t1 VALUES(99999, 99999, 99999);
+
+CALL p1();
+Warnings:
+Warning 1264 Out of range value for column 'x' at row 1
+Warning 1264 Out of range value for column 'y' at row 1
+Warning 1264 Out of range value for column 'z' at row 1
+
+SHOW WARNINGS;
+Level Code Message
+Warning 1264 Out of range value for column 'x' at row 1
+Warning 1264 Out of range value for column 'y' at row 1
+Warning 1264 Out of range value for column 'z' at row 1
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP PROCEDURE p1;
+# ----------------------------------------------------------------------
+CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
+CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+INSERT INTO t2 VALUES(
+CAST('111111 ' AS SIGNED),
+CAST('222222 ' AS SIGNED),
+NULL);
+END|
+CREATE PROCEDURE p1()
+INSERT INTO t1 VALUES(99999, 99999, 99999);
+
+CALL p1();
+ERROR 23000: Column 'c' cannot be null
+
+SHOW WARNINGS;
+Level Code Message
+Warning 1264 Out of range value for column 'x' at row 1
+Warning 1264 Out of range value for column 'y' at row 1
+Warning 1264 Out of range value for column 'z' at row 1
+Warning 1292 Truncated incorrect INTEGER value: '111111 '
+Warning 1264 Out of range value for column 'a' at row 1
+Warning 1292 Truncated incorrect INTEGER value: '222222 '
+Warning 1264 Out of range value for column 'b' at row 1
+Error 1048 Column 'c' cannot be null
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP PROCEDURE p1;
=== modified file 'mysql-test/t/sp-error.test'
--- a/mysql-test/t/sp-error.test 2010-07-30 15:28:36 +0000
+++ b/mysql-test/t/sp-error.test 2010-10-18 06:05:35 +0000
@@ -2719,10 +2719,6 @@ DROP PROCEDURE p1;
--echo # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
--echo #
---echo
---echo # - Case 1
---echo
-
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);
@@ -2747,36 +2743,6 @@ SELECT * FROM t1;
DROP TRIGGER t1_bu;
DROP TABLE t1;
---echo
---echo # - Case 2
---echo
-
-CREATE TABLE t1(a INT);
-CREATE TABLE t2(b CHAR(1));
-
-delimiter |;
-
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-BEGIN
- INSERT INTO t2 VALUES('ab'); # Produces a warning.
-
- INSERT INTO t2 VALUES('b'); # Does not produce a warning,
- # previous warning should be cleared.
-END|
-
-delimiter ;|
-
-INSERT INTO t1 VALUES(0);
-
-SHOW WARNINGS;
-
-SELECT * FROM t1;
-SELECT * FROM t2;
-
-DROP TRIGGER t1_bi;
-DROP TABLE t1;
-DROP TABLE t2;
-
--echo #
--echo # Bug#9857: Stored procedures: handler for sqlwarning ignored
--echo #
@@ -2813,3 +2779,83 @@ SHOW WARNINGS;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET sql_mode = @sql_mode_saved;
+
+--echo #
+--echo # Bug#55850: Trigger warnings not cleared.
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
+CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT,
+ d SMALLINT, e SMALLINT, f SMALLINT);
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+ INSERT INTO t2(a, b, c) VALUES(99999, 99999, 99999);
+
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+ INSERT INTO t2(d, e, f) VALUES(99999, 99999, 99999);
+
+CREATE PROCEDURE p1()
+ INSERT INTO t1 VALUES(99999, 99999, 99999);
+
+# What happened before the patch was:
+# - INSERT INTO t1 added 3 warnings about overflow in 'x', 'y' and 'z' columns;
+# - t1_bi run and added 3 warnings about overflow in 'a', 'b' and 'c' columns;
+# - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns;
+# => we had 9 warnings.
+#
+# Now what happens is:
+# - INSERT INTO t1 adds 3 warnings about overflow in 'x', 'y' and 'z' columns;
+# - t1_bi adds 3 warnings about overflow in 'a', 'b' and 'c' columns;
+# - The warnings added by triggers are cleared;
+# - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns;
+# - The warnings added by triggers are cleared;
+# => we have 3 warnings.
+
+--echo
+CALL p1();
+
+--echo
+SHOW WARNINGS;
+
+--echo
+DROP TABLE t1;
+DROP TABLE t2;
+DROP PROCEDURE p1;
+
+--echo # ----------------------------------------------------------------------
+
+CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
+CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL);
+
+delimiter |;
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ INSERT INTO t2 VALUES(
+ CAST('111111 ' AS SIGNED),
+ CAST('222222 ' AS SIGNED),
+ NULL);
+END|
+
+delimiter ;|
+
+CREATE PROCEDURE p1()
+ INSERT INTO t1 VALUES(99999, 99999, 99999);
+
+--echo
+--error ER_BAD_NULL_ERROR
+CALL p1();
+
+--echo
+SHOW WARNINGS;
+
+--echo
+DROP TABLE t1;
+DROP TABLE t2;
+DROP PROCEDURE p1;
=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc 2010-10-13 09:34:02 +0000
+++ b/sql/sp_head.cc 2010-10-18 06:05:35 +0000
@@ -1176,10 +1176,16 @@ find_handler_after_execution(THD *thd, s
/**
Execute the routine. The main instruction jump loop is there.
Assume the parameters already set.
+
+ @param thd Thread context.
+ @param merge_warnings_to_caller Flag specifying if Warning Info should be
+ propagated to the caller or not.
+
@todo
- Will write this SP statement into binlog separately
(TODO: consider changing the condition to "not inside event union")
+ @return Error status.
@retval
FALSE on success
@retval
@@ -1187,7 +1193,7 @@ find_handler_after_execution(THD *thd, s
*/
bool
-sp_head::execute(THD *thd)
+sp_head::execute(THD *thd, bool merge_warnings_to_caller)
{
DBUG_ENTER("sp_head::execute");
char saved_cur_db_name_buf[NAME_LEN+1];
@@ -1484,8 +1490,15 @@ sp_head::execute(THD *thd)
thd->stmt_arena= old_arena;
state= EXECUTED;
- /* Restore the caller's original warning information area. */
- saved_warning_info->merge_with_routine_info(thd, thd->warning_info);
+ /*
+ Restore the caller's original warning information area:
+ - warnings generated during trigger execution should not be
+ propagated to the caller on success;
+ - if there was an exception during execution, warning info should be
+ propagated to the caller in any case.
+ */
+ if (err_status || merge_warnings_to_caller)
+ saved_warning_info->merge_with_routine_info(thd, thd->warning_info);
thd->warning_info= saved_warning_info;
done:
@@ -1707,7 +1720,7 @@ sp_head::execute_trigger(THD *thd,
thd->spcont= nctx;
- err_status= execute(thd);
+ err_status= execute(thd, FALSE);
err_with_cleanup:
thd->restore_active_arena(&call_arena, &backup_arena);
@@ -1924,7 +1937,7 @@ sp_head::execute_function(THD *thd, Item
*/
thd->set_n_backup_active_arena(&call_arena, &backup_arena);
- err_status= execute(thd);
+ err_status= execute(thd, TRUE);
thd->restore_active_arena(&call_arena, &backup_arena);
@@ -2157,7 +2170,7 @@ sp_head::execute_procedure(THD *thd, Lis
#endif
if (!err_status)
- err_status= execute(thd);
+ err_status= execute(thd, TRUE);
if (save_log_general)
thd->variables.option_bits &= ~OPTION_LOG_OFF;
=== modified file 'sql/sp_head.h'
--- a/sql/sp_head.h 2010-08-16 15:16:07 +0000
+++ b/sql/sp_head.h 2010-10-18 06:05:35 +0000
@@ -527,7 +527,7 @@ private:
HASH m_sptabs;
bool
- execute(THD *thd);
+ execute(THD *thd, bool merge_warnings_to_caller);
/**
Perform a forward flow analysis in the generated code.
Attachment: [text/bzr-bundle] bzr/alexander.nozdrin@oracle.com-20101018060535-lhlax2o6104dpkld.bundle
