Bug #47303 RBR: Replicating from master with PK into slave with KEY fails.
Submitted: 14 Sep 11:17 Modified: 20 Oct 18:59
Reporter: Luís Soares
Status: In progress
Category:Server: RBR Severity:S3 (Non-critical)
Version:6.0 codebase OS:Any
Assigned to: Luís Soares Target Version:
Tags: INDEX, RBR, regression
Triage: Triaged: D2 (Serious)

[14 Sep 11:17] Luís Soares 
Description:
When the master and the slave contain different index structures,
in some cases, replication may break unexpectedly. Take the
following example:

 master:
  CREATE TABLE t (c1 int, c2 char(1), primary key(c1));

 slave:
  CREATE TABLE t (c1 int, c2 char(1), key(c2));

 If one updates c1 (eg, where c1=1) then the BI only contains the
 value for c1:

 mysqlbinlog -v .../master-bin.000001
 (...)
 ### UPDATE test.t
 ### WHERE
 ###   @1=1
 ### SET
 ###   @1=7
 (...)

 It seems that when the slave thread tries to find the record, it
 will not use the PK (because it does not have it). Instead will
 use the key (which has no value set on BI), thus failing with
 something similar to:

Last_SQL_Error	Could not execute Update_rows event on table test.t; Can't find record in
't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log
master-bin.000001, end_log_pos 432

This was found in mysql 5.4.

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

-- connection master
SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int, c2 char(1), primary key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int, c2 char(1), key(c2));
SET SQL_LOG_BIN=1;

-- connection master
INSERT INTO t VALUES (1, '2');

-- sync_slave_with_master

-- connection master
UPDATE t SET c1 = '7';

-- sync_slave_with_master
-- connection master

DROP TABLE t;

-- sync_slave_with_master

exit;

Suggested fix:
n/a
[14 Sep 11:59] Sveta Smirnova 
Thank you for the report.

Verified as described.
[1 Oct 13:45] Luís Soares 
Using the test case in the howto repeat section, I checked
against the following trees:

mysql-5.1-bugteam            => Test SUCCEEDS
mysql-next-mr                => Test SUCCEEDS
mysql-6.0-codebase-bugfixing => Test FAILS

The regression may be caused BUG#33055.

BUG#33055 - introduces the feature for only transferring fields
            signaled in the master as used, in BI and AI. If the
            slave has different index structure, it may be that
            while using the index to find the record, the BI
            won't have values set for the index fields used.