1000 FAQs, 500 tutorials and explanatory videos. Here, there are only solutions!
Solve MySQL error (server has gone away)
This guide solves a type error "Invalid query: MySQL server has gone away
".
Preamble
- This type of error often originates from keeping a MySQL connection open without submitting requests for a time interval beyond which the connection is closed: http://dev.mysql.com/doc/refman/5.7/en/gone-away.html
- Variables wait_timeout and interactive_timeout which control this disconnection are at 30 seconds: http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
Solutions
To avoid the "MySQL server has gone away" error, here are several possible approaches.
Automatic verification and reconnection
Before executing a query, it is recommended to test if the MySQL connection is still active. If the connection has been closed, you can automatically restore it before continuing with your request. Here is an example in PHP:
if (!mysqli_ping($connexion)) {
mysqli_close($connexion);
$connexion = mysqli_connect($host, $user, $password, $database);
}
The function mysqli_ping()
check if the connection is still valid. If it is not, the script closes the connection and opens a new one.
Sending regular "Ping"
Another way is to run a script that regularly sends a "ping" to the database to keep the connection active. P.ex you could create a planned task (cron job) that sends a light request, like SELECT 1;
at regular intervals.
Adjusting MySQL (Cloud Server) Settings
With a Cloud Server, you can increase variable values wait_timeout
and interactive_timeout
since the MySQL menu of your server to extend the duration of the connection before it is closed.