Statement Based Replication still giving duplicate Entry Error

Question

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

1. made one slave.
2. configured two masters to it.

Now i am performing following scenario:

1. disconnect both masters from this slave. (TEST_TABLE having the last row with P_ID 30)

2. After discoonecting, add an entry (into TEST_TABLE) on both MASTERS. (primary key entry with auto_incremeent went to 31)

3. reconnect slave to master-1 (It replicate the data with P_ID 31 on it)

4. 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.

Answer 1

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.