Why mysql INSERT ... ON DUPLICATE KEY UPDATE can break RBR replication on a master / master configuration

Question

here is the problem:

2 MySQL 5.5 servers
Row based replications + master master
Writes are on both servers (both active)
autoinc trick (1 server odd, the other one even)

I have a table like

byUserDailyStatistics:

id (PK + AUTO INC)
date
idUser
metric1
metric2
UNIQUE(idUser, date)

All requests are

INSERT INTO byEmailDailyStatistics
(date, idUser, metric1, metric2)
VALUES (:date, :user:, 1, 1)
ON DUPLICATE KEY UPDATE
metric1 = metric1 + 1,
metric2 = metric2 +1

And sometimes, the replication breaks with message like

could not execute Write_rows event on table stats.byUserDailyStatistics; Duplicate entry '6447412-2016-01-06' for key 'UNIQUE', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.035580, end_log_pos 279798813

What could be the origin of this issue?

if you put full timestamp instead of date this may work. – Adarsh Gangadharan Jan 13 '16 at 14:37 — Adarsh Gangadharan, Jan 13 '16 at 14:37
I can't see why it should work with a timestamp – nemenems Jan 13 '16 at 14:44 — nemenems, Jan 13 '16 at 14:44

score 3 · Accepted Answer · answered Jan 13 '16 at 14:33

3

You are trying to write the same idUser, date pair to both your replicas at the same time.

One client writes to master1 using an odd primary key
Another client writes to master2 using an even primary key, before the first write was synced
The servers try to sync up with each other

In the last step the same pair exists on both server under different primary keys; different rows but the secondary unique key is the same.

answered Jan 13 '16 at 14:33

Emil Vikström

90,431
16
141
175

2

+1 for this. What you need to do to avoid this issue is setup a "floating ip" via a virtual IP (VIP) address that can be shared between both instances running mysql. And direct your app servers to connect only via this VIP, this will ensure that only one of the mysql instances is receiving the writes at any time. And because of replication, the write will sync up to the standby. And you can use a service like `keepalived` to automatically switch the VIP between primary and secondary if one of them happens to go offline. – Mike Purcell Jan 13 '16 at 14:40
1

@emil > So ON DUPLICATE KEY UPDATE is not safe with RBR + master / master – nemenems Jan 13 '16 at 14:43

score 2 · Answer 2 · answered Jun 27 '16 at 05:53

Let's make the statement more general: "Simultaneously acting on the same row in both Masters is unsafe." It is not only IODKU. Also, INSERTing rows with the same unique key (especially if they have different values in other columns) will cause errors.

Galera avoids the problem by checking with other nodes at COMMIT time.

NDB Cluster avoids the problem by implementing "eventual consistency".

Off-the-shelf Master-Master is full of problems; you have identified only one of them. Most of the problems can be avoided, as Mike points out, but only writing to one Master.

Why mysql INSERT ... ON DUPLICATE KEY UPDATE can break RBR replication on a master / master configuration

2 Answers2

Linked