Victor's room

Overview & Root Cause Summary: The error ERROR 2013 (HY000): Lost connection to MySQL server during query occurs when an active TCP connection between a client application and MySQL/MariaDB is abruptly severed while a query is in mid-execution. Unlike Error 2006 (which occurs before a query is transmitted), Error 2013 indicates that the server began processing the query, but communication timed out due to restrictive network buffer timeouts (net_read_timeout / net_write_timeout), oversized result sets, or the MySQL daemon crashing / being terminated by the Linux OOM (Out Of Memory) killer.

Understanding the Root Causes

• Network Buffer Timeout Exceeded (net_read_timeout / net_write_timeout): When fetching massive result sets or dumping large tables, the server pauses waiting for the client to read more data. If the client is slow to consume the stream, MySQL closes the socket once net_write_timeout (default: 60s) expires.
• Heavy Analytical Queries & Server-Side Processing Delays: If a query takes an extensive time to gather initial rows, intermediate firewalls, proxies, or cloud load balancers drop the TCP session due to idle connection inactivity.
• Query Payload Exceeds max_allowed_packet: Massive INSERT, UPDATE, or mysqldump payloads exceeding the maximum packet threshold prompt MySQL to forcefully disconnect the client mid-stream.
• MySQL Daemon Crash or Linux OOM Killer: Complex queries consuming excessive memory cause mysqld to crash (segmentation fault) or trigger the Linux kernel Out-Of-Memory killer, instantly dropping all in-flight connections.

Step 1: Quick Fix (Increase net_read_timeout & net_write_timeout in my.cnf)

Expand network socket transmission timeouts to accommodate slow clients and large query result streams.

# 1. Edit your MySQL server configuration file:
# Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
# CentOS/RHEL:   /etc/my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# 2. Add or update timeout settings under the [mysqld] section:
[mysqld]
# Increase network read and write timeout thresholds (in seconds):
net_read_timeout = 300
net_write_timeout = 600

# Also increase general wait timeouts for persistent connections:
wait_timeout = 28800
interactive_timeout = 28800

# 3. Restart the MySQL service to apply changes:
sudo systemctl restart mysql
# (On CentOS/RHEL: sudo systemctl restart mysqld)

Step 2: Increase max_allowed_packet & Client Driver Timeouts

Ensure both server-side packet limits and client-side driver read timeouts can handle large data payloads.

# --- Server-Side max_allowed_packet Tuning ---
# In mysqld.cnf under [mysqld]:
max_allowed_packet = 128M

# Dynamically apply without restarting MySQL:
# Connect as root:
mysql -u root -p -e "SET GLOBAL max_allowed_packet = 134217728;"

# --- Client-Side Timeout Tuning Examples ---
# Python (PyMySQL / mysqlclient):
# Set read_timeout in connection kwargs:
# connection = pymysql.connect(..., read_timeout=300, write_timeout=300)

# Node.js (mysql2):
# Configure connectTimeout:
# const connection = mysql.createConnection({ ..., connectTimeout: 60000 });

# mysqldump CLI (pass max_allowed_packet directly):
mysqldump --max-allowed-packet=128M -u root -p database_name > backup.sql

Step 3: Investigate Server Crashes & Linux Kernel OOM Killer

Verify whether the connection dropped because the MySQL server process crashed or was terminated by the operating system.

# 1. Inspect kernel ring buffer for Out Of Memory events:
dmesg -T | grep -i -E "killed process|oom|mysqld"

# 2. Check the MySQL error log for crash stack traces:
sudo tail -n 50 /var/log/mysql/error.log
# (Or using journalctl on systemd):
sudo journalctl -u mysql -n 50 --no-pager

# 3. If OOM occurred, reduce memory pressure by tuning the InnoDB buffer pool:
# In mysqld.cnf, ensure buffer pool does not exceed 70% of available RAM:
# innodb_buffer_pool_size = 2G

Verification & Testing Steps

Confirm updated timeout values in the running server and execute large query benchmarks.

# 1. Connect to MySQL CLI and verify active timeout variables:
mysql -u root -p -e "SHOW VARIABLES LIKE 'net_%_timeout';"
# Expected output:
# net_read_timeout  | 300
# net_write_timeout | 600

# 2. Verify active max_allowed_packet:
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_allowed_packet';"

# 3. Execute the previously failing heavy query and confirm successful completion:
mysql -u root -p mydb -e "SELECT COUNT(*) FROM large_table WHERE complex_condition;"

Summary Comparison Table