The “MySQL server has gone away” error indicates that the MySQL server (mysqld) has timed out and closed the connection. This often occurs after a period of inactivity, but can also point to underlying configuration issues. Solving this issue can involve changes across multiple layers of your application and server setup. This article provides potential solutions for this common MySQL error.
Addressing wait_timeout
The most frequent cause is exceeding MySQL’s wait_timeout setting. This variable defines the number of seconds the server waits for activity on a non-interactive connection before closing it. Ensuring that wait_timeout is appropriately configured is often the most effective first step.
Step 1: Access your MySQL server’s configuration file (my.cnf). The location varies based on your operating system and installation method. Common locations include /etc/my.cnf, /etc/mysql/my.cnf, or /usr/local/mysql/etc/my.cnf.
Step 2: Open the my.cnf file in a text editor with root privileges.
Step 3: Locate or add the [mysqld] section.
Step 4: Add or modify the wait_timeout variable within the [mysqld] section. It is also good to adjust the interactive_timeout, net_read_timeout, and net_write_timeout variables. For example, to set a wait_timeout of 90 seconds, add the following:
[mysqld]
wait_timeout=90
net_read_timeout=90
net_write_timeout=90
interactive_timeout=300
connect_timeout=90
Step 5: Save the changes to the my.cnf file.
Step 6: Restart the MySQL server to apply the changes. The command varies based on your operating system. For example, on systemd-based systems, use:
sudo systemctl restart mysql
or
sudo systemctl restart mariadb
Increasing max_allowed_packet
The max_allowed_packet variable limits the size of a single packet that the MySQL server can receive. If the server receives a packet exceeding this limit, it assumes an issue and closes the connection.
Step 1: Open your MySQL configuration file (my.cnf) as described above.
Step 2: Locate or add the [mysqld] section.
Step 3: Add or modify the max_allowed_packet variable. Increase it to a larger value, such as 512MB. The maximum value is 1GB.
[mysqld]
max_allowed_packet = 512M
Step 4: Save the changes to the my.cnf file.
Step 5: Restart the MySQL server:
sudo systemctl restart mysql
or
sudo systemctl restart mariadb
Adjusting PHP Configuration
If your application uses PHP to connect to MySQL, adjust the relevant PHP settings.
Step 1: Locate your PHP configuration file (php.ini). The location varies depending on your PHP installation. Common locations include /etc/php.ini, /usr/local/etc/php/php.ini, or similar paths.
Step 2: Open the php.ini file in a text editor.
Step 3: Find the mysql.connect_timeout setting. Ensure it’s not lower than your MySQL wait_timeout. Also, confirm that mysql.allow_persistent is enabled (default).
mysql.connect_timeout=90
mysql.allow_persistent=1
IMPORTANT: Read about PHP Persistent Database Connections to understand the benefits and caveats.
Step 4: Adjust default_socket_timeout.
default_socket_timeout=90
Step 5: Modify max_execution_time and max_input_time, if necessary.
max_execution_time = 90
max_input_time = 90
Step 6: Save the changes to the php.ini file.
Step 7: Restart your web server (e.g., Apache or Nginx) to apply the PHP configuration changes.
Correcting innodb_log_file_size
The innodb_log_file_size MySQL variable may need to be increased. MySQL’s innodb_log_file_size should ideally be 25% of innodb_buffer_pool_size (but no less than 20%).
Step 1: Open your MySQL configuration file (my.cnf) as described above.
Step 2: Determine your current innodb_buffer_pool_size.
Step 3: Calculate the appropriate innodb_log_file_size based on the innodb_buffer_pool_size and innodb_log_files_in_group. For example, if innodb_buffer_pool_size=16G and innodb_log_files_in_group=2, then innodb_log_file_size should be set to 2G.
[mysqld]
innodb_log_file_size=2G
Step 4: Save the changes to the my.cnf file.
Step 5: Stop the MySQL server.
Step 6: Apply the changes.
Step 7: Start the MySQL server:
sudo systemctl start mysql
or
sudo systemctl start mariadb
WARNING: You must stop the MySQL server before changing innodb_log_file_size or innodb_log_files_in_group to avoid data corruption. Refer to MySQL Log Redo instructions for further details.
Other Potential Causes
- Remote Connections: The error might be related to remote connections to third-party services or plugins.
- Database Charset and Collation: Some users have reported that changing the default database charset to
latin1and the default collation tolatin1_general_ciresolved the issue. - Exceeding
max_connections: Themax_connectionssetting limits the number of simultaneous client connections. Setting this value too high can exhaust server resources. As a guide, setmax_connectionsto approximately double the previous number of maximum simultaneous client connections. Refer to How MySQL Handles Client Connections before making changes.
Error Log Examples
The “MySQL server has gone away” error can manifest in different forms in your logs:
General error: 2006 MySQL server has gone away
Error Code: 2013. Lost connection to MySQL server during query
Warning: Error while sending QUERY packet
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Keep in mind that these errors can be indicative of deeper problems. Correlate the timestamps with other application and system logs to identify the root cause.
By systematically checking these settings, you should be able to resolve most instances of the “MySQL server has gone away” error. Remember to back up your configuration files before making any changes.