Magento EE: Missing products in catalog_product_index_price

4
votes

I'm working with a store that runs on Magento EE 1.12. One day we opened up the store and found all the configurable products have gone missing from category listing. I inspected the SQL that loads the products and found out that the inner join with catalog_product_index_price had made it return empty.

I opened up the table and found out that while the child simple products of these configurable products are still in the table, the configurable products are not.

What could be causing this problem?

4
magentoe-commerce
Are you running multiple storeviews and what is the status of the products in all storeviews? We ran into a same problem in Magnto 1.12, which equals 1.7 CE I believe. The problem in our case was that on the admin / global level the products were disabled, while they were enabled on a lower storeview level. The disabled status on the admin level caused the price index join to not match the products, resulting in products not showing up on the storefront. – Tim Hofman

4 Answers

5
votes

I have found the solution after tearing down the core indexers. I found out that price indexing works by running the stock indexers first then the price indexers. The problem is in one of the configurable product stock indexer.

This indexer renders the stock_status of the configurable products stored in cataloginventory_stock_status_idx to be 0. The configurable product price indexer then comes in and found out that these products have no stock available so it won't border to reindex.

So the fix is:

In Mage_CatalogInventory_Model_Resource_Indexer_Stock_Configurable this line 

$adapter->getCheckSql("{$psCond} AND le.required_options = 0", 'i.stock_status', 0);

should be 

$adapter->getCheckSql("{$psCond}", 'i.stock_status', 0);

It's questionable why required_options=0 was there in the first place. Makes little sense to me

2
votes

The exact same thing happens when you disable a product and run price indexer afterwards. Magento computes the stock index first, populating the table cataloginventory_stock_status_idx. If a product is disabled it will be added with stock_status == 0. From that point on, price index will not be calculated for those products.

1
votes

The file is under app/code/core/Mage/CatalogInventory/Model/Resource/Stock/Configurable.php. It is named for the class as per PHP-FIG PSR0.

0
votes

Despite this is an old question, I'll leave a possible solution of what appears to be the core issue.

The problem is with configurable attribute's backend_type field, it must be always of type int, and not varchar or whatever. 

Source: https://magento.stackexchange.com/a/97328/47383