Wrong Product URL when found via category on M2

Question

I am using Magento version 2.3.1 EE and I observed that some of the product page URL's are coming wrong when found via category even if the URL rewrites are created in the Magento admin. It is happening only for few products in the category, rest all products are working fine.

URL coming when found via category https://staging.tradingdepot.co.uk/catalog/product/view/id/74012/s/merlyn-8-series-walk-in-with-swivel-panel-with-end-panel-900mm-m80291sv/category/4490/

Actual URL should be https://staging.tradingdepot.co.uk/merlyn-8-series-walk-in-with-swivel-panel-with-end-panel-900mm-m80291sv

URL rewrite is created for this product and no duplicate URL rewrite is created. Also we have tried creating URL rewrite through olegkoval/magento2-regenerate_url_rewrites but still it picks the wrong URL and when we change the URL key of product and then save the product. In that case product URL gets corrected and new URL starts showing which is the correct URL.

Please help me out with this probelm.

Answer 1

Cause of the issue: corrupted data in the database. The url rewrite which is responsible for product request_path has a column 'is_autogenerated' set to 0. Using the next query you can find the list of products which has a similar issue:

select * from url_rewrite where entity_type = 'product' and redirect_type = 0 and is_autogenerated = 0 and (metadata is null or metadata = '[]');

Solution: for the records which are returned using the previous query set 'is_autogenerated' equal to 1 and clean the cache.

update url_rewrite set is_autogenerated = '1' where entity_type = 'product' and redirect_type = 0 and is_autogenerated = 0 and (metadata is null or metadata = '[]');