We use Master-Master replication to avoid situations in which writing to replica will get it out of sync with the real master and in case we wish to switch masters. However, there's a seemingly known problem with AUTOINCREMENT fields which causes a "Duplicate entry for key 'PRIMARY'" to which I haven't found good solutions thus far and so asking this question.
The situation: both masters get an INSERT statement into a table that has an AUTOINCREMENT field. When both of them INSERT this at the same time (yes, happens...) and thus with the same number, they both end up failing to then launch the statement from the other master and have the replication process stop.
Seems the issue is common enough, just that current solutions I've found seem inadequate.
First, here's the remedy to launch on both servers when this happens, assuming the specific row isn't important
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SELECT sleep(0.5); SHOW SLAVE STATUS\G\
This can be launched several times, as needed, to ensure all these errors move forward.
Another suggested solution from https://mariadb.com/kb/en/auto_increment/#replication is
To make master-master or Galera safe to use AUTO_INCREMENT one should use the system variables auto_increment_increment and auto_increment_offset to generate unique values for each server.
Problem is that this setting while create "holes" in all tables, not using sequential IDs.
Are there better solutions for such situations such as an ON DUPLICATE do something?
Looking into https://mariadb.com/kb/en/binary-log-formats/ I'm also curious as to why MIXED mode doesn't consider these INSERT statements to be unsafe even though they obviously are...
To answer your question, you can't use
INSERT ON DUPLICATE...because replication is delayed at least by the time it takes to commit and write to the binary log. You can always get a "split-brain" effect if both instances are writeable.The way I solve this without using auto-increment-increment is to make one instance writeable, and the other one read-only. If you need to switch which is the master, set both to read-only briefly, let replication completely flow through so they are in sync, and then set the second instance to read-write.
Update to respond to one more point you added to your question above:
There is no mode or binlog format (e.g. MIXED) that can detect when a transaction is doing an INSERT on the other instance. The only way an instance knows what ran on its source is that it reads the binlog events, but the other instance does not write to the binlog until it commits its transaction.
The only way you could ensure no conflicts in a distributed system is to have some kind of global locking that allows one instance or the other to start a transaction. But that's not how MySQL replication works.