0

I have just made a maria db master to master replication and after few days, I realized following the tutorial now the auto increment is by 2 instead of 1 as normal.

I am wondering, would it be save to set it back to 1? or it's the best to leave it at 2 instead of same id bumped to each other while creating rows. That seeing id always increment by 2 would consider a healthy db too?

Dora
  • 6,776
  • 14
  • 51
  • 99

1 Answers1

1

auto_increment_increment > 1 is needed for any multi-master setup. Without it (and auto_increment_offset), AUTO_INCREMENT values will collide and cause serious trouble.

AUTO_INCREMENT only guarantees uniqueness, nothing else. Do not depend on incr by 1, being sequential, no gaps, or anything else. If you have a need for some other requirement, let's discuss that in a new Question.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • got what you mean. don't really have requirements but this is my first time doing this. I just want to make sure that I am not doing it really wrong that I have no way turning back in the future. Just wondering, I know I have other nodes `innodb_autoinc_lock_mode=2` which is the reason for 2 increments. I am fine with it as long as it's not a trouble but if I only change the first node `innodb_autoinc_lock_mode=0` would that cause collide easily too? – Dora Jan 04 '18 at 01:59
  • @Dora - I think you must use `auto_increment_increment` when you are writing to multiple masters. I don't think the `lock_mode` plays a role in this -- multiple masters do _not_ coordinate with each other; replication is _asynchronous_. – Rick James Jan 04 '18 at 20:56
  • I was looking at the wrong setting, at first I thought it's the `lock_mode` is actually the offset setting I should be looking at – Dora Jan 04 '18 at 21:52
  • I do have another question though. I do know about the gap but I wonder if it's a normal behavior with the gap though? https://stackoverflow.com/questions/48102516/mariadb-is-this-kind-of-increment-offset-reaction-consider-normal – Dora Jan 04 '18 at 21:56
  • You should simply expect to be missing id=2 (or other number**s**) in some situations. – Rick James Jan 04 '18 at 22:13