#At file:///home/lsoares/Workspace/bzr/work/bugfixing/53893/mysql-5.1-bugteam/ based on revid:sven.sandberg@stripped
3393 Luis Soares 2010-05-21
BUG#53893: RBR: nullable unique key can lead to out-of-sync slave
When using Unique Keys with nullable parts in RBR, the slave can
choose the wrong row to update. This happens because a table with
an unique key containing nullable parts is allowed to have
multiple rows with NULL value for that same unique key fields.
We fix this at the slave by extending the checks before assuming
that the row found through an unique index is is the correct
one. This means that when a record is fetched from the storage
engine and a key that is not primary (say, KEY) is used, we check
before doing an index scan:
- key is unique and has no nullable parts
- if it has, we check if any field in before image is null
- if there is one field that is null proceed to index scan
- if not return row.
A side change: renamed the existing test case file and added a
test case covering the changes in this patch.
renamed:
mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result => mysql-test/suite/rpl/r/rpl_row_find_row.result
mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test => mysql-test/suite/rpl/t/rpl_row_find_row.test
modified:
sql/log_event.cc
sql/log_event_old.cc
mysql-test/suite/rpl/r/rpl_row_find_row.result
mysql-test/suite/rpl/t/rpl_row_find_row.test
=== renamed file 'mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result' => 'mysql-test/suite/rpl/r/rpl_row_find_row.result'
--- a/mysql-test/suite/rpl/r/rpl_row_disabled_slave_key.result 2009-09-27 21:02:47 +0000
+++ b/mysql-test/suite/rpl/r/rpl_row_find_row.result 2010-05-21 18:32:10 +0000
@@ -24,3 +24,15 @@ INSERT INTO t VALUES (1,2,4);
INSERT INTO t VALUES (4,3,4);
DELETE FROM t;
DROP TABLE t;
+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 INT NOT NULL, c2 INT NOT NULL, c3 INT, UNIQUE KEY(c1,c3), KEY(c2));
+INSERT INTO t1(c1,c2) VALUES(1,1);
+INSERT INTO t1(c1,c2) VALUES(1,2);
+UPDATE t1 SET c1=1000 WHERE c2=2;
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
=== renamed file 'mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test' => 'mysql-test/suite/rpl/t/rpl_row_find_row.test'
--- a/mysql-test/suite/rpl/t/rpl_row_disabled_slave_key.test 2009-09-27 21:02:47 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_find_row.test 2010-05-21 18:32:10 +0000
@@ -71,3 +71,34 @@ DELETE FROM t;
DROP TABLE t;
-- sync_slave_with_master
+
+#
+# BUG#53893: RBR: nullable unique key can lead to out-of-sync slave
+#
+
+#
+# We insert two rows. Both with part of UNIQUE KEY set to null.
+# Then we update the last row inserted. On master the correct
+# row is updated. On the slave the wrong row would be updated
+# because the engine would look it up by the NULL Unique KEY.
+# As a consquence, the wrong row would be updated.
+#
+
+-- connection master
+-- source include/master-slave-reset.inc
+-- connection master
+
+CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, c3 INT, UNIQUE KEY(c1,c3), KEY(c2));
+INSERT INTO t1(c1,c2) VALUES(1,1);
+INSERT INTO t1(c1,c2) VALUES(1,2);
+UPDATE t1 SET c1=1000 WHERE c2=2;
+-- sync_slave_with_master
+
+-- let $diff_table_1=master:test.t1
+-- let $diff_table_2=slave:test.t1
+-- source include/diff_tables.inc
+
+-- connection master
+DROP TABLE t1;
+-- sync_slave_with_master
+
=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc 2010-05-19 23:50:42 +0000
+++ b/sql/log_event.cc 2010-05-21 18:32:10 +0000
@@ -9015,8 +9015,38 @@ int Rows_log_event::find_row(const Relay
*/
if (table->key_info->flags & HA_NOSAME)
{
- table->file->ha_index_end();
- goto ok;
+ /* Unique does not have non nullable part */
+ if (!(table->key_info->flags & (HA_NULL_PART_KEY)))
+ {
+ table->file->ha_index_end();
+ goto ok;
+ }
+ else
+ {
+ KEY *keyinfo= table->key_info;
+ /*
+ Unique has nullable part. We need to check if there is any field in the
+ BI image that is null and part of UNNI.
+ */
+ bool null_found= FALSE;
+
+ for (uint i=0, fieldnr= keyinfo->key_part[i].fieldnr - 1 ;
+ (i < keyinfo->key_parts) && !null_found ;
+ i++, fieldnr= keyinfo->key_part[i].fieldnr - 1)
+ {
+ Field **f= table->field+fieldnr;
+ if ((*f)->is_null())
+ null_found= TRUE;
+ }
+
+ if (!null_found)
+ {
+ table->file->ha_index_end();
+ goto ok;
+ }
+
+ /* else fall through to index scan */
+ }
}
/*
=== modified file 'sql/log_event_old.cc'
--- a/sql/log_event_old.cc 2010-04-21 12:47:55 +0000
+++ b/sql/log_event_old.cc 2010-05-21 18:32:10 +0000
@@ -2428,8 +2428,38 @@ int Old_rows_log_event::find_row(const R
*/
if (table->key_info->flags & HA_NOSAME)
{
- table->file->ha_index_end();
- DBUG_RETURN(0);
+ /* Unique does not have non nullable part */
+ if (!(table->key_info->flags & (HA_NULL_PART_KEY)))
+ {
+ table->file->ha_index_end();
+ DBUG_RETURN(0);
+ }
+ else
+ {
+ KEY *keyinfo= table->key_info;
+ /*
+ Unique has nullable part. We need to check if there is any field in the
+ BI image that is null and part of UNNI.
+ */
+ bool null_found= FALSE;
+
+ for (uint i=0, fieldnr= keyinfo->key_part[i].fieldnr - 1 ;
+ (i < keyinfo->key_parts) && !null_found ;
+ i++, fieldnr= keyinfo->key_part[i].fieldnr - 1)
+ {
+ Field **f= table->field+fieldnr;
+ if ((*f)->is_null())
+ null_found= TRUE;
+ }
+
+ if (!null_found)
+ {
+ table->file->ha_index_end();
+ DBUG_RETURN(0);
+ }
+
+ /* else fall through to index scan */
+ }
}
/*
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20100521183210-5v8yhy34d751e60i.bundle
