MySQL Bugs: #40045: Replication failure on RBR + no PK + UPDATE + integer key + char with no key

Bug #40045	Replication failure on RBR + no PK + UPDATE + integer key + char with no key
Submitted:	15 Oct 2008 12:40	Modified:	13 May 2009 13:07
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	6.0-rpl,	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
The following test case breaks replication with the following error:

081015 15:38:00 [ERROR] Slave SQL: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log master-bin.000001, end_log_pos 587, Error_code: 1032
081015 15:38:00 [ERROR] Slave: Can't find record in 't1' Error_code: 1032
081015 15:38:00 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 437

The following has been observed:
* If the column being updated is an integer, and not a char, failire is not observed;
* If the key is removed from the integer column, faliure is not observed.
* Partitioning is also affected;

How to repeat:
--source include/master-slave.inc

SET binlog_format = 'row';

CREATE TABLE t1 (
        i1 int,
        c1 char(1),
        key ( i1 )
);

INSERT IGNORE INTO t1 VALUES (1, 'a');
UPDATE t1 SET c1 = 'b' WHERE i1 = 1;

--save_master_pos
--connection slave
--sync_with_master

On Windows:

081015 12:20:29 [Note] Slave I/O thread: connected to master 'miguel@localhost:3600',replication started in log 'server60.000002'
at position 107
081015 12:21:38 [ERROR] Slave SQL: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1
32; handler error HA_ERR_END_OF_FILE; the event's master log server60.000002, end_log_pos 611, Error_code: 1032
081015 12:21:38 [Warning] Slave: Can't find record in 't1' Error_code: 1032
081015 12:21:38 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SL
VE START". We stopped at log 'server60.000002' position 461

Thank you for the bug report. Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69396

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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69914

2831 Luis Soares	2009-03-20
      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).
     @ mysql-test/extra/rpl_tests/rpl_row_record_find.test
        Shared part of the test battery while testing with different engines.
     @ mysql-test/suite/rpl/r/rpl_row_record_find.result
        Result file.
     @ mysql-test/suite/rpl/t/rpl_row_record_find.test
        Test case that checks for different combinations of engines and
        updates/deletes.
     @ 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.
     @ sql/records.cc
        Added changes for forcing marking the correct read_set when binlogging.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71511

2831 Luis Soares	2009-04-07
      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).
     @ mysql-test/extra/rpl_tests/rpl_row_record_find.test
        Shared part of the test battery while testing with different engines.
     @ mysql-test/suite/rpl/r/rpl_row_record_find_falcon.result
        
     @ mysql-test/suite/rpl/t/rpl_row_record_find_falcon.test
        Test case that checks for different combinations of engines and
        updates/deletes for Falcon engine.
     @ mysql-test/suite/rpl/t/rpl_row_record_find_innodb.test
        Test case that checks for different combinations of engines and
        updates/deletes for InnoDB engine.
     @ mysql-test/suite/rpl/t/rpl_row_record_find_myisam.test
        Test case that checks for different combinations of engines and
        updates/deletes for MyISAM engine. This is the master test file,
        as it provides description on the test and references other engines
        test files.
     @ 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.
     @ sql/sql_delete.cc
        Added calls to mark the BI read_set before reading the record and
        to restore the read_set after the interaction with the storage engine.
     @ sql/sql_update.cc
        Added calls to mark the BI read_set before reading the record and
        to restore the read_set after the interaction with the storage engine.
     @ sql/table.cc
        Added two methods: 
         mark_columns_needed_for_rbr_bi
         restore_columns_maps_after_rbr_bi
        
        These take care of setting and restore the BI read_set to bitmap
        needed for RBR.
     @ sql/table.h
        Declared the new mark and restore methods for RBR read_set setting.
        Added a container for storing temporary read_set when using RBR and
        marking takes place.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72184

2831 Luis Soares	2009-04-15
      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 in some cases
      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 moves the mark columns needed
      for update on the master so that read_set bitmap is always correct.
     @ mysql-test/extra/rpl_tests/rpl_row_record_find.test
        Shared part of the test battery while testing with different engines.
     @ mysql-test/suite/rpl/t/rpl_row_record_find_falcon.test
        Test case that checks for different combinations of engines and
        updates/deletes for Falcon engine.
     @ mysql-test/suite/rpl/t/rpl_row_record_find_innodb.test
        Test case that checks for different combinations of engines and
        updates/deletes for InnoDB engine.
     @ mysql-test/suite/rpl/t/rpl_row_record_find_myisam.test
        Test case that checks for different combinations of engines and
        updates/deletes for MyISAM engine. This is the master test file,
        as it provides description on the test and references other engines
        test files.
     @ 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.
     @ sql/sql_update.cc
        Moved the mark update columns map setting to before the 
        select->quick_check. This makes it conceptually more correct
        for the optimizer and also fixes the problem of read_set 
        getting reset to RBR incompatible values further down in the
        mysql_update execution.

Pushed to 6.0-rpl.

Pushed into 6.0.12-alpha (revid:alik@sun.com-20090513032549-rxa73jbxd1qv09xc) (version source revid:luis.soares@sun.com-20090429164859-k5938577uhp726nn) (merge vers: 6.0.12-alpha) (pib:6)

Documented bugfix in the 6.0.12 changelog as follows:

        Updating a table having no primary key, using an unindexed CHAR column
        as the key, caused row-based replication to fail.