List:Commits« Previous MessageNext Message »
From:Luis Soares Date:June 8 2009 10:36am
Subject:bzr commit into mysql-5.1-bugteam branch (luis.soares:2918) Bug#42851
View as plain text  
#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
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