List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:November 13 2009 8:41am
Subject:bzr commit into mysql-5.1-rep+2 branch (Dao-Gang.Qu:3140) Bug#45827
View as plain text  
#At file:///home/daogangqu/mysql/bzrwork/bug45827backport/mysql-5.1-rep%2B2/ based on revid:aelkin@stripped

 3140 Dao-Gang.Qu@stripped	2009-11-13
      Backport 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 mode too.
     @ mysql-test/extra/rpl_tests/rpl_insert_id.test
        The test case is updated due to the patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        The test result is updated due to the patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result
        The test result is updated due to the patch of bug#45827.
     @ mysql-test/suite/rpl/t/rpl_trigger.test
        The test case is updated due to the patch of bug#45827.
     @ mysql-test/suite/rpl/t/rpl_variables_stm.test
        The test case is updated due to the patch of bug#45827.
     @ sql/sql_base.cc
        Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function 
        for producing unsafe warnings by executing 'decide_logging_format' 
        function later in statement mode

    modified:
      mysql-test/extra/rpl_tests/rpl_insert_id.test
      mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
      mysql-test/suite/binlog/r/binlog_unsafe.result
      mysql-test/suite/binlog/t/binlog_unsafe.test
      mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result
      mysql-test/suite/rpl/t/rpl_trigger.test
      mysql-test/suite/rpl/t/rpl_variables_stm.test
      sql/sql_base.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_insert_id.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test	2008-10-08 11:23:53 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test	2009-11-13 08:29:35 +0000
@@ -179,7 +179,9 @@ begin
 end|
 delimiter ;|
 
+--disable_warnings
 insert into t1 (last_id) values (0);
+--enable_warnings
 
 drop trigger t1_bi;
 
@@ -512,7 +514,9 @@ set sql_log_bin=0;
 insert into t2 (id) values(5),(6),(7);
 delete from t2 where id>=5;
 set sql_log_bin=1;
+--disable_warnings
 insert into t1 select insid();
+--enable_warnings
 select * from t1 order by id;
 select * from t2 order by id;
 

=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result	2009-09-28 12:41:10 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result	2009-11-13 08:29:35 +0000
@@ -456,7 +456,11 @@ return n;
 end|
 reset master;
 insert into t2 values (bug27417(1));
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 insert into t2 select bug27417(2);
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 reset master;
 insert into t2 values (bug27417(2));
 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
@@ -473,6 +477,8 @@ select count(*) from t2;
 count(*)
 2
 delete from t2 where a=bug27417(3);
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 select count(*) from t2 /* nothing got deleted */;
 count(*)
 2
@@ -485,6 +491,8 @@ select count(*) from t1 /* must be 5 */;
 count(*)
 5
 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 affected rows: 0
 select count(*) from t1 /* must be 7 */;
 count(*)
@@ -700,7 +708,11 @@ return n;
 end|
 reset master;
 insert into t2 values (bug27417(1));
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 insert into t2 select bug27417(2);
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 reset master;
 insert into t2 values (bug27417(2));
 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
@@ -716,6 +728,8 @@ select count(*) from t2;
 count(*)
 2
 delete from t2 where a=bug27417(3);
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 select count(*) from t2 /* nothing got deleted */;
 count(*)
 2
@@ -727,6 +741,8 @@ select count(*) from t1 /* must be 5 */;
 count(*)
 5
 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 affected rows: 0
 select count(*) from t1 /* must be 7 */;
 count(*)

=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2009-07-10 23:12:13 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2009-11-13 08:29:35 +0000
@@ -327,4 +327,59 @@ Warnings:
 Note	1592	Statement may not be safe to log in statement format.
 DROP TABLE t1, t2;
 SET @@SESSION.SQL_MODE = @save_sql_mode;
+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 12283;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	12283	Query	1	12351	BEGIN
+master-bin.000001	12351	Table_map	1	12393	table_id: 44 (test.t2)
+master-bin.000001	12393	Table_map	1	12435	table_id: 45 (test.t1)
+master-bin.000001	12435	Write_rows	1	12473	table_id: 45
+master-bin.000001	12473	Write_rows	1	12511	table_id: 44 flags: STMT_END_F
+master-bin.000001	12511	Query	1	12580	COMMIT
+DROP TABLE t1,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 13426;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	13426	Query	1	13494	BEGIN
+master-bin.000001	13494	Table_map	1	13535	table_id: 47 (test.t1)
+master-bin.000001	13535	Table_map	1	13577	table_id: 48 (test.t3)
+master-bin.000001	13577	Table_map	1	13619	table_id: 49 (test.t2)
+master-bin.000001	13619	Write_rows	1	13657	table_id: 49
+master-bin.000001	13657	Write_rows	1	13695	table_id: 48
+master-bin.000001	13695	Write_rows	1	13729	table_id: 47 flags: STMT_END_F
+master-bin.000001	13729	Query	1	13798	COMMIT
+DROP TABLE t1,t2,t3;
 "End of tests"

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2009-07-10 23:12:13 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2009-11-13 08:29:35 +0000
@@ -388,4 +388,72 @@ DELETE FROM t1 LIMIT 1;
 
 DROP TABLE t1, t2;
 SET @@SESSION.SQL_MODE = @save_sql_mode;
+
+#
+# BUG#45827
+# The test verifies if stmt that have more than one 
+# different tables to update with autoinc columns 
+# will produce unsafe warning
+#
+
+# 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;
+
+# clean up
+DROP TABLE t1,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;
+
+# clean up
+DROP TABLE t1,t2,t3;
+
 --echo "End of tests"

=== modified file 'mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result'
--- a/mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result	2008-03-29 22:54:08 +0000
+++ b/mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result	2009-11-13 08:29:35 +0000
@@ -42,6 +42,9 @@ INSERT INTO t2 VALUES (NULL);
 RETURN i;
 END//
 CALL p1();
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
+Note	1592	Statement may not be safe to log in statement format.
 show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Intvar	#	#	INSERT_ID=1
@@ -98,6 +101,9 @@ SELECT * FROM t2;
 id
 DROP TRIGGER tr1;
 CALL p2();
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
+Note	1592	Statement may not be safe to log in statement format.
 show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Intvar	#	#	INSERT_ID=11

=== modified file 'mysql-test/suite/rpl/t/rpl_trigger.test'
--- a/mysql-test/suite/rpl/t/rpl_trigger.test	2009-08-03 15:01:06 +0000
+++ b/mysql-test/suite/rpl/t/rpl_trigger.test	2009-11-13 08:29:35 +0000
@@ -38,10 +38,12 @@ insert into t3 values(100,"log",0,0,0);
 SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
 
 # Emulate that we have rows 2-9 deleted on the slave
+--disable_warnings
 insert into t1 values(1,1,rand()),(NULL,2,rand());
 insert into t2 (b) values(last_insert_id());
 insert into t2 values(3,0),(NULL,0);
 insert into t2 values(NULL,0),(500,0);
+--enable_warnings
 
 select a,b, truncate(rand_value,4) from t1;
 select * from t2;

=== modified file 'mysql-test/suite/rpl/t/rpl_variables_stm.test'
--- a/mysql-test/suite/rpl/t/rpl_variables_stm.test	2008-03-11 17:43:29 +0000
+++ b/mysql-test/suite/rpl/t/rpl_variables_stm.test	2009-11-13 08:29:35 +0000
@@ -471,7 +471,9 @@ BEGIN
 END|
 DELIMITER ;|
 
+--disable_warnings
 INSERT INTO trigger_table VALUES ('bye.');
+--enable_warnings
 
 
 --echo ==== Insert variables from a prepared statement ====

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-10-07 15:03:42 +0000
+++ b/sql/sql_base.cc	2009-11-13 08:29:35 +0000
@@ -5286,9 +5286,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
         We can solve these problems in mixed mode by switching to binlogging 
         if at least one updated table is used by sub-statement
       */
-      /* The BINLOG_FORMAT_MIXED judgement is saved for suppressing 
-         warnings, but it will be removed by fixing bug#45827 */
-      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED && tables && 
+      if (thd->variables.binlog_format != BINLOG_FORMAT_ROW && tables && 
           has_write_table_with_auto_increment(thd->lex->first_not_own_table()))
       {
         thd->lex->set_stmt_unsafe();


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20091113082935-y31ahk5p3swj0y0t.bundle
Thread
bzr commit into mysql-5.1-rep+2 branch (Dao-Gang.Qu:3140) Bug#45827Dao-Gang.Qu13 Nov