Autoincrement in MariaDB Master-Master Replication

If you are running master-master replication in MariaDB, the auto-increment settings need to be different on both servers. Otherwise, you might run into similar issues with key collitions:

Error ‘Duplicate entry ‘1’ for key ‘PRIMARY” on query <SQL query details>

This can be solved by altering two MariaDB settings: auto_increment_offset and auto_increment_increment. The auto_increment_offset sets the initial value and auto_increment_increment configures by how much it should be incremented. These settings depend on the number of replicated servers. The goal is to prevent duplicate IDs on different servers.

With correct settings a evenly balancing write between all the masters IDs should form an uninterrupted sequence after replicating. But until the records are synchronized, or if write operations are not evenly balanced, there will be gaps. Your application needs to handle this gracefully. Or else you might need to adapt it to running on master-master setup first.