Hi I have magento 2.0.2
I am having trouble refreshing lifetime statistics. I have narrowed it down to a problem with bestsellers. If I unselect it then it works. If I select bestsellers and try and do a lifetime statistics refresh it fails with the above error. It will let me do a refresh statistics for the last day. I found this is the execption.log.
[2017-01-31 23:07:27] main.CRITICAL: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`eeuu`.`sales_bestsellers_aggregated_daily`, CONSTRAINT `SALES_BESTSELLERS_AGGRED_DAILY_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entit)' in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#3 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#5 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(444): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#6 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(499): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('INSERT INTO `sa...', Array)
#7 /var/www/html/vendor/magento/module-sales/Model/ResourceModel/Report/Bestsellers.php(162): Magento\Framework\DB\Adapter\Pdo\Mysql->query('INSERT INTO `sa...')
#8 /var/www/html/vendor/magento/module-reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime.php(22): Magento\Sales\Model\ResourceModel\Report\Bestsellers->aggregate()
#9 /var/www/html/var/generation/Magento/Reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime/Interceptor.php(25): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime->execute()
#10 /var/www/html/vendor/magento/framework/App/Action/Action.php(102): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->execute()
#11 /var/www/html/vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#12 [internal function]: Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#13 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#14 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->___callParent('dispatch', Array)
#15 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'adminAuthentica...')
#16 /var/www/html/vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#17 [internal function]: Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#18 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(68): call_user_func_array(Array, Array)
#19 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'designLoader')
#20 /var/www/html/vendor/magento/framework/App/Action/Plugin/Design.php(39): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#21 [internal function]: Magento\Framework\App\Action\Plugin\Design->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#22 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(68): call_user_func_array(Array, Array)
#23 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'adminMassaction...')
#24 /var/www/html/vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#25 [internal function]: Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#26 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(141): call_user_func_array(Array, Array)
#27 /var/www/html/var/generation/Magento/Reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime/Interceptor.php(53): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->___callPlugins('dispatch', Array, Array)
#28 /var/www/html/vendor/magento/framework/App/FrontController.php(55): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#29 [internal function]: Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#30 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#31 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#32 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#33 /var/www/html/vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#34 [internal function]: Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#35 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(141): call_user_func_array(Array, Array)
#36 /var/www/html/var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#37 /var/www/html/vendor/magento/framework/App/Http.php(115): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#38 /var/www/html/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#39 /var/www/html/index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#40 {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 (`eeuu`.`sales_bestsellers_aggregated_daily`, CONSTRAINT `SALES_BESTSELLERS_AGGRED_DAILY_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entit), query was: INSERT INTO `sales_bestsellers_aggregated_daily` (`period`, `store_id`, `product_id`, `product_name`, `product_price`, `qty_ordered`) SELECT STRAIGHT_JOIN DATE(CASE WHEN (`source_table`.`created_at` between '2016-10-30 01:00:00' and '2017-01-31 23:07:27') OR (`source_table`.`created_at` between '2015-10-25 01:00:00' and '2016-03-27 01:00:00') THEN DATE_ADD(`source_table`.`created_at`, INTERVAL 0 SECOND) ELSE DATE_ADD(`source_table`.`created_at`, INTERVAL 3600 SECOND)END ) AS `period`, `source_table`.`store_id`, `order_item`.`product_id`, MIN(order_item.name) AS `product_name`, MIN(order_item.base_price) * MIN(source_table.base_to_global_rate) AS `product_price`, SUM(order_item.qty_ordered) AS `qty_ordered` FROM `sales_order` AS `source_table`
INNER JOIN `sales_order_item` AS `order_item` ON order_item.order_id = source_table.entity_id WHERE (source_table.state != 'canceled') AND (order_item.product_type NOT IN('bundle', 'grouped', 'configurable')) GROUP BY DATE(CASE WHEN (`source_table`.`created_at` between '2016-10-30 01:00:00' and '2017-01-31 23:07:27') OR (`source_table`.`created_at` between '2015-10-25 01:00:00' and '2016-03-27 01:00:00') THEN DATE_ADD(`source_table`.`created_at`, INTERVAL 0 SECOND) ELSE DATE_ADD(`source_table`.`created_at`, INTERVAL 3600 SECOND)END ),
`source_table`.`store_id`,
`order_item`.`product_id` ON DUPLICATE KEY UPDATE `period` = VALUES(`period`), `store_id` = VALUES(`store_id`), `product_id` = VALUES(`product_id`), `product_name` = VALUES(`product_name`), `product_price` = VALUES(`product_price`), `qty_ordered` = VALUES(`qty_ordered`)' in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /var/www/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#4 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(444): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#5 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(499): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('INSERT INTO `sa...', Array)
#6 /var/www/html/vendor/magento/module-sales/Model/ResourceModel/Report/Bestsellers.php(162): Magento\Framework\DB\Adapter\Pdo\Mysql->query('INSERT INTO `sa...')
#7 /var/www/html/vendor/magento/module-reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime.php(22): Magento\Sales\Model\ResourceModel\Report\Bestsellers->aggregate()
#8 /var/www/html/var/generation/Magento/Reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime/Interceptor.php(25): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime->execute()
#9 /var/www/html/vendor/magento/framework/App/Action/Action.php(102): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->execute()
#10 /var/www/html/vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#11 [internal function]: Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#12 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#13 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->___callParent('dispatch', Array)
#14 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'adminAuthentica...')
#15 /var/www/html/vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#16 [internal function]: Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#17 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(68): call_user_func_array(Array, Array)
#18 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'designLoader')
#19 /var/www/html/vendor/magento/framework/App/Action/Plugin/Design.php(39): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#20 [internal function]: Magento\Framework\App\Action\Plugin\Design->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#21 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(68): call_user_func_array(Array, Array)
#22 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Reports...', 'dispatch', Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Array, 'adminMassaction...')
#23 /var/www/html/vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#24 [internal function]: Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#25 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(141): call_user_func_array(Array, Array)
#26 /var/www/html/var/generation/Magento/Reports/Controller/Adminhtml/Report/Statistics/RefreshLifetime/Interceptor.php(53): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->___callPlugins('dispatch', Array, Array)
#27 /var/www/html/vendor/magento/framework/App/FrontController.php(55): Magento\Reports\Controller\Adminhtml\Report\Statistics\RefreshLifetime\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#28 [internal function]: Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#29 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#30 /var/www/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#31 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#32 /var/www/html/vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#33 [internal function]: Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#34 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(141): call_user_func_array(Array, Array)
#35 /var/www/html/var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#36 /var/www/html/vendor/magento/framework/App/Http.php(115): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#37 /var/www/html/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#38 /var/www/html/index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#39 {main} [] []
