MySQL Master-Master replication & Auto-Increment column issue

Question

I am doing some testing with master-master replication and I ran on some weird problem, I will try and describe the procedure I followed so that someone can perhaps reproduce the issue.

I set up replication on 2 VMs and in the configuration file for each one:

-- Master1 -- 
auto_increment_increment = 2
auto_increment_offset = 1

-- Master2 -- 
auto_increment_increment = 2
auto_increment_offset = 2

These settings should result in an arithmetic progression for the auto-increment columns:

- Master1: 1,3,5,7,9,11,13  ...
- Master2: 2,4,6,8,10,12,14 ...

Master1 gets the odd numbers and Master2 gets the even ones. I then create a test database and I add a table with the following definition:

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c1` varchar(50) DEFAULT NULL,
 `d1` date DEFAULT '1970-01-01',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Of course the database is created on both servers. Afterwards, I execute

START SLAVE;

on both servers so that replication begins. In order to generate data, I use the following procedure:

• A single record must be inserted for the process to take off

  INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) ;

• Then you use INSERT - SELECT from the same table which will start inserting at a rate of 2ⁿ, n being the times you execute the query:

  INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1;

Tip: I find this method described here also very convenient for generating random data for your tables.

So, when I start executing these queries simultaneously on both servers and this always results in replication duplicate key error for the auto-increment column. If anyone has any ideas, I would be thankful!

PS: Of course this kind of queries seldom happen in production applications, but I believe it still proves a point.

Answer 1

NOTE: I did find the answer and I've put it at top. Below the answer is some other rantings (my initial answer) that still could hold some value to explain this.

Since your query doubles the number of rows your statement INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1; can insert a different number of rows on server 1 and the server 2. All statements that uses a auto increment column sends its INSERT_ID along with the replication and that value will not be true on server 2 if the a statement has been run there as well.

Lets look at an example. I will do stop slave to simulate a long running query or a bad network.

1. Create two databases and set up master-master replication
2. Create the table and insert the inital row
3. Stop replication on server 2
4. Run the statement that doubles the number of rows a couple of time on server 1. 2 is enough but I did 3.

5. Check show binlog events (warning, don't do this on an old database, it will take forever). This is what I see.

   Query | BEGIN
   Intvar | INSERT_ID=3
   Query | use test; INSERT INTO t1(c1,d1) SELECT ...
   Query | COMMIT
   Query | BEGIN
   Intvar | INSERT_ID=5
   Query | use test; INSERT INTO t1(c1,d1) SELECT ...
   Query | COMMIT
   Query | BEGIN
   Intvar | INSERT_ID=9
   Query | use test; INSERT INTO t1(c1,d1) SELECT ... Query | COMMIT

6. Note that for each time I run the duplication INSERT_ID changes accordingly. On the second insert it is 5 meaning that the first insert inserted 1 row (remember, the increment is 2). On the third insert INSERT_ID is 9 meaning that the second insert inserted 2 rows. This all makes sense. Let's continue

7. On server 2 do the duplication once, don't start replication yet. Doing a select * from t1 now correctly shows two rows, with id 1 and 2.

8. Now start the slave again and run a SHOW SLAVE STATUS \G. It has stopped with a duplicate id of 5. Selecting all values from t1 again shows four rows. The first was the inital one. The second is what we did on server 2 and the two last once with id 3 and 5 was from that first statement on server 1, the one that added just 1 row.

9. The next part of replication is this

   Query | BEGIN
   Intvar | INSERT_ID=5
   Query | use test; INSERT INTO t1(c1,d1) SELECT ...
   Query | COMMIT

10. On server 1 INSERT_ID was 5 when this happend and that is what replication is going to use, however, on server 2 we already have id 5, cause we duplicated the rows one extra time before getting this. So replication breaks.

Bottom line is this. When doing master-master replication each statement needs to affect the database in the same way. Adding or deleting the same number of rows among other things.

That said, there is an easy fix for this particular case if you need to do things like this.

1. Add a server_id to the data and create a table like this

   CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, server_id int(1) DEFAULT NULL, c1 varchar(50) DEFAULT NULL, d1 date DEFAULT '1970-01-01', PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

2. Prepare two rows, one for each server id

   INSERT INTO t1(server_id, c1,d1) SELECT 1, LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) ; INSERT INTO t1(server_id, c1,d1) SELECT 2, LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) ;

3. For each duplication just take the rows created on your server into account.

   INSERT INTO t1(server_id, c1,d1) SELECT server_id, LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1 where server_id = 1;

BELOW IS THE ORIGNAL ANSWER

First of all your wrong when you assume that you will have two sets of ids ranged 1, 3, 5, .. and 2, 4, 6 ... Regardless of what server the statement is run on the value if the Auto_increment is always max(id)+1. So if you do two inserts on server 1 it will get the odd values of 1 and 3. If you then do one insert on server 2 it will get the even value of 4 (4 is the next number higher than 3 that satisfies auto_increment_offset + N × auto_increment_increment).

You can see the Auto_increment value by running show table status;

Second, each of your inserts after the first doubles the number of rows in the table, quickly making it a very slow operation and I wouldn't be surprised if this has something to do with that each query is so slow.

That said, this is how I tested this (and got the same surprising results).

1. I created a new empty setup with two servers and master master make_replication_sandbox --master_master mysql-5.5.17-osx10.6-x86_64.tar.gz. They are both started and so are there slaves. They are configured automatically as you did your setup.
2. I then created the table and inserted the first row as per your question. Auto_increment is now 2 on both servers and there is one row in the table
3. I'm then running while (true) do ./n1 test -e "INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1;"; done; against both servers at the same time (./n2 on the other one).

And I have a theory.

Say you have 1000 rows in the table and you initiate this same duplication on both servers at the same time. In a perfect word you would get 4000 rows afterwards on both servers and they would all be the same.

But what happens is that you duplicate the rows on each database so that server 1 sees 2000 rows and server 2000 rows, but only the first 1000 is the same, the other 1000 has been generated differently on the two servers.

Then replication kicks in. This is statement based replication so the same statement runs meaning that on both servers the rows get duplicated again to 4000 and that is the correct count but still only 1000 of them are the same, the other 3000 will differ.

As long as each server runs the same number of queries this might work (no duplicates, but data differs) but if one server manages to run two queries before replication caches up then you get a statement in replication that on server 2 added 1000 rows (if there was 1000 rows before) but on server 1 adds 4000 rows (cause server 1 has already doubled 1000 twice). If the next statement added another 2000 rows on server 2 and the binary log contains something like "first auto increment used on server" then you would get a collision.

I know this is abstract and weird, and even harder to put into writing than it was to think about it :)

I hope this helps, and I hope this is the problem... Master-master is hard, and this is definitely one of the things I wouldn't do in master-master.