The MySQL server error is gone, it means that the MySQL server (mysqld) timed out and the connection was closed. By default, if nothing happens, MySQL will close the connection after eight hours (28800 seconds). However, in some cases, your web host, DBA or app developer may have lowered this timeout setting, which is discussed below.
The MySQL server has gone down, which can be a frustrating error to resolve. This is partly due to this error being resolved; Sometimes the solution involves multiple layer, application, or service configuration changes. This article covers the solutions I have seen for this MySQL Server Common Error. If you found a solution that is not listed or linked on this page, please send me a note or leave a comment.
The MySQL Server Error Log example is gone.
Keep in mind that this error can be logged in a few ways, as listed below. Also, sometimes, the error is simply a sign of a deeper underlying problem. Meaning the error can be caused by a problem or a bug in your connecting application or remote service. In this case, you need to check all associated error logs with the same timestamp to determine if another problem could be to blame. Application performance monitoring solutions and php stack trace tool can be helpful. With that in mind, here are error log examples with the MySQL server error gone:
General error: 2006 MySQL server has gone away
Error Code: 2013. Lost connection to MySQL server during query
Warning: Error while sending QUERY packet
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
The reason why the MySQL server goes down is often because of MySQL’s error. wait_timeout was crossed. MySQL wait_timeout is the number of seconds the server waits for activity on non-interactive connections before shutting down. you should make sure that wait_timeout Not set too low. default for mysql wait_timeout is 28800 seconds. Often, this is arbitrarily reduced. That said, the low you can set wait_timeout without affecting the database connection can be a good indicator of MySQL database efficiency. Also, check the variables: net_read_timeout, net_write_timeout And interactive_timeout, Adjust or add the following lines to my.cnf to meet your needs:
wait_timeout=90 net_read_timeout=90 net_write_timeout=90 interactive_timeout=300 connect_timeout=90
MySQL Connect Timeout in PHP Configuration
Take a look at your php.ini config file. you will know MySQL configuration options, Make sure that mysql.connect_timeout setting is not set less than mysql wait_timeout, discussed above. PHP Options mysql.connect_timeout Not only used for connect timeouts. This also happens while waiting for the first response from the MySQL server. try to increase mysql.connect_timeout to match or exceed your MySQL wait_timeout and make sure mysql.allow_persistent is on (default = enabled).
Necessary: read about first PHP persistent database connection To understand the benefits and warnings.
Also, adjust PHP default_socket_timeout, For example, a PHP script may be running a slow query. creating a wait that uses default_socket_timeout, Eventually, it exits with a “MySQL server has gone away” error. Before sending hate mail, please read here first, Here is an excerpt:
“PHP, by default, sets a read timeout of 60s for the stream. This is set via php.ini, default_socket_timeout. This default applies to all streams that do not set any other timeout values. mysqlnd doesn’t set any other values and so Connections for long running queries may be disconnected after default_socket_timeout seconds resulting in an error message
2006 – MySQL server is gone,
To live across, also adjust max_execution_time And max_input_time still in php.ini, if needed. If the execution time of PHP is . More than max_execution_timeSo the MySQL server may be disconnected.
max_execution_time = 90 max_input_time = 90
max_allowed_packet There is a maximum size of a packet. The default size of 4MB helps the MySQL server to catch large (possibly incorrect) packets. As of MySQL 8, the default has been increased to 16MB. If mysqld receives a packet that is too large, it assumes that something is wrong and closes the connection. To fix this, you should increase max_allowed_packet in my.cnf, then restart MySQL. The maximum for this setting is 1GB. for example:
max_allowed_packet = 512M
you may need to increase innodb_log_file_size MySQL variable in your my.cnf configuration. of MySQL innodb_log_file_size should be 25% of innodb_buffer_pool_size (not less than 20% if possible). Remember that the larger this value, the longer it will take to recover from a database crash. (Source: Phpmyadmin Consultant,
This means for example: if the size of your buffer pool is set to innodb_buffer_pool_size=16g And yours innodb_log_files_in_group The setting is still set to the recommended default of 2 files (innodb_log_files_in_group=2,then yours innodb_log_file_size should be set to 2G, This will create two (2) log files at 2GB each, which is 25% of . Is equal to innodb_buffer_pool_size=16g,
Warning: You must shutdown the MySQL server to change innodb_log_file_size either innodb_log_files_in_group, If you don’t, you risk disaster! (Reading: MySQL Log Reset Instructions,
Other reasons the MySQL server has gone away
remote mysql connection
Remember that earlier I mentioned that the error is, sometimes, just a sign of a deeper underlying problem. For example, remote MySQL connections third party services, using the a third party payment processing Plugin for osCommerce, Magento, etc.
MySQL database charset and collation
Changing the default database charset to latin1 and changing the default collation to latin1_general_ci seems to work Resolved MySQL server has been down for some time,
MySQL max_connections setting exceeded
max_connections Set the maximum allowed number of simultaneous client connections. Be careful with this setting!! Setting too high can lead to memory and other resources depletion and also increases scheduling overhead. As a guide, set max_connections To almost double the previous number of maximum simultaneous client connections. For example, if after one month of uptime, the maximum simultaneous client connections were 114, set max_connections=250. Before you go crazy about this setting, please read: How does MySQL handle client connections,
still unsolved? See MySQL’s help page.
Oracle has a good The self help page for the MySQL server is gone errors. On that page, they also suggest that you make sure MySQL doesn’t stop/restart during the query. Part:
“You can check if the MySQL server is dead and restarted by executing” mysqladmin version And checking the uptime of the server. If the client connection was lost because mysqld Crashed and restarted, you should focus on finding the cause of the crash. ,
# mysqladmin version mysqladmin Ver 9.1 Distrib 10.1.40-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.1.40-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 20 days 11 hours 49 min 40 sec Threads: 5 Questions: 1030744326 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.150
# mysqladmin status Uptime: 1770590 Threads: 4 Questions: 1030752268 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.151
I hope this helps!
Published: June 7, 2019 | Last Updated: August 10, 2021