Solving Magento Product Visibility Issues After Multistore .htaccess Setup
Hello, fellow Magento developers! I’m here to help you troubleshoot an issue that many of us have faced when setting up multistore environments: products disappearing from category views after implementing the .htaccess code for multistore.
The Problem at Hand
Let’s break down the scenario presented by our developer:
- An existing Magento website with version 1.12.0.2
- A new storefront being added via a subdomain (e.g.,
second-store.my-domain.com) - Products are available when accessed directly via URL (e.g.,
my-domain.com/category/product) but disappear from category views after implementing the multistore.htaccesscode
Here’s the relevant .htaccess code for reference:
## 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
The Investigation So Far
The developer has already explored several troubleshooting steps, including:
- Verifying that products are assigned to the proper category and store in Catalog
- Ensuring that products are enabled and have stock available
- Checking database group settings for
USER_NOT_LOGGED_IN(set to 0) - Reindexing and clearing cache multiple times
Despite these efforts, they still encounter a “There are no products matching the selection.” error in category views.
Diving Deeper into the Database
The developer shared some SQL queries from the list.php troubleshooting process:
-- Base store (1) query
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
-- Store 2 query with issues
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.tie
r_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price,
price_index.tie
r_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.s
tore_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
-- Store 3 query with issues
SELECT e.*, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id,
price_index.final_price, IF(price_index.tie
r_price IS NOT NULL, LEAST(price_index.min_price, price_index.tie
r_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price,
price_index.tie
r_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_in
dex.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
The culprit: Missing Products in catalog_product_index_price table
Upon comparing the queries and playing around with them, I discovered that the problem lies within the catalog_product_index_price table. It appears to be missing 90% of products for stores other than store ID 1.
The developer mentioned they’ve tried various database repair tools without success. This suggests a data inconsistency issue rather than a technical one.
The Solution: Recreate Indexes and Validate Data Integrity
To resolve this, I recommend following these steps:
- Backup your current database to prevent any possible data loss.
- Open the Magento admin panel and navigate to System > Index Management.
- In the ‘Status’ column, ensure all indexes are set to ‘Reindex required.’ If not, manually reindex them.
- Go to System > Configuration > Catalog > Frontend.
- Set the following options:
- Use Flat Catalog Category: Yes
- Use Flat Catalog Product: Yes
- Save your changes and flush the cache (System > Cache Management).
- Close the admin panel, open MySQL terminal, and run the following commands:
DROP INDEX ON catalog_product_index_price;
CREATE INDEX idx_catalog_product_index_price_product_id ON catalog_product_index_price (entity_id);
- Restart Apache or the web server.
- Reindex all Magento indexes again.
If the issue persists after these steps, I would recommend engaging a professional Magento support service like the one offered by our team at Laravel Company. Our experts have extensive experience in troubleshooting complex multistore setups and can provide tailored solutions for your specific environment.
Conclusion
In summary, the disappearance of products from category views after implementing the .htaccess code for multistore is often caused by data inconsistencies within Magento’s index tables. By following the steps outlined aboveâbackup, reindexing, and recreating indexesâyou should be able to resolve the issue and restore product visibility in your category views.
Remember that Magento is a complex system with many moving parts, and troubleshooting these types of issues often requires a deep understanding of its database structure and indexing mechanisms. If you find yourself stuck or dealing with an especially challenging scenario, don’t hesitate to reach out to a professional support service for expert guidance.
Happy coding! Let me know if you need any further assistance.
Best, [Your Name] Senior Developer & Technical Blogger Laravel Company