In this tutorial, we'll discuss how to solve the “MySQL server has gone away” error.
The “MySQL Server Has Gone Away” error is one of the most commonly encountered issues with MySQL databases. It typically happens when the connection between the MySQL client and server is lost. This can occur due to various reasons such as query timeout, incorrect MySQL settings, or large query sizes. This guide will walk you through the latest and most effective solutions to resolve this issue.
Solve the “MySQL Server Has Gone Away” Error
Step 1: Understand the Causes of the Error
Before applying any solution, it's important to understand the possible causes of this error. The error usually occurs due to:
- Connection Timeout: The MySQL server closes the connection if it remains idle beyond the configured timeout value.
- Too Large Query: If a query or packet exceeds the maximum size allowed by MySQL settings.
- Corrupted MySQL Tables: Corrupted tables might trigger this error when accessed by queries.
- Network Issues: Unstable network connectivity between the MySQL client and server.
Improper MySQL Configuration: Incorrect server settings can also cause the connection to drop.
Step 2: Review the MySQL Error Log
The first step in troubleshooting the "MySQL Server Has Gone Away" error is to check the MySQL error log. The log provides detailed information about why the server might have closed the connection.
You can find the MySQL error log at the following location:
/var/log/mysql/error.log
Look for any error messages or warnings that correspond to the time of the issue. These logs can provide clues to the root cause.
Step 3: Increase the wait_timeout and interactive_timeout Settings
If the error is due to connection timeout, you can adjust the MySQL server’s timeout values to prevent premature disconnection.
Open the MySQL configuration file for editing:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Increase the wait_timeout
and interactive_timeout
values. Add or modify the following lines in the [mysqld]
section:
wait_timeout = 28800
interactive_timeout = 28800
The default values for both parameters are typically set low. Increasing them will keep the connection alive for 8 hours (28800 seconds)
.
Save and close the file, then restart MySQL to apply the changes:
sudo systemctl restart mysql
Step 4: Increase the max_allowed_packet Size
If the error is caused by large queries or data packets, you need to increase the max_allowed_packet
size. This setting controls the maximum size of a single SQL statement or packet that can be sent to the MySQL server.
Open the MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Increase the max_allowed_packet
value in the [mysqld]
section:
max_allowed_packet = 64M
You can increase this value further depending on the size of your queries, but 64MB
is usually sufficient for most scenarios.
Save and close the file, then restart MySQL:
sudo systemctl restart mysql
Verify the new setting by connecting to MySQL and running:
SHOW VARIABLES LIKE 'max_allowed_packet';
Step 5: Adjust the net_read_timeout and net_write_timeout Values
Sometimes, network latency or slow query execution can cause timeouts during reading from or writing to the MySQL server. You can increase the net_read_timeout
and net_write_timeout
values to give the server more time to handle the queries.
Open the MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add or modify the following lines in the [mysqld] section:
net_read_timeout = 120
net_write_timeout = 120
This increases the read and write timeout to 120
seconds.
Save and close the file, then restart MySQL:
sudo systemctl restart mysql
Step 6: Check and Repair Corrupted Tables
In some cases, the "MySQL Server Has Gone Away" error may occur because of corrupted tables. You can repair corrupted tables using the mysqlcheck utility:
Check and repair all tables in your database:
sudo mysqlcheck --auto-repair --all-databases
This will automatically repair any corrupted tables.
Alternatively, you can run the REPAIR TABLE command from within the MySQL shell for specific tables:
REPAIR TABLE your_table_name;
Step 7: Handle Large Bulk Inserts
If the error occurs during large bulk insert operations, MySQL might be closing the connection due to insufficient memory or packet size. Here are a few steps to handle this:
Use smaller insert batches: Break up large INSERT statements into smaller batches to avoid overwhelming the server.
Increase the innodb_buffer_pool_size
: This value controls how much memory InnoDB can use to cache table and index data. You can increase this to improve performance during bulk inserts.
Open the MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Increase the innodb_buffer_pool_size:
innodb_buffer_pool_size = 512M
This setting can be increased based on your system’s available memory.
Save the file and restart MySQL:
sudo systemctl restart mysql
Step 8: Adjust Application-Level Timeouts
If you’re experiencing the error while interacting with MySQL through an application, the issue might be on the application side rather than with MySQL settings.
PHP Configuration: For applications running on PHP, ensure that the mysql.connect_timeout
, default_socket_timeout
, and max_execution_time
in your php.ini
file are set to appropriate values:
mysql.connect_timeout = 28800
default_socket_timeout = 28800
max_execution_time = 300
MySQL Client Libraries: Ensure that any client-side MySQL libraries (such as MySQLi or PDO) are correctly handling timeouts.
Step 9: Verify Network Stability
If your MySQL server is on a remote host, network instability might be causing the error. Check for any signs of packet loss, high latency, or connection drops between the client and server using tools like ping, traceroute, or netstat.
You can also use MySQL’s connection pooling to maintain long-running connections efficiently, reducing the likelihood of network-related issues.
Step 10: Restart MySQL and Test Your Application
Once you’ve applied the necessary changes, restart the MySQL server:
sudo systemctl restart mysql
After restarting, test your application to see if the “MySQL Server Has Gone Away” error has been resolved.
Conclusion
The “MySQL Server Has Gone Away” error can be caused by a variety of issues ranging from connection timeouts to large queries. By following the steps outlined in this guide, you can resolve the issue by adjusting MySQL’s timeout values, increasing packet sizes, repairing corrupted tables, or making adjustments to your application. Always remember to check the MySQL error logs and test after each configuration change to ensure the issue is fixed.
Checkout our dedicated servers India, Instant KVM VPS, and Web Hosting India