What the message looks like
ERROR 2006 (HY000): MySQL server has gone away
PHP Warning: mysqli::query(): MySQL server has gone away in /var/www/app/db.php on line 42Common causes
- The connection was idle longer than
wait_timeout, the server closed it, and the app reused the dead connection, typical for long-running scripts and workers. - A query or a row is larger than
max_allowed_packet, for example a big import or a blob. - The server crashed or was restarted, often killed by the OOM killer, so all connections are gone.
- A firewall, load balancer or proxy cuts idle connections earlier than the database does.
- The connection was ended with
KILLor the server hit another limit.
Check
- Idle timeout
mysql -e "SHOW VARIABLES LIKE 'wait_timeout'" - Maximum packet size
mysql -e "SHOW VARIABLES LIKE 'max_allowed_packet'" - Did the server restart? Uptime in seconds
mysql -e "SHOW GLOBAL STATUS LIKE 'Uptime'" - Crash, kill or restart in the log
Fix
- If
Uptimeis small or the log shows a kill, the server restarted. Find out why first (memory, crash). The error is only the consequence. - For idle connections: let the app reconnect on this error, or check the connection before use (ping). Pools should recycle connections earlier than
wait_timeout. - For big queries and imports raise the limit on the server, for example
max_allowed_packet = 64Min[mysqld], and on the client too (mysql --max-allowed-packetormysqldumpsetting). The value is an example, size it to your data. - Align the idle timeout of the firewall or proxy with
wait_timeout, or send keepalives. - Do not simply set
wait_timeoutvery high. It piles up idle connections and leads to Too many connections.
Typical scenario
A 200 MB import file cannot be loaded with mysql db < dump.sql. After a few minutes "ERROR 2006 (HY000) at line 1234: MySQL server has gone away" appears. A single INSERT with very many rows probably exceeds max_allowed_packet. First look at SHOW GLOBAL STATUS LIKE 'Uptime': if the value is small, the server restarted, and you look in the error log for the reason.
FAQ
Why does it only happen after a long pause?
The server closed the idle connection after wait_timeout and the app tried to use it afterwards. The fix is on the client side: reconnect or recycle.
It happens only on big imports.
Then the packet is bigger than max_allowed_packet. Raise it on both server and client, or split the import into smaller statements.
How do I tell 2006 from 2013 "Lost connection"?
With 2006 the client noticed that the connection was already gone when it wanted to send the query, for example after a timeout. With 2013 the connection dropped during a running query, for instance because of a crash, network problem or timeout. The causes overlap, but diagnosis starts in the same log.
Does max_allowed_packet have to be set on client and server?
Yes, both sides have their own limit and the smaller one applies. On the server it is in the [mysqld] section; for the command-line program in the [mysql] section or as the option --max-allowed-packet. On the server a restart or SET GLOBAL for new connections is enough.
Where do I see why the server ended the connection?
In the server's error log, whose location SHOW VARIABLES LIKE 'log_error' shows. It holds restarts, crashes and messages such as "Aborted connection", often with the reason. In addition, SHOW GLOBAL STATUS LIKE 'Aborted_%' counts aborted clients and connections.