1
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.
FULL AI ANALYSIS
MySQL replica SQL thread idles and relay logs grow after short network blips — slave lags and no obvious errors
Generated Oct 14, 2026, 9:39:02 AM
Analysis 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.
Possible 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%Network middlebox (NAT, proxy) truncating or reordering packets on reconnect leading to incomplete event framing at SQL layer
25%Suggested fixes
2
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.
3
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.
4
Diagnostic step 4
Temporarily lengthen slave_net_timeout and net_read_timeout to reduce disconnect churn and observe whether condition still occurs (test in maintenance window).
5
Diagnostic step 5
If reproducing reliably, escalate to MySQL support or check upstream bug tracker with collected logs/packet captures; consider automating a safe watchdog to issue STOP SLAVE; START SLAVE when SQL thread is idle for X minutes as a temporary mitigation (ensure you understand implications for GTID and transaction safety).