If you followed my previous post about high availability MySQL your application now has one less single point of failure. That's good, but as Graeme points out there's a possibility of data collision if the replication process fails.

If replication has stopped and a query inserts into db-01 while a second query inserts into db-02 then the value of any auto_increment columns will be the same. When you get replication running again this will cause a problem.

To avoid this situation we can use auto-increment-increment and auto-increment-offset. These variables affect the way that MySQL generates the next value in an auto-incrementing series.

# On db-01, in /etc/mysql/my.cnf
auto-increment-increment = 10
auto-increment-offset = 1
# On db-02, in /etc/mysql/my.cnf
auto-increment-increment = 10
auto-increment-offset = 2

Restart MySQL on both boxes and you should now be safe from this threat of data collision.

Love me!

If you've found this article useful I'd appreciate beer and / or recommendations at Working With Rails.

written by
Disagree? Found a typo? Got a question?
If you'd like to have a conversation about this post, email craig@barkingiguana.com. I don't bite.
You can verify that I've written this post by following the verification instructions:
curl -LO http://barkingiguana.com/2008/07/17/avoiding-auto_increment-collision-with-high-availability-mysql.html.orig
curl -LO http://barkingiguana.com/2008/07/17/avoiding-auto_increment-collision-with-high-availability-mysql.html.orig.asc
gpg --verify avoiding-auto_increment-collision-with-high-availability-mysql.html.orig{.asc,}