Magento 2 - Magento 1 migration : all product links are error 404

Question

I am busy with another migration of Magento 1 to Magento 2.3.5,

I have managed to get my categories working, all categories show products associated to them (used Iazel_RegenProductUrl module for this.

However, every single product (simple/configurable/etc) returns with an error 404 page of Magento 2.

I am aware that indexer:reindex _in Magento 2 does not recreate URL rewrites` according to other resources found here on StackOverflow, there were also suggestions to run a 3rd party url rewrite regen module.

I have used the following modules:

Iazel_RegenProductUrl
OlegKoval_RegenerateUrlRewrites

First off, I decided to drop OlegKoval_RegenerateUrlRewrites as an option. 22000 products would take hours with this module. It takes around 15 minutes on my dev machine to generate 1500 product SKUs and their URLs, so that is absolutely no solution to attempt further.

Running bin/magento regenerate:product:url command that was added by Iazel_RegenProductUrl takes around 5 minutes to generate ~103000 rewrites (around 4+ each per product, based on categories).

One product example:

SELECT * FROM url_rewrite ur 
INNER JOIN catalog_url_rewrite_product_category curpc 
ON ur.url_rewrite_id = curpc.url_rewrite_id
WHERE entity_type NOT IN ('category')
AND entity_id = 409398;

I need some assistance with someone that can compare data with their database, or advise which queries to run to verify data consistency.

• The store_id here is ID 1, I also have a store id 0 inside store table (and store website). I don't know how to make sure they are configures correctly ---- this is from the Migration Tool import result.

Does the store_id here have an impact on why the products are showing error 404?

This reason I am asking: it does not make sense when I take the working category URLs into account:

SELECT DISTINCT(store_id) FROM url_rewrite ur;

Only returns 1. The whole url_rewrite table only has store_id 1.

Please help me to assist in resolving / compare database tables.

SOLUTION

While this answer asked about database tables (specifically url_rewrite), the issue turned out to be another table: see my answer regarding catalog_product_entity and its attribute_set_ids.

Also: If you just came here about URL rewrites: use the module I linked in the answer, and do not use Iazel_RegenProductUrl because it has been depecrated.

Answer 1

First check your affected rows with select query in url_rewrite

SELECT
request_path,
SUBSTRING(request_path, 1, CHAR_LENGTH(request_path) - 1) AS col_trimmed
FROM url_rewrite
WHERE request_path LIKE '%/'

Update affected row

UPDATE url_rewrite SET request_path = SUBSTRING(request_path, 1, CHAR_LENGTH(request_path) - 1) WHERE request_path LIKE '%/'

Update: (2021/03/30)

This answer helps for <= M2.1

According to information in Elgentos Regenerate URLs module referencing Magento 2 issue 9113 (closed): url_path is not used in versions of Magento ~2.1+: so this answer will help those pre <~2.1, in my case (M2.3.5) I had no entries to delete (so the migration did it already?).

From the referenced module:

However, the use of url_path has been deprecated since early Magento 2.1 versions (see here). If you are running on a recent Magento 2 version, you can safely delete those values by running DELETE FROM catalog_product_entity_varchar WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_path' AND entity_type_id = 4)

From issue 9113:

this ticket can be closed as url_path is deprecated.

Answer 2

Note: The other answer from LitExtension relates to Magento versions <= M 2.1, more recent versions do not use url_path attribute entries for URL rewrites. (Read more information in that answer for relevance - additional information related to this issue at the end of this answer as well).

This answer discusses the issue I solved eventually. My original question wanted assistance with comparing tables and specifically url_rewrite table.

To summarize:

In my case the migration issue for error 404s were not the URL rewrites at all. The issue I experienced is with catalog_product_entity and incorrect attribute_set_id's assigned on migration.

My products and attribute sets were imported, but migration did not update the catalog_product_entity field attribute_set_id correctly for most of my products. I had catalog_product_entity rows (almost all rows) that had attribute_set_id x, but in fact everything else were expecting attribute_set_id z instead.

For instance:

The migration had both attribute_set_name AttributeSetName and attribute_set_name Migration_AttributeSetName as well (All attribute sets had a AttributeSetName and Migration_AttributeSetName version.

The migration then attached products to Migration_AttributeSetName to catalog_product_entity but should have been using AttributeSetName.

SOLUTION

(Summary version)

Please note that this information should be used as a starting point, and the queries mentioned below should be investigated with your individual case to see if your data has some additional filters required.

Three steps are needed:

1. Create a filter of matching attribute sets with a sourceid and targetid attribute_set_id
2. INNER JOIN the catalog_product_entity table to use that filter to see which products will be updated and store it into a temporary table or view.
3. Create a loop (most likely stored procedure) that can update the catalog_product_entity table using the results of the temporary table/view. UPDATE catalog_product_entity SET attribute_set_id = targetid WHERE attribute_set_id = sourceid
4. (Optional - but recommended) Use Elgentos Regenerate URL Module which will help you regenerate all your categories/product/cms-page urls because Magento 2 does not have this capability built-in anymore (it only refreshes on individual product save).

(Explanation)

1. Use this filter as reference:

This query filters out those products that have an attribute set WHERE NOT LIKE %Migration%:

    (SELECT DISTINCT(attset.attribute_set_id) AS LookupAttributeSetId, attset.attribute_set_name AS LookupAttributeSetName, 
        CONCAT("Migration_", attset.attribute_set_name) AS TargetAttributeSetName 
         FROM catalog_product_entity AS prod
         INNER JOIN eav_attribute_set AS attset 
         ON prod.attribute_set_id  = attset.attribute_set_id
         WHERE attset.attribute_set_name 
         NOT LIKE "%Migration%") ;

2. Then expanding on this: INNER JOIN eav_attribute_set AS targetset and store the information into a View/Temporary Table:

CREATE TEMPORARY TABLE _resultset_updatethese_attribute_setids_in_catalog_product_entity_ AS (
SELECT FilteredResults.*, targetset.attribute_set_id AS TargetAttributeSetId FROM 
    (SELECT DISTINCT(attset.attribute_set_id) AS LookupAttributeSetId, attset.attribute_set_name AS LookupAttributeSetName, 
        CONCAT("Migration_", attset.attribute_set_name) AS TargetAttributeSetName 
         FROM catalog_product_entity AS prod
         INNER JOIN eav_attribute_set AS attset 
         ON prod.attribute_set_id  = attset.attribute_set_id
         WHERE attset.attribute_set_name 
         NOT LIKE "%Migration%") 
    AS FilteredResults
INNER JOIN eav_attribute_set AS targetset
ON targetset.attribute_set_name = FilteredResults.TargetAttributeSetName);

3. Then update all those attribute_set_id entries that are currently assigned to the catalog_product_entity table that were stored in the temporary table/view (I only managed to do this using a Stored Procedure and **looping through each row in order to get the current attribute_set_id and the 'target' attribute_set_id from that result set.

UPDATE catalog_product_entity 
SET attribute_set_id = newid 
WHERE attribute_set_id = targetid (_IN THAT LOOKUP result (each row needs sourceid and targetid)_).

4. (Optional - but recommended) Use Elgentos Regenerate URL Module which will help you regenerate all your categories/product/cms-page urls because Magento 2 does not have this capability built-in anymore (it only refreshes on individual product save).

This should update most of your catalog which should fix error 404 if your issue is similar.
NOTE: I have noticed some of my products have not been updated using this method, but ~90% of my products work now!

(Additional Information)

Regarding <= M2.1 and url_path

Just in case the other answer does not have this information anymore:

Elgentos Regenerate Catalog URLs module explains on url_path on its readme:

However, the use of url_path has been deprecated since early Magento 2.1 versions (see here). If you are running on a recent Magento 2 version, you can safely delete those values by running DELETE FROM catalog_product_entity_varchar WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_path' AND entity_type_id = 4)