BUG#49481: RBR: MyISAM and bit fields may cause slave to stop on delete:

cant find record

Some engines return data for the record. Despite the fact that
the null bit is set for some fields, their old value may still in
the row. This can happen when unpacking an AI from the binlog on
top of a previous record in which a field is set to NULL, which
previously contained a value. Ultimately, this may cause the
comparison of records to fail when the slave is doing an index or
range scan.

We fix this by deploying a call to reset() for each field that is
set to null while unpacking a row from the binary log.
Furthermore, we also add mixed mode test case to cover the
scenario where updating and setting a field to null through a
Query event and later searching it through a rows event will
succeed.

Finally, we also change the reset() method, from Field_bit class,
so that it takes into account bits stored among the null bits and
not only the ones stored in the record.

mysql-test/suite/rpl/t/rpl_set_null_innodb.test:
  InnoDB test.
mysql-test/suite/rpl/t/rpl_set_null_myisam.test:
  MyISAM test.
mysql-test/suite/rpl_ndb/t/rpl_ndb_set_null.test:
  NDB test.
sql/field.h:
  Changed reset so that it also clears the bits
  among the null_bits for the Field_bit class.
sql/rpl_record.cc:
  Resetting field after setting it to null when unpacking
  row.

6 files changed, 122 insertions, 0 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_set_null_innodb.result b/mysql-test/suite/rpl/r/rpl_set_null_innodb.result
new file mode 100644
index 00000000000..41600a5fe1b
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_set_null_innodb.result
@@ -0,0 +1,35 @@
+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;
+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;
+CREATE TABLE t1 (c1 BIT, c2 INT) Engine=InnoDB;
+INSERT INTO `t1` VALUES ( 1, 1 );
+UPDATE t1 SET c1=NULL where c2=1;
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 WHERE c2=1 LIMIT 1;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
+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;
+CREATE TABLE t1 (c1 CHAR) Engine=InnoDB;
+INSERT INTO t1 ( c1 ) VALUES ( 'w' ) ;
+SELECT * FROM t1;
+c1
+w
+UPDATE t1 SET c1=NULL WHERE c1='w';
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 LIMIT 2;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
diff --git a/mysql-test/suite/rpl/r/rpl_set_null_myisam.result b/mysql-test/suite/rpl/r/rpl_set_null_myisam.result
new file mode 100644
index 00000000000..cbd7010664a
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_set_null_myisam.result
@@ -0,0 +1,35 @@
+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;
+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;
+CREATE TABLE t1 (c1 BIT, c2 INT) Engine=MyISAM;
+INSERT INTO `t1` VALUES ( 1, 1 );
+UPDATE t1 SET c1=NULL where c2=1;
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 WHERE c2=1 LIMIT 1;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
+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;
+CREATE TABLE t1 (c1 CHAR) Engine=MyISAM;
+INSERT INTO t1 ( c1 ) VALUES ( 'w' ) ;
+SELECT * FROM t1;
+c1
+w
+UPDATE t1 SET c1=NULL WHERE c1='w';
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 LIMIT 2;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
diff --git a/mysql-test/suite/rpl/t/rpl_set_null_innodb.test b/mysql-test/suite/rpl/t/rpl_set_null_innodb.test
new file mode 100644
index 00000000000..dba79b78fa1
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_set_null_innodb.test
@@ -0,0 +1,6 @@
+-- source include/have_binlog_format_mixed_or_row.inc
+-- source include/master-slave.inc
+-- source include/have_innodb.inc
+
+-- let $engine= InnoDB
+-- source extra/rpl_tests/rpl_set_null.test
diff --git a/mysql-test/suite/rpl/t/rpl_set_null_myisam.test b/mysql-test/suite/rpl/t/rpl_set_null_myisam.test
new file mode 100644
index 00000000000..7b433071553
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_set_null_myisam.test
@@ -0,0 +1,5 @@
+-- source include/have_binlog_format_mixed_or_row.inc
+-- source include/master-slave.inc
+
+-- let $engine= MyISAM
+-- source extra/rpl_tests/rpl_set_null.test
diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_set_null.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_set_null.result
new file mode 100644
index 00000000000..473cd63169c
--- /dev/null
+++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_set_null.result
@@ -0,0 +1,35 @@
+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;
+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;
+CREATE TABLE t1 (c1 BIT, c2 INT) Engine=NDB;
+INSERT INTO `t1` VALUES ( 1, 1 );
+UPDATE t1 SET c1=NULL where c2=1;
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 WHERE c2=1 LIMIT 1;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
+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;
+CREATE TABLE t1 (c1 CHAR) Engine=NDB;
+INSERT INTO t1 ( c1 ) VALUES ( 'w' ) ;
+SELECT * FROM t1;
+c1
+w
+UPDATE t1 SET c1=NULL WHERE c1='w';
+Comparing tables master:test.t1 and slave:test.t1
+DELETE FROM t1 LIMIT 2;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
diff --git a/mysql-test/suite/rpl_ndb/t/rpl_ndb_set_null.test b/mysql-test/suite/rpl_ndb/t/rpl_ndb_set_null.test
new file mode 100644
index 00000000000..454807d9591
--- /dev/null
+++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_set_null.test
@@ -0,0 +1,6 @@
+-- source include/have_ndb.inc
+-- source include/have_binlog_format_mixed_or_row.inc
+-- source include/ndb_master-slave.inc
+
+-- let $engine= NDB
+-- source extra/rpl_tests/rpl_set_null.test