Magento 2 Duplicate Product URLs Problem

Question

When creating products in Magento 1 the url was created from the product name, and if there were duplicate product names M1 would add a numerical suffix to the end of the URL.

Since it seems this is no longer the case in Magento 2, how do I deal with products with the same name having duplicate URLs and causing an error?

Thanks

Answer 1

Found 2 solutions for that:

1. Clean up your database in table url_rewrite. You can write UpgradeData script for this solution.(Make sure you took the database backup)

2. The second one: Remove the duplication data when saving category. This data is throw in method doReplace($urls) in \vendor\magento\module-url-rewrite\Model\Storage\DbStorage.php file.

   protected function doReplace($urls)
   {
       foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
           $urlData[UrlRewrite::ENTITY_TYPE] = $type;
           $this->deleteByData($urlData);
       }
       $data = [];
       foreach ($urls as $url) {
           $data[] = $url->toArray();
       }
       $this->insertMultiple($data);
   } 
   

After debugging, I found out $data variable has a duplicate record. If you want this method to work without any errors. Rewrite this method above to:

    protected function doReplace($urls) {
        foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
            $urlData[UrlRewrite::ENTITY_TYPE] = $type;
            $this->deleteByData($urlData);
        }
        $data = [];
        $storeId_requestPaths = [];
        foreach ($urls as $url) {
            $storeId = $url->getStoreId();
            $requestPath = $url->getRequestPath();
            // Skip if is exist in the database
            $sql = "SELECT * FROM url_rewrite where store_id = $storeId and request_path = '$requestPath'";
            $exists = $this->connection->fetchOne($sql);

            if ($exists) continue;

            $storeId_requestPaths[] = $storeId . '-' . $requestPath;
            $data[] = $url->toArray();
        }

        // Remove duplication data;
        $n = count($storeId_requestPaths);
        for ($i = 0; $i < $n - 1; $i++) {
            for ($j = $i + 1; $j < $n; $j++) {
                if ($storeId_requestPaths[$i] == $storeId_requestPaths[$j]) {
                    unset($data[$j]);
                }
            }
        }
        $this->insertMultiple($data); }

If you want to get more details. Please, read comment in

https://github.com/magento/magento2/issues/7298

Hope this will help you.

Answer 2

The solution that follows assumes that you have removed all duplicate url keys of products and there are none left in the system.

Cause

In addition to the attribute 'url key', there is another attribute 'url path'. You cannot see it in the backend, but it is present in the database and must be unique (if it is there). There is a type for 'category' and one for 'product'. They each have a separate attribute ID.

In our case there was a bug in Magento 2 that did not adjust the url-path when copying a product. Therefore it was not unique. For the url-key you can see this clearly because Magento puts "_1" behind it.

Solution

1.Find the attribute ID in the database in the eav_attribute table that matches 'url path' and is the product variant. SELECT * FROM `eav_attribute` WHERE `attribute_code` LIKE 'url_path'

Click through on the 'entity_typ_id' to deduce which is the product variant

2.Use the ID to search the catalog_product_entity_varchar table for all records with this ID; and delete. DELETE FROM `catalog_product_entity_varchar` WHERE `attribute_id` = [ID attribuut]

3.Run the indexer in CLI

bin/magento indexer:reindex

Answer 3

Duplicated URL could be updated with a database query, that add the product id to the duplicated url :

CREATE TEMPORARY TABLE tmp_filter SELECT o.`entity_id`, o.store_id, f.cnt, f.`value`
FROM `catalog_product_entity_varchar` o
JOIN (SELECT COUNT(t.`entity_id`) as cnt, t.`store_id`, t.`value`, t.attribute_id  FROM `catalog_product_entity_varchar` t GROUP BY t.`store_id`, t.`value`, t.attribute_id) f ON o.`store_id` =f.`store_id` AND o.attribute_id = f.attribute_id
WHERE o.`value` = f.`value` AND f.cnt  > 1
AND o.attribute_id = (SELECT ea.attribute_id FROM `eav_attribute` ea
JOIN `eav_entity_type` et on ea.`entity_type_id` = et.`entity_type_id`
WHERE `ea`.`attribute_code` =  'url_key' AND et.entity_type_code = 'catalog_product');

UPDATE `catalog_product_entity_varchar` ev, tmp_filter
SET ev.`value` = CONCAT(ev.`value`,'-',ev.`entity_id`)
WHERE tmp_filter.`entity_id` = ev.entity_id AND tmp_filter.store_id = ev.store_id and ev.value = tmp_filter.`value`
AND ev.attribute_id = (SELECT ea.attribute_id FROM `eav_attribute` ea
JOIN `eav_entity_type` et on ea.`entity_type_id` = et.`entity_type_id`
WHERE `ea`.`attribute_code` =  'url_key' AND et.entity_type_code = 'catalog_product');

DROP TABLE tmp_filter;

For categories just replace "product" by "category"