Products missing from category view(only) in Magento after multistore .htaccess

Question

I'm in the process of adding an additional website/storefront to an already active Magento website.

While setting this up on the development site, I noticed that whenever I drop in the .htaccess code for multistore all products disappear from the category views. however, I can still get to the produscts via direct link e.g. my-domain.com/category/product.

Here is the .htaccess code I'm using:

## Storefronts

# Main Store
SetEnvIf Host www\.my-domain\.com MAGE_RUN_CODE=main-store_code
SetEnvIf Host www\.my-domain\.com MAGE_RUN_TYPE=website
SetEnvIf Host ^my-domain\.com MAGE_RUN_CODE=main-store_code
SetEnvIf Host ^my-domain\.com MAGE_RUN_TYPE=website

# Second Store
SetEnvIf Host second-store\.my-domain\.com MAGE_RUN_CODE=second_store_code
SetEnvIf Host second-store\.my-domain\.com MAGE_RUN_TYPE=website

I've set up the subdomain to use the root Magento install as its base directory. Multistore works, I can change the themes, settings, products etc.

I've meticulously gone through everything seen here and more at least 5 times: accepted answer has a category troubleshooting checklist

Does anyone have any ideas as to what might cause the products to be available on the front end but not in category view? We're running Magento Enterprise 1.12.0.2

Edit: So far we've tried:

1. The products are assigned to the proper category/store in Catalog.
2. The Products are Enabled. (we've tried batch enable/disable)
3. The products have a quantity.
4. The products are set to "In Stock"
5. Manage inventory os set to off
6. Display out of stock is set to yes.
7. Multi-website is being used and the products have been assigned to the target Website.
8. We've refreshed, deleted then disabled cache
9. We've reindexed all through admin and command line.
10. We've performed various var_dumps to check for proper store IDs
11. We've checked database group for USER_NOT_LOGGED_IN. it is 0 as it should be.
12. Products are aware of what store they're in and function as they should if you navigate directly to them.
13. We've run a database repair tool
14. We've run the database compare with fresh database tool. (this found two minor indexing issues) we reindexed again but with no luck.
15. We've disabled every non essential module with cache off.

We simply get a "There are no products matching the selection." error in category views.

EDIT #2 These are the SQL Statements from the list.php troubleshooting that OSdave pointed out:

I think it would also be advantageous to note that the default store isn't being used on the front end. It is simply a base we use to import products. The visible storefronts are seen in the .htaccess file above.

#Base store (1)(this works):
2013-10-03T14:12:48+00:00 DEBUG (7): SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='3'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY `cat_index`.`position` ASC LIMIT 10

#This is from the store2 store (MAGE_RUN_CODE=main-store_code):
2013-10-03T13:35:38+00:00 DEBUG (7): SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=2 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='3'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '2' AND price_index.customer_group_id = 0 AND price_index.stock_id = 15 ORDER BY `cat_index`.`position` ASC LIMIT 10

#This is from the store3 store (RUN_CODE=second_store_code):
2013-10-03T14:48:05+00:00 DEBUG (7): SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='37'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '2' AND price_index.customer_group_id = 0 AND price_index.stock_id = 1  ORDER BY `cat_index`.`position` ASC LIMIT 9

I'm looking into the differences. At first glance I've noticed the Website Ids may be off.

EDIT #3 I've compared all three queries and played with them a bit. What I've found is that it appears my catalog_product_index_price table appears to be missing 90% of my products and EVERY product that isn't store id=1

I can pull up any products with a store ID of 1 via an SQL query. Everything else fails. I've manually verified the values being passed. The products simply don't exist in the catalog_product_index_price table.

What now? I've tried database repair tools...

Edit #4 Stock_ID is coming from Multi-Warehouse an innoexts plugin we're using to manage shipping regions.

Answer 1

The message "There are no products matching the selection." shows up if the product collection is empty:
app/design/frontend/base/default/template/catalog/product/list.phtml:

<?php
    $_productCollection=$this->getLoadedProductCollection();
    $_helper = $this->helper('catalog/output');
?>
<?php if(!$_productCollection->count()): ?>
<p class="note-msg"><?php echo $this->__('There are no products matching the selection.') ?></p>
<?php else: ?>

As you see the product collection is retrieved by the method getLoadedProductCollection() which is defined in app/code/core/Mage/Catalog/Block/Product/List.php (if not overriden):

public function getLoadedProductCollection()
{
    return $this->_getProductCollection();
}

In order to determine what is wrong, momentarily edit this file and add a log of the mysql query just before the return, i.e.:

public function getLoadedProductCollection()
{
    Mage::log($this->_getProductCollection()->getSelect()->assemble(), null, 'product_collection.log', TRUE);
    return $this->_getProductCollection();
}

Load a category and go to MAGENTO_ROOT/var/log/ and open the product_collection.log file. There you will see the mysql query executed.
I would then remove some parts of it, one by one, and run it against the db until I determine what causes the collection to be empty.

---

now that you have the mysql query, we can go on. There is one difference between the query that works (the first one) and the others: AND price_index.stock_id = 1
In a native EE 1.12, the catalog_product_index_price table does NOT have a stock_id column.
That makes me think that someone altered the code someway to add this: I'd look into to code to see where it comes from, and from there take actions to solve the bug

HTH