Question :
I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence values
1
2
3
5
6
8
its missing 4 & 7 because its replicate latest auto increment value from one server to another server so its skip the numbers. Is there any workaround through which we can maintain this sequence
Server A:
auto-increment-increment=2
auto-increment-offset = 1
Server B:
auto-increment-increment=2
auto-increment-offset = 2
Answer :
Aside from @mustaccio’s link, in general, mysql will guarantee auto_increments will be unique aand monotonically increasing, but for several reasons it cannot guarantee there is no gaps.
One reason is distributelly generated ones like in your case, but it can also happen with 2 concurrent write threads on the same server- so your applications should always avoid assuming anything about concrete id growth. Use last_insert_id() and ignore gaps.