6

We use Master-Master replication to avoid situations in which writing to replica will get it out of sync with the real master and in case we wish to switch masters. However, there's a seemingly known problem with AUTOINCREMENT fields which causes a "Duplicate entry for key 'PRIMARY'" to which I haven't found good solutions thus far and so asking this question.

The situation: both masters get an INSERT statement into a table that has an AUTOINCREMENT field. When both of them INSERT this at the same time (yes, happens...) and thus with the same number, they both end up failing to then launch the statement from the other master and have the replication process stop.

Seems the issue is common enough, just that current solutions I've found seem inadequate.

First, here's the remedy to launch on both servers when this happens, assuming the specific row isn't important

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SELECT sleep(0.5); SHOW SLAVE STATUS\G\

This can be launched several times, as needed, to ensure all these errors move forward.

Another suggested solution from https://mariadb.com/kb/en/auto_increment/#replication is

To make master-master or Galera safe to use AUTO_INCREMENT one should use the system variables auto_increment_increment and auto_increment_offset to generate unique values for each server.

Problem is that this setting while create "holes" in all tables, not using sequential IDs.

Are there better solutions for such situations such as an ON DUPLICATE do something?

Looking into https://mariadb.com/kb/en/binary-log-formats/ I'm also curious as to why MIXED mode doesn't consider these INSERT statements to be unsafe even though they obviously are...

Collector
  • 2,034
  • 4
  • 22
  • 39
  • You shouldn't care about gaps in the sequence of auto-increment id's, because gaps will also occur if you delete rows, or rollback transactions, or even spontaneously (InnoDB is not guaranteed to allocate consecutive values always). – Bill Karwin Sep 05 '22 at 06:13
  • There's a difference between sporadic gaps for deleted items and having the next half of IDs unused. Also, halving the amount of available IDs is a consideration – Collector Sep 05 '22 at 06:15
  • 1
    If you are worried about running out of integers, then use BIGINT. You will not run out of those values in your lifetime! – Bill Karwin Sep 05 '22 at 06:16
  • Increasing a field size for a relatively rare problem isn't something I'm interested in doing. This isn't a small DB where increasing column size will have little to no effect. – Collector Sep 05 '22 at 06:20
  • In my experience, larger databases are _more_ likely to need to use BIGINT primary keys. I've supported some MySQL databases that were over 4TB. – Bill Karwin Sep 05 '22 at 07:40
  • Definitely, just not in every table. Using the auto_increment_increment solution would do it on all tables all the time whereas this is more a specific scenario to be handled. Perhaps we'll resort to a situation-specific solution, if there's no good general solution coming up here. – Collector Sep 05 '22 at 10:57
  • @Collector What is the current definition of your auto_increment column and your primary index? – Georg Richter Sep 08 '22 at 12:57

1 Answers1

0

To answer your question, you can't use INSERT ON DUPLICATE... because replication is delayed at least by the time it takes to commit and write to the binary log. You can always get a "split-brain" effect if both instances are writeable.

The way I solve this without using auto-increment-increment is to make one instance writeable, and the other one read-only. If you need to switch which is the master, set both to read-only briefly, let replication completely flow through so they are in sync, and then set the second instance to read-write.

Update to respond to one more point you added to your question above:

There is no mode or binlog format (e.g. MIXED) that can detect when a transaction is doing an INSERT on the other instance. The only way an instance knows what ran on its source is that it reads the binlog events, but the other instance does not write to the binlog until it commits its transaction.

The only way you could ensure no conflicts in a distributed system is to have some kind of global locking that allows one instance or the other to start a transaction. But that's not how MySQL replication works.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for answering. Well, I enjoy having master-master as sometimes it makes sense to run queries on the other machine. What you're describing is essentially masking it into a master-replica situation, which is why I'm not accepting the answer. – Collector Sep 05 '22 at 06:19
  • To be clear, I am talking about master-master. I.e. they are both configured as replicas of the other. But one of the masters is set read-only. – Bill Karwin Sep 05 '22 at 07:31
  • Understood, but master-master when one is read-only all the time is kind of master-replica, right? :) – Collector Sep 05 '22 at 12:49
  • It's different because if you want to switch the primary master, you don't have to do any reconfiguration of replication. If it were simply master-replica, then to switch you'd have to do several steps to set up replication in the opposite direction. – Bill Karwin Sep 05 '22 at 16:02