From: Alexander Nozdrin Date: October 18 2010 6:05am Subject: bzr commit into mysql-5.5-bugteam branch (alexander.nozdrin:3247) Bug#55850 List-Archive: http://lists.mysql.com/commits/120920 X-Bug: 55850 Message-Id: <201010180605.o9HM1RWQ017526@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4689632165068339576==" --===============4689632165068339576== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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. --===============4689632165068339576== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/alexander.nozdrin@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: alexander.nozdrin@stripped\ # lhlax2o6104dpkld # target_branch: file:///home/alik/MySQL/bzr/00/bug55850/mysql-5.5-\ # bugteam-bug55850/ # testament_sha1: c6fd8b217ffe9ff2d6bc9387fed8906df3cee27e # timestamp: 2010-10-18 10:05:41 +0400 # base_revision_id: li-bing.song@stripped\ # cq65ce5e0pf2szgg # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWYpqtIUABv1/gHQ0EAFY9/// f+ffwL////RgDWfNaSAGgAAAPuZQXMxAFKAAAoDCRSE9RomaozAk0/SntEHpIyGgGg9J6JtEAeoJ KVPZAmpspp+qepo0AaNA0NBppmpoAAABxoyZGEYgGE0GATQaBkyaMmQwgMcaMmRhGIBhNBgE0GgZ MmjJkMIDCUqA0AAANAAAADQAAAACSITQBMTQIYQCU8EwjTRqnp6KeoBkeT1JaFwALBhbbkDv1IXY ZdR40iJv01YXIwpYttf4kVygcS3nlt8mOxltSsGd51awvTYp6tBou3I14iw707os2T8fqQX9GoKy xBwKgIsPDPq0pVDM1uy0n9KDPJzS1+pnQzMMB1YOVER0rrjO4iWTJh/Q2a2rAnJIpH2YB1rIVZiD SdCnQ0xsIrsUY1abrsfrTbEIckc2NwDIWgeQtClZjKYw446HGE5b/vbZ94wLg+YOwIg2xNibG1a/ hsAaB/qadq/kdPB3ZQbTY+1luqnQRKe93qeluDV8NEzdlBXbO1lGDPCt4s1dO0A9WWw2KZyBglE4 Zih+YqatYo/mKw9Y1Cy08RrBc4w3LMBfUiDD5i1hSZtoOB3B0BiKT/BtC2FwGDBgxYC+dGQpMYc4 cweGW66AtSm5yd+YuQOM6RgKjsYYZyLji1r1NQVvMGOxIiJvlTU1QdCDfVUdJBUj+7JF3/1+EcBr Krt/rE9zESnsb+Mvk38+4Kbpqty9Icg4uYf9gweRKkJnYkOimlI61sIcHyqh+Iv7CuC4YPUbRgYi QBgiOFwFmnXcsTHFqM1HHz6jOUkjEs2+8UTOulQI+19XL6FHGwB9Xd8W+ez15m09l3J2y+FAb4NX 5JRZIPH5jMgZgZjcnFcbTrPcQ81BQswXJoDwLSgq+gzDAG4ZJdx8Q3AOBeR+4Bh1vkEyk/Bi7Bm+ YX/UkMF9fmq/VKQM7sUH6YL4kmG94pgFigGg44bhhxjMf2wAqD6MDMekOxCea5bDcSDeVws5Mhol xRYgahcQ4lymbZCCkLvpPx6ASOVJV6aOx98XmXTuIKBQMmQpQWNaA80y/rvRTdUFIeHptmhfcy82 S4SdRY9bDS0CEHCgYIrKdMUgiAUDDDJKMggMJgiqxpJJiATQp6FgKGyoqgPSHv+fIutai0cKRi5S Le9XDlMqaLzvLzJULEmyLmT0ugoGCp+AK87AHC/KhIMmAP2iWgu0069QvJV4JSorMQ1BakXMRt2Y 5PnAl9TeMSFNZQMNMMNcZ1muRiNRFblNZjFEz6UAnpZgmFBBhcXcgz8Bi4W8WIMIoIBIMXA1kyfu L1rLDYrDMuEVBqmHekLatpXQHYbbmqCSgmZdaxJRUjZG2WhRWkflKCKTeVE8qYMUV7oqKWQryqk/ nbPP7HaZHxBbxzFZj3W5JdtlpLEmcDQCECVU4k9ZSWdrlaqBRhMa2NZrLDfK7UUaqTbLxIjnWeOw YhwwSH0uszyzyxhSwqJUMQWzIrIdo1MQ1lN05FJKJQUTgVj7bdCF4zoi8y4YFGgu+x2mN1T2XTHx XdWktjVVYurykH3kt0gugNirXLZArOs1wgPT4fouHKu2cxg2moLMDlsXpNpNdZsKSdeBtJZ3uPsu YdRYqBc4QMd2fqBSRWDESeIKFYxf1rwp5+JtxvMLxXat+8u+ZVHQ7SRZVsjmrTKMiLVmwhE4F/Zw Mt0q7zqMiFhQVybcb68RQL9cqayJrKplexoQZmtvYFnUVSGCmZRFrhqZq0gEjXAIxDUO5ZVOFg1g wSGmRKL7ZarzE4a+ZSAq8ptqcevViSGCBlGcDDAiozeVZOBqlEBQGAVYB8AMSw4F1hksC3wUDAL6 nWrAcgNVg+Wui151KEBUOJqWkiSVFLes+QxNMj9GFermUWd4MyVInE6oG1nuRNS6Yg/oCtyA4DCh GwAsUVT82ERRLJTTCCCwk1kdw9CIm/4IbgmCYIiDNdMW82xjY3tiEh+wRGRSB3NvsETCgIOlTbCI SiBiJu07TrBhfEDAgAd32KxREGEwyEbQqGLRz/AGULBBXYIYUG+wmSwDAekUCImGC0LRWFYTFeJw pLASk40222K8JJiW0TIAXgQxJbgXQYSTMkPQVCSyLzMWouCAXhaOJJMa6soGIKgQ4mBG8RkOLQLk i4QUCNrgXiMhwYDQQWCzD+4j/wQWlYqgRQNjMICgIzDTUIMwpEBMNYqwUjAcWgQBKgEUCBzWfgOC iBIVQ6/mTmhUWiKUYiJCLghhaQmN3hRjY4xHSH2Sz6QpRnGlDl7iAHgDIA33JPCjlveSaYOsa6YR hgrNdUVGEx1ztfJQLGU5iSbRGlKMiAY8nsdMqkUqXAHicZgovYipDA/2Yn/qgmhVmNQbDlrkyD90 k6RSQ1G045kVYk6R+SS/2SQzJU/1pJpDP8p2UHgpF6OdN/AF8Il7cB8KgZMXXVnGspaq0ayJOlUL UZ1hOArpIkgqIkSVc4kMLOHeCQkKzjKOU69N8iYN2dDTXEbDYtJsUS3zeU0LGMsZLqynCXblQikc 8nEQSXhSutAYgjk6qLjiuZYVMl4mqjypFIKP8dsVSpKTIdgnTBHQjUj3DHIIFIqA3HK3uckWeJA2 uYC89Khd5G8rvKkDd5Zv5nEeYG4kLO4tSUsfSifDxtORIcF7UlaY08SsxJDLyO+R1hH1GYQ8Ej2E ZERxqUhgfoeKKjnWkHRU8Dca4dOJTCupnkZrkcLSA+ZuDxDnzVjD2hyuScZGnqbaBTJK45iOVB4J cQsYpjN0OLjt8tmDHpOWst6HorL9Km4/w5E047Nmps0WJhQ00pqiOmCDogkuEMe7fWLr0Fh6O4/X IyGY1JrvHL/gscih/lotBqz5DpQSV3qYVIizbkHwJDKyQzAVnnCAchl3VJSUzHJmNJWQBo3GCKzQ +8PYGKICqXel3EULBjdHo7bVbFINbHamXo6yHuIokvWdnEgRSO52ydJkPhg6DzV6CZvixlU4ClL1 08RvnKmUYBFC9VcsjBdTbY30Ja4dRvBViKwzpzOIluPaJisqW97GiSNLRtm3UgaeId6JzEWl4Kgh AoSdmErnOxKWELE1qSn/Zx6b1AQrqPZ3bLLQ2Mwr81SVXbEuRubA11hr+DGRuW43HI6Eyw6dSVng C8ufQjcYHd3m+5SCg1DInuiH18QXWWAwjWFSSYKWDcQSaCDE/W8SwNoLmoF43kXHS2KCB1i7B1oX yQrUg6Tj3yLaEgegfoHxIi7g+DY7EdLirBIMyqaIlh2Eo6EqqxkwyYYTE6k8cGrZEUN1m4cKBJqH vCgnAZAyTJJoyZ/2ZmBKCYikMArK0hHtIlXgMijoJbCZZgJkWUOmYWBSQVfHXt5CGdMhlsBWEAtr P3KYTLEjTmYEjiUjCQSwU2QeHcpVFIq2ZIm7sL/KSvAhnJwmWSWwC/nrC0VsqxOkcxfrtKTkQmJZ 5FQG1hOkDXLM5QBhNJFBRiME/uVpvBTNZx51YjyLfsf5wlwyLFj8T4HVyJHuOyJtR29prN97DDJD JmNHHEethJxMbBvy5hESKDpvSKIDRGe+gcQeC5b/TUjwKgVbWjX5nwTBLlzSXEriHEhqSEbDZVvL YBrQPWkXhYfs7Mg8RoRWodeJ1EsT5QA1WjmTKLEe3ODNQ5GMeMxSWcQ40e+a5H57MLfVTxpGOplj DCEyUAh6aAvs8i+ItTbbbJxszWYCpVAKyLK5KEKB13IjtOiz8Dkk51IBgXTYmLuyj34AC+H4VUBV QAyUCtHQjfEXUb8961Fl4D8ycD8V/AzoPMszw2mMejmFGh6NVgPtDYBFbvzKBQvYWVl6IFxUumiF 5/qvI+/MKhylTSC5Ohi97bDqTDL90isWSGY7DEhqrQolRIvVArtIpe3aaFAg8yw+e9OgGBkmiaMS OwJ+tGAK/z2GCS+4brLR0rRhF4dWvqcuQ9rU+BcexVBNHacTGovSVhDVNPCTE/93n1XP8XckU4UJ CKarSFA= --===============4689632165068339576==--