#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,<FILE>);
+ print "When --log-warnings >= 2, Occurrences: $count1\n";
+ my $count2 = () = grep(/--log-warnings < 2/g,<FILE>);
+ 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",
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20091203083500-w4tp23gg9r9e34t9.bundle
