Categories not showing products in frontend after migration from Magento 1.7.2 to 2.3.5

Question

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!

Answer 1

I'm doubtful anyone will ever run into this same issue as it all stems from a mistake I made months ago which took me until now to realize but just in case I'll post what the issue ended up being for me. I tried many things before realizing my solution, figure I'll post those for someone else who may have this issue and are looking for things to try:

• I tried creating new categories / products to see if they would show up after reindex / recache but they also didn't appear in the frontend.

• I am using a custom theme so to test if that was the issue, switched to the default theme on a few categories, reindexed / recached, no difference so switched back.

• I read that if the parent categories don't have "is_anchor" set to yes then there can be issues with the subcategories so I went in that direction and tried to set "is_anchor" to yes all the way up to root category. After migration there were inconsistencies with category / product url-key data though which were preventing some categories from saving updates. The cases I saw were mainly products which were replaced, added to a category, but the old disabled product was never removed; thus when you try to save a category and URL rewrites attempt to be generated the duplicate link that would be created is prevented thus preventing the save. Tested this by going through each category and trying to flip "is_anchor" on and off to tell me which url-keys were duplicates within that category then went in the product index and corrected the issues accordingly until I could flip "is_anchor" on and off successfully. After doing this with all categories I was able to set is_anchor on the root without an error and then reindexed / recached but issue wasn't fixed.

• I tried tweaking some php.ini variables like "max_execution_time", "max_input_time", "max_input_nesting_level", "max_input_vars", "memory_limit", etc. thinking maybe that was the issue. Although I did have to increase one of these variables (can't recall which) to save a product that belonged to many categories, otherwise nothing came of changing these variables.

• In my "core_config_data" table "catalog/category/root_id" wasn't set so I tried setting that then reindexing / recaching but didn't help me as I think it was set in the "store_group" table anyway but something to check I suppose.

• Made sure the right "store_id" was set in the right places in multiple tables but there were no issues anywhere; although I did read some other people having that be their issue so wanted to mention it.

• Thought maybe I missed something in my server configuration when installing that was preventing the functionality from working so I went through and made sure all of the prerequisites, etc. for installation had been met. I found a few things I actually missed in the guide initially but fixing them also made no difference for this issue.

After doing all this I decided to just take a step back and re-read the initial thread I made, remember what the issue is, and focus. I knew that "catalog_product_index_eav" being empty was causing the issue so I had to figure out why that could be. I felt like I exhausted the online resources and was feeling hopeless so I went back and read the notes I took throughout the initial migration on things I had to do to fix different issues.

One of the first issues I noticed with the initial migration was that the customer list in admin was broken. When looking for a solution to that I found this thread that talked about finding the attributes in the "eav_attribute" table which had "frontend_input" set to select and "source_model" set to null. I did this and took note of which attributes I changed and moved on because the customer grid loaded fine.

Thankfully I wrote down what I did though because while looking at my notes I realized that rather than JUST getting the attributes with "source_model" set to null I got ALL of the "eav_attribute" entries where "frontend_input" was set to select through this query instead:

select attribute_id from eav_attribute where frontend_input = "select";

And then changed them all to text through this query:

update attribute_id set frontend_input = 'text' WHERE attribute_id IN ({resulting ids of above});

This all made sense as to why my "catalog_product_index_eav" was blank as was the temporary table being created on the category pages. So I reversed what I did by setting them back to select, reindexed, recached, and now everything is working. To fix the customer grid after reverting everything I made sure to only update the 2 attributes from "eav_attribute" with null "source_model" like I should have in the first place and then recached and it was working once again.

So if you have done something similar with changing the "frontend_input" on some attributes and are not seeing products in categories on the frontend it's worth checking you didn't change one (or all like me) of them which is actually necessary and not a remnant of migration. Definitely took a lot more time, effort, and frustration than I would have liked but I found a bunch of other issues which needed to be remedied regardless along the way and learned a good bit about Magento 2 so it's alright; hopefully smooth sailing from here!