List:Commits« Previous MessageNext Message »
From:Alfranio Correia Date:September 28 2009 12:55pm
Subject:bzr commit into mysql-5.1-bugteam branch (alfranio.correia:3139)
Bug#47287
View as plain text  
#At file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-47287/mysql-5.1-bugteam/ based on revid:li-bing.song@stripped

 3139 Alfranio Correia	2009-09-28
      BUG#47287 RBR: replication diff on basic case with txn- and non-txn tables in a statement
      
      Let
        - T be a transactional table and N non-transactional table.
        - B be begin, C commit and R rollback.
        - M be a mixed statement, i.e. a statement that updates both T and N.
        - M* be a mixed statement that fails while updating either T or N.
      
      This patch restore the behavior presented in 5.1.37 for rows either produced in
      the RBR or MIXED modes, when a M* statement that happened early in a transaction
      had their changes written to the binary log outside the boundaries of the
      transaction and wrapped in a BEGIN/ROLLBACK. This was done to keep the slave
      consistent with with the master as the rollback would keep the changes on N and
      undo them on T. In particular, we do what follows:
      
        . B M* T C would log - B M* R B T C.
      
      Note that, we are not preserving history from the master as we are introducing a
      rollback that never happened. However, this seems to be more acceptable than
      making the slave diverge. We do not fix the following case:
      
        . B T M* C would log B T M* C.
      
      The slave will diverge as the changes on T tables that originated from the M
      statement are rolled back on the master but not on the slave. Unfortunately, we
      cannot simply rollback the transaction as this would undo any uncommitted
      changes on T tables.
      
      SBR is not considered in this patch because a failing statement is written to
      the binary along with the error code and a slave executes and then rolls back
      the statement when it has an associated error code, thus undoing the effects
      on T. In RBR and MBR, a full-fledged fix will be pushed after the WL 2687.

    added:
      mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test
      mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result
      mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result
      mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test
      mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test
    modified:
      sql/log.cc
=== added file 'mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test'
--- a/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test	2009-09-28 12:55:54 +0000
@@ -0,0 +1,190 @@
+################################################################################
+# Let 
+#   - T be a transactional table and N non-transactional table.
+#   - B be begin, C commit and R rollback.
+#   - M be a mixed statement, i.e. a statement that updates both T and N. 
+#   - M* be a mixed statement that fails while updating either T or N.
+#
+# In this test case, when changes are logged as rows either in the RBR or MIXED
+# modes, we check if a M* statement that happens early in a transaction is
+# written to the binary log outside the boundaries of the transaction and
+# wrapped in a BEGIN/ROLLBACK. This is done to keep the slave consistent with
+# with the master as the rollback will keep the changes on N and undo them on
+# T. In particular, we expect the following behavior:
+#
+#  1. B M* T C would log - B M* R B T C.
+#  2. B T M* C would log B T M* C.
+#
+# SBR is not considered in this test because a failing statement is written to
+# the binary along with the error code and a slave executes and then rolls back
+# the statement when it has an associated error code, thus undoing the effects
+# on T.
+#
+# Note that, in the first case, we are not preserving history from the master as
+# we are introducing a rollback that never happened. However, this seems to be
+# more acceptable than making the slave diverge. In the second case, the slave
+# will diverge as the changes on T tables that originated from the M statement
+# are rolled back on the master but not on the slave. Unfortunately, we cannot 
+# simply rollback the transaction as this would undo any uncommitted changes 
+# on T tables.
+#
+# Such issues do not happen in SBR. In RBR and MBR, a fix will be pushed after
+# the WL 2687.
+#
+# Please, remove this test case after pushing WL 2687.
+################################################################################
+
+
+--echo ###################################################################################
+--echo #                                   CONFIGURATION
+--echo ###################################################################################
+CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+
+DELIMITER |;
+
+CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
+BEGIN
+  INSERT INTO nt_1 VALUES (NEW.a, NEW.b);
+END|
+
+CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW
+BEGIN
+  INSERT INTO tt_2 VALUES (NEW.a, NEW.b);
+END|
+
+DELIMITER ;|
+
+--echo ###################################################################################
+--echo #                             CHECK HISTORY IN BINLOG
+--echo ###################################################################################
+--echo
+--echo
+--echo
+--echo *** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO nt_1 VALUES ("new text 1", 1);
+BEGIN;
+--error ER_DUP_ENTRY
+INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1);
+INSERT INTO tt_2 VALUES ("new text 3", 3);
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO tt_2 VALUES ("new text 4", 4);
+BEGIN;
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4);
+INSERT INTO tt_2 VALUES ("new text 6", 6);
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo
+--echo
+--echo
+--echo *** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO nt_1 VALUES ("new text 10", 10);
+BEGIN;
+INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8);
+--error ER_DUP_ENTRY
+INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10);
+INSERT INTO tt_2 VALUES ("new text 11", 11);
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO tt_2 VALUES ("new text 15", 15);
+BEGIN;
+INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13);
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15);
+INSERT INTO tt_2 VALUES ("new text 16", 16);
+COMMIT;
+--source include/show_binlog_events.inc
+
+
+--echo
+--echo
+--echo
+--echo *** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO nt_1 VALUES ("new text 18", 18);
+INSERT INTO nt_1 VALUES ("new text 20", 20);
+BEGIN;
+--error ER_DUP_ENTRY
+INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18);
+--error ER_DUP_ENTRY
+INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20);
+INSERT INTO tt_2 VALUES ("new text 21", 21);
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO tt_2 VALUES ("new text 23", 23);
+INSERT INTO tt_2 VALUES ("new text 25", 25);
+BEGIN;
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23);
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25);
+INSERT INTO tt_2 VALUES ("new text 26", 26);
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo
+--echo
+--echo
+--echo *** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+--echo *** "Nothing" in RBR and "B SELECT..INSERT* R" in MIXED mode.
+--echo *** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+--echo *** error code. However, there is a bug in RBR that will be fixed after WL#2687. Please, check BUG#47175
+--echo *** for further details.
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+INSERT INTO tt_2 VALUES ("new text 27", 27);
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 SELECT * FROM nt_1;
+INSERT INTO tt_2 VALUES ("new text 28", 28);
+ROLLBACK;
+--source include/show_binlog_events.inc
+
+--echo
+--echo
+--echo
+--echo *** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+--echo *** "B SELECT..INSERT* R" in RBR and "B SELECT..INSERT* C" in MIXED mode.
+--echo *** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+--echo *** error code.
+--echo
+let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+--error ER_DUP_ENTRY
+INSERT INTO nt_2 SELECT * FROM nt_1;
+COMMIT;
+--source include/show_binlog_events.inc
+
+--echo ###################################################################################
+--echo #                                        CLEAN
+--echo ###################################################################################
+
+DROP TABLE tt_1;
+DROP TABLE tt_2;
+DROP TABLE nt_1;
+DROP TABLE nt_2;

=== added file 'mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result'
--- a/mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result	2009-09-28 12:55:54 +0000
@@ -0,0 +1,240 @@
+###################################################################################
+#                                   CONFIGURATION
+###################################################################################
+CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
+BEGIN
+INSERT INTO nt_1 VALUES (NEW.a, NEW.b);
+END|
+CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW
+BEGIN
+INSERT INTO tt_2 VALUES (NEW.a, NEW.b);
+END|
+###################################################################################
+#                             CHECK HISTORY IN BINLOG
+###################################################################################
+
+
+
+*** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 1", 1);
+BEGIN;
+INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 3", 3);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_1 VALUES ("new text 1", 1)
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 3", 3)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 4", 4);
+BEGIN;
+INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4);
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 6", 6);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 4", 4)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 6", 6)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 10", 10);
+BEGIN;
+INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8);
+INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10);
+ERROR 23000: Duplicate entry '10' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 11", 11);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_1 VALUES ("new text 10", 10)
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 11", 11)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 15", 15);
+BEGIN;
+INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13);
+INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15);
+ERROR 23000: Duplicate entry '15' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 16", 16);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 15", 15)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 16", 16)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 18", 18);
+INSERT INTO nt_1 VALUES ("new text 20", 20);
+BEGIN;
+INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18);
+ERROR 23000: Duplicate entry '18' for key 'PRIMARY'
+INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20);
+ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 21", 21);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_1 VALUES ("new text 18", 18)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_1 VALUES ("new text 20", 20)
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 21", 21)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 23", 23);
+INSERT INTO tt_2 VALUES ("new text 25", 25);
+BEGIN;
+INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23);
+ERROR 23000: Duplicate entry '23' for key 'PRIMARY'
+INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25);
+ERROR 23000: Duplicate entry '25' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 26", 26);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 23", 23)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 25", 25)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 26", 26)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+*** "Nothing" in RBR and "B SELECT..INSERT* R" in MIXED mode.
+*** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+*** error code. However, there is a bug in RBR that will be fixed after WL#2687. Please, check BUG#47175
+*** for further details.
+
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+INSERT INTO tt_2 VALUES ("new text 27", 27);
+INSERT INTO nt_2 SELECT * FROM nt_1;
+ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 28", 28);
+ROLLBACK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE nt_2
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE tt_2
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 7", 7)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 27", 27)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_2 SELECT * FROM nt_1
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 28", 28)
+master-bin.000001	#	Query	#	#	ROLLBACK
+
+
+
+*** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+*** "B SELECT..INSERT* R" in RBR and "B SELECT..INSERT* C" in MIXED mode.
+*** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+*** error code.
+
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+INSERT INTO nt_2 SELECT * FROM nt_1;
+ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE nt_2
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE tt_2
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO tt_2 VALUES ("new text 7", 7)
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO nt_2 SELECT * FROM nt_1
+master-bin.000001	#	Query	#	#	COMMIT
+###################################################################################
+#                                        CLEAN
+###################################################################################
+DROP TABLE tt_1;
+DROP TABLE tt_2;
+DROP TABLE nt_1;
+DROP TABLE nt_2;

=== added file 'mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result'
--- a/mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result	2009-09-28 12:55:54 +0000
@@ -0,0 +1,274 @@
+###################################################################################
+#                                   CONFIGURATION
+###################################################################################
+CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
+CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb;
+CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
+BEGIN
+INSERT INTO nt_1 VALUES (NEW.a, NEW.b);
+END|
+CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW
+BEGIN
+INSERT INTO tt_2 VALUES (NEW.a, NEW.b);
+END|
+###################################################################################
+#                             CHECK HISTORY IN BINLOG
+###################################################################################
+
+
+
+*** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 1", 1);
+BEGIN;
+INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 3", 3);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 4", 4);
+BEGIN;
+INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4);
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 6", 6);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 10", 10);
+BEGIN;
+INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8);
+INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10);
+ERROR 23000: Duplicate entry '10' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 11", 11);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 15", 15);
+BEGIN;
+INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13);
+INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15);
+ERROR 23000: Duplicate entry '15' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 16", 16);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries
+
+INSERT INTO nt_1 VALUES ("new text 18", 18);
+INSERT INTO nt_1 VALUES ("new text 20", 20);
+BEGIN;
+INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18);
+ERROR 23000: Duplicate entry '18' for key 'PRIMARY'
+INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20);
+ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 21", 21);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+INSERT INTO tt_2 VALUES ("new text 23", 23);
+INSERT INTO tt_2 VALUES ("new text 25", 25);
+BEGIN;
+INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23);
+ERROR 23000: Duplicate entry '23' for key 'PRIMARY'
+INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25);
+ERROR 23000: Duplicate entry '25' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 26", 26);
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+*** "Nothing" in RBR and "B SELECT..INSERT* R" in MIXED mode.
+*** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+*** error code. However, there is a bug in RBR that will be fixed after WL#2687. Please, check BUG#47175
+*** for further details.
+
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+INSERT INTO tt_2 VALUES ("new text 27", 27);
+INSERT INTO nt_2 SELECT * FROM nt_1;
+ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
+INSERT INTO tt_2 VALUES ("new text 28", 28);
+ROLLBACK;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE nt_2
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE tt_2
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+
+
+*** "B SELECT...INSERT* C" with an error in SELECT...INSERT* generates in the binlog the following entries:
+*** "B SELECT..INSERT* R" in RBR and "B SELECT..INSERT* C" in MIXED mode.
+*** Note that there is nothing wrong with the behavior in MIXED as the statement is logged along with the 
+*** error code.
+
+TRUNCATE TABLE nt_2;
+TRUNCATE TABLE tt_2;
+INSERT INTO tt_2 VALUES ("new text 7", 7);
+BEGIN;
+INSERT INTO nt_2 SELECT * FROM nt_1;
+ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
+COMMIT;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE nt_2
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; TRUNCATE TABLE tt_2
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.nt_2)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.tt_2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+###################################################################################
+#                                        CLEAN
+###################################################################################
+DROP TABLE tt_1;
+DROP TABLE tt_2;
+DROP TABLE nt_1;
+DROP TABLE nt_2;

=== added file 'mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test'
--- a/mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test	2009-09-28 12:55:54 +0000
@@ -0,0 +1,4 @@
+--source include/have_binlog_format_mixed.inc
+--source include/have_innodb.inc
+
+--source extra/binlog_tests/binlog_failure_mixing_engines.test

=== added file 'mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test'
--- a/mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test	2009-09-28 12:55:54 +0000
@@ -0,0 +1,4 @@
+--source include/have_binlog_format_row.inc
+--source include/have_innodb.inc
+
+--source extra/binlog_tests/binlog_failure_mixing_engines.test

=== modified file 'sql/log.cc'
--- a/sql/log.cc	2009-09-11 17:06:27 +0000
+++ b/sql/log.cc	2009-09-28 12:55:54 +0000
@@ -153,7 +153,7 @@ private:
 class binlog_trx_data {
 public:
   binlog_trx_data()
-    : at_least_one_stmt(0), incident(FALSE), m_pending(0),
+    : at_least_one_stmt_committed(0), incident(FALSE), m_pending(0),
     before_stmt_pos(MY_OFF_T_UNDEF)
   {
     trans_log.end_of_file= max_binlog_cache_size;
@@ -182,7 +182,10 @@ public:
   {
     DBUG_PRINT("info", ("truncating to position %lu", (ulong) pos));
     DBUG_PRINT("info", ("before_stmt_pos=%lu", (ulong) pos));
-    delete pending();
+    if (pending())
+    {
+      delete pending();
+    }
     set_pending(0);
     reinit_io_cache(&trans_log, WRITE_CACHE, pos, 0, 0);
     trans_log.end_of_file= max_binlog_cache_size;
@@ -192,12 +195,12 @@ public:
     /*
       The only valid positions that can be truncated to are at the
       beginning of a statement. We are relying on this fact to be able
-      to set the at_least_one_stmt flag correctly. In other word, if
+      to set the at_least_one_stmt_committed flag correctly. In other word, if
       we are truncating to the beginning of the transaction cache,
       there will be no statements in the cache, otherwhise, we will
       have at least one statement in the transaction cache.
      */
-    at_least_one_stmt= (pos > 0);
+    at_least_one_stmt_committed= (pos > 0);
   }
 
   /*
@@ -239,7 +242,7 @@ public:
     Boolean that is true if there is at least one statement in the
     transaction cache.
   */
-  bool at_least_one_stmt;
+  bool at_least_one_stmt_committed;
   bool incident;
 
 private:
@@ -1539,9 +1542,10 @@ static int binlog_commit(handlerton *hto
   {
     Query_log_event qev(thd, STRING_WITH_LEN("COMMIT"), TRUE, TRUE, 0);
     error= binlog_end_trans(thd, trx_data, &qev, all);
-    goto end;
   }
 
+  trx_data->at_least_one_stmt_committed = my_b_tell(&trx_data->trans_log) > 0;
+
 end:
   if (!all)
     trx_data->before_stmt_pos = MY_OFF_T_UNDEF; // part of the stmt commit
@@ -1608,15 +1612,18 @@ static int binlog_rollback(handlerton *h
   {
    /*
       We flush the cache with a rollback, wrapped in a beging/rollback if:
-        . aborting a transcation that modified a non-transactional table or;
+        . aborting a transaction that modified a non-transactional table;
         . aborting a statement that modified both transactional and
-          non-transctional tables but which is not in the boundaries of any
-          transaction;
+          non-transactional tables but which is not in the boundaries of any
+          transaction or there was no early change;
         . the OPTION_KEEP_LOG is activate.
     */
     if ((all && thd->transaction.all.modified_non_trans_table) ||
         (!all && thd->transaction.stmt.modified_non_trans_table &&
          !(thd->options & (OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT))) ||
+        (!all && thd->transaction.stmt.modified_non_trans_table &&
+         !trx_data->at_least_one_stmt_committed &&
+         thd->current_stmt_binlog_row_based) ||
         ((thd->options & OPTION_KEEP_LOG)))
     {
       Query_log_event qev(thd, STRING_WITH_LEN("ROLLBACK"), TRUE, TRUE, 0);


Attachment: [text/bzr-bundle]
Thread
bzr commit into mysql-5.1-bugteam branch (alfranio.correia:3139)Bug#47287Alfranio Correia28 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (alfranio.correia:3139)Bug#47287Luís Soares28 Sep