Hello,
I am in the process of testing multi-master replication (two seperate write servers) and both of the masters are slaves of each other. Auto increment controls through even odd restoration.
Master1 stores odd values while master2 stores even values. The only problem i found gaps in auto-increment column on master2. i.e. gaps are in even auto-increment.
On Master 1:
auto_increment_increment = 2
auto_increment_offset = 1
Create Table: CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO test (name) VALUES (‘odd1’),(‘odd2’),(‘odd3’);
select * from test;
±—±------+
| id | name |
±—±------+
| 1 | odd1 |
| 3 | odd2 |
| 5 | odd3 |
±—±------+
On Master 2:
auto_increment_increment = 2
auto_increment_offset = 2
- verified Master1 data replicated on Master2.
select * from test;
±—±------+
| id | name |
±—±------+
| 1 | odd1 |
| 3 | odd2 |
| 5 | odd3 |
±—±------+
- Inserting records on Master2.
INSERT INTO test (name) VALUES (‘even1’),(‘even2’),(‘even3’);
select * from test;
±—±------+
| id | name |
±—±------+
| 1 | odd1 |
| 3 | odd2 |
| 5 | odd3 |
| 6 | even1 |
| 8 | even2 |
| 10 | even3 |
±—±------+
On master2 id column stores data from 6 And it leaves the starting point (auto_increment_offset = 2). Data should be stores from 2,4,6 and so on.
Again after insertion on master1 it leaves odd id’s 7, 9 and started after last even id i.e. 10
On Master 1:
INSERT INTO test (name) VALUES (‘odd4’),(‘odd5’),(‘odd6’);
select * from test;
±—±------+
| id | name |
±—±------+
| 1 | odd1 |
| 3 | odd2 |
| 5 | odd3 |
| 6 | even1 |
| 8 | even2 |
| 10 | even3 |
| 11 | odd4 |
| 13 | odd5 |
| 15 | odd6 |
±—±------+
Can someone please help on this behaviour. Both of the server’s are on 5.1.55-log MySQL Community Server.
