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?

Answer 1

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

1. One client writes to master1 using an odd primary key
2. Another client writes to master2 using an even primary key, before the first write was synced
3. 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.