0

if I want to update the lifetime statistics it quickly returns 'Unable to refresh lifetime statistics.'

The only statistics I can refresh using the lifetime option are - coupons - most viewed

errors

All the others return the above error, without even running for a very long time (within just a few seconds).

Refreshing daily statistics works.

How can I find the bug here?

edit: I'm running CE 1.8.1

I receive the following bugtrace:

    2014-03-14T14:40:08+00:00 ERR (3): 
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`usr_p194982_2`.`sales_order_aggregated_created`, CONSTRAINT `FK_SALES_ORDER_AGGREGATED_CREATED_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON D)' in /html/dev/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /html/dev/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /html/dev/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /html/dev/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /html/dev/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /html/dev/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#5 /html/dev/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#6 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order/Createdat.php(229): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `sa...')
#7 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order/Createdat.php(55): Mage_Sales_Model_Resource_Report_Order_Createdat->_aggregateByField('created_at', NULL, NULL)
#8 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order.php(55): Mage_Sales_Model_Resource_Report_Order_Createdat->aggregate(NULL, NULL)
#9 /html/dev/app/code/core/Mage/Adminhtml/controllers/Report/StatisticsController.php(159): Mage_Sales_Model_Resource_Report_Order->aggregate()
#10 /html/dev/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Report_StatisticsController->refreshLifetimeAction()
#11 /html/dev/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('refreshLifetime')
#12 /html/dev/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#13 /html/dev/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#14 /html/dev/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#15 /html/dev/index.php(90): Mage::run('', 'store')
#16 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`usr_p194982_2`.`sales_order_aggregated_created`, CONSTRAINT `FK_SALES_ORDER_AGGREGATED_CREATED_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON D)' in /html/dev/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /html/dev/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /html/dev/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /html/dev/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /html/dev/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#4 /html/dev/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#5 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order/Createdat.php(229): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `sa...')
#6 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order/Createdat.php(55): Mage_Sales_Model_Resource_Report_Order_Createdat->_aggregateByField('created_at', NULL, NULL)
#7 /html/dev/app/code/core/Mage/Sales/Model/Resource/Report/Order.php(55): Mage_Sales_Model_Resource_Report_Order_Createdat->aggregate(NULL, NULL)
#8 /html/dev/app/code/core/Mage/Adminhtml/controllers/Report/StatisticsController.php(159): Mage_Sales_Model_Resource_Report_Order->aggregate()
#9 /html/dev/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Report_StatisticsController->refreshLifetimeAction()
#10 /html/dev/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('refreshLifetime')
#11 /html/dev/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#12 /html/dev/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#13 /html/dev/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#14 /html/dev/index.php(90): Mage::run('', 'store')
#15 {main}
  • I've disabled all app/local and app/community modules to see if one of my installed modules could cause the issue, but it still has the same error. – tecjam Mar 14 '14 at 14:08
  • Yes, I am running a multistore - 2 stores from the same core. – tecjam Mar 14 '14 at 14:45
  • Have you recently deleted a store? – philwinkle Mar 14 '14 at 14:47
  • No, not as far as I am aware. The store was updated by my predecessor, who then left the project with me after the go live. It quickly became apparent that it wasn't by any means ready, so I had to fix issues asap and learning magento asap although actually coming from a few years of drupal development. The store goes back to 2009 and has orders from back then. – tecjam Mar 14 '14 at 14:53
1

This error could be anything from syntax errors in an extension that overrides the default resource collection to a database error. Lifetime stats refresh is handled by a method implemented on each individual resource model - that method is called aggregate and it is wrapped in a try/catch block to prevent exceptions from being displayed to the user.

Line 159 (CE 1.8) of Mage_Adminhtml_Report_StatisticsController is the part of the catch that is key to figuring out the issue:

Mage::logException($e);

Which tells me that, if you have logging enabled, the file var/log/exception.log should contain a stack trace that tells you specifically what the error is. To enable logging, navigate to System > Configuration > Developer and enable the logs.

In reality, the most likely scenario is that it is a db exception or a PHP out of memory error.

Edit

So, it seems as if you're getting a foreign key constraint error:

CONSTRAINT `FK_SALES_ORDER_AGGREGATED_CREATED_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) 

This can come up because the store it's trying to enter no longer exists. I would debug this by getting a list of current store ids:

SELECT GROUP_CONCAT(store_id) as stores from core_store;

And matching it up with a list of historical sales store ids:

SELECT GROUP_CONCAT(DISTINCT store_id) as stores from sales_flat_order;

If the store ids don't match, you're probably going to need to replace the old ids on the old orders. Either try to inventory and update the orders manually with the correct store id, or change it to 0 - the admin store.

Other thoughts

You may also want to try truncating the sales_order_aggregated_created table and re-running. Be extremely careful when truncating tables - always run (and test) a backup, do it when store is in maintenance mode, and test it on a staging/dev box prior.

| improve this answer | |
  • Phil, thanks you very much for your comment! I did indeed get an bugtrace - added to my first post – tecjam Mar 14 '14 at 14:41
  • I'm not seeing it @tecjam – philwinkle Mar 14 '14 at 14:42
  • Now hopefully ;) – tecjam Mar 14 '14 at 14:44
  • 1
    I tried to change the store_id value directly in the db. Now I can refresh the lifetime statistics for nearly every report type, apart from tax and orders. – tecjam Mar 17 '14 at 8:15
  • 1
    Finally, I also searched for store_id in the sales_order_aggregated_created table and also set it to the id I previously set in the sales_flat_order. Now it all works! Thanks for your help. =) – tecjam Mar 17 '14 at 8:18

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.