Not the answer you need?
Register and ask your own question!

Duplicate entry after change auto_increment_increment from 2 to1

baoxiaobaoxiao ContributorCurrent User Role Beginner
We have a master-master replication during database migration, now we want to change back to master-slave. After change auto_increment_incremen and auto_increment_offset to 1 on both database, the application sometimes got below error. Application only connect to the master for write.

Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '72141167'

If we change auto_increment_incremen back to 2 then error disappeared.

MySQL version:
5.7.19-17,Percona Server (GPL), Release 17, Revision e19a6b7b73f


Thanks

Comments

  • TarasProsvirovTarasProsvirov Entrant Current User Role Beginner
    Hi baoxiao, I'm having the same issue. Have you found a solution?
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello there, first of all sorry we didn't catch the original question... but here are two possible scenarios for the error.
    1. You didn't stop writes on the slave. In this case you'll still need to keep auto_increment_increment at the higher value.
    2. You stopped writes on the slave. But the master has values, say: 1--3-5-7 and the slave has: 10-12-14. Occasionally, an app will try to reuse those 10-12-14 values. The workaround in this case is to make sure that maximum numbers in the auto-incremented fields are greater on master than on slave
    Hopefully this will sort out the issue for you.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.