#At file:///home/lsoares/Workspace/mysql-server/bugfix/b42851/5.1-bt/ based on revid:luis.soares@stripped
2918 Luis Soares 2009-06-08
BUG#42851: Spurious "Statement is not safe to log in statement
format." warnings
Several warnings are printed when using statement based logging and
unsafe operations are logged to the binlog. For example, this is the
case for statements using LIMIT + ORDER BY PK. As a consequence, this
would rapidly increase mysqld error log size, in some cases to
several gigabytes, causing a maintenance nightmare.
This patch addresses this issue by adding a variable that enables the
user to choose whether the warnings should be issued or not. The
variable has SESSION and GLOBAL scope and is dynamically changeable.
NOTE: this is an incremental patch, that builds on top of previous fix
for this bug, the one that handles spurious warnings for filtered
out statements.
@ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
Renamed test file and added test results for new variable.
@ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
Added CLI variable setting for suppressing statmenent unsafe warnings.
@ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
Added testing to assess that new variable is indeed working as
expected.
@ sql/mysqld.cc
Added new option: binlog-stmt-suppress-unsafe-warnings.
@ sql/set_var.cc
Added binlog_stmt_suppress_unsafe_warnings variable.
@ sql/sql_class.cc
Added check to thd->variables.binlog_stmt_suppress_unsafe_warnings
before printing out the warning.
@ sql/sql_class.h
Added binlog_stmt_suppress_unsafe_warnings to the system_variables
structure.
renamed:
mysql-test/suite/binlog/r/limit_warning.result => mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
mysql-test/suite/binlog/t/limit_warning-master.opt => mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
mysql-test/suite/binlog/t/limit_warning.test => mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
modified:
sql/mysqld.cc
sql/set_var.cc
sql/sql_class.cc
sql/sql_class.h
mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
=== renamed file 'mysql-test/suite/binlog/r/limit_warning.result' => 'mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result'
--- a/mysql-test/suite/binlog/r/limit_warning.result 2009-06-05 09:09:59 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result 2009-06-08 10:36:13 +0000
@@ -1,3 +1,50 @@
+SET @old_binlog_stmt_suppress_unsafe_warnings= @@global.binlog_stmt_suppress_unsafe_warnings;
+###### PART I: basic variable testing
+### assertion: show that the variable exists and is turned on
+SHOW VARIABLES LIKE 'binlog_stmt%';
+Variable_name Value
+binlog_stmt_suppress_unsafe_warnings ON
+### assertion: show that setting global variable will affect new connections but not current one
+SET GLOBAL binlog_stmt_suppress_unsafe_warnings= 0;
+SHOW VARIABLES LIKE 'binlog_stmt%';
+Variable_name Value
+binlog_stmt_suppress_unsafe_warnings OFF
+SHOW VARIABLES LIKE 'binlog_stmt%';
+Variable_name Value
+binlog_stmt_suppress_unsafe_warnings ON
+###### PART II: check that warnings are/aren't printed when option to suppress is used
+### assertion: no warnings because CLI option to suppress warnings was set
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+DROP TABLE t1;
+### assertion: we get warnings again by reverting CLI option dynamically
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 0;
+SHOW VARIABLES LIKE 'binlog_stmt%';
+Variable_name Value
+binlog_stmt_suppress_unsafe_warnings OFF
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+DROP TABLE t1;
+### assertion: warnings are turned off again by setting option dynamically
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 1;
+SHOW VARIABLES LIKE 'binlog_stmt%';
+Variable_name Value
+binlog_stmt_suppress_unsafe_warnings ON
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+DROP TABLE t1;
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 0;
+###### PART III: check that warnings are not printed when statement is filtered out from binlog
### NOT filtered database => assertion: warnings ARE shown
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int, b int, primary key (a));
@@ -28,3 +75,4 @@ UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
DROP TABLE t1;
DROP DATABASE b42851;
+SET @@global.binlog_stmt_suppress_unsafe_warnings= @old_binlog_stmt_suppress_unsafe_warnings;
=== renamed file 'mysql-test/suite/binlog/t/limit_warning-master.opt' => 'mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt'
--- a/mysql-test/suite/binlog/t/limit_warning-master.opt 2009-06-05 09:09:59 +0000
+++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt 2009-06-08 10:36:13 +0000
@@ -1 +1 @@
---binlog-ignore-db=b42851
+--binlog-ignore-db=b42851 --binlog-stmt-suppress-unsafe-warnings=1
=== renamed file 'mysql-test/suite/binlog/t/limit_warning.test' => 'mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test'
--- a/mysql-test/suite/binlog/t/limit_warning.test 2009-06-05 09:09:59 +0000
+++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test 2009-06-08 10:36:13 +0000
@@ -5,14 +5,29 @@
# ===
#
# This test aims at checking that the fix that removes spurious
-# entries in the error log when the statement is filtered out from
-# binlog, is working.
+# entries in the error log when: (i) option to suppresss warnings
+# is used; or (ii) the statement is filtered out from binlog; is
+# indeed working.
#
# HOW
# ===
#
-# The test case is split into three assertions when issuing statements
-# containing LIMIT and ORDER BY:
+# - PART I: basic variable testing
+#
+# i) Check that global and session variables are working properly
+#
+# - PART II: is split into three assertions:
+#
+# i) assert that warnings are suppressed when CLI option was used
+#
+# ii) assert that warnings are thrown when global variable is changed
+# dynamically to not suppress warnings.
+#
+# iii) assert that warnings are suppressed when global variable is
+# changed dynamically to suppress warnings.
+#
+# - Part III: of the test is split into three assertions when issuing
+# statements containing LIMIT and ORDER BY:
#
# i) issue statements in database that is not filtered => check
# that warnings ARE shown;
@@ -26,6 +41,57 @@
-- source include/have_log_bin.inc
-- source include/have_binlog_format_statement.inc
+SET @old_binlog_stmt_suppress_unsafe_warnings= @@global.binlog_stmt_suppress_unsafe_warnings;
+
+-- echo ###### PART I: basic variable testing
+
+-- echo ### assertion: show that the variable exists and is turned on
+SHOW VARIABLES LIKE 'binlog_stmt%';
+
+-- echo ### assertion: show that setting global variable will affect new connections but not current one
+SET GLOBAL binlog_stmt_suppress_unsafe_warnings= 0;
+connect (conn2,localhost,root,,);
+-- connection conn2
+SHOW VARIABLES LIKE 'binlog_stmt%';
+-- disconnect conn2
+-- connection default
+SHOW VARIABLES LIKE 'binlog_stmt%';
+
+-- echo ###### PART II: check that warnings are/aren't printed when option to suppress is used
+
+-- echo ### assertion: no warnings because CLI option to suppress warnings was set
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+DROP TABLE t1;
+
+-- echo ### assertion: we get warnings again by reverting CLI option dynamically
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 0;
+SHOW VARIABLES LIKE 'binlog_stmt%';
+
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+DROP TABLE t1;
+
+-- echo ### assertion: warnings are turned off again by setting option dynamically
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 1;
+SHOW VARIABLES LIKE 'binlog_stmt%';
+
+CREATE TABLE t1 (a int, b int, primary key (a));
+INSERT INTO t1 VALUES (1,2), (2,3);
+UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
+UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
+DROP TABLE t1;
+
+# set to mysqld 5.1 default before moving to part II of the test
+# (suppress OFF).
+SET SESSION binlog_stmt_suppress_unsafe_warnings= 0;
+
+-- echo ###### PART III: check that warnings are not printed when statement is filtered out from binlog
+
-- echo ### NOT filtered database => assertion: warnings ARE shown
-- disable_warnings
@@ -71,3 +137,5 @@ DROP TABLE t1;
# clean up
DROP DATABASE b42851;
+SET @@global.binlog_stmt_suppress_unsafe_warnings= @old_binlog_stmt_suppress_unsafe_warnings;
+exit;
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2009-05-15 12:57:51 +0000
+++ b/sql/mysqld.cc 2009-06-08 10:36:13 +0000
@@ -5497,6 +5497,7 @@ enum options_mysqld
OPT_BINLOG_SHOW_XID,
#endif
OPT_BINLOG_ROWS_EVENT_MAX_SIZE,
+ OPT_BINLOG_STMT_SUPPRESS_UNSAFE_WARNINGS,
OPT_WANT_CORE, OPT_CONCURRENT_INSERT,
OPT_MEMLOCK, OPT_MYISAM_RECOVER,
OPT_REPLICATE_REWRITE_DB, OPT_SERVER_ID,
@@ -5708,6 +5709,13 @@ struct my_option my_long_options[] =
/* sub_size */ 0, /* block_size */ 256,
/* app_type */ 0
},
+ {"binlog-stmt-suppress-unsafe-warnings",
+ OPT_BINLOG_STMT_SUPPRESS_UNSAFE_WARNINGS,
+ "Suppress warnings when statement based logging is in use and unsafe "
+ "statements are logged to binary log.",
+ (uchar **) &global_system_variables.binlog_stmt_suppress_unsafe_warnings,
+ (uchar **) &max_system_variables.binlog_stmt_suppress_unsafe_warnings,
+ 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
#ifndef DISABLE_GRANT_OPTIONS
{"bootstrap", OPT_BOOTSTRAP, "Used by mysql installation scripts.", 0, 0, 0,
GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc 2009-05-15 12:57:51 +0000
+++ b/sql/set_var.cc 2009-06-08 10:36:13 +0000
@@ -336,6 +336,10 @@ static sys_var_const sys_log_error(&v
static sys_var_bool_ptr
sys_log_queries_not_using_indexes(&vars, "log_queries_not_using_indexes",
&opt_log_queries_not_using_indexes);
+static sys_var_thd_bool
+sys_binlog_stmt_suppress_unsafe_warnings(&vars,
+ "binlog_stmt_suppress_unsafe_warnings",
+ &SV::binlog_stmt_suppress_unsafe_warnings);
static sys_var_thd_ulong sys_log_warnings(&vars, "log_warnings", &SV::log_warnings);
static sys_var_microseconds sys_var_long_query_time(&vars, "long_query_time",
&SV::long_query_time);
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2009-06-05 09:09:59 +0000
+++ b/sql/sql_class.cc 2009-06-08 10:36:13 +0000
@@ -3685,7 +3685,8 @@ int THD::binlog_query(THD::enum_binlog_q
*/
if (sql_log_bin_toplevel && lex->is_stmt_unsafe() &&
variables.binlog_format == BINLOG_FORMAT_STMT &&
- binlog_filter->db_ok(this->db))
+ binlog_filter->db_ok(this->db) &&
+ !this->variables.binlog_stmt_suppress_unsafe_warnings)
{
/*
A warning can be elevated a error when STRICT sql mode.
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2009-05-30 13:32:28 +0000
+++ b/sql/sql_class.h 2009-06-08 10:36:13 +0000
@@ -359,6 +359,7 @@ struct system_variables
my_bool low_priority_updates;
my_bool new_mode;
+ my_bool binlog_stmt_suppress_unsafe_warnings;
/*
compatibility option:
- index usage hints (USE INDEX without a FOR clause) behave as in 5.0
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090608103613-s4feq7tft4l0g2ud.bundle
