While executing queries in MySQL Server, you may encounter the "MySQL has gone away" error. The primary reason for this error is that the server has timed out and closed the connection. This happens when you try to execute large queries that include INSERT or REPLACE statements, which require considerable time to execute. However, there are many other reasons that can lead to this error in MySQL. In this article, we'll discuss the causes behind the "MySQL has gone away" error in MySQL and the solutions to resolve it.
Reasons behind the ‘MySQL has gone away’ Error
This error can occur due to one or more of the following reasons:
- MySQL tables are corrupted.
- Time out duration in the Time-out Settings are too low.
- Queries are damaged or corrupted.
- mysqladmin kill command is incorrect.
- Trying to execute query when the server is not connected.
- TCP/IP connection on the client/server system is timed out.
- Automatic reconnection is not enabled on the client-side system.
- Connection between the client system and server system is lost before executing the command.
- Executing queries that are too large or unorganized (out of order).
- MySQL Server fails to use IP-to-host name lookups.
- Firewall settings are blocking MySQL application.
Methods to Fix the "MySQL has gone away" Error
Here are some methods to troubleshoot and resolve the MySQL has gone away error.
1 - Increase the Time-out Duration on MySQL Server
You can encounter the MySQL has gone away error due to server/client connection timeout. A connection timeout is the time duration for which a client waits, expecting a response from the server search engine. If the server fails to respond within this duration, the connection is considered unsuccessful due to no activity. By default, the MySQL server closes the connection after 8 hours if the system is idle.
The connection time-out issues in MySQL usually happen when you execute queries that require large operations. For example, you are using a function that may be called multiple times to set different operations, like mysql_options(), mysql_get_options, etc. To prevent this, you can increase the time-out duration on the MySQL server using the wait_timeout command.
2 - Increase the Packet Limit in MySQL Server
You can encounter the "MySQL has gone away" error when the query you are requesting to the server is too big and requires multiple operations. However, you can increase the query limit by changing the max_allowed packet variable. If the packet is too large with large BLOB values, then you can increase the packet limit. To do so, run the below command:
3 - Check your Network Connection
The MySQL server has gone away error can also occur if the server connection is lost. In such a case, you can use the mysql_ping() command (as given below) to check whether the connection to the server is working or not.
int
mysql_ping(MYSQL *mysql)
If the above command returns the result as ‘0’, it indicates the server is active. If it shows any non-zero value, then it means there are network issues. You can troubleshoot the network connectivity issues.
4 - Enable Skip Networking
You can also enable skip networking system variable to resolve the network connection issues in MySQL. This works for applications where only local clients are involved. By default, the skip networking variable is disabled. To enable skip networking, follow the steps below:
- Find the MySQL configuration file on your system. The default path is:
- Open the configuration file and then locate [mysqld] section, and add the --skip-networking[={ON}]
- Then, restart the MySQL Server.
Enabling the skip networking system variable disables the TCP/IP connection entirely, reducing the TCP/IP connection overhead.
5 - Check and Repair MySQL Database
Queries may also fail due to corrupt tables or indexes in the database, resulting in the error. To check if tables are corrupt, you can use the CHECK TABLE statement. It will check the tables view and their references, including indexes for corruption. If it detects any issue, it will display the error with the table name. Then, you can use the REPAIR TABLE command (as given below) to repair the MySQL database’s table.
Note: To use this command, make sure you have the INSERT and SELECT privileges for the table.
REPAIR TABLE tablename [options]
If the REPAIR TABLE fails to provide the expected results, then you can use a professional MySQL database repair tool, like Stellar Repair for MySQL. This tool can quickly repair corrupted or damaged InnoDB/MyISAM tables in MySQL database with complete integrity and precision. It then saves the repaired data in MySQL, MariaDB, SQL Script, and other file formats (CSV, HTML, and XLS). The tool helps to effectively resolve a wide range of corruption-related errors in MySQL database. The tool supports MySQL version 8.0.36 and earlier, including MariaDB versions up to 11.3.2. It is compatible with Windows and Linux operating systems.
Conclusion
The MySQL has gone away error may occur due to large-sized queries that takes significant time to load, network connection issues, low timeout duration, and various other reasons. You can follow the methods mentioned above to resolve the error. If corruption in the database has caused the error, then you can use Stellar Repair for MySQL to repair the database. It is a reliable tool that can help you repair corrupt MySQL database on Windows and Linux operating systems. You can install the tool’s demo version to scan the database and preview the recoverable objects.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
4 min read
4 min read
4 min read
