Rambow4 Rambow4 - 3 months ago 8
MySQL Question

Magento Products Ordered Report returns "No records found for this period" after upgrade to 1.6.2.0

We upgraded our magento 1.3.3 website to version 1.6.2.0 about three months ago. Since the upgrade our Products Ordered Report shows no history before the upgrade date, but displays all orders after the upgrade date just fine.

I have looked through all types of posts about the subject in places such as magentocommerce, Stack overflow, Google, etc... All my searches resulted in either people getting 404 errors (which I’m not experiencing) or people who are experiencing my same problem but no one seems to know how to fix it on a live site. I have spent over 3 days and countless hours searching for an answer to fixing my live sites report issue with no luck.

There are many fixes out there as long as the site is in development stages and has not gone live, such as truncing a bunch of database tables, but once the site has gone live no one seems to know how to resolve the issue

I know that for the most part EAV tables were eliminated with the upgrade and it’s possible that somehow that could be part of the problem.

Does anyone know what database table(s) the Products Ordered Report used in version 1.3.3 and if it is the same one(s) it uses in version 1.6.2.0. If they are different which table(s) where used then and what table(s) are used now for the report?

Or if you know how to resolve this issue on a live site or have a few suggestions, please don’t hold back, I need your help

Thank you in advance

Answer

The issue is most likely coming from the sales_flat_order_item table which is used to generate this report.

I had similar problem. fixed mine using phpmyadmin.

Browse the table and if you find store_id empty, edit and select the correct store_id for your store.

I run the following query to update all order items:

UPDATE `sales_flat_order_item` SET `store_id`=1;

(this is assuming you only have 1 store and its store id =1. Also, make backup before running.