2

I have a series of stock importers, all of which run of mostly the same code, each with a few tweaks, with regards to how they fetch their stock data from different suppliers.

These all run off cron, however, I'm running into issues with one of them - it fetches its data by API which is rate limited to 1 req/s, because of this the data fetching takes about 15 minutes. After this, it attempts to get the size of a previously fetched product collection, for logging purposes. At this point the process crashes out with a 2006 MySQL server has gone away error.

I've tried increasing various timeouts and changing some MySQL settings to no avail, e.g.

        ini_set('mysql.connect_timeout', 14400);
        ini_set('default_socket_timeout', 14400);
        ini_set('mysql.allow_persistent', 1);

I've been talking to the server hosting company who insist it's a code issue, but it still feels like some form of timeout.

What settings should I be looking into? It always crashes out at the same point if data is fully fetched - if I modify the script to test, e.g. by using dummy data instead of taking the time to fetch it from the supplier, no SQL error occurs.

Improve this question
6
  • Additional information request. Post on pastebin.com and share the links. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME, if you can get there C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis.
    – Wilson Hauck
    Jul 24, 2019 at 23:36
  • Still willing to assist, if additional info could be made available.
    – Wilson Hauck
    Sep 4, 2019 at 19:01
  • Hey Wilson, As I mentioned in my other comment, it was due to the mysql connection timing out. I closed the connection manually before the long running data fetch and the process stopped erroring. While it's not an ideal solution it'll do
    – eddhall
    Sep 4, 2019 at 20:36
  • Would you like to process your queries faster? If so, please post requested information for suggestions.
    – Wilson Hauck
    Sep 4, 2019 at 21:38
  • This question has never been about processing the queries faster...
    – eddhall
    Sep 5, 2019 at 11:19

1 Answer 1

Reset to default
0

It's tricky this sort of error as MySQL server has gone away is quite a generic message and is caused by lots of different reasons.

wait_timeout is another

Try these queries

SHOW VARIABLES LIKE 'wait_%';

SHOW global VARIABLES LIKE 'wait_%';

SHOW session VARIABLES LIKE 'wait_%';

Value is seconds. Normally defaults to 28800

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeout

max_allowed_packet is another setting you should check.

But basically the application is doing something that the database server doesn't like. Whether that is take too long or create too large a packet. So if the server isn't working particular hard (mysql isn't constantly at 100% CPU) you could say this is a misconfiguration.

However it could be an indication of inefficient coding. Basically massive queries and/or processes that need to be broken up. Or PHP is busy doing other stuff and MySQL is waiting. Like waiting for an API to respond. This results in time between the start and commit of a SQL transaction. But normally these sort of issue results in lock wait timeouts.

Improve this answer
2
  • The connection with the DB is definitely timing out, the error occurs at the very first point at which the script tries to contact the DB again. The issue has been resolved by closing the connection manually using Mage::getSingleton('core/resource')->getConnection('read')->closeConnection();, but this does not feel reliable, as if anything else prompts a db connection between this close and the end of slow portion of the script the same error will occur again
    – eddhall
    Jul 7, 2019 at 16:12
  • @eddhall Please post the data requested July 24 and I will try to help you. Thanks
    – Wilson Hauck
    Aug 24, 2019 at 17:25

Your Answer

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

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