Sev1: MySQL replication stopped with error 1062 or 1032. Fixing it with GTIDs without breaking consistency

#Symptom

SHOW REPLICA STATUS\G

Replica_SQL_Running: No
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). ...
  Error 'Duplicate entry '10442' for key 'orders.PRIMARY'' on query ...
Retrieved_Gtid_Set: 3e11fa47-...:1-982215
Executed_Gtid_Set:  3e11fa47-...:1-982190

(SHOW SLAVE STATUS was removed in 8.4; use SHOW REPLICA STATUS.)

Both mean the replica's data already differs from the source. Skipping makes replication run again but doesn't fix the difference.

#Step 1: find the failing transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- LAST_ERROR_MESSAGE, APPLYING_TRANSACTION (the GTID)

Look at the row on the source and the replica:

-- both servers
SELECT * FROM app.orders WHERE id = 10442;

#Step 2: decide

#Step 3: skip one transaction (GTID way)

With GTIDs you can't use sql_replica_skip_counter. Commit an empty transaction with the failing GTID:

STOP REPLICA;
SET GTID_NEXT = '3e11fa47-71ca-11e1-9e33-c80aa9429562:982191';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;
SHOW REPLICA STATUS\G

Repeat for each failing transaction, but if it keeps failing, stop skipping and rebuild.

(Position-based replication: SET GLOBAL sql_replica_skip_counter = 1; START REPLICA;.)

Warning

Don't set replica_skip_errors = 1062,1032 permanently. It hides every future divergence until a failover promotes a replica with wrong data.

#Step 4: errant transactions (the hidden danger)

Writes made directly on the replica create GTIDs with the replica's server UUID. If that replica is ever promoted, the other replicas try to fetch those transactions, which may no longer exist in binlogs, and replication breaks during your failover.

-- On the replica: GTIDs it executed that the source doesn't have
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, '<paste source @@GLOBAL.gtid_executed>') AS errant;

If the result isn't empty:

• Find what they were (mysqlbinlog --include-gtids=... on the replica's binlogs).
• If they're harmless or now replicated in other ways, inject empty transactions with those GTIDs on the source, so every server has them:

-- on the source, for each errant GTID
SET GTID_NEXT = '<replica-uuid>:1-5';   -- one GTID at a time: '<uuid>:1', '<uuid>:2', ...
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

#Step 5: verify consistency

Skipping hides differences. Check:

pt-table-checksum --replicate=percona.checksums --databases=app h=source-host,u=checksum_user,p=...
pt-table-sync --print --replicate=percona.checksums h=source-host,u=...    # review before --execute

For heavy differences, rebuilding the replica (MySQL Shell clone, or XtraBackup) is faster and cleaner than syncing.

#Prevent it next time

• super_read_only = ON on every replica (read_only alone doesn't stop users with admin privileges).
• enforce_gtid_consistency = ON, gtid_mode = ON.
• Alert on Replica_SQL_Running = No and on errant GTIDs (compare sets in monitoring).
• Regular pt-table-checksum runs on critical schemas.