List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:June 11 2009 3:25am
Subject:Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2918)
Bug#42851
View as plain text  
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 
> 

Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:2918) Bug#42851Luis Soares8 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2918)Bug#42851He Zhenxing11 Jun