The “MySQL server has gone away” error indicates that the connection to the MySQL server (mysqld
) was lost, usually due to a timeout. MySQL closes inactive connections after a default period of eight hours (28800 seconds), but this timeout can be configured. Resolving this error often requires adjustments at multiple levels, including application and service configurations.
Here’s how to tackle this common MySQL issue.
Increasing max_allowed_packet
One of the more effective solutions is to increase the max_allowed_packet
size. The max_allowed_packet
is the maximum size of a single packet that the MySQL server can receive. If the server receives a packet larger than this value, it assumes there is an issue and closes the connection.
Step 1: Open your MySQL configuration file (my.cnf
). The location varies based on the operating system, but common locations include /etc/my.cnf
, /etc/mysql/my.cnf
, or /usr/local/etc/my.cnf
.
Step 2: Add or modify the max_allowed_packet
setting in the [mysqld]
section. A value of 512M
is a reasonable starting point.
[mysqld]
max_allowed_packet = 512M
Step 3: Restart the MySQL server for the changes to take effect. Use the appropriate command for your system:
sudo systemctl restart mysql
or
sudo service mysql restart
Adjusting wait_timeout
The wait_timeout
variable determines how long the server waits for activity on a non-interactive connection before closing it. If this value is too low, connections may be prematurely terminated. Check also net_read_timeout
, net_write_timeout
and interactive_timeout
variables.
Step 1: Open your MySQL configuration file (my.cnf
).
Step 2: Add or modify the following variables under the [mysqld]
section. Adjust values according to your requirements.
[mysqld]
wait_timeout=90
net_read_timeout=90
net_write_timeout=90
interactive_timeout=300
connect_timeout=90
Step 3: Restart the MySQL server.
sudo systemctl restart mysql
or
sudo service mysql restart
Configuring PHP Connection Timeouts
If you’re using PHP to connect to your MySQL database, review your php.ini
file for relevant timeout settings.
Step 1: Locate your php.ini
file. Its location varies depending on your PHP installation.
Step 2: Adjust the mysql.connect_timeout
and default_socket_timeout
settings. Ensure that mysql.connect_timeout
is not lower than MySQL’s wait_timeout
. Also, confirm mysql.allow_persistent
is enabled.
mysql.connect_timeout=90
mysql.allow_persistent=1
default_socket_timeout=90
Step 3: Modify max_execution_time
and max_input_time
as needed.
max_execution_time = 90
max_input_time = 90
Step 4: Restart your web server (e.g., Apache, Nginx) to apply the changes.
Adjusting innodb_log_file_size
The innodb_log_file_size
variable affects InnoDB’s ability to handle transactions. If this is not properly configured, it can indirectly cause connection issues.
Step 1: Open your MySQL configuration file (my.cnf
).
Step 2: Determine your innodb_buffer_pool_size
. Then, set innodb_log_file_size
to approximately 25% of the buffer pool size. Make sure innodb_log_files_in_group
is set to 2. For example:
innodb_buffer_pool_size=16G
innodb_log_files_in_group=2
innodb_log_file_size=2G
Step 3: Stop the MySQL server.
Step 4: Apply the changes and start the MySQL server.
Other Potential Causes
-
Remote Connections: Issues with remote connections can trigger this error. Ensure stable network connectivity.
-
Character Set and Collation: Incorrect character set or collation settings can lead to problems.
-
Exceeding
max_connections
: If the number of simultaneous connections exceeds themax_connections
limit, the server might refuse new connections and existing ones might be dropped. Settingmax_connections
too high can lead to memory exhaustion. -
MySQL Server Crashes/Restarts: Verify if the MySQL server has unexpectedly crashed or restarted during query execution using
mysqladmin version
.
mysqladmin version
Oracle provides a helpful page for troubleshooting “MySQL server has gone away” errors.
By adjusting timeout settings and packet sizes, you can resolve the “MySQL server has gone away” error and ensure stable database connections. Remember to restart your MySQL server after making changes to the configuration file.