1

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.

0

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.

  • we have a DB in cloud plus all the same database locally as well. We want every branch to replicate its orders to the cloud. which makes a cloud to have multiple masters. Cloud data will also be replicated to branches. If I go with auto_increment* family, it still stuck in DELETE operation while an update from other nodes. Slave: Can't find record in 'table' Error_code: 1032 – vicky Nov 28 '18 at 7:09
  • i am still amazed why the whole concept is vague with this enterprise level utility when it goes to multi-master slaves or master-master. Why i have to go to percona or some other utility to deal with the nightmare. Anyway, your answer includes all my reading which i read from whole internet lol. Thanks to clear things up. And if you can, answer the above scenario or i can tell you in more detail. Thanks again – vicky Nov 28 '18 at 7:14
  • Another scenario i am facing is: i have three nodes Node1(Cloud) Node2(Branch) Node3(Branch). Cloud has multi-master N2,N3. Both N2,N3 are slaves of Cloud. Now i add row to cloud which replicates both branches. but when i delete that particular row at branch1, it deletes on cloud but doesnt on branch2 while there is no error in error.log – vicky Nov 28 '18 at 7:27
  • Just to be clear, multi-master slaves or master-master is NOT supported or recommended, and in general, it is not possible*(*This a very general statement) with relational, hard CP over-the-internet-distributed systems. There are specific use cases or hacks to make it work, but it is not the right tool. NoSQL, append-only, AP systems can work, but obvious restrictions will apply there in terms of consistency and features. – jynus Nov 28 '18 at 12:06
  • Rearchitecturing can make it work, but you have to use them right- distributed application processing/message queues, views for aggregated results, etc. Your setup is too complex for the tool you are using- check distributed cluster technologies instead or change your requirements to use only the subset of supported topologies (master-replica one way replication). – jynus Nov 28 '18 at 12:06

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.