List:Commits« Previous MessageNext Message »
From:Luis Soares Date:May 21 2010 6:32pm
Subject:bzr commit into mysql-5.1-bugteam branch (luis.soares:3393) Bug#53893
View as plain text  
#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
Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:3393) Bug#53893Luis Soares21 May
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:3393)Bug#53893Daogang Qu1 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:3393)Bug#53893Alfranio Correia2 Jun
    • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:3393)Bug#53893Luís Soares2 Jun
      • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:3393)Bug#53893Alfranio Correia3 Jun