I have my store setup and ready to work with it. When I'm going to make a test order, I noticed that Magento is sending the confirmation email multiple times, doesn´t stop sending. I'm receiving the emails two by two.
And I've noticed that an exception is being thrown in exception.log
:
Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (message_id='3')' at line 1, query was: UPDATE `core_email_queue` SET WHERE (message_id='3')' in /home/newolders/public_html/store/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /home/newolders/public_html/store/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/newolders/public_html/store/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/newolders/public_html/store/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/newolders/public_html/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `core_em...', Array)
#4 /home/newolders/public_html/store/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `core_em...', Array)
#5 /home/newolders/public_html/store/lib/Zend/Db/Adapter/Abstract.php(635): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `core_em...', Array)
#6 /home/newolders/public_html/store/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(433): Zend_Db_Adapter_Abstract->update('core_email_queu...', Array, 'message_id='3'')
#7 /home/newolders/public_html/store/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Core_Model_Resource_Db_Abstract->save(Object(Mage_Core_Model_Email_Queue))
#8 /home/newolders/public_html/store/app/code/core/Mage/Core/Model/Email/Queue.php(244): Mage_Core_Model_Abstract->save()
#9 [internal function]: Mage_Core_Model_Email_Queue->send(Object(Mage_Cron_Model_Schedule))
#10 /home/newolders/public_html/store/app/code/core/Mage/Cron/Model/Observer.php(325): call_user_func_array(Array, Array)
#11 /home/newolders/public_html/store/app/code/core/Mage/Cron/Model/Observer.php(72): Mage_Cron_Model_Observer->_processJob(Object(Mage_Cron_Model_Schedule), Object(Mage_Core_Model_Config_Element))
#12 /home/newolders/public_html/store/app/code/core/Mage/Core/Model/App.php(1338): Mage_Cron_Model_Observer->dispatch(Object(Varien_Event_Observer))
#13 /home/newolders/public_html/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))
#14 /home/newolders/public_html/store/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('default', Array)
#15 /home/newolders/public_html/store/cron.php(76): Mage::dispatchEvent('default')
#16 {main}
Could anyone help me?
Best Answer
I'm not sure if you found the solution in the mean time, but I recently came into the same problem on a client's server.
What happened was indeed the same, the cron job core_email_queue_send_all was sending the same emails multiple times and each time the same exception you found was added into the exception log.
The cron job was sending the same emails multiple times because the
processed_at
field did not get saved in thecore_email_queue
table for the corresponding messages.I've added some logs into the code and looked into how the query for saving the
core_email_queue
message was built, and why it was missing the SET part of it (which should have contained the columns to be updated):In Magento, before building the database queries, the columns used in the query get checked against the column descriptions for the respective table inside the
Mage_Core_Model_Resource_Abstract::_prepareDataForTable
method by calling:In order not to execute the DESCRIBE query each time, Magento caches the DDL info for the tables. Inside the
Varien_Db_Adapter_Pdo_Mysql::describeTable
method the cache is first checked:I found that the columns received from the cache for the
core_email_queue
table, were not the ones expected, instead sometimes they were:data
,lifetime
,expire
andpriority
.This pointed to a cache problem and I found that
Zend_Cache_Core
was saving data into the DDL cache files, sometimes by callingZend_Cache_Backend_File::save()
directly and sometimes by callingZend_Cache_Backend_TwoLevels::save()
.The two levels cache from Zend uses the
_prepareData
method to build a serialized array to store the data and metadata information:Finally, the issue was that the cron (which sent the emails) was called from the command line. Comparing a request from the browser with a command line request, I found that
Mage_Core_Model_Cache::getBackendOptions
was returning different options. This server was set up to use APC cache, however when the cron was runningini_get(‘apc.enabled’)
was false.On this server there were 2 php configuration files fpm/php.ini where apc.enabled was 1, and cli/php.ini where apc.enabled was 0. The Magento instance that was run from the command line was not able, thus, to use the APC cache, so it did not use a two-level cache, which led to it not knowing how to correctly read the data from the cache files.
The fpm Magento instance used APC and the two level cache and was saving DDL data into the var/cache folder enclosed in an array with the
data
,lifetime
,expire
andpriority
keys. When the cron ran and read the DDL cache file, it used the data found there and basically considered for each table, that the columns aredata
,lifetime
,expire
andpriority
.Changing apc.enabled to 1 in the cli/php.ini configuration file did the trick and solved the issue.
If you are interested on reading more about how I debugged this issue you can have a look over the more detailed explication I wrote for a blog post: http://ayg.ro/magento-cron-twolevel-cache-issue-pdoexception-sqlstate-42s22-and-sqlstate-42000