Fixing the "MySQL Server Has Gone Away" Error

Linux
1

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 the max_connections limit, the server might refuse new connections and existing ones might be dropped. Setting max_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.