MySQL Bugs: #71077: Memcached replication fail

Bug #71077	Memcached replication fail
Submitted:	4 Dec 2013 10:08	Modified:	10 Dec 2013 17:17
Reporter:	Michael Ivanov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Memcached	Severity:	S2 (Serious)
Version:	5.6.14	OS:	Linux (CentOS 6)
Assigned to:		CPU Architecture:	Any
Tags:	memcached replication

Description:
When you run delete query with where condition using index range scan on master server, the memcached is able to insert deleted rows even though the actual delete query is not finished. This causes insert query to be written to binlog before the  delete query which in turn causes replication fail with duplicate key error on slave.

How to repeat:
Run on a memcached container table with a lot of rows:

delete from memcached_container where c5<unix_timestamp();

During the query execution add value though memcached interface which should already be deleted.

Suggested fix:
Lock the memcached write operation on rows which are locked by InnoDB?

Alright. I was wrong in the initial problem description. After more detailed investigation it seems that delete query on slave found in its relay logs was not actually executed. This was fixed by changing binlog format from mixed to row.

Thank you for the report.

This is not a bug according to the last comment.

I must disagree with you. The delete query was not executed on slave server though it was inside its relay log. No replication error was reported etc. The slave just continued to execute the following queries from the relay log. I did reproduced this bug with replication type MIXED but with replication type ROW it worked as expected.

Thank you for the feedback.

Well, this DELETE statement was written into binary log as a statement while a row, updated via memcached API was written as a row event. Such a failure which you describe can happen, because executing a statement can take time. Safety rule is to use binary log format row if you use Memcahed API.

But I agree this is confusing and http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-replication.html does not mention this side effect, therefore I verify this as a feature request: don't allow to use both innodb_api_enable_binlog and binlog_format=statement|mixed