MySQL Bugs: #47303: RBR: Replicating from master with PK into slave with KEY fails.

Bug #47303	RBR: Replicating from master with PK into slave with KEY fails.
Submitted:	14 Sep 2009 9:17	Modified:	24 Mar 2011 15:13
Reporter:	Luís Soares	Email Updates:
Status:	Closed
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)

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

Thank you for the report.

Verified as described.

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.

This issue is only visible to the user no 6.0-codebase.

Please re-triage for future release (this bug will be fixed as 
part of WL#5092, which didn't make it into Celosia merge window).

Documented fix in the 5.6.2 changelog -- see WL#5092 for docs info. Closed.