I'm looking for some help with a MySql Master-Master configuration issue.
I'm working on a server config that has been set up by another employee who is now unavailable to offer any assistance in this matter. This is my first experience of a setup such as this and after doing quite a bit of research all I've managed to find is conflicting (or missing!) information. As such I apologise if my question is stupid!
My issue is this:
While the replication is working between servers I have an issue with the way that Auto_Increment values are behaving.
I'm content that the AI Offset and Increment are correctly set:
-- Master1 --
auto_increment_increment = 2
auto_increment_offset = 1
-- Master2 --
auto_increment_increment = 2
auto_increment_offset = 2
And this currently works correctly (Master 1 has Odd AI_Numbers and Master 2 has Even AI_Numbers). Unfortunately the software that has been using this system only connects to Master 2 if Master 1 fails (which hasn't happened since boot 2 years ago). This means that for the part of the system I've now been tasked with updating we haven't seen an even AI_Number... ever!
Using:
SHOW TABLE STATUS FROM `Database` LIKE 'TableName';
I can see that the AI Value is 7765. However this is true for both tables - so even though Master2 has never had an insert run on it directly when the next one is run it will generate 7766 as the AI_Value. My question therefore is this:
Is this expected or should the table in Master2 have a value of 1 (thus generating 2 when an insert is run on it)? If this is expected then is there a way to keep the replication config but only increase the AI_Value of Master2 when an insert is run on it?
Thanks for your help!
Chris
No, what you are seeing is correct.
When rows are replicated in from another server, the AUTO_INCREMENT value of the table server that wasn't directly written to will be bumped up so that it's never smaller than the largest value inserted so far.
The options are named a little bit counterintuitively, perhaps. Each server will only assign values no smaller than the current value, and also in the set of all integers beginning at 0, offset by offset, incremented by increment, for that server.
The value of the auto increment for the next insert on a given server is INT(current_value / increment) x increment + offset.
So if AUTO_INCREMENT is 9, and increment is 2, server A with offset 1 would do its next insert at int(9/2) x 2 + 1 so the next row would be 9... but server B with offset 2 given the same AUTO_INCREMENT of 9 uses int(9/2) x 2 + 2 = 10
That way, the auto increment values on both servers are assigned almost in exactly the same order, relative to time, as the inserts actually occurred, with the offset providing protection against collisions.
In a setup like you describe, it's normal to have mostly all-odd or all-even values, and the gaps remain unused, by design. The next row inserted, by whichever server is written to, will have an auto increment value higher than any previously inserted row.
Your "Master 2" will do the right thing when the first local insert occurs. The current AUTO_INCREMENT value for a given table is expected to be identical on all masters at any given time.
So, no, it can't be changed, and it shouldn't be changed. Inserting rows into those gaps would cause a mess of page splits and tablespace fragmentation, because when the rows were originally inserted, there were no gaps left in the storage -- you don't have unused space there, only unallocated numbers.