MySQL replica SQL thread idles and relay logs grow after short network blips — slave lags and no obvious errors

Environment

Primary and one async replica hosted in same data center (MySQL 8.0.33), replication configured using GTID and a replication user with REPLICATION SLAVE. Replication was healthy for weeks. Intermittent 10–30s packet loss events reported by network monitoring.

Symptoms

Replica shows increasing replication delay (minutes to hours) after several short network interruptions. IO thread reconnects and continues fetching relay logs, but SQL thread remains idle applying no events. SHOW SLAVE STATUS shows Seconds_Behind_Master growing; Last_SQL_Errno is 0, Last_SQL_Error empty.

Observations

Relay-log files accumulate and relay_log_space grows while relay_log_pos advances. SHOW PROCESSLIST on replica shows IO_THREAD active, SQL thread in 'Waiting for master to send event' (or 'Sleep') states intermittently. No corruption logged. Restarting the replica's SQL thread manually with STOP SLAVE; START SLAVE resumes apply. This recurs after next blip. Binlog_checksum and relay_log_info_repository are standard.

Already attempted

Checked disk space and I/O — OK. Verified permissions on relay logs. Increased net_read_timeout and slave_net_timeout temporarily to reduce disconnect churn; problem persists after next network blip. Ran mysqlcheck; no table corruption. Switched to semi-sync off for test — no change. Captured packet traces showing reconnection TCP FIN/ACK sequences during blips.

Question

Why does the replica's SQL thread stop applying after short network blips even though IO thread reconnects and fetches relay logs? How to make replication resilient and avoid manual SQL thread restarts?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1d ago• Trouble ID

Most likely causes

MySQL replica SQL thread state machine bug triggered by reconnect timing (requires STOP/START to resume) 70%

Replica waiting on a lock or transaction conflict during SQL apply caused by application or maintenance connections, leaving SQL thread blocked without reporting an error 50%

Relay-log index or relay_log_info corruption requiring thread reset to reload correct position 40%

Summary

Evidence (IO thread active, SQL thread idle, manual restart fixes) suggests the replica's SQL thread can get stuck waiting for an event or a metadata condition after reconnection. Likely causes include a bug in the replica SQL thread state machine triggered by reconnect timing, relay log metadata corruption requiring STOP/START to reset, or deadlock/hang inside SQL apply due to transaction state (e.g., long-running incomplete transaction or lock held by other session). Network blips provoke the condition. Disk/permission issues unlikely because manual restart resumes apply and restarts are temporary remedies.

Suggested Fixes

Diagnostic step 1

Enable increased replication debug logging on the replica (log_slave_updates and log-warnings; set log_error_verbosity higher or enable --debug-slave for short windows) and reproduce during a controlled network flap.

Diagnostic step 2

When SQL thread becomes idle, capture SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST, and EXPLAIN for likely blocking transactions to detect locks or long-running queries.

Diagnostic step 3

Compare relay_log_info and relay logs before and after manual restart; back them up and run mysqlbinlog to inspect event boundaries around the last applied position.