I was using mariadb 5.5 before but now I have upgraded it and using percona xtradb cluster 5.6 I have 3 nodes cluster. I am only using single node for read and write.

I saw some increment gaps in ids so i had to turn off wsrep auto increment control.

I am still confused about innodb_autoinc_lock_mode which is 2 and bin log format is Row.

Is it safe to make it 1 to avoid gaps issue since we are only using single node for write and read?


  • It is unwise to worry about auto_increment issues. Are you threatening to run out of ids? Is your application bothered by the gaps.
    – Rick James
    Commented Dec 15, 2018 at 20:40
  • Actually, my application does not behave correctly when there is a gap in increments. I want to keep my application run smoothly as it was before on mariadb 5.5
    – Jaz
    Commented Dec 15, 2018 at 21:27
  • Are there any pitfalls if i change innodb_autoinc_lock_mode to 1 in pxc?
    – Jaz
    Commented Dec 15, 2018 at 21:35

1 Answer 1


Galera (hence PXC) prefers innodb_autoinc_lock_mode = 2. I don't know whether this is a "requirement" or just "advisory". Keep in mind that conflicts with other nodes are not caught until COMMIT, and the AI value has already been grabbed. Without 2, two different nodes might grab the same AI value, thereby adding to the conflict?

  • So using 2 will only be beneficial if i m writing on multiple nodes at the same time. And 1 will be ok if using single node?
    – Jaz
    Commented Dec 16, 2018 at 10:08
  • If you are writing to multiple nodes there will be a case where inserts happen on both nodes trying to insert the same id, they will fail certification and you'll have a gap. There are may way's a gap can occur, especially with a clustered system. Application changes is the only real certain way to fix this.
    – danblack
    Commented Dec 16, 2018 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.