Fixing Broken MySQL Master-Master Replication: Error during XID COMMIT

So you’re stuck with master-master [or possibly circular] replication and that’s unfortunate, but if you’re reading this you’re probably already feeling bad enough about it so let’s get to it.

The error in question from SHOW SLAVE STATUS looks like:

Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-11648' for key 'PRIMARY'

and I’ll also bet that you’ve got a blank Replicate_Ignore_Server_Ids: line in the slave status as well.

What seems to be happening is that statements from one master are getting bounced back to the originating server and mucking up the mySQL internals. In order to fix this you need to tell your masters to ignore statements from themselves.

What you’ll want to do is:

  1. Get the server’s ID: SELECT @@server_id
  2. STOP SLAVE
  3. CHANGE MASTER TO IGNORE_SERVER_IDS = ($server_id)
  4. START SLAVE

Once that’s all done your replication should pick right up where it left off, provided that other bounced statements hadn’t already corrupted your data.

Now just remember to set this same setting in all of your master-master and/or circular replication deployments, otherwise this problem is bound to crop up.