From: Dao-Gang.Qu Date: December 3 2009 8:35am Subject: bzr commit into mysql-5.1-bugteam branch (Dao-Gang.Qu:3233) Bug#42851 List-Archive: http://lists.mysql.com/commits/92596 X-Bug: 42851 Message-Id: <200912030836.nB38aKxe029971@daogangqu-desktop> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0937803860==" --===============0937803860== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/daogangqu/mysql/bzrwork/bugtest/mysql-5.1-bugteam/ based on revid:dao-gang.qu@stripped 3233 Dao-Gang.Qu@stripped 2009-12-03 Bug #42851 Spurious "Statement is not safe to log in statement format." warnings The unsafe warnings in error log make error log grow too large. To fix the problem, the unsafe warnings only will be written to error log, when the level of --log-warnings option >= 2. @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result Test Result for BUG#42851. @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test Added the test file to verify the unsafe warnings will be written to error log when the level of --log-warnings option >= 2, and the 'SHOW WARNINGS' is not controlled by --log-warnings option. modified: mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test sql/sql_class.cc === modified file 'mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result' --- a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result 2009-07-31 13:00:35 +0000 +++ b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result 2009-12-03 08:35:00 +0000 @@ -43,8 +43,87 @@ SET GLOBAL LOG_WARNINGS = 1; INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); Warnings: Note 1592 Statement may not be safe to log in statement format. +SET GLOBAL LOG_WARNINGS = 2; +INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +Warnings: +Note 1592 Statement may not be safe to log in statement format. DROP TABLE t1; SET GLOBAL log_warnings = @old_log_warnings; # Count the number of times the "Unsafe" message was printed # to the error log. Occurrences: 1 +set @save_log_warnings= @@global.log_warnings; +CREATE TABLE `t1` ( +`recNo` int(10) unsigned NOT NULL AUTO_INCREMENT, +`string` varchar(64) NOT NULL, +`inUseBy` varchar(38) NOT NULL DEFAULT '', +`tsLastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE +CURRENT_TIMESTAMP, +PRIMARY KEY (`recNo`), +KEY `tsLastUpdated` (`tsLastUpdated`), +KEY `inUseBy` (`inUseBy`) +); +INSERT INTO t1 SET string='one'; +INSERT INTO t1 SET string='two'; +INSERT INTO t1 SET string='three'; +set @@global.log_warnings= 0; +select @@global.log_warnings; +@@global.log_warnings +0 +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings < 2' limit 1; +# When the --log-warnings option is set to 0, Test the unsafe +# warning will be displayed by 'SHOW WARNINGS', but it will not +# be written to error log. +SHOW WARNINGS; +Level Code Message +Note 1592 Statement may not be safe to log in statement format. +set @@global.log_warnings= 1; +select @@global.log_warnings; +@@global.log_warnings +1 +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings < 2' limit 2; +# When the --log-warnings option is set to 1, Test the unsafe +# warning will be displayed by 'SHOW WARNINGS', but it will not +# be written to error log. +SHOW WARNINGS; +Level Code Message +Note 1592 Statement may not be safe to log in statement format. +set @@global.log_warnings= 2; +select @@global.log_warnings; +@@global.log_warnings +2 +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 3; +# When the --log-warnings option is set to 2, Test the unsafe +# warning will be displayed by 'SHOW WARNINGS', and it also will +# be written to error log. +SHOW WARNINGS; +Level Code Message +Note 1592 Statement may not be safe to log in statement format. +set @@global.log_warnings= 3; +select @@global.log_warnings; +@@global.log_warnings +3 +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 4; +# When the --log-warnings option is set to 3, Test the unsafe +# warning will be displayed by 'SHOW WARNINGS', and it also will +# be written to error log. +SHOW WARNINGS; +Level Code Message +Note 1592 Statement may not be safe to log in statement format. +set @@global.log_warnings= 500; +select @@global.log_warnings; +@@global.log_warnings +500 +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 5; +# When the --log-warnings option is set to 500, Test the unsafe +# warning will be displayed by 'SHOW WARNINGS', and it also will +# be written to error log. +SHOW WARNINGS; +Level Code Message +Note 1592 Statement may not be safe to log in statement format. +set @@global.log_warnings= @save_log_warnings; +drop table t1; +# Count the number of times the "Unsafe" message was printed to +# the error log, when --log-warnings >= 2 and --log-warnings < 2. +When --log-warnings >= 2, Occurrences: 3 +When --log-warnings < 2, Occurrences: 0 === modified file 'mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test' --- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test 2009-10-08 12:00:43 +0000 +++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test 2009-12-03 08:35:00 +0000 @@ -90,6 +90,8 @@ SET GLOBAL LOG_WARNINGS = 0; INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); SET GLOBAL LOG_WARNINGS = 1; INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); +SET GLOBAL LOG_WARNINGS = 2; +INSERT INTO t1 VALUES(UUID(), 'Bug#46265'); DROP TABLE t1; SET GLOBAL log_warnings = @old_log_warnings; @@ -115,3 +117,89 @@ perl; print "Occurrences: $count\n"; close(FILE); EOF + +# +# BUG#42851 +# This test verifies if the unsafe warnings will be written to error log +# when the level of --log-warnings option >= 2, and the 'SHOW WARNINGS' +# is not controlled by --log-warnings option. +# + +set @save_log_warnings= @@global.log_warnings; +# create table +CREATE TABLE `t1` ( + `recNo` int(10) unsigned NOT NULL AUTO_INCREMENT, + `string` varchar(64) NOT NULL, + `inUseBy` varchar(38) NOT NULL DEFAULT '', + `tsLastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE +CURRENT_TIMESTAMP, + PRIMARY KEY (`recNo`), + KEY `tsLastUpdated` (`tsLastUpdated`), + KEY `inUseBy` (`inUseBy`) +); +# insert test data +INSERT INTO t1 SET string='one'; +INSERT INTO t1 SET string='two'; +INSERT INTO t1 SET string='three'; + +--disable_warnings + +set @@global.log_warnings= 0; +select @@global.log_warnings; +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings < 2' limit 1; +--echo # When the --log-warnings option is set to 0, Test the unsafe +--echo # warning will be displayed by 'SHOW WARNINGS', but it will not +--echo # be written to error log. +SHOW WARNINGS; + +set @@global.log_warnings= 1; +select @@global.log_warnings; +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings < 2' limit 2; +--echo # When the --log-warnings option is set to 1, Test the unsafe +--echo # warning will be displayed by 'SHOW WARNINGS', but it will not +--echo # be written to error log. +SHOW WARNINGS; + +set @@global.log_warnings= 2; +select @@global.log_warnings; +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 3; +--echo # When the --log-warnings option is set to 2, Test the unsafe +--echo # warning will be displayed by 'SHOW WARNINGS', and it also will +--echo # be written to error log. +SHOW WARNINGS; + +set @@global.log_warnings= 3; +select @@global.log_warnings; +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 4; +--echo # When the --log-warnings option is set to 3, Test the unsafe +--echo # warning will be displayed by 'SHOW WARNINGS', and it also will +--echo # be written to error log. +SHOW WARNINGS; + +set @@global.log_warnings= 500; +select @@global.log_warnings; +UPDATE t1 SET inUseBy='me' WHERE inUseBy='--log-warnings >= 2' limit 5; +--echo # When the --log-warnings option is set to 500, Test the unsafe +--echo # warning will be displayed by 'SHOW WARNINGS', and it also will +--echo # be written to error log. +SHOW WARNINGS; + +--enable_warnings + +set @@global.log_warnings= @save_log_warnings; +drop table t1; + +--echo # Count the number of times the "Unsafe" message was printed to +--echo # the error log, when --log-warnings >= 2 and --log-warnings < 2. + +perl; + use strict; + my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set"; + open(FILE, "$log_error") or die("Unable to open $log_error: $!\n"); + my $count1 = () = grep(/--log-warnings >= 2/g,); + print "When --log-warnings >= 2, Occurrences: $count1\n"; + my $count2 = () = grep(/--log-warnings < 2/g,); + print "When --log-warnings < 2, Occurrences: $count2\n"; + close(FILE); +EOF + === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2009-11-01 23:13:11 +0000 +++ b/sql/sql_class.cc 2009-12-03 08:35:00 +0000 @@ -3859,7 +3859,11 @@ int THD::binlog_query(THD::enum_binlog_q push_warning(this, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_BINLOG_UNSAFE_STATEMENT, ER(ER_BINLOG_UNSAFE_STATEMENT)); - if (global_system_variables.log_warnings && + /* + The unsafe warning will be written to error log, when + the level of --log-warnings option >= 2. + */ + if (global_system_variables.log_warnings >= 2 && !(binlog_flags & BINLOG_FLAG_UNSAFE_STMT_PRINTED)) { sql_print_warning("%s Statement: %.*s", --===============0937803860== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dao-gang.qu@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dao-gang.qu@stripped # target_branch: file:///home/daogangqu/mysql/bzrwork/bugtest/mysql-\ # 5.1-bugteam/ # testament_sha1: 2c00d542e839cad21df020bee4a70263f1d0fc6b # timestamp: 2009-12-03 16:35:20 +0800 # base_revision_id: dao-gang.qu@stripped\ # 0mqlv9ffjfr72imi # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWatF5Z8ABtpfgFAwfff//3/v /6T////6YA07774dxynvd9Hu3fb06h3LvXQA23uziUgokKhC7apiIpo0/VGmgDQDQA0GRoyaGTQA aaAACUhpNVPbQU9pJ6niao8apoPSGgANNAAAAaBKJT09CmiDJp6mmmmQBoAAAAGgAACU9SQmhpTT 1P0oafqj0QNABo00ZDQaHqeoAABFIjQENATCmyinsaqfpkU9QZNBk9R6mg009QGnqBUoQTSYBARg RpD01TYyphqDT1G1NpqNAyZNHlZU6BL6CoOxRL1q6lypL1svtyzUsTOl5QMl1yrCeALRbr6+n8Os 6qMhjw5W1Ujjijq2csXdbMwi0X3xdsTY3BATixJdkXbba/Msl++FyITwok+OGoTE5W8hscGpJst5 aWkF6RYcnIvWwrumpiuqqyIJyKqoterHEfY6MUZEbqoMrBiCCmfmpYbMAY9Ha/crpnPJxGUyFZB7 S0TjE158ZrEKBaQWncDNrjTDCqLnLcx8QvszWDO031mIxMeEGIRtFHKqqt24xSCqxGQsxMSeZDyl cgMSgxUheV5TtQpr975F0jcpo4IpSCij7Hd/zqVWKo6XmWdLgf7UYD3vMY8GwO9cjR9q58Uj1IuR rZTMqrknEzjuRZKGKLGwmHFhfTe9COgiuRIgSqR3xOfyhfA4HjyzRJdQm8TP4lu2d+gS8ZBmzhQT JmsK41jcaWDSRrDKYwfzwLjJdnzVD9ee/PL17Nad9FU0XIpsktvThR94JvQpKy0YNhcJtIaH4VSE 47c7vLyhQS0XBD8IV1mq0Kbc4lL4HMzqHeJkaJWhYL9fX1JJYVExA00+CGCB5FMt8hVzDNK823YR eAQCge/GHcEMZQASEDxhKKqqkICgwiOqXAkjbTzN6W+nmRT2Iucb2LkaEYa1Jg/WhLg8KrqPvdgu LwffEgB1TxcAwzLcR4PJLyGPbYvL5+M8nJ4JlLrrruvLB0x3bHppa1bYpcodmaNUhY4jXRVKK0DG e2d7n9KZwujz4fT6JPfIc98TLd13kPqfl+xXO7dGlyI4yGfRyO25FbJtwX5r8PX9BfAvSUANSsoP h6u44hnNRllh9FbnPgmMW2E9N/SUgDldCkjRUgU0dhCMKXbxTFTfJkiCwsKFIoyqwUdaiLl8owVa kwSlbpZF9kirqFoVjHm70ZGxJZNp0BbQ6xippRAqg4pTvrNryLSQ9XFAqXZWIWHEb7nRmQSLNEne uM61LHEnypJAF+TtE3zNw8NmS7s5G62rMov2HIyNRWWbXGo8JsUmg43cZkt5onhUI6LIFIajQSE1 yNrbvMeyyQlTPeusxolpku1qvJObNew0VSyaszerqmzerXsu3b8szXdZrtozqJoJTTZo1N7Z1b0Z XkvUac/A51QOR1bgtG9IxLycYWFghCjmFmnjngWVoeVGwgaVVy5xdNXCx3rnEWbm9+jy1R7kae3z pm6FMt9y2cLHvI7wmGucUQYZlVDDXPUFRQTDua4USc16jIyHajnjxKYFhhrkUMoGWDODSOKRNhtp FFEhgVRHyLjnaUIWGg0tHC5WG8JlGNxQgcKdaclxPJrcEkWo+gXQSa1Ck0C/6A03Yme/ekZiq4ko 4alCjhB5QVCtGg6no9p2SBIZ5iFeaCKYAlerbJp0jSQsIa1rhW+oceUXFRiSl2+m6lxIr1l+RqKz echOr39B6Cd9cBmLY3a5bip2GVc6VEwqiSVlIDDEm2I8G0oUOWyrjPmI1adGDBmZmYRicGJ0HoPV pyvysta1sdByw7vmcfhX7VJPbScmLUXE/pSlLLECVbGPdZbhk6hMO9CSwPgCDSRhvhS6lc0cfqV0 pL8GyLCfhElDx1ac1ontdz81T4ifkfuNBAn9gxPdpcmGD+h8ist5eksEbD7yhI+xJPxe8lTs3Hvf ciqKlWbcVb1D8LvxR/56h+jEqh+acnGfc+LFtcQmB9/QjOkYOg2uE1shNo4JFKU4U51HU5jgH+ny dOS4zPOmeMzjULjpjiWRNomQqMFBQqKe4zJuGxdQvYto8qRguPKayVy8NLgR+ZwHY2iVZF6LMuJJ ZRGocC4OElUXm/iom4s4BqFQyZOY0JOUuGpQ7cEf91VpoeRixGRgwRvLOptncneiYs21I1rAz40p SqNrcofn9fhys7nKu5/CeMqbJtlSWt5/de8aF6KMa/XIzEqg0MHi+QklQk0W+NGyTMkywMmXV7iO MnYA4jmGojI+0+7UYO7dH3c+v2X/aWCBIhT4Dycwrd2DTBMZjcjo54nD+/nifGx8Tajp67ePSB7a JJdPUm+IRtYJIRIk1twPgIohx7TaYnsPeZB0fkfAzLyIi8+Bzy0k2yIhsM57zddzcmZZWm1kIucL MnpuG1Qnej7JrdG53sV7oeRxMDbOZ6zW+G9pjv3d5ngvu5Jw10QVcPvK4ov3HR16NLCSUFJyN+43 XFRaXlqcdx+LDBK6CX1Dx7TtJJDp7RqyPDh9Mskw4bRleXhOQmRTwGidgmvzTX5C7hP9yK2FmPjM uqtWZJWSWlBZHeJv4EYX3W3kpdbc8jtWoi7Gl2jL18FGiMmnThWZ0SzSl6fOXos7WzlY9vc1qnNB mRseKKvPy5QxU+atzhVkhqEtmro7YUJCyzXBtFhtYQ0GbdbhRl5XQh8svuTy6Gu20uaubu90Zk+j WoiiVlPrxaET2RpLIvkw4Nh1Vq9M+Vc7FVfNko6e0TAIBuDud0vi8PLq+wmlzlq8OngUU+bbj3XH lCFikiQeZAqTEIPH32esSsa0BgtR2rvgSfET+qL9VxsppbBUP+OV3sH2GXjuN5p6DV3gQlQgdSHh 7vpIIBhqrQPfQ0dEOs9Ed2nk7eEvaDUW6Y43Hy40C03WnZ4Fwu0BvH3PkJgi/6J4pxaN8k1CiSHg kxsoo83fjdoNtCUhhZH83HZxEgTsErEqCgs9lrqk+2Kd/MizkPoPd2EJeAwHMf8Bai4/UOdNFbXl nd/MT75aGGFIMwmckWC21gPka9fmK0V5ghAnM/S0J7UOVRFFG3Qmd89N7Z6azDw9HB0buKgaOxFs /NuoiianwRjGCTkXsXj6LoObT2avZwTI16/F9pz87hyfTcdDWVkDpkxAkLJR+bEzcgQlQjAfxquT P9Ew0qWDY+skjMWAYgVDCxFSQtdou2yvBXAgSyCC8WRIgJMiGLSS9Li2t2F98jyQIAfy3yQ6jXT3 mQvL4jDUkmPk/rFedtT7cAkuehDnTeieZXG5t4cWogYANUEkSoZieUnZh3C6hnWZGAZ7+vrcTM8j jTh1ov2no9Hmk3dSYHA8KJ9Wv5mpHFuebJrjXrlHT26Q+G32aNMOCd9vnidmURzO2tD1a7nmngjB aifPidn0nyM/MXo71NTl6fJNlJbsYNee1VBvOf0pLvXvR5ckkjqvZspn/asm6I3epqLUSuHGApJo BXCJVH42EDEkpMmVc2dUUrkEAsYkELVnE1LCBYp+oLrufgI9gnW2QtymNQawlWYiTcyeY8f1hyig m7uZyJG1wBYGEzLE8BMw1LzLyoo7ECk9aZF+ZHUjMk47JhJVtxHGUS5y1XoxRTExq5Rk86JS8fWC DKxhsyw09MQlqSuktpEinEI0WpJCdV2jVsKQX2l9Y25okLyVweVZQlMZc6E5D4Ug9b9mvyrkxDQj tvXELJqnEjhWgTTgN0k1q4kwp8/aj36fi9t+zMGfyk1EIGTJ1oo3Jbh6C2F+c0UeX93qapgaMTzw yly/033ievHoJft9A4jgpARC1oRCkiACCQLBgYknxIQfUQLdkPb6Jjsz5LKbsBruNQ9lS34AhMOa JMIZZxroQ1N68LA3SH41iWy++3/i7kinChIVaLyz4A== --===============0937803860==--