List:Commits« Previous MessageNext Message »
From:Guangbao Ni Date:February 26 2009 2:38pm
Subject:bzr commit into mysql-5.1-bugteam branch (gni:2805) Bug#41980
View as plain text  
#At file:///home/ngb/mysql/bzr/bugteam-5.1-bug41980/

 2805 Guangbao Ni	2009-02-26
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
                  
      When binlog_format is STATEMENT and the statement is unsafe before,      
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
                  
      Fixed with adding two conditions:
      1) to check if SQL_LOG_BIN is turned on, OR
      2) in sub statement and the top level SQL_LOG_BIN is on(for the sp disabled the binary logging automatically)
      So add a enabled_bin_log flag in THD to indicate whether the top level SQL_LOG_BIN is on or not when in sp.
modified:
  mysql-test/suite/binlog/r/binlog_unsafe.result
  mysql-test/suite/binlog/t/binlog_unsafe.test
  sql/sql_class.cc
  sql/sql_class.h

per-file messages:
  mysql-test/suite/binlog/r/binlog_unsafe.result
    Test case result for unsafe warning/error message
  mysql-test/suite/binlog/t/binlog_unsafe.test
    Test case for unsafe message warning/error
  sql/sql_class.cc
    Fixed with adding two conditions:
    1) to check if SQL_LOG_BIN is turned on, OR
    2) in sub statement and the toplevel SQL_LOG_BIN is on
  sql/sql_class.h
    Add a enabled_bin_log flag in THD to indicate whether the toplevel SQL_LOG_BIN is on or not when in sp.
=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2009-02-09 18:17:58 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2009-02-26 14:37:20 +0000
@@ -227,3 +227,52 @@ UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%
 Warnings:
 Warning	1592	Statement is not safe to log in statement format.
 DROP TABLE t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1(i INT PRIMARY KEY);
+CREATE TABLE t2(i INT PRIMARY KEY);
+CREATE TABLE t3(i INT, ch CHAR(50));
+"Should issue message Statement is not safe to log in statement format."
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+CREATE FUNCTION func6()
+RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (11);
+INSERT INTO t1 VALUES (12);
+RETURN 0;
+END|
+"Should issue message Statement is not safe to log in statement format only once"
+INSERT INTO t3 VALUES(func6(), UUID());
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+set @save_log_bin = @@SESSION.SQL_LOG_BIN;
+set @@SESSION.SQL_LOG_BIN = 0;
+"Should NOT have any warning message issued in the following statements"
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+DROP TABLE t1,t2;
+"Should NOT have any warning message issued in the following func7()"
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a CHAR(40));
+CREATE FUNCTION func7()
+RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (@@global.sync_binlog);
+INSERT INTO t1 VALUES (@@session.insert_id);
+INSERT INTO t2 SELECT UUID();
+INSERT INTO t2 VALUES (@@session.sql_mode);
+INSERT INTO t2 VALUES (@@global.init_slave);
+RETURN 0;
+END|
+SHOW VARIABLES LIKE "SQL_LOG_BIN";
+Variable_name	Value
+sql_log_bin	OFF
+SELECT func7();
+func7()
+0
+DROP FUNCTION func6;
+DROP FUNCTION func7;
+DROP TABLE t1, t2, t3;
+set @@SESSION.SQL_LOG_BIN = @save_log_bin;
+"End of tests"

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2009-02-09 18:17:58 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2009-02-26 14:37:20 +0000
@@ -8,6 +8,7 @@
 # executed cannot be determined (e.g., INSERT DELAYED).  Such
 # statements should be marked unsafe.  All unsafe statements should
 # give a warning.
+# Yet the warning/error message isn't issued when SQL_LOG_BIN is turned off.
 #
 # This test verifies that a warning is generated for statements that
 # should be unsafe, when they are executed under statement mode
@@ -32,14 +33,18 @@
 # We try to insert the variables that should not be unsafe into a
 # table, and verify that *no* warning is issued.
 #
-#
+# Execute a unsafe statement when SQL_LOG_BIN is turned ON,
+# a warning/error should be issued
+# Execute a unsafe statement when @@SQL_LOG_BIN is turned OFF,
+# NO warning/error is issued
+
 # ==== Related bugs and worklogs ====
 #
 # WL#3339: Issue warnings when statement-based replication may fail
 # BUG#31168: @@hostname does not replicate
 # BUG#34732: mysqlbinlog does not print default values for auto_increment variables
 # BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
-#
+# BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
 #
 # ==== Related test cases ====
 #
@@ -271,3 +276,60 @@ INSERT INTO t1 VALUES ('a','b');
 UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
 DROP TABLE t1;
 
+#
+#For bug#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+CREATE TABLE t1(i INT PRIMARY KEY);
+CREATE TABLE t2(i INT PRIMARY KEY);
+CREATE TABLE t3(i INT, ch CHAR(50));
+
+--echo "Should issue message Statement is not safe to log in statement format."
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+
+DELIMITER |;
+CREATE FUNCTION func6()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (10);
+  INSERT INTO t1 VALUES (11);
+  INSERT INTO t1 VALUES (12);
+  RETURN 0;
+END|
+DELIMITER ;|
+--echo "Should issue message Statement is not safe to log in statement format only once"
+INSERT INTO t3 VALUES(func6(), UUID());
+
+set @save_log_bin = @@SESSION.SQL_LOG_BIN;
+set @@SESSION.SQL_LOG_BIN = 0;
+--echo "Should NOT have any warning message issued in the following statements"
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+DROP TABLE t1,t2;
+
+--echo "Should NOT have any warning message issued in the following func7()"
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a CHAR(40));
+DELIMITER |;
+CREATE FUNCTION func7()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (@@global.sync_binlog);
+  INSERT INTO t1 VALUES (@@session.insert_id);
+  INSERT INTO t2 SELECT UUID();
+  INSERT INTO t2 VALUES (@@session.sql_mode);
+  INSERT INTO t2 VALUES (@@global.init_slave);
+  RETURN 0;
+END|
+DELIMITER ;|
+SHOW VARIABLES LIKE "SQL_LOG_BIN";
+SELECT func7();
+
+#clean up
+DROP FUNCTION func6;
+DROP FUNCTION func7;
+DROP TABLE t1, t2, t3;
+set @@SESSION.SQL_LOG_BIN = @save_log_bin;
+--echo "End of tests"

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2009-02-13 16:20:56 +0000
+++ b/sql/sql_class.cc	2009-02-26 14:37:20 +0000
@@ -538,6 +538,7 @@ THD::THD()
    Open_tables_state(refresh_version), rli_fake(0),
    lock_id(&main_lock_id),
    user_time(0), in_sub_stmt(0),
+   enabled_bin_log(false),
    binlog_table_maps(0), binlog_flags(0UL),
    table_map_for_update(0),
    arg_of_last_insert_id_function(FALSE),
@@ -2937,7 +2938,9 @@ void THD::reset_sub_statement_state(Sub_
     auto_inc_intervals_forced.swap(&backup->auto_inc_intervals_forced);
   }
 #endif
-  
+ 
+  if (!in_sub_stmt)
+    enabled_bin_log= options & OPTION_BIN_LOG;
   backup->options=         options;
   backup->in_sub_stmt=     in_sub_stmt;
   backup->enable_slow_log= enable_slow_log;
@@ -3018,7 +3021,10 @@ void THD::restore_sub_statement_state(Su
     stack.
   */
   if (!in_sub_stmt)
+  {
     is_fatal_sub_stmt_error= FALSE;
+    enabled_bin_log= FALSE;
+  }
 
   if ((options & OPTION_BIN_LOG) && is_update_query(lex->sql_command) &&
     !current_stmt_binlog_row_based)
@@ -3662,7 +3668,8 @@ int THD::binlog_query(THD::enum_binlog_q
     If we are in statement mode and trying to log an unsafe statement,
     we should print a warning.
   */
-  if (lex->is_stmt_unsafe() &&
+  if (((options & OPTION_BIN_LOG) || (in_sub_stmt && enabled_bin_log)) &&
+      lex->is_stmt_unsafe() &&
       variables.binlog_format == BINLOG_FORMAT_STMT)
   {
     push_warning(this, MYSQL_ERROR::WARN_LEVEL_WARN,

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2009-01-23 12:22:05 +0000
+++ b/sql/sql_class.h	2009-02-26 14:37:20 +0000
@@ -1350,6 +1350,10 @@ public:
 
   /* <> 0 if we are inside of trigger or stored function. */
   uint in_sub_stmt;
+  /* TRUE in sub statment when the current top level satement write binary log.
+     The flag is used in function()/trigger() 
+  */
+  bool enabled_bin_log;
 
   /* container for handler's private per-connection data */
   Ha_data ha_data[MAX_HA];

Thread
bzr commit into mysql-5.1-bugteam branch (gni:2805) Bug#41980Guangbao Ni26 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (gni:2805) Bug#41980Andrei Elkin27 Feb
    • Re: bzr commit into mysql-5.1-bugteam branch (gni:2805) Bug#41980Guangbao Ni28 Feb