List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:September 16 2009 2:20am
Subject:bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827
View as plain text  
#At file:///home/daogangq/mysql/bzrwork/bug45827/mysql-pe/ based on revid:luis.soares@stripped

 3605 Dao-Gang.Qu@stripped	2009-09-16
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement and row mode too.
     @ mysql-test/suite/rpl/r/rpl_unsafe_warning.result
        Test result for bug#45827
     @ mysql-test/suite/rpl/t/rpl_unsafe_warning.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ sql/sql_base.cc
        Get rid of the check of BINLOG_FORMAT_MIXED

    added:
      mysql-test/suite/rpl/r/rpl_unsafe_warning.result
      mysql-test/suite/rpl/t/rpl_unsafe_warning.test
    modified:
      sql/sql_base.cc
=== added file 'mysql-test/suite/rpl/r/rpl_unsafe_warning.result'
--- a/mysql-test/suite/rpl/r/rpl_unsafe_warning.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_unsafe_warning.result	2009-09-16 02:20:30 +0000
@@ -0,0 +1,59 @@
+drop function if exists func_modify_t1;
+CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE FUNCTION func_modify_t1 ()
+RETURNS INT
+BEGIN
+INSERT INTO t1 SET a = 1;
+RETURN 0;
+END|
+# The following statement causes auto-incrementation  
+# of both t1 and t2. It is logged in statement format, 
+# so it should produce unsafe warning.
+INSERT INTO t2 SET a = func_modify_t1();
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
+SET SESSION binlog_format = MIXED;
+# Check if the statement is logged in row format.
+INSERT INTO t2 SET a = func_modify_t1();
+SHOW BINLOG EVENTS FROM 764;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	764	Query	1	832	BEGIN
+master-bin.000001	832	Table_map	1	874	table_id: 25 (test.t2)
+master-bin.000001	874	Table_map	1	916	table_id: 26 (test.t1)
+master-bin.000001	916	Write_rows	1	954	table_id: 26
+master-bin.000001	954	Write_rows	1	992	table_id: 25 flags: STMT_END_F
+master-bin.000001	992	Query	1	1061	COMMIT
+DROP TABLE t1;
+DROP TABLE t2;
+DROP FUNCTION func_modify_t1;
+SET SESSION binlog_format = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+create trigger tri_modify_two_tables before insert on t1 for each row begin
+insert into t2(a) values(new.a);
+insert into t3(a) values(new.a);
+end |
+# The following statement causes auto-incrementation  
+# of both t2 and t3. It is logged in statement format, 
+# so it should produce unsafe warning
+INSERT INTO t1 SET a = 1;
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
+SET SESSION binlog_format = MIXED;
+# Check if the statement is logged in row format.
+INSERT INTO t1 SET a = 2;
+SHOW BINLOG EVENTS FROM 1980;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	1980	Query	1	2048	BEGIN
+master-bin.000001	2048	Table_map	1	2089	table_id: 28 (test.t1)
+master-bin.000001	2089	Table_map	1	2131	table_id: 29 (test.t3)
+master-bin.000001	2131	Table_map	1	2173	table_id: 30 (test.t2)
+master-bin.000001	2173	Write_rows	1	2211	table_id: 30
+master-bin.000001	2211	Write_rows	1	2249	table_id: 29
+master-bin.000001	2249	Write_rows	1	2283	table_id: 28 flags: STMT_END_F
+master-bin.000001	2283	Query	1	2352	COMMIT
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;

=== added file 'mysql-test/suite/rpl/t/rpl_unsafe_warning.test'
--- a/mysql-test/suite/rpl/t/rpl_unsafe_warning.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_unsafe_warning.test	2009-09-16 02:20:30 +0000
@@ -0,0 +1,74 @@
+#
+# BUG#45827
+# The test verifies if stmt that have more than one 
+# different tables to update with autoinc columns 
+# will produce unsafe warning
+#
+
+source include/have_binlog_format_statement.inc;
+--disable_warnings
+drop function if exists func_modify_t1;
+--enable_warnings
+
+# Test case1: stmt that have more than one different tables 
+#             to update with autoinc columns should produce 
+#             unsafe warning when calling a function
+CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+DELIMITER |;
+CREATE FUNCTION func_modify_t1 ()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 SET a = 1;
+  RETURN 0;
+END|
+DELIMITER ;|
+
+--echo # The following statement causes auto-incrementation  
+--echo # of both t1 and t2. It is logged in statement format, 
+--echo # so it should produce unsafe warning.
+INSERT INTO t2 SET a = func_modify_t1();
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t2 SET a = func_modify_t1();
+eval SHOW BINLOG EVENTS FROM $pos0_master;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP FUNCTION func_modify_t1;
+
+# Test case2: stmt that have more than one different tables 
+#             to update with autoinc columns should produce 
+#             unsafe warning when invoking a trigger 
+SET SESSION binlog_format = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+delimiter |;
+create trigger tri_modify_two_tables before insert on t1 for each row begin
+    insert into t2(a) values(new.a);
+    insert into t3(a) values(new.a);
+end |
+delimiter ;|
+--echo # The following statement causes auto-incrementation  
+--echo # of both t2 and t3. It is logged in statement format, 
+--echo # so it should produce unsafe warning
+INSERT INTO t1 SET a = 1;
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t1 SET a = 2;
+eval SHOW BINLOG EVENTS FROM $pos1_master;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-09-11 10:45:04 +0000
+++ b/sql/sql_base.cc	2009-09-16 02:20:30 +0000
@@ -4992,11 +4992,9 @@ bool lock_tables(THD *thd, TABLE_LIST *t
         statement-based binlogging won't work. We can solve this problem in
         mixed mode by switching to row-based binlogging:
       */
-      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
-          has_two_write_locked_tables_with_auto_increment(tables))
+      if (has_two_write_locked_tables_with_auto_increment(tables))
       {
         thd->lex->set_stmt_unsafe();
-        thd->set_current_stmt_binlog_row_based_if_mixed();
       }
     }
 


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090916022030-h3fy2ig68x1luibb.bundle
Thread
bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827Dao-Gang.Qu16 Sep
  • Re: bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827Sven Sandberg16 Sep
    • Re: bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827Daogang Qu16 Sep
      • Re: bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827Sven Sandberg16 Sep
        • Re: bzr commit into mysql-pe branch (Dao-Gang.Qu:3605) Bug#45827Daogang Qu17 Sep