📋

Description

Error 2013 indicates that the client application lost its connection to the MySQL server while a query was actively being processed. This often happens when the server unexpectedly closes the connection due to various factors, preventing the query from completing successfully.

💬

Error Message

Lost connection to MySQL server during query

🔍

Known Causes

4 known causes

⚠️

Network Instability

Intermittent network issues, firewalls, or routing problems can disrupt the communication path between the client and the MySQL server.

⚠️

Server Timeout Settings

The MySQL server's `wait_timeout` or `interactive_timeout` variables might be set too low, causing the server to close connections prematurely.

⚠️

Long-Running Queries

Queries that take an exceptionally long time to execute can exceed server or client timeout limits, leading to a dropped connection.

⚠️

Server Resource Exhaustion

The MySQL server might be running out of memory, CPU, or other critical resources, forcing it to terminate active connections.

🛠️

Solutions

4 solutions available

1. Increase `wait_timeout` and `interactive_timeout` easy

Adjust server variables to prevent premature connection closure.

1

Connect to your MySQL server as a user with sufficient privileges (e.g., root).

2

Check the current values of `wait_timeout` and `interactive_timeout`.

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

3

Increase these values. A common starting point is 28800 seconds (8 hours). You can set them globally for the current session (until the server restarts) or permanently in the MySQL configuration file.

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

4

For permanent changes, edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`). Add or modify the following lines under the `[mysqld]` section:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

5

Restart the MySQL server for the permanent changes to take effect.

# For systemd-based systems:
sudo systemctl restart mysql
# For init.d-based systems:
sudo service mysql restart

2. Optimize Long-Running Queries medium

Identify and tune slow queries that might be exceeding server timeouts.

1

Enable the MySQL slow query log to capture queries that take longer than a specified time to execute.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5; -- Log queries taking longer than 5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; -- Specify log file path

2

After a period of activity, examine the slow query log file. Look for queries that are consistently taking a long time.

cat /var/log/mysql/mysql-slow.log

3

Use `EXPLAIN` to analyze the execution plan of slow queries. Identify missing indexes, inefficient joins, or full table scans.

EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';

4

Add appropriate indexes to tables based on `EXPLAIN` output. For example, to add an index on `some_column`:

CREATE INDEX idx_some_column ON your_table (some_column);

5

Rewrite inefficient queries to be more performant. This might involve using subqueries differently, joining tables more effectively, or selecting only necessary columns.

3. Increase Network Buffer Sizes medium

Adjust client and server network buffers to handle larger data transfers.

1

On the MySQL server, increase the `max_allowed_packet` variable. This controls the maximum size of a single packet that can be sent or received.

SET GLOBAL max_allowed_packet = 67108864; -- Set to 64MB (adjust as needed)

2

For permanent changes, edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`) under the `[mysqld]` section:

[mysqld]
max_allowed_packet = 64M

3

Restart the MySQL server for the permanent changes to take effect.

# For systemd-based systems:
sudo systemctl restart mysql
# For init.d-based systems:
sudo service mysql restart

4

On the client side (where the application connects), you may also need to adjust the `max_allowed_packet` setting for the specific client library or connection string. For example, in a PHP script using `mysqli`:

$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$mysqli->set_charset('utf8mb4');
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
// Set max_allowed_packet for the connection if supported by the client library
// This might not be directly settable via mysqli in all versions, but server setting is key.

5

If using a connector like `mysql-connector-python`, the parameter might be `max_allowed_packet` in the connection string or configuration.

connection = mysql.connector.connect(host='localhost', database='mydb', user='user', password='password', max_allowed_packet=67108864)

4. Check Server and Network Health medium

Ensure the MySQL server is not overloaded and network connectivity is stable.

1

Monitor server resource utilization (CPU, RAM, disk I/O) on the machine hosting the MySQL server. High utilization can lead to query timeouts.

# On Linux:
top
htop
iostat
vmstat

2

Check MySQL server status for errors or warnings in its error log.

tail -f /var/log/mysql/error.log

3

Test network connectivity between the client and the MySQL server. Use `ping` and `traceroute` to identify any latency or packet loss.

ping <mysql_server_ip>
traceroute <mysql_server_ip>

4

If running MySQL on a remote server, ensure there are no firewalls or network devices aggressively dropping idle connections.

5

Consider increasing the TCP keepalive settings on both the client and server operating systems if network interruptions are suspected.

🔗

Related Errors

5 related errors

MySQL Error 2013: Lost Connection During Query