Root Cause Analysis of MySQL Aborted Connections Caused by TCP Buffer Exhaustion and Net Write Timeout

1 Background

During batch job execution, the application encountered a problem where some tasks lost their database connections abruptly, as indicated by "Aborted connection" in MySQL error logs, meaning the client‑server communication was unexpectedly terminated.

2 Analysis

We first considered common causes of aborted connections:

Client did not correctly close the connection, i.e., did not call mysql_close() .

Client idle time exceeded wait_timeout or interactive_timeout parameters, causing the server to drop the connection.

Data packet size exceeded the max_allowed_packet value.

Client lacked proper privileges, used a wrong password, or sent an incorrect connection packet.

All the above scenarios were ruled out after investigation.

Case 1: The tasks had run normally before and the program had not changed, so missing mysql_close() was unlikely.

Case 2: The MySQL wait_timeout and interactive_timeout were both 28800 seconds (8 hours), far longer than the job duration.

Case 3: The max_allowed_packet was set to 64 M, which was not exceeded.

Case 4: Client permissions, password, and connection packet were verified as correct.

Thus the issue seemed to lie beyond MySQL configuration.

We then modified several kernel parameters to improve TCP stability:

net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 120
net.core.rmem_default = 2097152
net.core.wmem_default = 2097152
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_syn_backlog = 16384

These changes did not resolve the connection aborts.

Using Wireshark, we captured a large number of ACK packets sent by the server, as shown in the following image:

The ACK packets indicate that the server has received data from the client and is requesting the client to continue sending, but the client appears unable to acknowledge them promptly.

Further capture revealed many TCP window‑warning packets, indicating that the receive window on either side was full:

These warnings correspond to TCP "Window Full" and "ZeroWindow" conditions, meaning the receiver cannot accept more data.

From the capture we inferred that the MySQL server was trying to send a large result set, filling the client’s TCP receive buffer. The client could not drain the buffer within the default 60‑second window, so MySQL timed out and aborted the connection.

Checking the MySQL slow‑query log showed many entries with Last_errno: 1161 , which corresponds to error number 1161 (ER_NET_WRITE_INTERRUPTED) – "Got timeout writing communication packets".

Adjusting the MySQL net_write_timeout parameter to 600 seconds resolved the batch jobs.

3 Conclusion

Wireshark captured excessive ACK packets because the client’s TCP buffer was full; the server kept retransmitting ACKs until the default net_write_timeout of 60 seconds expired, causing MySQL to abort the connection.

The slow‑log entries with Last_errno: 1161 occurred because the SQL had finished executing, but sending the large result set exceeded the client’s TCP buffer, leading to a write timeout.

Increasing net_write_timeout can mitigate the symptom, but the root cause is the oversized result set that overwhelms the client’s buffer.

4 Optimization Suggestions

Split large data retrieval into smaller batches to avoid overwhelming the client’s TCP buffer.

Increase net_write_timeout on the MySQL server or enlarge the client’s TCP buffer, which can alleviate but not fully solve the issue.

Optimize SQL statements to return only necessary data (e.g., use LIMIT, appropriate WHERE clauses, or aggregation) to reduce payload size and improve query efficiency.