Fix MySQL Replication Errors: Skip Failed Transactions

1. Problem Identification​

When checking replication status with SHOW REPLICA STATUS, you may encounter an error like:

Worker 1 failed executing transaction '230e8ddf-5bff-11f0-854d-0022a593688b:1' at source log mysql-bin.000002, end_log_pos 478;  
Error 'Operation ALTER USER failed for 'root'@'localhost'' on query.  
Default database: ''.  
Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005sC1B...';

‍

Meaning: The primary executed an ALTER USER command to modify the root@localhost account, but the secondary failed to apply it, halting replication.

​Possible Causes:

• The root@localhost account exists but lacks permissions or is locked.
• The account was deleted or not synced after primary-secondary changes.
• Authentication plugins differ (e.g., mysql_native_password vs. caching_sha2_password).
• Security policies block local user modifications.

​2. Solution: Skip the Problematic GTID Transaction​

​Goal: Bypass the failed ALTER USER operation while maintaining replication integrity.

​Steps:

-- 1. Stop replication  
STOP REPLICA;  

-- 2. Set GTID_NEXT to the failed transaction  
SET GTID_NEXT = '230e8ddf-5bff-11f0-854d-0022a593688b:1';  

-- 3. Execute an empty transaction to "complete" the GTID  
BEGIN;  
COMMIT;  

-- 4. Restore automatic GTID assignment  
SET GTID_NEXT = AUTOMATIC;  

-- 5. Restart replication  
START REPLICA;  

‍

How It Works:

• ​GTID (Global Transaction Identifier)​: Uniquely identifies transactions in MySQL replication.
• ​Skipping Mechanism:
  1. Manually set GTID_NEXT to the failed GTID.
  2. Commit an empty transaction to mark it as "executed."
  3. Resume automatic GTID assignment to continue replication.

​3. GTID Mechanics Explained​

​Example Workflow:

1. Primary executes a transaction → MySQL assigns a GTID (e.g., 230e8ddf-5bff-11f0-854d-0022a593688b:1).
2. The transaction is logged to the binary log and sent to the secondary.
3. The secondary records the GTID in Executed_Gtid_Set upon successful execution.

​In This Case:

• The failed GTID is manually "completed" via an empty transaction, allowing replication to proceed.

​4. Production Considerations​

⚠️ ​Risks:

• Skipping transactions may cause data divergence between primary and secondary.
• ​Use Only If: The skipped operation is non-critical (e.g., a failed user permission update).

​Alternatives:

• ​Reinitialize the Secondary: Restore from a fresh backup if consistency is critical.
• ​Manual Data Sync: Use mysqldump or tools like Percona XtraBackup to fill gaps.

‍