Troubleshooting MariaDB and MySQL Replication Failures: Resolving Error 1062 (Duplicate Entry), Error 1032 (Key Not Found), MTS Worker Crashes, and GTID Transaction Skipping: Sysadmin Runbook

In mission-critical e-commerce, banking, and SaaS environments, asynchronous or semi-synchronous replication between a primary database and read replicas is fundamental for load distribution, point-in-time recovery, and zero-downtime failover^[1]. However, an unexpected primary failover, an accidental direct write executed on a replica, or a corrupt relay log event can halt the replication applier thread instantly^[1].

When this occurs, executing SHOW REPLICA STATUS\G (or SHOW SLAVE STATUS\G in legacy syntax) displays non-zero error codes, leaving Replica_SQL_Running: No and causing Seconds_Behind_Master to climb continuously^[1]:

               Slave_IO_Running: Yes
              Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table db.orders; 
                               Duplicate entry '98214' for key 'PRIMARY', 
                               Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY

Blindly skipping events or forcing slave_skip_errors=all causes silent data drift, leading to permanent desynchronization between primary and replica clusters^[1][2]. This engineering runbook provides systematic diagnosis and safe remediation procedures for resolving replication breaks without data corruption.

---

Common Replication Error Codes: Root Causes & Impact

Most replication breaks trace back to one of three underlying database conditions:

Error Code	Error Description	Architectural Root Cause	Safe Remediation Strategy
Error 1062	HA_ERR_FOUND_DUPP_KEY	An INSERT event from the primary conflicts with a row that already exists locally on the replica (often caused by direct writes to the replica or non-read-only configurations)[1].	Compare conflicting rows, delete or update the rogue replica row, or inject an empty GTID transaction[2].
Error 1032	HA_ERR_KEY_NOT_FOUND	An UPDATE or DELETE event received from the primary references a row key that does not exist in the replica table[1].	Extract the row from the primary, insert a dummy placeholder row on the replica, and resume the SQL thread[1].
Error 1205 / MTS Stalls	Lock wait timeout exceeded	Multi-Threaded Slave (MTS) workers attempt concurrent updates on dependent tables, causing applier deadlocks[1].	Tune replica_parallel_workers, enforce replica_parallel_type=LOGICAL_CLOCK, or increase innodb_lock_wait_timeout.

For high-throughput database workloads running large-scale transactional clusters, hosting on Dedicated Servers provides single-tenant NVMe storage with enterprise power-loss protection (PLP), preventing write cache corruption and fsync bottlenecks that destabilize database replication.

---

Step 1: Diagnosing the Exact Failure Event

Do not restart or clear replication until you extract the exact failing SQL statement and binary log coordinates^[1]:

-- On the Replica: Query full replication status
SHOW REPLICA STATUS\G

Record the following critical values:

• Relay_Master_Log_File and Exec_Master_Log_Pos
• Relay_Log_File and Relay_Log_Pos
• Last_SQL_Errno and Last_SQL_Error
• Retrieved_Gtid_Set and Executed_Gtid_Set

Inspecting the Offending Event in the Relay Log

Use mysqlbinlog to decode the exact row event causing the applier thread to break:

# Decode the binary log event using the Relay_Log_Pos
mysqlbinlog --base64-output=DECODE-ROWS -v -v \
    --start-position=14502 --stop-position=15500 \
    /var/lib/mysql/replica-relay-bin.000042

This output displays the exact pseudo-SQL statements (including primary key values and row column mappings) that failed to apply.

---

Step 2: Resolving Error 1062 (Duplicate Entry)

Error 1062 occurs when an INSERT statement attempts to write a record whose primary or unique key already exists on the replica^[1].

Method A: Comparing and Removing Rogue Replica Records

Check the row on both primary and replica:

-- On the Replica: Inspect existing row
SELECT * FROM db.orders WHERE order_id = 98214;

-- On the Primary: Verify source row state
SELECT * FROM db.orders WHERE order_id = 98214;

If the record on the replica was inserted by a rogue script or reporting tool and the primary’s version is authoritative:

-- On the Replica:
DELETE FROM db.orders WHERE order_id = 98214;
START REPLICA;
SHOW REPLICA STATUS\G

If the rows match identically (the row was already replicated earlier), you can proceed to Step 4 to skip the duplicate transaction safely.

---

Step 3: Resolving Error 1032 (Key Not Found)

Error 1032 occurs when a binary log event issues an UPDATE or DELETE for a row that does not exist in the replica’s local table^[1].

Remediation: Inserting a Placeholder Record

To allow the applier thread to process the UPDATE or DELETE event without error, fetch the row from the primary and insert it into the replica:

1. Query the missing row on the primary:

   -- On Primary:
   SELECT * FROM db.users WHERE user_id = 45012\G

2. Insert the row manually on the replica:

   -- On Replica:
   INSERT INTO db.users (user_id, email, status) VALUES (45012, '[email protected]', 'active');

3. Restart the replica SQL thread:

   START REPLICA SQL_THREAD;
   SHOW REPLICA STATUS\G

The applier thread will execute its pending UPDATE or DELETE against the newly inserted placeholder row and resume replication seamlessly.

---

Step 4: Safely Skipping Transactions under GTID

When running with Global Transaction Identifiers (gtid_mode = ON), traditional file/position skip counters (sql_replica_skip_counter) are disabled and will return an error^[2][3].

The standard and safe procedure to skip a broken transaction under GTID is to inject an empty transaction with the exact GTID coordinate of the failing transaction^[2].

Step-by-Step GTID Injection Workflow

1. Query the exact failing GTID from the Performance Schema^[2]:

   SELECT APPLYING_TRANSACTION, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE 
   FROM performance_schema.replication_applier_status_by_worker 
   WHERE LAST_ERROR_NUMBER > 0;

   Example Result: 3e114a80-1a22-11ec-91b4-00163e0034a1:184920

2. Stop the replica threads:

   STOP REPLICA;

3. Commit an empty transaction with that specific GTID^[2]:

   SET GTID_NEXT = '3e114a80-1a22-11ec-91b4-00163e0034a1:184920';
   BEGIN;
   COMMIT;
   SET GTID_NEXT = 'AUTOMATIC';

4. Restart replication:

   START REPLICA;

Because systemd and the MySQL replication engine observe that the specified GTID is now recorded in Executed_Gtid_Set, the applier thread skips the failing binary log payload automatically and proceeds to the next transaction^[2].

Skipping Transactions in MariaDB GTID

In MariaDB, skip operations with parallel replication domains are handled by advancing gtid_slave_pos^[3]:

STOP SLAVE;
-- Advance gtid_slave_pos past the failing sequence number (domain-server-seq)
SET GLOBAL gtid_slave_pos = '0-1-184920';
START SLAVE;

---

Step 5: Resolving Multi-Threaded Replica (MTS) Worker Stalls

Multi-Threaded Replicas allow multiple worker threads to apply independent transactions in parallel (replica_parallel_workers > 0)^[1]. However, if transaction dependencies are miscalculated, workers can block each other.

Remediation

1. Set Logical Clock Scheduling: Ensure parallel evaluation is based on commit order rather than database schema partitioning:

   # /etc/my.cnf.d/server.cnf
   [mysqld]
   replica_parallel_type = LOGICAL_CLOCK
   replica_preserve_commit_order = ON
   replica_parallel_workers = 8

2. Increase Lock Wait Timeout: Prevent transient locks from killing worker threads prematurely:

   SET GLOBAL innodb_lock_wait_timeout = 120;

---

Step 6: Verifying and Resynchronizing Data Drift with Percona Toolkit

After resolving an applier crash, verify data consistency across the cluster using pt-table-checksum and pt-table-sync^[4]:

# Run non-blocking table checksum from the Primary
pt-table-checksum --host=10.0.10.1 --user=pt_admin --password=Secret \
    --databases=db --replicate=percona.checksums

# Print differences and generate remediation statements on Replica
pt-table-sync --print --sync-to-master \
    h=10.0.10.2,u=pt_admin,p=Secret \
    --databases=db

---

High-Availability Database Infrastructure

When deploying master-slave replication topologies, hardware performance and network consistency are critical. Unreliable hypervisor disk I/O, storage latency spikes, or cross-datacenter packet drops trigger replica lag and connection timeouts.

Deploying database nodes on dedicated enterprise hardware ensures isolated CPU cores, enterprise NVMe endurance, and sub-10ms network routing. For organizations in Pakistan requiring ultra-low latency transaction processing and strict financial data residency, hosting on Dedicated Servers in Pakistan ensures compliant, high-availability database performance across domestic peering exchanges.

---

Sources & Official References

1. MySQL 8.0 Reference Manual — Troubleshooting Replication Problems. Authoritative documentation on resolving replication applier thread failures, error log diagnostic strategies, duplicate key conflicts, and multi-threaded worker troubleshooting.
2. MySQL 8.0 Reference Manual — Skipping Transactions with GTID. Detailed runbook on handling replication errors under `gtid_mode=ON`, creating empty transactions with `SET GTID_NEXT`, and safely advancing the replica execution position.
3. MariaDB Corporation — SET GLOBAL sql_slave_skip_counter and GTID Positioning. Official documentation on skip counter limitations under parallel multi-domain replication and updating `gtid_slave_pos` coordinates.
4. Percona Toolkit Documentation — pt-table-sync — Synchronize MySQL Table Data Efficiently. Technical reference for detecting replication drift, calculating cryptographic chunk checksums, and safely repairing desynchronized replica tables.

---