MySQL Bugs: #69831: Replication fails on updating a MEMORY table with an index using btree

Bug #69831	Replication fails on updating a MEMORY table with an index using btree
Submitted:	24 Jul 2013 16:50	Modified:	10 Mar 2015 15:43
Reporter:	Elena Stepanova	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With the provided test case, replicating the update event fails with "Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND"

Reproducible on 5.5, but not on 5.6. Sorry if it's a duplicate, but I failed to find out whether it was fixed in 5.6 and left in 5.5 as is on purpose, or the behavior changed in 5.6 on its own. 

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

CREATE TABLE t1 (f1 VARCHAR(128), f2 VARCHAR(128), KEY (f2,f1) USING BTREE) ENGINE=MEMORY;
INSERT INTO t1 VALUES (NULL,'not'),('one',NULL),('two',NULL),('three','');

UPDATE t1 SET f2 = 'new';

--sync_slave_with_master

Hello Elena,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh

Testcase results..

Attachment: 69831.txt (text/plain), 14.09 KiB.

Posted by developer: Tiago Jorge

This bug happens because of everything that is described in the MTR test:
- MEMORY/HEAP engine
- Row based replication
- Binary Tree indexing
- NULL values in variable size fields

The difference between 5.5 and 5.6 resides on the treatment given to null
fields from VARCHAR or BLOB type when building the hash function in the
engine
itself in hp_hash.c in method hp_rb_pack_key.

For fields that are null and variable size, in 5.5 the case was not
contemplated and in 5.6 it is. I've tried the back patch and it works.

Why does this happen with RBR and not with SBR and only on the slave? Maybe
because of the direct access of RBR when applying data, not updating the
index, because if we do a sync_slave_with_master right after insert and query the server with a "SELECT *", the data is there.

We don't see a strong reason to backport the fix.