Why are database driven Magento websites popular? It is precisely because of their robust tracking system.
Hence, database management is crucial for Magento Website.
Often, due to bad settings in the MySQL configuration files, Magento websites show errors like 2006 MySQL server has gone away
We know this because, at 1 onlyhost, we often get requests from our customers to fix Magento MySQL errors as part of our Server Management Services.
Therefore, in this article, we discuss how 1 onlyhost Support Engineers can help you fix problems of this nature quickly and efficiently.
What, When and Why MySQL server has gone away error?
Magento stores all information in MySQL databases.
Managing Database is crucial when you are in charge of a Magento website. However, it can be quite tricky. Even small mistakes may lead to website errors or even complete data loss.
The error isSQLSTATE[HY000]: General error: 2006 MySQL server has gone away a common error faced by most of the website owners while they manage a database. Mostly, this error can happen when we connect to the Magento database or when trying to create a database. The error on the Magento panel looks like.
Here, the code SQLSTATE indicates an error condition in the database. Also. HY000 indicates the “general error” description.
From our experience in managing MySQL servers, there are various reasons for this error. It includes:
- Server time out settings
- Improper query limit setting
- Insertion of too many rows
- Firewall block
- Hostname lookup failure
How we fixed error 2006 MySQL server has gone away on Magento
Now, we’ll see the information about why this error occurred and how our Support Engineers fixed it.
1. Time out setting in MySQL configuration
Often, this error SQLSTATE[HY000]: General error: 2006 MySQL server has gone away happens when there is an unavailability of the MySQL server. In such cases, MySQL stops working as such. This can be due to the memory shortage in the server or because of bad network connection that prevents server access.
Sometimes, improper setting in MySQL configuration triggers the unavailability of MySQL server. For example, when the parameters like wait_timeout are shorter than 10 seconds, it can lead to errors.
To fix this problem, our Support Engineers check the MySQL configuration at my.cnf/my.ini file and tweak this value as per the MySQL query requirements.
On Linux servers,
We open /etc/my.cnf and modify the value of wait_timeout. Also, we set the option, reconnect to 1, that makes sure that MySQL tries to reconnect after a connection failed attempt. The final configuration will be like:
[mysqld]
wait_timeout = 60
reconnect = 1After that, we restart the MySQL service using it.
/etc/init.d/mysql restart
On Windows servers:
We open /path-to-mysql-installation/bin/my.ini, set wait_timeout = 60 seconds.
2. Improper query limit setting
Similarly, another reason for this error could be improper query limit settings in the MySQL configuration file. If a MySQL client or MySQL server gets a packet that is incorrect (out of order) or larger than max_allowed_packet bytes, this error may occur. And, if the communication packet is too large we can lose connection to the MySQL server and result in query error.
So, our Support Engineers solved this issue by increasing the query limit, max_allowed_packet variable in the MySQL configuration file.
On Linux,
We open /etc/my.cnf and increase the max_allowed_packet value.
[mysqld]
max_allowed_packet=256M
On Windows:
Similarly, we open /path-to-mysql-installation/bin/my.ini, set max_allowed_packet = 256M.
3. Firewall block
Most of the time firewall block can create problems with a new Magento installation. By default, MySQL uses port 3306 for establishing the connection. Mostly, every server uses a firewall to protect them from unauthorized access. Therefore, this firewall restriction also might be a reason for 2006 MySQL server has gone away error on the Magento server.
Also, the error can occur when the MySQL port is blocked by the firewall, thus preventing any connections to the MySQL server.
Likewise, customers may also see the MySQL server has gone away error if MySQL is started with the option--skip-networking.
Therefore, our Support Engineers fix this problem by tweaking the firewall configuration.
[Getting error 2006 MySQL server has gone away on the Magento server?We’ll fix it for you.]
Conclusion
In short, error 2006 MySQL server has gone away on Magento can happen due to improper settings in MySQL configuration file such as query limit setting, timeout setting and much more. Today, we saw the reasons for the error and how our Support Engineers fix it.
