If you're a fond MySQL and/or MariaDB user you most certainly experienced at least one of these common errors:
MySQL server has gone away
Error reading result set's header
Error executing query
MySQL server has gone away for query
2006, MySQL server has gone away
Packets out of order. Expected X received Y. Packet size=Z
... and so on. You can find these warnings basically anywhere, as they are usually written in the log files of the applications, tools and services that try to connect to your database and fail: however, in most scenarios, they are firstly noticed by the zealous System Administrator inspecting the PHP error log file (or the Joomla or WordPress counterpart) while trying to understand why his beloved web site or service isn't working like it should.
That said, let's try to understand the underlying cause and see what we can do to overcome them.
There are a number of possible reasons that could cause these issues: hovewer, since they are all related to connection issues, nine times out of ten we can fix them by looking our MySQL or MariaDB configuration file - my.ini for Windows, my.cnf for Linux - and tuning up some underrated system variables that could hinder our server from working properly under heavy load.
Here's the checklist that might save your day:
- Ensure that the MySQL server instance is properly running and that is still accessible by the server that issues the call: check the port availability (3306 being the default one, unless you changed it) and ensure that there are no firewalls, proxies, TCP filters, port-scanners or any other software that might affect it.
- There's a good chance that the server is dropping an incorrect or too large packet sent by the client: when this happens, MySQL usually assumes that something has gone wrong with the client and shuts down the connection. To fix that, check the max_allowed_packet variable value and set it to a very high value (such as max_allowed_packet=2G ). Based from our experience this is the most common scenario, so it can be wise to try this before anything else.
- The issue might be due to the fact that the server timed out and closed the connection. Check the wait_timeout MySQL variable value and ensure it's large enough: once done, check the interactive_timeout as well. Be aware that the default value for both of these variables is 28800 seconds in MySQL 5.7+.
- If anything else fails, try upgrading your PHP and/or your MySQL instances to the latest versions.
Additionally, if you're getting the Packets out of order error in the PHP log, there's an high chance that the issue is due to the fact that there's an incompatibility between the PHP and MySQL versions you're using. If upgrading them is not an option, you could try to add the following statement to the PHP script that causes the issue:
|
1 |
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); |
This will tell PDO (aka PHP Data Objects) to emulate the prepared statements instead of using the MySQL native ones. This is not something that you should normally do, but if you're not using the most up to date version of MySQL and PHP that might fix your issue for good. For additional info regarding this specific topic, check this MySQL bug page.
Regardless of what you do, always remember to restart the MySQL server if you change something in your my.ini or my.cnf file.
If this post is not enough to help you, we strongly suggest to look at this official excerpt from the MySQL reference Manual dedicated to the MySQL Server has gone away error, where you'll be able to find more possible causes and other viable workarounds.
That's it for now: good luck with your server!
