Repairing Corrupt MySQL Database When You Encounter “MySL Server Has Gone Away” Error
MySQL is an open source database that has got a fair amount of users all over the world. It is fast, secure, reliable, and easy to use. Apart from being the most used open source database, it is an excellent option for the applications built on the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) platform. It efficiently runs on various operating system platforms such as Windows, Linux, Macintosh, Solaris, etc.
However, at times some inconsistencies occur in the MySQL database that could lead to database corruption. The reasons behind such situations could be virus infections, human errors, abrupt system shutdown while the database is open, power surges, etc. In such cases, you should consider taking appropriate corrective measures to sort the issue. However, if you are unable to do so then you should use a third-party MySQL recovery software to recover MySQL database.
Consider a scenario wherein you are working on a MySQL database. You try to execute a query but are unable to do so. One of the following error messages may be displayed in such case:
MySQL server has gone away
Lost connection to server during query
Cause:
There could be the following reasons for this error messages:
The server timed out and the connection is now closed.
You have closed the connection yourself and are now trying to run the query on the closed connection.
The TCP/IP connection on the client side has timed out.
A timeout has occurred on the server side and the automatic reconnection on the client side is disabled.
You have issued a query that is either too large or too complex.
However, if the aforementioned reasons have not caused the error message that means that the MySQL database is corrupt.
Resolution:
To resolve the issue, you can perform either of the following methods:
If the connection is closed, then you should reestablish the connection and see if it works well.
Run the “OPTIMIZE TABLE ” command. If you get the “ERROR 1030 (HY000): Got error 28 from storage engine” message, then this would mean that there is no space in the drive to get the query result. Then, to repair the database by optimizing it, you need to perform the following steps:
Add the following line in the “mysqld” section in :
“innodb_force_recovery=4”
Stop “mysqld” and restore data backup to “mysqld” data folder.
Restart “mysqld” and perform a thorough check on your tables.
Try to execute a large query.
Optimize all the database tables.
However, if you are not able to recover MySQL database using the aforementioned methods then you should use a third-party MySQL recovery tool to do so. Such read-only tools are able to recover MySQL database using fast yet sophisticated scanning algorithms.
Stellar Phoenix MySQL Database Recovery is a MySQL repair software that enables you to recover MySQL databases. This tool is able to repair MySQL components such as tables, views, etc. It is compatible with Windows 7, Vista, Server 2003, XP, and 2000.