Mysql – Fix Duplicate Entry Error in Statement Based Replication

multi-mastermysql-8.0replication

New to the replication. I am configuring multi-master replication. I did the following steps:

made one slave.
configured two masters to it.

Now i am performing following scenario:

disconnect both masters from this slave. (TEST_TABLE having the last row with P_ID 30)
After discoonecting, add an entry (into TEST_TABLE) on both MASTERS. (primary key entry with auto_incremeent went to 31)
reconnect slave to master-1 (It replicate the data with P_ID 31 on it)
reconnect slave to master-2 (Here comes the Duplicate Entry ERROR)

I am unable to figure out what the purpose of replication is if i have to face this issue? or am i missing something too basic?

I have done this with both Row Based Replication as well as Statement Based Replication but same error. So what's the point of SBR? Only to log statement rather than row data? Or if it's replicating the statement, why issuing the same P_ID and not just running the command generally and auto_incrementing the id to 32.

Reference query can be: **INSERT INTO TEST_TABLE ('firstname','lastname') VALUES('f_name_entry','l_name_entry')**

If really i am getting all this wrong and this is the exact behavior replication do, then whats the solution to it?

Banging my head for whole week on this. Kindly ask if anything is missing and Sorry if i am unable to explain something. Thanks in advance to bear me.

Best Answer

Replication behaved exactly how it is supposed to be- you wrote the same row (as in, it had the same id on both masters, even if data was different), but a multi-master setup is not a tight cluster- it just redos what on the replicas the same things that happened on the master, up to the ids. It does not handle conflict resolution automatically.

I understand that you expected, with Statement Based Replication, to replicate the actual SQL (vs ROW, which replicates the previous old and new rows), so it gets a new, proper ID. While this is exactly what happens (what it is written to the binary log), it also stores internally the auto_increment id. This is because if later you run a DELETE or an UPDATE, or an INSERT... SELECT it would affect the wrong rows.

Generally, multi-source replication does not behave like a tight cluster- you want to use it just to consolidate data from different tables, or to share tables among several different replica sets. If you want automatic conflict resolution and being able to write to any server on a set, you want clustering technologies such as Galera or InnoDB Cluster which would make your writes either fail or work in a coordinated way when inserted the same data on several hosts.

Technically, it would be possible to making work the setup you want- there is this option called auto_increment_increment (combined with auto_increment_offset) that allows to "merge" inserts from several masters by, for example, creating only odd auto_increment values on one host and even on the other so you configure:

MASTER 1:
auto_increment_increment=2
auto_increment_offset=0

MASTER 2:
auto_increment_increment=2
auto_increment_offset=1

so the autoincs don't collide. There are other multiple options, like dropping or renaming the auto_increment column on the replica or creating triggers on it, both while using STATEMENT based replication so they generate the right code.

However, these 2 options are quite fragile (e.g. auto_increment_increment only works for inserts, not for updates or deletes), and tend to make replication break due to side effects- plus when it breaks it is difficult to fix it due to this custom setup, so I don't recommend it normally. I would suggest to avoid setting up replication with different masters and replicas unless it is in a very controlled environment (separate tables or dbs) and try clustering technologies instead. The actual decision would depend on what you really want to achieve in the end, as it is not fully clear on your question- so I am just explained why it didn't work and how people make it work normally.

Related Solutions

MySQL SBR vs RBR

There is nothing special about master/master replication in MySQL - it is bidirectional Master/Slave where each server is just a slave of the other server. Otherwise, it behaves exactly as a "regular" master/slave topology. There are some suggestions on how to safeguard this configuration from duplicate primary keys, such as setting an auto_increment_offset and auto_increment_increment, only write to one master (run active/passive), and making sure that log_slave_updates is set.

So, master/master is just master/slave in two directions. Your next question is about how replication works in general, for which you should read this: Replication Implementation Details.

In summary:

A query executes on the master
After successful execution, the either the query itself (SBR) or a binary representation of the row(s) affected (RBR) are written into the local binary log along with some metadata.
On the slave, there is an I/O Thread that runs that connects to the master and streams the master's binary logs and writes them to a local file called the relay log.
On the slave, the SQL thread reads the relay log and executes the statements contained (SBR)/applies the rows (RBR)

You can see both the I/O thread and the SQL thread active in SHOW SLAVE STATUS and also in the processlist.

In MySQL 5.5, the default is statement based replication. (See Replication Formats)

What you are referring to is Mixed Binary Logging Format which will ordinarily use SBR, but switch to RBR under a number of circumstances where SBR is not considered safe because there is potential for the statement to have different results on the master and the slave. Those are described clearly in the documentation.

Because SBR is the default, in order to use MIXED, you have to explicitly set it in my.cnf for a persistent change:

binlog_format = MIXED

or dynamically by executing

-- Set binary log format globally
mysql> SET GLOBAL binlog_format = MIXED;
-- set binary log format just for this session
mysql> SET SESSION binlog_format = MIXED;

If you want to set the binlog_format for a particular statement, you can by setting binlog_format in the session (as shown above) and then executing your statement. However, you have to have SUPER privilege in order to do that, which no application should have. Really, except for certain administrative tasks, there is almost no reason to do that and MIXED replication should be your choice.

MySQL statement-based replication and stored procedure

In short, no, your CALL is not replicated

You can take a look into the FAQ starting from B.4.22 and the Binary Logging of Stored Programs

B.4.23: Are stored procedures and functions created on a master server replicated to a slave?

Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.

B.4.24: How are actions that take place inside stored procedures and functions replicated?

MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.

Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

Best Answer

Related Solutions

MySQL SBR vs RBR

MySQL statement-based replication and stored procedure

Related Question