15

I'm having loads of weird issues on Magento CE 1.7.0.2. During normal operations, the site will occasionally produce a Magento Error Page (There has been an error processing your request) on both frontend and backend. Viewing the associated report, I see the following message:

"SQLSTATE[HY000] [2006] MySQL server has gone away"

Sometimes, but more rarely, the report message will read:

 Connection reset by peer

I have looked at the var > log > system.log and the MySQL has gone away error is accompanied by the following:

Warning: PDO::__construct(): MySQL server has gone away  in /var/www/html/domain.com/live/lib/Zend/Db/Adapter/Pdo/Abstract.php on line 129
Error while reading greeting packet. PID=1863  in /var/www/html/domain.com/live/lib/Zend/Db/Adapter/Pdo/Abstract.php on line 129

In addition to this, the following error seems to be happening on every request, as well as the MySQL has gone away errors:

 Warning: include(File.php): failed to open stream: No such file or directory  in /var/www/html/domain.com/live/lib/Varien/Autoload.php on line 93
 Warning: include(): Failed opening 'File.php' for inclusion

I have looked most of the articles I can find about this and tinkered with the database parameters till the cows came home but the error remains.

After following another QnA about the compiler, I notice that the admin page System > Tools > Compilation is completely blank. I think these are all related errors but any insight into debugging or causes would be so helpful.

I apologise if this is incoherent; I've been awake for about 42 hours, so please ask for any clarification. Thank you.

-- update --

My server stack for clarity:

PHP 5.5.4 (PHP-FPM)
Nginx 1.4.2
MySQL 5.5.33

-- update --

It occurs to me (after some sleep) that I never specified - the PHP codebase and the MySQL db are on separate hardware servers - very important to know if ya'll are going to help me!! I apologise.

Improve this question
5
  • 1
    Are you using persistent database connections? If so, try disabling them. I'd also check the mysql logs to see if there are errors there or if it's actually restarting vs just the connection being dropped.
    – davidalger
    Commented Oct 7, 2013 at 0:27
  • Thx David, tailing the MySQL logs and the DB is never hit when the error occurs. I was using persistent connections, disabling didn't help :(
    – Jongosi
    Commented Oct 7, 2013 at 6:45
  • 1
    Have you considered that the connection is bad. A cause of that error is that the DB never got the msg. Try debugging by using the connection info from local.xml to call mysqli functions. See what happens.
    – SH-
    Commented Oct 7, 2013 at 23:59
  • thank you, seemed to have fixed my problem editing the local file
    – user28632
    Commented Sep 19, 2015 at 21:41
  • devdocs.magento.com/guides/v2.2/install-gde/trouble/…
    – Khoa TruongDinh
    Commented Oct 16, 2017 at 4:18

7 Answers 7

Reset to default
10

This is mostly due to any of the below two reasons

  1. Server timed out and closed the connection.
    fix: try increasing the wait_timeout variable in your mysqld’s my.cnf/my.ini configuration file.
  2. Server dropped an incorrect or too large packet.
    fix : increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf/my.ini file.

Please check in files if you are trying to get something that is taking too long or inapplicable.

Improve this answer
3
  • Thx Anshu, I am following the MySQL query log and the database is never hit with a request. Also, if I reboot the server, an error can sometimes occur within 20 seconds of the server being online - much too short for even default settings to timeout. I set max_allowed_packet to 2G and wait_timout to 86400, still no help.
    – Jongosi
    Commented Oct 7, 2013 at 6:43
  • Please check if database connection is proper, check your app/etc/local.xml file
    – Anshu Mishra
    Commented Oct 7, 2013 at 6:46
  • Thx Anshu, yes, it's correct - connection happens about 68% of requests
    – Jongosi
    Commented Oct 7, 2013 at 6:54
6

Issue resolved! Thanks to all for the help. This was a hardware firewall issue with the web host, even after they were disabled by us.

As confirmed by 1&1's server team, the hardware firewalls were correctly configured, but they were incorrectly intercepting valid traffic between the file server and the db server about 25% of the time.

We have configured iptables instead and shutdown the hardware firewalls entirely. 100% availability now.

Improve this answer
2
  • 2
    Oh my! Sporadic firewall… gotta love that sort of issue. Glad you got it sorted. :)
    – davidalger
    Commented Oct 8, 2013 at 23:59
  • Same problem here and it looks like your solution will work out for us too. Gonna talk 1&1 too. Did the support help you in any way? Can I contact you? Twitter? Facebook? Please see my profile for details. Thanks
    – webDEVILopers
    Commented Mar 2, 2015 at 9:52
2

I experienced the same issue for Magento 2.1 and my mysql error log showed the following error multiple times during the "MySQL has gone away" process:

...[Warning] File Descriptor 1228 exceeded FD_SETSIZE=1024

To potentially resolve this issue, firstly check the open files value with $ ulimit -n, which in my case was 256.

Secondly, add table_open_cache = {that ulimit -n value} under the [mysqld] section in your my.cnf.

Now restart MySQL and hopefully you're back into the action.

Note: I'm running Magento 2.1 locally on OS X El Capitan with PHP 7.1 and MySQL 5.7.15 build with Homebrew. But I bet this solution would work on older or different setups too.

Improve this answer
1

Edit the app/etc/local.xml file in your Magento folder, replacing the entry for the host to be '127.0.0.1' instead of 'localhost'.

Improve this answer
1
  • The database is on a separate hardware server, so the IP address of the MySQL server is used.
    – Jongosi
    Commented Mar 2, 2015 at 10:23
1

Experienced the same error when migrating a large database between 2 servers.

Temporarily adding the following in the mysql configuration file (/etc/mysql/my.cnf) on my local (destination) server & restarting mysql (service mysql restart) fixed the problem for me:

max_allowed_packet      = 160M
wait_timeout            = 28800000
Improve this answer
0

@Dexters answer worked for me.

Just to update the answer for Magento 2.3.5 (and the last few), those setting are now in app/etc/env.php instead of app/etc/local.xml

Improve this answer
1
0

Copying an answer from Github here for anyone running into this issue during a Magento 2 migration.

Try setting the <bulk_size> to 500 or so in your config.xml

Depending on the size of your catalog, the tool can end up sending some ridiculously large packets to MySQL that exceed the 1G max_allowed_packet setting.

See MySQL documentation:

The largest possible packet that can be transmitted to or from a MySQL 8.4 server or client is 1GB.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.