8

After moving to a new server I am getting the MySQL crash [1] issue once a day, which is coming to my email and annoying not to mention potential impact. Any hints on how to debug this issue?

Obviously crash happens on $schedule->save() so I tried to wrap it with a try ... catch but that didn't help

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Trace:
#0 /var/www/vhosts/site/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(305): PDO->beginTransaction()
#1 /var/www/vhosts/site/store/lib/Zend/Db/Adapter/Abstract.php(495): Zend_Db_Adapter_Pdo_Abstract->_beginTransaction()
#2 /var/www/vhosts/site/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(219): Zend_Db_Adapter_Abstract->beginTransaction()
#3 /var/www/vhosts/site/store/app/code/core/Mage/Core/Model/Resource/Abstract.php(76): Varien_Db_Adapter_Pdo_Mysql->beginTransaction()
#4 /var/www/vhosts/site/store/app/code/core/Mage/Core/Model/Abstract.php(313): Mage_Core_Model_Resource_Abstract->beginTransaction()
#5 /var/www/vhosts/site/store/app/code/core/Mage/Cron/Model/Observer.php(125): Mage_Core_Model_Abstract->save()
#6 /var/www/vhosts/site/store/app/code/core/Mage/Core/Model/App.php(1338): Mage_Cron_Model_Observer->dispatch(Object(Varien_Event_Observer))
#7 /var/www/vhosts/site/store/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mage_Cron_Model_Observer), 'dispatch', Object(Varien_Event_Observer))
#8 /var/www/vhosts/site/store/app/Mage.php(447): Mage_Core_Model_App->dispatchEvent('default', Array)
#9 /var/www/vhosts/site/store/cron.php(46): Mage::dispatchEvent('default')
#10
{main}

Timeout settings:

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 30       |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 3600     |
+----------------------------+----------+
10 rows in set (0.00 sec)
  • 1
    This is PHP losing it's connection with MySQL. Knowing magento it's probably because the cron.php file is taking hours to run. Try looking at your MySQL timeout settings... – Matt Humphrey Feb 11 '13 at 14:45
  • Could you check mysql LOG ! most likely mysql crashing and restarting once you try to query that table – Meabed Feb 11 '13 at 14:46
  • @MattHumphrey problem is it's happening only once a day while cron.php runs every 15 minutes, timeouts are already pretty high – Zifius Feb 11 '13 at 14:53
  • 1
    I don't think this is a Magento specific question. What you're describing is a connection timeout on a MySQL server, which normally is restored by setting a reconnect option on the connection and pinging the server prior to use. I'd look at your MySQL configuration (my.cnf) to see what the timeout is and increasing it. See stackoverflow.com/questions/4284194/… for details. – Karlson Feb 11 '13 at 14:54
  • @Zifius What are the timeout settings? – Karlson Feb 11 '13 at 14:55
5

As others have said it's likely triggered by a long running script. Any script that takes a long time to run without using the database can potentially timeout.

I've had this happen before. We have a script that connects to a remote server, downloads a few hundred xml files, parses and converts them into a .csv file for import via the built in Magento ImportExport module. We have a custom logging module, which allows us to track what happened with our routines. The very first thing the class does, is add a row to this log table to say routine started. It then takes 5-10 minutes to fetch the remote xml files. After downloading the files it attempts to write another log entry to say that it's finished. Since the mysql connection has been open since the first log event and hasn't been used since, mysql has closed the connection as it has received no query for longer than the timeout period.

  • Yeah, seems to be the culprit taking in account that cron jobs are ran above the line that saves the entry. Added more logging to find out which job is that – Zifius Feb 13 '13 at 14:51
3

In your /etc/mysql/my.cnf try increasing the value for max_allowed_packet

Eg.

max_allowed_packet = 256M

Then restart MySQL.

  • Right now it's at 64M, will try increasing. I also see a lot of "Too late for the schedule." exceptions, must be some heavy job running too long – Zifius Feb 12 '13 at 9:27
  • Disabled FPC crawler as per your recommendation in other question, let's see how it goes – Zifius Feb 12 '13 at 9:44
  • This is the most frequent cause of the issue when we run into this error. – davidalger Feb 19 '13 at 14:28
1

If you ask me, it is not a good idea to keep a mysql connection open for hours. So the alternative is, to check, wether the connection still exists, if no, open a new one.

  • It's a core code, but yes, you are right :) Just need to track down the job that runs so long now – Zifius Feb 13 '13 at 14:51
  • maybe there is a observer you can use to check, wether the connection exists? – Fabian Blechschmidt Feb 13 '13 at 14:56
  • I think I just need to find and fix that job :) – Zifius Feb 13 '13 at 15:03
  • Depending on how much storeviews, categories and products you have, it can be an indexer and this might just take time. But yes, "just fix the job" and the problem is gone :D – Fabian Blechschmidt Feb 13 '13 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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