Just want to preface this by saying this is my first time using Magento 2 so I apologize if I'm missing something easy here; I always try all I can before reaching out as the last thing I want to do is ask of someone else's time when a solution is within my grasp but I'm at my wits' end.
I have been handling the migration of my company's Magento 1.7.2 website to Magento 2.3.5 and finally was able to get everything migrated and looking good in the backend after many iterations and inconsistencies which had to be cleared up. I've now just step by step been testing features / fixing them as I go preparing the site for live use.
One I most recently noticed was that on the category pages there were no products showing up. After doing some searching I found that the display mode on my categories had defaulted to "Static block only" in the migration so I changed that to "Static block and products". Now after recaching the category pages were all giving me the "We can't find products matching the selection." message. This didn't make sense however as in the backend the products were set on the categories correctly.
I began searching and can confirm I did / tried all of these things that are commonly found when searching for this issue:
- Enabled = yes
- Product in website = yes
- Product in category = yes
- Stock status in stock = yes
- Visibility = catalog, search
- Reset index, Reindex and flush cache = yes
- Can reach product pages = yes
I also noticed I was missing the "inventory_stock_1" view which is mentioned in a lot of threads revolving around this issue which I found but after adding that manually via a snippet someone posted that too made no difference.
Since none of the common issues were the culprit I tried to do some debugging and found the template where the product list is created for the category page; there I printed the select for the query and it looked like this:
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`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable`, `at_sale_number`.`value` AS `sale_number` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index_store1` 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=814 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 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product')) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 INNER JOIN `search_tmp_5faf09ea9da887_74380017` AS `search_result` ON e.entity_id = search_result.entity_id LEFT JOIN `catalog_product_entity_int` AS `at_sale_number` ON (`at_sale_number`.`entity_id` = `e`.`entity_id`) AND (`at_sale_number`.`attribute_id` = '148') AND (`at_sale_number`.`store_id` = 0) ORDER BY `sale_number` ASC, `e`.`entity_id` DESC LIMIT 20
I tried running that in the database and sure enough it returned an empty set. I realized there was a temporary table involved in the query though so I removed the line involving the "search_tmp" table and sure enough then all the correct products which are set on that category in the backend were returned from the query.
So I started searching for an issue similar to this and found this thread magento2 search_tmp join leads to empty results in category listing thinking it must be the same issue. I checked though and the bug described there had already been fixed in this version of Magento 2. I did take note of the debugging option to swap the temporary tables being created into normal tables.
I did this and noticed that the "search_tmp" tables that were being created were indeed empty and thought this must be the cause of the products not showing up. So logically the next step would be to see how is that "search_tmp" table being created. I found somewhere on my hunt how to turn on the logging of all queries so I did that and tried refreshing the category page. I found that the table was made with this query:
INSERT INTO `search_tmp_5fb304350d3a21_39100804` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index` INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_id` = 1 INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id in ('245'))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
After playing around with that query I noticed that the catalog_product_index_eav table had no entries in it at all which made it make some sense why the category page was getting no products. I figured it must be an issue with this eav index building then and tried reseting it and reindexing many times to no avail. I was finding issues with rebuilding the eav index but none where it was just empty; I tried to not use the temporary table strategy as described in this comment https://github.com/magento/magento2/issues/12124#issuecomment-646107790 but that made no difference either.
I tried to then clear the sql log and run just the eav re-index to see what was happening. I noticed that it runs this query which returns an empty set:
SELECT `ca`.`attribute_id` FROM `catalog_eav_attribute` AS `ca` INNER JOIN `eav_attribute` AS `ea` ON ca.attribute_id = ea.attribute_id WHERE (ca.is_filterable_in_search > 0 OR ca.is_visible_in_advanced_search > 0 OR ca.is_filterable > 0 OR ea.attribute_code = 'visibility') AND (ea.backend_type = 'int') AND (ea.frontend_input IN( 'select', 'boolean' ))
I noticed a similar query to that but with backend_type as 'decimal' which made me realize that catalog_product_index_eav_decimal was empty as well.
So should these indexes be empty then if these queries have no results? This is where I'm stuck. If this index is only concerned with the attributes from that query then why or how does that hold any control over the category page collection that is built if the categories don't fall into that table. So that's where I'm hoping to get some guidance as it's clear that there's something going on in this reindex that I'm not understanding.
Also just to note I did test the search function thinking it also wouldn't work but surprisingly I'm able to search for products with no issue as the temporary table created for that product list doesn't involve the catalog_product_index_eav table (after similar investigation to above).
I did notice something that may be of use though; when I search for let's say "Accident" it returns many products which it should but many of the products belong to the "Accident Investigation" category. In the category filter to the left however the only categories which are shown are those 1 level after the root, not the deepest child category. So rather than "Accident Investigation" being a filter on the left, or even "Workplace Safety" the category above that, I just see "Brands", "Online Training", and "Training Programs". So it seems to be having an issue with those categories here too but not sure if that's related or just another problem to solve.
Another interesting thing I noticed is that on the category pages which are 2 levels after the root, for example "Workplace Safety", all the categories are showing in a list correctly with an accurate amount of products:
Anyways that's all the relevant information I have, hopefully one of you experts may have some insight on what I can try next. If there's any more information I can provide to lead me to a solution let me know. Any suggestions anyone has on debugging this I would greatly appreciate, thanks!