From: Date: December 8 2008 4:07pm
Subject: bzr commit into mysql-5.1-bugteam branch (aelkin:2728) Bug#40221
List-Archive: http://lists.mysql.com/commits/60955
X-Bug: 40221
Message-Id: <200812081507.mB8F7CQv005581@mysql1000.dsl.inet.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///home/andrei/MySQL/BZR/FIXES/5.1-bt-bug40221-rbr-stmt-no-stmt-reset/

 2728 Andrei Elkin	2008-12-08
      Bug #40221  	Replication failure on RBR + UPDATE the primary key
      
      Extending bug#40221 regression test: 1. include INSERT 2. convert prim key + autoinc to
      unique.
modified:
  mysql-test/suite/binlog/r/binlog_innodb_row.result
  mysql-test/suite/binlog/t/binlog_innodb_row.test

per-file messages:
  mysql-test/suite/binlog/r/binlog_innodb_row.result
    results changed.
  mysql-test/suite/binlog/t/binlog_innodb_row.test
    Extending bug#40221 regression test to include the INSERT  dup key errored-out case.
=== modified file 'mysql-test/suite/binlog/r/binlog_innodb_row.result'
--- a/mysql-test/suite/binlog/r/binlog_innodb_row.result	2008-12-02 17:32:07 +0000
+++ b/mysql-test/suite/binlog/r/binlog_innodb_row.result	2008-12-08 15:07:08 +0000
@@ -1,16 +1,10 @@
-CREATE TABLE t1 (pk int auto_increment primary key) ENGINE=innodb;
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `pk` int(11) NOT NULL AUTO_INCREMENT,
-  PRIMARY KEY (`pk`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+CREATE TABLE t1 (i int unique) ENGINE=innodb;
 reset master;
 begin;
 insert into t1 values (1),(2);
 *** the following UPDATE query wont generate any updates for the binlog ***
-update t1 set pk = 3 where pk < 3;
-ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+update t1 set i = 3 where i < 3;
+ERROR 23000: Duplicate entry '3' for key 'i'
 commit;
 *** Results of the test: the binlog must have only Write_rows events not any Update_rows ***
 show binlog events from <binlog_start>;
@@ -19,4 +13,19 @@ master-bin.000001	#	Query	#	#	use `test`
 master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
 master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
 master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+delete from t1;
+reset master;
+begin;
+insert into t1 values (1),(2);
+*** the following UPDATE query wont generate any updates for the binlog ***
+insert into t1 values (3),(4),(1),(2);
+ERROR 23000: Duplicate entry '1' for key 'i'
+commit;
+*** Results of the test: the binlog must have only one Write_rows event not two ***
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
 drop table t1;

=== modified file 'mysql-test/suite/binlog/t/binlog_innodb_row.test'
--- a/mysql-test/suite/binlog/t/binlog_innodb_row.test	2008-12-02 17:32:07 +0000
+++ b/mysql-test/suite/binlog/t/binlog_innodb_row.test	2008-12-08 15:07:08 +0000
@@ -9,18 +9,34 @@ source include/have_binlog_format_row.in
 # Bug #40221 Replication failure on RBR + UPDATE the primary key
 #
 
-CREATE TABLE t1 (pk int auto_increment primary key) ENGINE=innodb;
-show create table t1;
+CREATE TABLE t1 (i int unique) ENGINE=innodb;
 reset master;
 
+# part 1: update can cause the dup key
+
 begin;
 insert into t1 values (1),(2);
 --echo *** the following UPDATE query wont generate any updates for the binlog ***
 --error ER_DUP_ENTRY
-update t1 set pk = 3 where pk < 3;
+update t1 set i = 3 where i < 3;
 commit;
 
 --echo *** Results of the test: the binlog must have only Write_rows events not any Update_rows ***
 source include/show_binlog_events.inc;
 
+# part 2: insert can cause the dup key
+
+delete from t1;
+reset master;
+
+begin;
+insert into t1 values (1),(2);
+--echo *** the following UPDATE query wont generate any updates for the binlog ***
+--error ER_DUP_ENTRY
+insert into t1 values (3),(4),(1),(2);
+commit;
+
+--echo *** Results of the test: the binlog must have only one Write_rows event not two ***
+source include/show_binlog_events.inc;
+
 drop table t1;