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
Hi baoxiao, I’m having the same issue. Have you found a solution?
Hello there, first of all sorry we didn’t catch the original question… but here are two possible scenarios for the error. [LIST=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.
[]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
[/LIST] Hopefully this will sort out the issue for you.