SQLSTATE[HY000] [2002] Connection refused

[BestBudget]

I am running Magento Commerce 2.2.1 and once every 10/15 minutes (seems like this is happening during running my cron) I receive an SQLSTATE[HY000] [2002] Connection refused error message: 

Herewith the last 3 error messages I have received:

{"0":"SQLSTATE[HY000] [2002] Connection refused","1":"#0 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Mysql.php(111): Zend_Db_Adapter_Pdo_Abstract->_connect()\n#1 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(398): Zend_Db_Adapter_Pdo_Mysql->_connect()\n#2 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(460): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_connect()\n#3 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `store_w...', Array)\n#4 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(517): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `store_w...', Array)\n#5 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(580): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_query('SELECT `store_w...', Array)\n#6 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(737): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->query(Object(Magento\\Framework\\DB\\Select), Array)\n#7 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(87): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\\Framework\\DB\\Select))\n#8 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(57): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->getEntities('store_website', 'code')\n#9 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config\/ConfigSourceAggregated.php(40): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->get('')\n#10 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Framework\/App\/Config\/ConfigSourceAggregated\/Proxy.php(95): Magento\\Framework\\App\\Config\\ConfigSourceAggregated->get('')\n#11 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Type\/Scopes.php(63): Magento\\Framework\\App\\Config\\ConfigSourceAggregated\\Proxy->get()\n#12 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config.php(131): Magento\\Store\\App\\Config\\Type\\Scopes->get('stores\/kantoork...')\n#13 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreRepository.php(68): Magento\\Framework\\App\\Config->get('scopes', 'stores\/kantoork...', Array)\n#14 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreManager.php(168): Magento\\Store\\Model\\StoreRepository->get('kantoorkasten')\n#15 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Request\/PathInfoProcessor.php(39): Magento\\Store\\Model\\StoreManager->getStore('kantoorkasten')\n#16 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-backend\/App\/Request\/PathInfoProcessor.php(51): Magento\\Store\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#17 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Backend\/App\/Request\/PathInfoProcessor\/Proxy.php(95): Magento\\Backend\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#18 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(161): Magento\\Backend\\App\\Request\\PathInfoProcessor\\Proxy->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#19 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/HTTP\/PhpEnvironment\/Request.php(212): Magento\\Framework\\App\\Request\\Http->setPathInfo()\n#20 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(233): Magento\\Framework\\HTTP\\PhpEnvironment\\Request->getPathInfo()\n#21 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Http.php(130): Magento\\Framework\\App\\Request\\Http->getFrontName()\n#22 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Bootstrap.php(256): Magento\\Framework\\App\\Http->launch()\n#23 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/index.php(39): Magento\\Framework\\App\\Bootstrap->run(Object(Magento\\Framework\\App\\Http))\n#24 {main}","url":"\/kantoorkasten\/images\/favicon.ico","script_name":"\/index.php"}

{"0":"SQLSTATE[HY000] [2002] Connection refused","1":"#0 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Mysql.php(111): Zend_Db_Adapter_Pdo_Abstract->_connect()\n#1 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(398): Zend_Db_Adapter_Pdo_Mysql->_connect()\n#2 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(460): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_connect()\n#3 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `store_w...', Array)\n#4 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(517): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `store_w...', Array)\n#5 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(580): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_query('SELECT `store_w...', Array)\n#6 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(737): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->query(Object(Magento\\Framework\\DB\\Select), Array)\n#7 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(87): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\\Framework\\DB\\Select))\n#8 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(57): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->getEntities('store_website', 'code')\n#9 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config\/ConfigSourceAggregated.php(40): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->get('')\n#10 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Framework\/App\/Config\/ConfigSourceAggregated\/Proxy.php(95): Magento\\Framework\\App\\Config\\ConfigSourceAggregated->get('')\n#11 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Type\/Scopes.php(63): Magento\\Framework\\App\\Config\\ConfigSourceAggregated\\Proxy->get()\n#12 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config.php(131): Magento\\Store\\App\\Config\\Type\\Scopes->get('stores\/kantoork...')\n#13 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreRepository.php(68): Magento\\Framework\\App\\Config->get('scopes', 'stores\/kantoork...', Array)\n#14 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreManager.php(168): Magento\\Store\\Model\\StoreRepository->get('kantoorkasten')\n#15 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Request\/PathInfoProcessor.php(39): Magento\\Store\\Model\\StoreManager->getStore('kantoorkasten')\n#16 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-backend\/App\/Request\/PathInfoProcessor.php(51): Magento\\Store\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#17 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Backend\/App\/Request\/PathInfoProcessor\/Proxy.php(95): Magento\\Backend\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#18 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(161): Magento\\Backend\\App\\Request\\PathInfoProcessor\\Proxy->process(Object(Magento\\Framework\\App\\Request\\Http), '\/kantoorkasten\/...')\n#19 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/HTTP\/PhpEnvironment\/Request.php(212): Magento\\Framework\\App\\Request\\Http->setPathInfo()\n#20 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(233): Magento\\Framework\\HTTP\\PhpEnvironment\\Request->getPathInfo()\n#21 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Http.php(130): Magento\\Framework\\App\\Request\\Http->getFrontName()\n#22 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Bootstrap.php(256): Magento\\Framework\\App\\Http->launch()\n#23 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/index.php(39): Magento\\Framework\\App\\Bootstrap->run(Object(Magento\\Framework\\App\\Http))\n#24 {main}","url":"\/kantoorkasten\/chemiekasten.html","script_name":"\/index.php"}

{"0":"SQLSTATE[HY000] [2002] Connection refused","1":"#0 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Mysql.php(111): Zend_Db_Adapter_Pdo_Abstract->_connect()\n#1 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(398): Zend_Db_Adapter_Pdo_Mysql->_connect()\n#2 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(460): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_connect()\n#3 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Pdo\/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `store_w...', Array)\n#4 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(517): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `store_w...', Array)\n#5 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/DB\/Adapter\/Pdo\/Mysql.php(580): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->_query('SELECT `store_w...', Array)\n#6 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/zendframework1\/library\/Zend\/Db\/Adapter\/Abstract.php(737): Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql->query(Object(Magento\\Framework\\DB\\Select), Array)\n#7 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(87): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\\Framework\\DB\\Select))\n#8 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Source\/RuntimeConfigSource.php(57): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->getEntities('store_website', 'code')\n#9 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config\/ConfigSourceAggregated.php(40): Magento\\Store\\App\\Config\\Source\\RuntimeConfigSource->get('')\n#10 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Framework\/App\/Config\/ConfigSourceAggregated\/Proxy.php(95): Magento\\Framework\\App\\Config\\ConfigSourceAggregated->get('')\n#11 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Config\/Type\/Scopes.php(63): Magento\\Framework\\App\\Config\\ConfigSourceAggregated\\Proxy->get()\n#12 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Config.php(131): Magento\\Store\\App\\Config\\Type\\Scopes->get('stores\/bureaus')\n#13 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreRepository.php(68): Magento\\Framework\\App\\Config->get('scopes', 'stores\/bureaus', Array)\n#14 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/Model\/StoreManager.php(168): Magento\\Store\\Model\\StoreRepository->get('bureaus')\n#15 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-store\/App\/Request\/PathInfoProcessor.php(39): Magento\\Store\\Model\\StoreManager->getStore('bureaus')\n#16 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/module-backend\/App\/Request\/PathInfoProcessor.php(51): Magento\\Store\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/bureaus\/kantin...')\n#17 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/generated\/code\/Magento\/Backend\/App\/Request\/PathInfoProcessor\/Proxy.php(95): Magento\\Backend\\App\\Request\\PathInfoProcessor->process(Object(Magento\\Framework\\App\\Request\\Http), '\/bureaus\/kantin...')\n#18 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(161): Magento\\Backend\\App\\Request\\PathInfoProcessor\\Proxy->process(Object(Magento\\Framework\\App\\Request\\Http), '\/bureaus\/kantin...')\n#19 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/HTTP\/PhpEnvironment\/Request.php(212): Magento\\Framework\\App\\Request\\Http->setPathInfo()\n#20 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Request\/Http.php(233): Magento\\Framework\\HTTP\\PhpEnvironment\\Request->getPathInfo()\n#21 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Http.php(130): Magento\\Framework\\App\\Request\\Http->getFrontName()\n#22 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/vendor\/magento\/framework\/App\/Bootstrap.php(256): Magento\\Framework\\App\\Http->launch()\n#23 \/home\/rockmart\/domains\/bestbudgetkantoormeubelen.nl\/public_html\/index.php(39): Magento\\Framework\\App\\Bootstrap->run(Object(Magento\\Framework\\App\\Http))\n#24 {main}","url":"\/bureaus\/kantinetafels.html?afmeting_blad=250%2C248%2C251&bladkleur=243&kleur_metaal=217","script_name":"\/index.php"}

Site is running in production mode already and I am not able to identify where these problems come from. I hope anybody is able to help me out. 

[Damian Culotta]

Hi @BestBudget,

The Mysql error 2002 is a problem of connection between the store (the code) and the database.

Can you check if the database is up&running and the credentials are right? (Is more related with the connection than the credentials).

The best idea is to try to connect from your webserver shell using those credentials with something like:

mysql -h YOUR_HOST -u YOUR_USERNAME -p YOUR_DATABASE

[joeridrenth]

Dear @Damian Culotta, thank you for your answer.

We tried several things and I did some investigation together with our hosting partner but it seems MySQL runs out of memory and than gives us this error report. This should not be possible, since we work on quite a large VPS server while the website is not very big at all.

To reproduce the problem I have tried to run the magento cron:run command a couple of times and it takes more than 5 minutes to complete. Sometimes (this is when we get the error) it does not complete at all and this results in the SQLSTATE[HY000] [2002] Connection refused error. Our hosting partner told me that it is not a refused connection, but an out of memory issue. 

Is there a way to check what the magento cron:run command is doing exactly? I think there is something wrong with the cron but I cannot figure out what it is.

[BestBudget]

@Damian Culotta sorry, I replied with the wrong account but the question remains the same. 

[Damian Culotta]

Hi @BestBudget,

I guess I could start to check on cron_schedule table to see if there is some clue about the error.

Also, you should check your magento's cron logs. If you followed this instructions http://devdocs.magento.com/guides/v2.2/config-guide/cli/config-cli-subcommands-cron.html you should find 3 log files related with cronjobs.

[BestBudget]

Dear @Damian Culotta, I have checked the cron_schedule table today and it seems there are more than 200.000 records and 99,9% of these records have a pending status. Are you familiar with this problem? This table makes the database grow with a couple of MB's a day.

[Damian Culotta]

Hi @BestBudget,

Well... I've never got a 200k pending cronjobs so I'm not sure how to move forward.

I guess you should clean the table and start fresh (dev or stage environment, make a backup before delete information).

Also, you should check those cronjobs to see if everything is working properly.
Can you check also your Mysql server logs? The first questions was about "Connection refused" and now you said you have 200k cronjobs... maybe there is another clue there.

[BestBudget]

@Damian Culotta thank you for your help! I was not aware of the cron_schedule table, but I managed to fix the problem. As I told you before there were more than 200.000 entries pending in cron_schedule and this puts an excessive load on the server. So I tried:

TRUNCATE cron_schedule;

 
After this less than 1000 entries remained, but almost all of these entries have a success status or are pending to run in the future. I checked if pending turns into success now and this is the case. Instead of taking minutes for the cron to run, it now finishes in less than a second now. 

[Damian Culotta]

Perfect!

Here (Stores -> Configuration -> Advanced -> System) you'll see how cronjbs are processed and cleaned up.

Cronjobs options

Cheers!