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.

Continue reading…