Duplicate Product URL Key

Question

I have products using the same URL Keys and I need a way to remove all of the URL Keys, so Magento regenerate them all according to their names.

Answer 1

You can use the following query to see which products have duplicate URL keys and how many duplicates there are:

SELECT COUNT(DISTINCT entity_id) AS amount, `value`, entity_id
FROM catalog_product_entity_varchar v
WHERE EXISTS (
  SELECT *
  FROM eav_attribute a
  WHERE attribute_code = "url_key"
  AND v.attribute_id = a.attribute_id
  AND EXISTS (
     SELECT *
     FROM eav_entity_type e
     WHERE entity_type_code = "catalog_product"
     AND a.entity_type_id = e.entity_type_id
  )
)
GROUP BY v.VALUE
ORDER BY `amount` DESC;

There is also a bug in the URL indexer that appends very large numbers to the end of URL's if duplicates exist, so you might want to look into that.

More information: https://erfanimani.com/issues-with-magentos-catalog-url-rewrite-indexer/

Answer 2

In Order to remove exiting products urls from system.You need to follow below steps:

1. First Take backup of core_url_rewrite table.
2. Then you need to running some mysql delete queries at core_url_rewrite which will delete.

Sql1:

Delete FROM core_url_rewrite WHERE product_id is not null

Sql2:

Delete FROM core_url_rewrite WHERE category_id is not null

3.delete All exiting url_keys from catalog_product_entity_varchar table.

DELETe FROM catalog_product_entity_varchar where attribute_id =(SELECT attribute_id FROM eav_attribute WHERE attribute_code LIKE 'url_key' and entity_type_id=4 )

4. Then re-index Catalog URL Rewrites from Index Management

Answer 3

Try following steps:

1. In case things go wrong, make a backup of core_url_rewrite table and data

2. In app/code/core/Mage/Catalog/Model/Url.php line 809 change

   if ($product->getUrlKey() == '' && !empty($requestPath)
       && strpos($existingRequestPath, $requestPath) === 0
   )
   

   to

   if (!empty($requestPath)
       && strpos($existingRequestPath, $requestPath) === 0
   ) 
   

3. Truncate (empty) the core_url_rewrite table

4. Under System -> Index Management re-index the Catalog URL Rewrites data

You should now be able to re-index again without creating thousands of random URL rewrites.