{"action":"created","issue":{"url":"https://api.github.com/repos/mailchimp/mc-magento/issues/210","repository_url":"https://api.github.com/repos/mailchimp/mc-magento","labels_url":"https://api.github.com/repos/mailchimp/mc-magento/issues/210/labels{/name}","comments_url":"https://api.github.com/repos/mailchimp/mc-magento/issues/210/comments","events_url":"https://api.github.com/repos/mailchimp/mc-magento/issues/210/events","html_url":"https://github.com/mailchimp/mc-magento/issues/210","id":224358528,"number":210,"title":"max_user_connections exceeded when cron is run","user":{"login":"CMeldgaard","id":14794642,"avatar_url":"https://avatars2.githubusercontent.com/u/14794642?v=3","gravatar_id":"","url":"https://api.github.com/users/CMeldgaard","html_url":"https://github.com/CMeldgaard","followers_url":"https://api.github.com/users/CMeldgaard/followers","following_url":"https://api.github.com/users/CMeldgaard/following{/other_user}","gists_url":"https://api.github.com/users/CMeldgaard/gists{/gist_id}","starred_url":"https://api.github.com/users/CMeldgaard/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CMeldgaard/subscriptions","organizations_url":"https://api.github.com/users/CMeldgaard/orgs","repos_url":"https://api.github.com/users/CMeldgaard/repos","events_url":"https://api.github.com/users/CMeldgaard/events{/privacy}","received_events_url":"https://api.github.com/users/CMeldgaard/received_events","type":"User","site_admin":false},"labels":[{"id":602876326,"url":"https://api.github.com/repos/mailchimp/mc-magento/labels/need%20feedback","name":"need feedback","color":"5319e7","default":false}],"state":"open","locked":false,"assignee":{"login":"Santiagoebizmarts","id":8330778,"avatar_url":"https://avatars0.githubusercontent.com/u/8330778?v=3","gravatar_id":"","url":"https://api.github.com/users/Santiagoebizmarts","html_url":"https://github.com/Santiagoebizmarts","followers_url":"https://api.github.com/users/Santiagoebizmarts/followers","following_url":"https://api.github.com/users/Santiagoebizmarts/following{/other_user}","gists_url":"https://api.github.com/users/Santiagoebizmarts/gists{/gist_id}","starred_url":"https://api.github.com/users/Santiagoebizmarts/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Santiagoebizmarts/subscriptions","organizations_url":"https://api.github.com/users/Santiagoebizmarts/orgs","repos_url":"https://api.github.com/users/Santiagoebizmarts/repos","events_url":"https://api.github.com/users/Santiagoebizmarts/events{/privacy}","received_events_url":"https://api.github.com/users/Santiagoebizmarts/received_events","type":"User","site_admin":false},"assignees":[{"login":"Santiagoebizmarts","id":8330778,"avatar_url":"https://avatars0.githubusercontent.com/u/8330778?v=3","gravatar_id":"","url":"https://api.github.com/users/Santiagoebizmarts","html_url":"https://github.com/Santiagoebizmarts","followers_url":"https://api.github.com/users/Santiagoebizmarts/followers","following_url":"https://api.github.com/users/Santiagoebizmarts/following{/other_user}","gists_url":"https://api.github.com/users/Santiagoebizmarts/gists{/gist_id}","starred_url":"https://api.github.com/users/Santiagoebizmarts/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Santiagoebizmarts/subscriptions","organizations_url":"https://api.github.com/users/Santiagoebizmarts/orgs","repos_url":"https://api.github.com/users/Santiagoebizmarts/repos","events_url":"https://api.github.com/users/Santiagoebizmarts/events{/privacy}","received_events_url":"https://api.github.com/users/Santiagoebizmarts/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2017-04-26T06:34:51Z","updated_at":"2017-05-19T03:42:21Z","closed_at":null,"body":"Have installed the latest version of the module, but everytime it runs the cronjob, it exceeds the max_user_connections to the database, which means customers risk getting an error page on the frontend.\r\n\r\nThis is the systemlog thats generated of the error:\r\n```\r\na:5:{i:0;s:106:\"SQLSTATE[HY000] [1226] User '******' has exceeded the 'max_user_connections' resource (current value: 20)\";i:1;s:1602:\"#0 /var/www/hike.dk/public_html/lib/Zend/Db/Adapter/Pdo/Mysql.php(111): Zend_Db_Adapter_Pdo_Abstract->_connect()\r\n#1 /var/www/hike.dk/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(320): Zend_Db_Adapter_Pdo_Mysql->_connect()\r\n#2 /var/www/hike.dk/public_html/lib/Zend/Db/Adapter/Abstract.php(460): Varien_Db_Adapter_Pdo_Mysql->_connect()\r\n#3 /var/www/hike.dk/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SET NAMES utf8', Array)\r\n#4 /var/www/hike.dk/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SET NAMES utf8', Array)\r\n#5 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/Resource.php(179): Varien_Db_Adapter_Pdo_Mysql->query('SET NAMES utf8')\r\n#6 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/Resource.php(110): Mage_Core_Model_Resource->_newConnection('pdo_mysql', Object(Mage_Core_Model_Config_Element))\r\n#7 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/Resource/Setup.php(141): Mage_Core_Model_Resource->getConnection('core_setup')\r\n#8 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/Resource/Setup.php(234): Mage_Core_Model_Resource_Setup->__construct('core_setup')\r\n#9 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/App.php(417): Mage_Core_Model_Resource_Setup::applyAllUpdates()\r\n#10 /var/www/hike.dk/public_html/app/code/core/Mage/Core/Model/App.php(343): Mage_Core_Model_App->_initModules()\r\n#11 /var/www/hike.dk/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)\r\n#12 /var/www/hike.dk/public_html/index.php(87): Mage::run('', 'store')\r\n#13 {main}\";s:3:\"url\";s:47:\"/mailchimp/webhook/index/wkey/f0f608f8dbf1c41d/\";s:11:\"script_name\";s:10:\"/index.php\";s:4:\"skin\";s:7:\"default\";}\r\n```\r\n\r\nBeen in contact with hosting company, and they can see that in one day mailchimp made more than 25.000 POST request to the website. And its the same webhook url all the time.\r\n\r\nSome of the calls return 200 other returning 503.\r\n\r\nThis means im getting around 15-30 error reports in var/reports every 5min."},"comment":{"url":"https://api.github.com/repos/mailchimp/mc-magento/issues/comments/302600930","html_url":"https://github.com/mailchimp/mc-magento/issues/210#issuecomment-302600930","issue_url":"https://api.github.com/repos/mailchimp/mc-magento/issues/210","id":302600930,"user":{"login":"TroyPatteson","id":9345969,"avatar_url":"https://avatars1.githubusercontent.com/u/9345969?v=3","gravatar_id":"","url":"https://api.github.com/users/TroyPatteson","html_url":"https://github.com/TroyPatteson","followers_url":"https://api.github.com/users/TroyPatteson/followers","following_url":"https://api.github.com/users/TroyPatteson/following{/other_user}","gists_url":"https://api.github.com/users/TroyPatteson/gists{/gist_id}","starred_url":"https://api.github.com/users/TroyPatteson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TroyPatteson/subscriptions","organizations_url":"https://api.github.com/users/TroyPatteson/orgs","repos_url":"https://api.github.com/users/TroyPatteson/repos","events_url":"https://api.github.com/users/TroyPatteson/events{/privacy}","received_events_url":"https://api.github.com/users/TroyPatteson/received_events","type":"User","site_admin":false},"created_at":"2017-05-19T03:42:21Z","updated_at":"2017-05-19T03:42:21Z","body":"We saw a similar issue on our magento store with over 100,000 subscribers and orders. We tracked this down to the database query required to get the last date of purchase called as part of generating the merge variables when processing the subscribe webhook event during the initial two-way sync. (See _getLastDateOfPurchase()_ in the helper).\r\n\r\nThe issue is that _getLastDateOfPurchase()_ queries the _sales_flat_order_ table by _customer_email_. Unfortunately core Magento does not provide an index on _customer_email_ even though orders can be searched via customer email from the admin pages. As a result the query requires a full table scan which ultimately overloaded our 32-core DB server with 100 GB mysql buffer pool...\r\n\r\nThe answer is to manually create an index on _customer_email_ for the _sales_flat_order_ table using a query like:\r\n\r\n```\r\nALTER TABLE `<dbname>`.`<table_prefix>sales_flat_order`  ADD INDEX `IDX_OZSS_SALES_FLAT_ORDER_CUSTOMER_EMAIL` USING BTREE (`customer_email` ASC);\r\n```\r\n\r\nwhere  **< dbname>** is your database name and **<table_prefix>** is your database table prefix as specified in _app/etc/local.xml_."}}
{"actor":{"display_login":"TroyPatteson"}}