I am running Percona MySQL 5.5.39 on two masters with keepalived monitoring a VIP which will failover to the standby master if the primary master goes dark. While testing the fault tolerance of my setup in the lab I noticed that I am getting duplicate key entries on both masters.

Basically I run siege to emulate user load, for a duration of five minutes, within that time duration I shutdown the primary master, and after about 10 seconds for keepalived to detect the outtage, the VIP is switched the standby master, at which time everything is working as expected. Then after a minute or so, while siege is still running, I power on the primary master, and when online, it assumes the VIP.

It is during the swapping of the VIP that I think the duplicate entry issue is occurring. I followed this post explaining how to implement 'crash resilient' replication, but after implementing the suggested my.cnf changes and rerunning the user simulation, I ended up with the same broken replication.

There is also the ability for the standby master to increment it's indices by 2 instead of 1 to avoid collisions with the primary master, but I feel this is more of a hack than a solution.

Is there any better way to avoid primary key collisions in an HA environment?


This is not a hack. It is the intention of them: Use auto_increment_increment and auto_increment_offset



Your architecture is not right for asynchronous replication. I strongly recommend against runing a Master-Master standard replication in a fully automated system, as you will run into precisely the issue you are describing.

While you may not be writing to both servers at the same time, the fact that you are replicating in a non-syncronous way means that you have to have into account issues like pending writes being sent to the slave or pending writes being applied to it locally. An automatic failover is not easy or trivial with this conditions, and even less, the switch-back.

There are things that can help you do that:

  • Configuration like Binlog sync and InnoDB tables for controlling it can help, but not solve all replication problems
  • GTID replication prevents information being written twice, and resolves automatically binary log coordinate positioning
  • Galera clustering (and semysyncronous replication, depending on how it is used) eliminates the asynchronicity problem (and also implements its own GTIDs)
  • Tools like MHA allow for more graceful failovers, providing a set of checks and procedures on crash, specially on more than 2 nodes. It changes replication direction automatically for you.
  • I prefer load balancing tools over VIP for proper synchronous client fencing (but that is just me)
  • If you use non-transactional engines, forget about trying to do anything consistently. Remember that temporary tables may be created in MyISAM format, which is still a problem even if you use InnoDB.

The auto_increment_increment may solve insert collisions between nodes, but not updates between different nodes or inserts of the same queries in the same node twice due to binary log mismatches.

There is no right answer, but there are many wrong ones. I praise your methods (even if the final product is not right): test, test and test if you want something that fits your requirements.

  • I've actually seen single write master-master replication work in environments serving 200+ million hits per month. Perhaps it's not the ideal solution (still subjective imo), but at this juncture, I have no other alternative. As we are bootstrapping, we are lucky to even have this level of fault tolerance. +1 for the suggestions tho. Oct 21 '14 at 0:27
  • @MikePurcell The problem is not performance-wise, but the failover procedure. You can have a replication system that behaves both performant and consistent, but you need to "tame" it. I am not suggesting you to throw away your setup, but to complement it with the right tools.
    – jynus
    Oct 21 '14 at 9:09

If you feel it's a hack, try PXC (Percona XtraDB Cluster).

PXC uses Galera Cluster (by Codership), which is synchronous replication with InnoDB.

  • I'll take a look, thx. Dec 2 '14 at 0:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.