I have a three node Percona XtraDB Cluster (5.5) setup.
Every night, we shut down MySQL on one randomly selected node in order to take backups of the data directory.
When our traffic is reasonably busy, this causes a couple (2-4) error alerts along the lines of
SQLSTATE: Integrity constraint violation: 1062 Duplicate entry '140577' for key 'PRIMARY'. Primary key conflict, obviously, except we're using
auto_increment columns as the primary key on these tables. As each node has an offset assigned by the cluster engine, this shouldn't be occurring.
My suspicion is that taking the node out of the cluster causes the other two nodes to change their auto increment offsets, in a way that's causing them to potentially conflict while the change takes place. I'm at a loss as to why this wouldn't be an atomic action as well as how I might fix it.
Has anyone encountered this? Is there a way to temporarily freeze the auto increment settings in the cluster so they don't shuffle around during the backup process or some other solution I'm not thinking of?