#At file:///home/lsoares/Workspace/mysql-server/bugfix/40045/6.0-rpl/ based on revid:aelkin@stripped
2821 Luis Soares 2009-03-17
BUG#40045: Replication failure on RBR + no PK + UPDATE + integer key +
char with no key
Row based replication would break if updating a row and using a key
for searching for the correct row to update. This was observed when
using MyISAM storage engine on the slave. Since MyISAM does not
provide partial row fetch (HA_PARTIAL_COLUMN_READ) , the comparison
between the row fetched on the slave and the before image (BI)
replicated from master would fail. This happened because the BI (which
sometimes was only part of the row) would always be compared against a
complete row.
Furthermore, the read set mask was not being properly set when using
keys (not unique nor primary) which would lead to partial row matches,
thence changes might end up in the wrong row.
This patch addresses this by changing the record_compare function for
taking into consideration the fields set on the readset and the engine
used on the slave. Furthermore, it also changes the construction of
the read set on the master by forcing the setting of the appropriate
fields (the ones that uniquely identify the row) before the
delete/update/insert_row functions are called.
@ mysql-test/suite/rpl/r/rpl_row_record_find.result
Result file for the test case.
@ mysql-test/suite/rpl/t/rpl_row_record_find.test
Test file with three test cases for the given fix.
@ sql/handler.cc
Added changes for forcing marking the correct read_set when binlogging.
@ sql/log_event.cc
Changed the record_compare function to compare the entire record
when possible, or just the fields marked in the read_set otherwise.
@ sql/log_event_old.cc
Changed the record_compare function to compare the entire record
when possible, or just the fields marked in the read_set otherwise.
added:
mysql-test/suite/rpl/r/rpl_row_record_find.result
mysql-test/suite/rpl/t/rpl_row_record_find.test
modified:
sql/handler.cc
sql/log_event.cc
sql/log_event_old.cc
=== added file 'mysql-test/suite/rpl/r/rpl_row_record_find.result'
--- a/mysql-test/suite/rpl/r/rpl_row_record_find.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_row_record_find.result 2009-03-17 09:50:28 +0000
@@ -0,0 +1,441 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+** NO INDEX (MyISAM -> MyISAM)
+CREATE TABLE t1 (a int, b char(1));
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+a b
+1 X
+1 X
+SELECT * FROM t1;
+a b
+1 X
+1 X
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+a b
+SELECT * FROM t1;
+a b
+** INDEX (MyISAM -> MyISAM)
+CREATE TABLE t2 (a int, b char(1), key (a));
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+a b
+1 X
+1 X
+SELECT * FROM t2;
+a b
+1 X
+1 X
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+a b
+SELECT * FROM t2;
+a b
+** PARTIAL INDEX READ (MyISAM -> MyISAM)
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b));
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+a b c
+SELECT * FROM t3;
+a b c
+** UNIQUE INDEX READ (MyISAM -> MyISAM)
+CREATE TABLE t4 (a int, b char(1), unique key (a));
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+a b
+1 X
+2 B
+SELECT * FROM t4;
+a b
+1 X
+2 B
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+a b
+2 Y
+SELECT * FROM t4;
+a b
+2 Y
+** PRIMARY KEY READ (MyISAM -> MyISAM)
+CREATE TABLE t5 (a int, b char(1), primary key (a));
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+a b
+1 X
+2 B
+SELECT * FROM t5;
+a b
+1 X
+2 B
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+a b
+2 Y
+SELECT * FROM t5;
+a b
+2 Y
+DROP TABLE t1, t2, t3, t4, t5;
+** NO INDEX (InnoDB -> InnoDB)
+CREATE TABLE t1 (a int, b char(1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+a b
+1 X
+1 X
+SELECT * FROM t1;
+a b
+1 X
+1 X
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+a b
+SELECT * FROM t1;
+a b
+** INDEX (InnoDB -> InnoDB)
+CREATE TABLE t2 (a int, b char(1), key (a)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+a b
+1 X
+1 X
+SELECT * FROM t2;
+a b
+1 X
+1 X
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+a b
+SELECT * FROM t2;
+a b
+** PARTIAL INDEX READ (InnoDB -> InnoDB)
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+a b c
+SELECT * FROM t3;
+a b c
+** UNIQUE INDEX READ
+CREATE TABLE t4 (a int, b char(1), unique key (a)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+a b
+1 X
+2 B
+SELECT * FROM t4;
+a b
+1 X
+2 B
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+a b
+2 Y
+SELECT * FROM t4;
+a b
+2 Y
+** PRIMARY KEY READ
+CREATE TABLE t5 (a int, b char(1), primary key (a)) ENGINE=InnoDB;
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+a b
+1 X
+2 B
+SELECT * FROM t5;
+a b
+1 X
+2 B
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+a b
+2 Y
+SELECT * FROM t5;
+a b
+2 Y
+DROP TABLE t1, t2, t3, t4, t5;
+** NO INDEX (InnoDB -> MyISAM)
+SET SQL_LOG_BIN=0;
+CREATE TABLE t1 (a int, b char(1)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+CREATE TABLE t1 (a int, b char(1));
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+a b
+1 X
+1 X
+SELECT * FROM t1;
+a b
+1 X
+1 X
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+SELECT * FROM t1;
+a b
+1 Y
+1 Y
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+a b
+SELECT * FROM t1;
+a b
+** INDEX (InnoDB -> MyISAM)
+SET SQL_LOG_BIN=0;
+CREATE TABLE t2 (a int, b char(1), key(a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+CREATE TABLE t2 (a int, b char(1), key(a));
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+a b
+1 X
+1 X
+SELECT * FROM t2;
+a b
+1 X
+1 X
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+SELECT * FROM t2;
+a b
+1 Y
+1 Y
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+a b
+SELECT * FROM t2;
+a b
+** PARTIAL INDEX READ (InnoDB -> MyISAM)
+SET SQL_LOG_BIN=0;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b));
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+SELECT * FROM t3;
+a b c
+1 B X
+1 B X
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+SELECT * FROM t3;
+a b c
+1 B Y
+1 B Y
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+a b c
+SELECT * FROM t3;
+a b c
+** UNIQUE INDEX READ (InnoDB -> MyISAM)
+SET SQL_LOG_BIN=0;
+CREATE TABLE t4 (a int, b char(1), unique key (a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+CREATE TABLE t4 (a int, b char(1), unique key (a));
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+a b
+1 X
+2 B
+SELECT * FROM t4;
+a b
+1 X
+2 B
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+SELECT * FROM t4;
+a b
+1 Y
+2 Y
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+a b
+2 Y
+SELECT * FROM t4;
+a b
+2 Y
+** PRIMARY KEY READ (InnoDB -> MyISAM)
+SET SQL_LOG_BIN=0;
+CREATE TABLE t5 (a int, b char(1), primary key (a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+CREATE TABLE t5 (a int, b char(1), primary key (a));
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+a b
+1 X
+2 B
+SELECT * FROM t5;
+a b
+1 X
+2 B
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+SELECT * FROM t5;
+a b
+1 Y
+2 Y
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+a b
+2 Y
+SELECT * FROM t5;
+a b
+2 Y
+DROP TABLE t1, t2, t3, t4, t5;
=== added file 'mysql-test/suite/rpl/t/rpl_row_record_find.test'
--- a/mysql-test/suite/rpl/t/rpl_row_record_find.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_record_find.test 2009-03-17 09:50:28 +0000
@@ -0,0 +1,505 @@
+#
+# BUG#40045: Replication failure on RBR + no PK + UPDATE + integer key + char
+# with no key
+#
+# TEST DESCRIPTION
+# ================
+#
+# This test is structure in three cases. In all of them, roughly the same
+# update and delete operations are performed, but what changes are the keys
+# in the table:
+# no keys, one key, composite key (but only half is used in query),
+# unique key, primary key
+#
+# The three cases are distinct because the engine on both and master and
+# slave varies:
+#
+# CASE 1: MyISAM - master, MyISAM - slave
+# test without HA_PARTIAL_COLUMN_READ engine on master and on slave
+# CASE 2: InnoDB - master, InnoDB - slave
+# test with HA_PARTIAL_COLUMN_READ engine on master and on slave
+# CASE 3: InnoDB - master, MyISAM - slave
+# test with HA_PARTIAL_COLUMN_READ engine on master but not on slave
+
+
+source include/master-slave.inc;
+source include/have_binlog_format_row.inc;
+source include/have_innodb.inc;
+
+#################################################################
+# CASE 1: MyISAM - master, MyISAM - slave
+#################################################################
+
+#### test: no index ####
+--echo ** NO INDEX (MyISAM -> MyISAM)
+CREATE TABLE t1 (a int, b char(1));
+sync_slave_with_master;
+connection master;
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+
+# with key in WHERE clause
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+# without WHERE clause
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+# DELETE
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+
+##### test: index read ####
+connection master;
+--echo ** INDEX (MyISAM -> MyISAM)
+CREATE TABLE t2 (a int, b char(1), key (a));
+sync_slave_with_master;
+connection master;
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+
+# with key in WHERE clause
+connection master;
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+
+# without WHERE clause
+connection master;
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+
+## delete from table
+connection master;
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+
+#### test: half index read ####
+--echo ** PARTIAL INDEX READ (MyISAM -> MyISAM)
+connection master;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b));
+sync_slave_with_master;
+connection master;
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+
+# with key in WHERE clause
+connection master;
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+
+# without WHERE clause
+connection master;
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+
+# delete from table
+connection master;
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+
+#### test: unique index ####
+--echo ** UNIQUE INDEX READ (MyISAM -> MyISAM)
+connection master;
+CREATE TABLE t4 (a int, b char(1), unique key (a));
+sync_slave_with_master;
+connection master;
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# without WHERE clause
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# delete from table
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+
+#### test: unique index ####
+--echo ** PRIMARY KEY READ (MyISAM -> MyISAM)
+connection master;
+CREATE TABLE t5 (a int, b char(1), primary key (a));
+sync_slave_with_master;
+connection master;
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# without WHERE clause
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# delete from table
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+
+# CLEAN UP
+connection master;
+DROP TABLE t1, t2, t3, t4, t5;
+sync_slave_with_master;
+
+#################################################################
+# CASE 2: InnoDB - master, InnoDB - slave
+#################################################################
+
+#### test: no index ####
+--echo ** NO INDEX (InnoDB -> InnoDB)
+connection master;
+CREATE TABLE t1 (a int, b char(1)) ENGINE=InnoDB;
+sync_slave_with_master;
+connection master;
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+
+# with key in WHERE clause
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+# without WHERE clause
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+# DELETE
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+
+#### test: index read ####
+connection master;
+--echo ** INDEX (InnoDB -> InnoDB)
+CREATE TABLE t2 (a int, b char(1), key (a)) ENGINE=InnoDB;
+sync_slave_with_master;
+connection master;
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+
+# with key in WHERE clause
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+connection master;
+
+# without WHERE clause
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+connection master;
+
+# delete from table
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+
+##### test: half index read ####
+--echo ** PARTIAL INDEX READ (InnoDB -> InnoDB)
+connection master;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b)) ENGINE=InnoDB;
+sync_slave_with_master;
+connection master;
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+
+# with key in WHERE clause
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+connection master;
+
+# without WHERE clause
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+connection master;
+
+# delete from table
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+
+#### test: unique index ####
+--echo ** UNIQUE INDEX READ
+connection master;
+CREATE TABLE t4 (a int, b char(1), unique key (a)) ENGINE=InnoDB;
+sync_slave_with_master;
+connection master;
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# without WHERE clause
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# delete from table
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+
+#### test: primary key ####
+--echo ** PRIMARY KEY READ
+connection master;
+CREATE TABLE t5 (a int, b char(1), primary key (a)) ENGINE=InnoDB;
+sync_slave_with_master;
+connection master;
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# without WHERE clause
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# delete from table
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+
+#################################################################
+# CASE 3: InnoDB - master, MyISAM - slave
+#################################################################
+
+connection master;
+DROP TABLE t1, t2, t3, t4, t5;
+sync_slave_with_master;
+
+##### test: no index ####
+--echo ** NO INDEX (InnoDB -> MyISAM)
+connection master;
+SET SQL_LOG_BIN=0;
+CREATE TABLE t1 (a int, b char(1)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+connection slave;
+CREATE TABLE t1 (a int, b char(1));
+connection master;
+INSERT INTO t1 VALUES (1, 'B');
+INSERT INTO t1 VALUES (1, 'C');
+
+# with key in WHERE clause
+UPDATE t1 SET b = 'X' WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+## without WHERE clause
+UPDATE t1 SET b = 'Y';
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+
+# DELETE
+DELETE FROM t1 WHERE a = 1;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+
+#### test: index read ####
+connection master;
+--echo ** INDEX (InnoDB -> MyISAM)
+connection master;
+SET SQL_LOG_BIN=0;
+CREATE TABLE t2 (a int, b char(1), key(a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+connection slave;
+CREATE TABLE t2 (a int, b char(1), key(a));
+connection master;
+INSERT INTO t2 VALUES (1, 'B');
+INSERT INTO t2 VALUES (1, 'C');
+
+# with key in WHERE clause
+UPDATE t2 SET b = 'X' WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+connection master;
+
+# without WHERE clause
+UPDATE t2 SET b = 'Y';
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+connection master;
+
+# delete from table
+DELETE FROM t2 WHERE a = 1;
+SELECT * FROM t2;
+sync_slave_with_master;
+SELECT * FROM t2;
+
+##### test: half index read ####
+--echo ** PARTIAL INDEX READ (InnoDB -> MyISAM)
+connection master;
+SET SQL_LOG_BIN=0;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+connection slave;
+CREATE TABLE t3 (a int, b char(1), c char(1), key (a,b));
+connection master;
+INSERT INTO t3 VALUES (1, 'B', 'C');
+INSERT INTO t3 VALUES (1, 'B', 'D');
+
+# with key in WHERE clause
+UPDATE t3 SET c = 'X' WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+connection master;
+
+# without WHERE clause
+UPDATE t3 SET c = 'Y';
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+connection master;
+
+# delete from table
+DELETE FROM t3 WHERE a = 1;
+SELECT * FROM t3;
+sync_slave_with_master;
+SELECT * FROM t3;
+
+#### test: unique index ####
+--echo ** UNIQUE INDEX READ (InnoDB -> MyISAM)
+connection master;
+SET SQL_LOG_BIN=0;
+CREATE TABLE t4 (a int, b char(1), unique key (a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+connection slave;
+CREATE TABLE t4 (a int, b char(1), unique key (a));
+connection master;
+INSERT INTO t4 VALUES (1, 'B');
+INSERT INTO t4 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t4 SET b = 'X' WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# without WHERE clause
+UPDATE t4 SET b = 'Y';
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+connection master;
+
+# delete from table
+DELETE FROM t4 WHERE a = 1;
+SELECT * FROM t4;
+sync_slave_with_master;
+SELECT * FROM t4;
+
+#### test: primary key ####
+--echo ** PRIMARY KEY READ (InnoDB -> MyISAM)
+connection master;
+SET SQL_LOG_BIN=0;
+CREATE TABLE t5 (a int, b char(1), primary key (a)) ENGINE=InnoDB;
+SET SQL_LOG_BIN=1;
+connection slave;
+CREATE TABLE t5 (a int, b char(1), primary key (a));
+connection master;
+INSERT INTO t5 VALUES (1, 'B');
+INSERT INTO t5 VALUES (2, 'B');
+
+# with key in WHERE clause
+UPDATE t5 SET b = 'X' WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# without WHERE clause
+UPDATE t5 SET b = 'Y';
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+connection master;
+
+# delete from table
+DELETE FROM t5 WHERE a = 1;
+SELECT * FROM t5;
+sync_slave_with_master;
+SELECT * FROM t5;
+
+# CLEAN UP
+connection master;
+DROP TABLE t1, t2, t3, t4, t5;
+sync_slave_with_master;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2009-02-23 14:53:18 +0000
+++ b/sql/handler.cc 2009-03-17 09:50:28 +0000
@@ -5500,6 +5500,9 @@ int handler::ha_write_row(uchar *buf)
Log_func *log_func= Write_rows_log_event::binlog_row_logging_function;
DBUG_ENTER("handler::ha_write_row");
+ if (table->in_use->current_stmt_binlog_row_based && mysql_bin_log.is_open())
+ table->mark_columns_needed_for_insert();
+
MYSQL_INSERT_ROW_START(table_share->db.str, table_share->table_name.str);
mark_trx_read_write();
error= write_row(buf);
@@ -5519,6 +5522,13 @@ int handler::ha_update_row(const uchar *
Log_func *log_func= Update_rows_log_event::binlog_row_logging_function;
/*
+ If binlog is active and logging is in row mode, force the columns needed
+ to uniquely identify a row to be marked.
+ */
+ if (table->in_use->current_stmt_binlog_row_based && mysql_bin_log.is_open())
+ table->mark_columns_needed_for_update();
+
+ /*
Some storage engines require that the new record is in record[0]
(and the old record is in record[1]).
*/
@@ -5541,6 +5551,13 @@ int handler::ha_delete_row(const uchar *
int error;
Log_func *log_func= Delete_rows_log_event::binlog_row_logging_function;
+ /*
+ If binlog is active and logging is in row mode, force the columns needed
+ to uniquely identify a row to be marked.
+ */
+ if (table->in_use->current_stmt_binlog_row_based && mysql_bin_log.is_open())
+ table->mark_columns_needed_for_delete();
+
MYSQL_DELETE_ROW_START(table_share->db.str, table_share->table_name.str);
mark_trx_read_write();
error= delete_row(buf);
=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc 2009-03-04 13:33:47 +0000
+++ b/sql/log_event.cc 2009-03-17 09:50:28 +0000
@@ -8611,7 +8611,7 @@ void Write_rows_log_event::print(FILE *f
Returns TRUE if different.
*/
-static bool record_compare(TABLE *table)
+static bool record_compare(TABLE *table, const MY_BITMAP *cols_in_readset)
{
/*
Need to set the X bit and the filler bits in both records since
@@ -8643,14 +8643,23 @@ static bool record_compare(TABLE *table)
}
}
- if (table->s->blob_fields + table->s->varchar_fields == 0)
+ /*
+ We can compare the record straight away if:
+ i) there are no blob and varchar fields
+ ii) all columns were read or the slave SE provides partial reads
+ */
+ if ((table->s->blob_fields + table->s->varchar_fields == 0) &&
+ (bitmap_is_set_all(cols_in_readset) ||
+ (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ)))
{
result= cmp_record(table,record[1]);
goto record_compare_exit;
}
- /* Compare null bits */
- if (memcmp(table->null_flags,
+ /* Compare null bits only if all fields were read or slave SE has partial reads */
+ if ((bitmap_is_set_all(cols_in_readset) ||
+ (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ)) &&
+ memcmp(table->null_flags,
table->null_flags+table->s->rec_buff_length,
table->s->null_bytes))
{
@@ -8661,10 +8670,14 @@ static bool record_compare(TABLE *table)
/* Compare updated fields */
for (Field **ptr=table->field ; *ptr ; ptr++)
{
- if ((*ptr)->cmp_binary_offset(table->s->rec_buff_length))
- {
- result= TRUE;
- goto record_compare_exit;
+ /* compare field if it is set */
+ if (bitmap_is_set(cols_in_readset, (*ptr)->field_index))
+ {
+ if ((*ptr)->cmp_binary_offset(table->s->rec_buff_length))
+ {
+ result= TRUE;
+ goto record_compare_exit;
+ }
}
}
@@ -8875,7 +8888,7 @@ int Rows_log_event::find_row(const Relay
*/
DBUG_PRINT("info",("non-unique index, scanning it to find matching record"));
- while (record_compare(table))
+ while (record_compare(table, &m_cols))
{
/*
We need to set the null bytes to ensure that the filler bit
@@ -8956,7 +8969,7 @@ int Rows_log_event::find_row(const Relay
goto err;
}
}
- while (restart_count < 2 && record_compare(table));
+ while (restart_count < 2 && record_compare(table, &m_cols));
/*
Note: above record_compare will take into accout all record fields
=== modified file 'sql/log_event_old.cc'
--- a/sql/log_event_old.cc 2008-12-10 14:30:52 +0000
+++ b/sql/log_event_old.cc 2009-03-17 09:50:28 +0000
@@ -313,7 +313,7 @@ last_uniq_key(TABLE *table, uint keyno)
Returns TRUE if different.
*/
-static bool record_compare(TABLE *table)
+static bool record_compare(TABLE *table, const MY_BITMAP *cols_in_readset)
{
/*
Need to set the X bit and the filler bits in both records since
@@ -342,16 +342,25 @@ static bool record_compare(TABLE *table)
}
}
- if (table->s->blob_fields + table->s->varchar_fields == 0)
+ /*
+ We can compare the record straight away if:
+ i) there are no blob and varchar fields
+ ii) all columns were read or the slave SE provides partial reads
+ */
+ if ((table->s->blob_fields + table->s->varchar_fields == 0) &&
+ (bitmap_is_set_all(cols_in_readset) ||
+ (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ)))
{
result= cmp_record(table,record[1]);
goto record_compare_exit;
}
- /* Compare null bits */
- if (memcmp(table->null_flags,
- table->null_flags+table->s->rec_buff_length,
- table->s->null_bytes))
+ /* Compare null bits only if all fields were read or slave SE has partial reads */
+ if ((bitmap_is_set_all(cols_in_readset) ||
+ (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ)) &&
+ memcmp(table->null_flags,
+ table->null_flags+table->s->rec_buff_length,
+ table->s->null_bytes))
{
result= TRUE; // Diff in NULL value
goto record_compare_exit;
@@ -360,10 +369,14 @@ static bool record_compare(TABLE *table)
/* Compare updated fields */
for (Field **ptr=table->field ; *ptr ; ptr++)
{
- if ((*ptr)->cmp_binary_offset(table->s->rec_buff_length))
- {
- result= TRUE;
- goto record_compare_exit;
+ /* compare field if it is set */
+ if (bitmap_is_set(cols_in_readset, (*ptr)->field_index))
+ {
+ if ((*ptr)->cmp_binary_offset(table->s->rec_buff_length))
+ {
+ result= TRUE;
+ goto record_compare_exit;
+ }
}
}
@@ -761,7 +774,7 @@ static int find_and_fetch_row(TABLE *tab
DBUG_RETURN(0);
}
- while (record_compare(table))
+ while (record_compare(table, table->read_set))
{
int error;
@@ -837,7 +850,7 @@ static int find_and_fetch_row(TABLE *tab
DBUG_RETURN(error);
}
}
- while (restart_count < 2 && record_compare(table));
+ while (restart_count < 2 && record_compare(table, table->read_set));
/*
Have to restart the scan to be able to fetch the next row.
@@ -2387,7 +2400,7 @@ int Old_rows_log_event::find_row(const R
*/
DBUG_PRINT("info",("non-unique index, scanning it to find matching record"));
- while (record_compare(table))
+ while (record_compare(table, &m_cols))
{
/*
We need to set the null bytes to ensure that the filler bit
@@ -2463,7 +2476,7 @@ int Old_rows_log_event::find_row(const R
DBUG_RETURN(error);
}
}
- while (restart_count < 2 && record_compare(table));
+ while (restart_count < 2 && record_compare(table, &m_cols));
/*
Note: above record_compare will take into accout all record fields
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090317095028-px4c64k0402y270m.bundle
