Hi Luis,
New patch approved!
Luis Soares wrote:
> #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
>
