Previous Article

How to identify duplicate product URLs

Next Article

We recently had reports from a client that products were failing with an error stating that the product URL was already in use.

I decided to look and see what existing products would cause the problem while we dug into the cause of the problem.

In this article, I'll run through the MySQL queries run and the reason behind them so we can see which products are affected. I hope this article helps others find duplicated product URLs in Magento 2.

We'll start with the attribute url_key in my database it's 121 but it may not be in other installations. To get the value we'll run the following

SELECT eav_attribute.attribute_id FROM eav_attribute WHERE attribute_code = 'url_key' AND entity_type_id = '4';

This query will check product-specific attributes named url_key.

With that value know we can work on checking the catalog_product_entity_varchar table for the duplicated URLs. To do that we will be grouping the value field and counting the results. Any over 1 is a duplicate.

SELECT `value`
FROM `catalog_product_entity_varchar`
WHERE `attribute_id` = 121 AND `store_id` = 0
GROUP BY `value`
HAVING COUNT(`value`) > 1

We are running this against the default store view, if you want to run against other stores change the store_id = 0 to the store in question.

To combine this into a single query we can change the 121 absolute value to the sub-query we generated earlier.

SELECT `value`
FROM `catalog_product_entity_varchar`
WHERE `attribute_id` = (
    SELECT `eav_attribute`.`attribute_id` 
    FROM `eav_attribute` 
    WHERE `attribute_code` = 'url_key' AND `entity_type_id` = '4'
AND `store_id` = 0
GROUP BY `value`
HAVING COUNT(`value`) > 1

We found a total of 7 duplicated URLs. We can now notify the customer that they need to update those 7 products while we dig further into where the issue came from.

I hope this helps you find any similar issues in your stores and saves needless support time. If we determine the what caused the duplication problem, I'm sure I'll add an article on that.

Avatar of Clive Walkden

Clive Walkden


Latest Articles

Linux command line tools, installations etc


How to Install NordLayer VPN Client on Ubuntu 20.04 and Connect to a Virtual Network

A simple to follow installation guide for NordLayer VPN

MySQL usage, tweaks and learnings


Mastering MySQL Database Imports on Linux

Learn efficient ways to import MySQL databases on Linux using the mysql command-line client. Explore the --source option and < operator for seamless data migration. Master MySQL imports on Linux with our comprehensive guide.

DevOps principles and tool usage


Mastering SSH Key Conversions for DevOps

A guide to convert SSH keys from one version to another using Linux CLI