From: Dao-Gang.Qu Date: December 3 2009 9:31am Subject: bzr commit into mysql-5.1-bugteam branch (Dao-Gang.Qu:3233) Bug#42851 List-Archive: http://lists.mysql.com/commits/92615 X-Bug: 42851 Message-Id: <200912030932.nB39WWDw020132@daogangqu-desktop> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1954365856==" --===============1954365856== 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 will only be written to error log, when --log-warnings is set to 2 or higher. @ 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 --log-warnings is set to 2 or higher, 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 09:31:11 +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 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 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 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 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 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 09:31:11 +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 --log-warnings is set to 2 or higher, 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 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 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 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 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 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 09:31:11 +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 --log-warnings is set to 2 or higher. + */ + if (global_system_variables.log_warnings >= 2 && !(binlog_flags & BINLOG_FLAG_UNSAFE_STMT_PRINTED)) { sql_print_warning("%s Statement: %.*s", --===============1954365856== 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: 9d711170efa5f460eb0c74b96a915309145b6bbd # timestamp: 2009-12-03 17:31:32 +0800 # base_revision_id: dao-gang.qu@stripped\ # 0mqlv9ffjfr72imi # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbfKOlsABq3fgFAwfff//3/v /6T////6YA0/Hr7bQHdjooAAQlEUilJIqiSlSCqF4amojyjTQAAAAAaDIAAAAABxkyaNAaNMRkaG IYE0aYgxGgwgAMOMmTRoDRpiMjQxDAmjTEGI0GEABg09UqeaSYRk9RkyYhppiaYg0GjRoBoA0aGg RSIE0yGiaNDCqeap+J6qf6mhkjaU03qaj2lNP1J6QeU9RoJJAjTSZGmTQTTImJk0ammk0000HqYw pkbUDNSBgy9ovghkO2C8OHNcbZ/puhz6exbIun+a5Nz0SpIG2v6urljzDkxMCrGb7mRUhjlftRDm VkkPRGKIXowUwYHtUh2LTyrj4lTvayWkZHbJD+imAi04V3DyiQCc+RqCkJlGJnqbJDbowy3QDQwQ PZk6SADMM0fdNKz5HNQZwwz4IoyKgQ/VmxI2oCePh9pRH+JYeBjLSgT5FhB8SDywtPIgLAgIGw7A meTVGGAEE5RTF6JRvlvQ6qRwqJRDRJcKTmYTOHJ3PlIeGiRXMaJEnA6ID+VRInVItq8GgWcEEZfE 6igHHakQgQQeBv/3uJEwkJtPOTNhafIYkB+B1hVS8A8SAi37yB8RI8hEBF5NVjh0xYlgHgIeJl2V iIBqBVddUWxO0R0jRcOGEQcTZ7/0ZLuNx4TDhoZ6WAjaIu8CeT6ViUxODRERROSZJUgKs1JjSNrC x4mPhgTI2VyPn0ZhVRSJE3rxYcLRASbNJ+IshHwAeuRJ1VYmMiYjYSP1g4Rlo4MbiwkSEpiVEH6s ksTCsJbLhErGFaPgLcI0jVA6Ql/Tn3t5/Z5eqojJsoLJ/KmFM5ljw01V4FGZFSMAQcIWKmgMEAgA wYASIEAACYSQJDCEY3FJOTVBYSfnX6SZFtMomZ5EBFoiV4ykfNgUAD6HEGPgeJkZhHOhUB3HHMY4 GveRCJhD7jf5ND+f9fE+z+H1SgQhCHMnI6Ud+k8Ge9z6hbRg6phekDw2BpYcJhzAFS+t9Br8BtAK RKU6Jb+lfFQ2Uo4s/mpBD2n18CVhlboMgQTpnLsiIfAWnbGZVV9HsCKAohzAKCScx7ersN4Xmwpf T7IrUe1FrV1D9VnJzgBsBnMw2vvYdZ9RwqezCREiPHrMdAeIcQXSbjeKsapSpqdxIUmDI2lMFN9U RjLy40Fxr+pG50bJV0DG5/taC2JA7VVAxWAj62lnxKzIiyMHCM4MI8Wf8XbDOSFWqpUpLdunSIqP OuaecAd7hbMVPK0xgQ6teh9RDUGZUGgHLUGZWetUNRsemeKe9w6o1PsgIV1jCQyFryVpsN5Eu3O0 FjtA1eLxTWwsW6vRMThX3F4h2gem1jndUI66thVB5bYlTIE7IRUWvNJgNK8pHgWhgUgroA4d2l5t LLP2NN1lLZ7C+5Fp9yf1lhYOLiJthiYHEyCBQgaChafqbn9fUKeA2ieUL+toHTcLdxsDImXUabe6 4IDft1GgqFwKHTYTOBEhxOIsqy81m4tN5uxvINm1ZULkVnO0jEVWddpI1Dy60wI5Iq1uxPbpF0Vz vnsHuogY7F59tXobS9TJjrze5yIDh/zif0EejoPNjFWTmZ6i6MRywLr+2fDh99SoVWVK0i4oPLap WhcKp7LjErShwZySsiRFTpaIiLnEjeYFXcXF5EqOREid6E7mLz8mC2tWsaR61GiL8IDwgzkRJMGo Ufpr+36mDe5u5nHfEr0WbL6pSlKUhSKBIoiCIMaJxm8e976rTNBx8DL2O+8ZL7WWdRcEAThwhh9X rYsGvDnJMe1glCR2gnpHl+8J3TplOK/cSvBpqMqTAfmiwHdkvsmj7zqPsSDyAfqfAykCfMLj14OZ hg8j/JSWc+4rEaz9Sokfkkj6HiDIP75w+R+YkhJBIszhI0EB86PoJ/49QH/SsJCH2HPYHvP7lRqM gFIPjxEWCRI4hqMQvJgLUBRIZmxF0DHI3BQD5Hd0zIBWeYViKzYMEA2ppJiOADiAkBUQBASAjwCw c4GYgwRKjUBwEiRAOAXgnT+nQUEf+CgdRqATiYREPJ5CTxhFwFCABiCcIiGvJhYA8oBcA4AmTNwW iW0IAXDB2SEf9uc1xzFZWBiCoqE0BM3GDvHgI1lmAplJgBbXERITAzkB9u/t1FprCRvP5PcyDM4M gZz6PhSd0BSJBXLvUsBkIFxUdx8yCRQQSnEaFjtHOcOcyGcU/AXeXAfifcH+C494mPz491Fnm35f E/LV4lQgSIU/mPO1CtfINWJLWugTZrR0/hrR0zDyDATZun3bAAO+BWh9Q6RMeiBzi4yWvYEahQEe X4Ezhw+B0EfebCBkJKJ4HASUNB+J5Nba5zmczP20FRsZT/lQLxgXgI78mO0HYmJ+Ru+JaUMyoNJg pnkGg91A7xu/X3hRAUhtWTtCAkafiEqxKbw4v0u2iZCtWAIdT2Fhdy4ytNfmnJEiNZiViISF0cjk OQyw5ChE7ek4fvfqRThidRmKGg0A9wjEr7RSRvEZ+KMvQJZiPzcRTJ4+iZiosWZJWSWFQsjrE5NZ GO3FZaSdBrKuoqKHOcT1nnL9H0XsaUUKaZOVBCeaRRF6giIeYdBLvuLxwb0BUIvPYIcdu6aDIrHZ u3P0owEYpq7MASQsslgaRYbGF4CQaiwMmq26wx6zaIfwaaB15DNPiCgy8/o+CWD92UgSBkx31lwp etDhEEsBEqdTYHmDI7h3MPMFXSYHK4kc5gKwmDgWw0BJZu/BcPV516R6LFr2d2fcSSPmq7e6Z6YQ rUkSD1ECpMQg5eFdA0IDBWjpXVAk6Q6xP0RffM0VZmsVD/mXt1jwM2zjNRn3DR0gQlAr1kPL7j2E EBAnAOgTQFdiB68Rh1IPB+DT18Vnvd5AoFZr4TP01SCsz5BWbvOWCW0BxjvfWJci/Qe4eO/iVygQ Kh3C1zIIOz01Q1aBkEnoDDfjvEMIzEREQCQk/GtYOezcJKvq3Fgko1jsPVqITGA7h/0FiLf9w5Uz UtOGR5dwn6yysMKQZBMhIrEspAfSaNGkVqV2ghAm072AfeIaiBIIMLhtOyNBm9DlL6e2nHDJgC3q EPs3YMIYVx7xFSJCWZEqPZ2wQG64Rd1X+u1WBp0+xeo6fTMWpc5nIxSMJMA4B/ZiZyIEJQIwHyox Jk++YYKVjW9BJGYsAxAqGOtFSQtOsTELx2VXK3ECVsMUEnDhgcnDJpjkuUyuKyLJFjzxQDAvbbJD rGmpK/YYCzdniFeUVs5/kkthnPjUALQdIhsHiB/eSroM+mtoIGDRAyRKBmJwcsqeYSwE+JpKBdZ5 +wyKzYbBYw1B3d3mS1chSC07YH3ZN5eJxYHnxGVMuVg29N5+eHsuvQxvVPsR82JAtp2OYHHkxex1 qI5hemQcvrDtJ7QiI9A2gz47leyf3Ei+x7hgMA4eAlDy1iN8xJI6Ylc1Z7SY50TP2GQLESmG6Aqk 1AUwiUQr1sFxJWZNo2M6IqpkEAsXEELRkEzrCBWp/EMWLZ0iPIJ1WSFxK3UBoCVJcJNvTgPN+Ac8 VCcfQzkSNLjBYGEvWJ4xLxoXaWlBU8SBVPQmYtvE3CWC8cxqWRhWBxhA0GpxERUJNUFTnwGssS0R I3ghrxjn0bsdW+60wtG13jIovZCdK8Q0aCqC2wtpGy6JC8yuN5qSQ54ncZD0Aeo9F24gKRQR1xIA IeLQsByCScg2STBXGTCr9uCPRl+Lz257w4b5NBCBmV3CQZxnxawnVTaF0HN7/SZHpErC+wOhLWgq 300idF9unWOMbVICIXchEKSgAhkCwYy4k+j+0SAfOQLbnHdtmPHlwWU3jBvqtNA8s9/AERHuFER0 +al9qONwh/pr1BUI6MCXDSKer5dcou5IpwoSFvlHS2A= --===============1954365856==--