
Let's say the following error is being returned.
Error 'Duplicate entry '123456' for key 'PRIMARY'
This typically occurs when you have two MariaDB or mySQL databases configured in a Master-Master replication, you attempt to INSERT a new record into a table, and one of the columns in the table is configured as an auto_increment PRIMARY KEY.
In this example, the "id" column is set as an auto_increment PRIMARY KEY.
~]$ mysql -e "describe freekb.table001"
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name varchar(200) YES NULL
When the record is inserted into database "a", the record will have an auto_increment id, such as 123456. Attempting to insert the record into database "b" with the same id returns "Duplicate entry for key PRIMARY".
On the first master, define the following directives in the main configuration file (e.g. /etc/mysql/mariadb.conf.d/50-server.cnf on Docker) below the mysqld header.
[mysqld]
auto_increment_increment=2
auto_increment_offset=2
On the second master, define the following directives in the main configuration file.
[mysqld]
auto_increment_increment=2
auto_increment_offset=1
Did you find this article helpful?
If so, consider buying me a coffee over at