Magento EE: Missing products in catalog_product_index_price

Question

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?

Answer 1

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