Actually the issue I will describe affects all magento 2 version I have worked since 2.3.0
We operate a large store with around 80K products and 1000 categories.
So a lot of times we import products with csv files and also we work in admin creating new products with duplicating old ones.
And always in url_rewrite table there are duplicate urls no matter what we do to prevent this. I mean that I have several times all these years cleaned the url_rewrite table and then used magento2-regenerate_url_rewrites to regenerate the urls. Always before this procedure we double check url_keys of every product to be unique (we export all and in excel we check duplicates)
What I have found is that imported products with csv not affecting the duplicate issue. The imported product of course need to saved one by one, else no canonical url is generated.
The main problem in url duplication is when we work in admin and create new products duplicate old ones. Especially if the new ones are configurable products. This procedure when we work with a lot of products creates a mess in url_rewrite table. And I am talking about the children products which are created and they not use the unique url_key of the parent product. As I mention we always use product_id in url key so to be unique.
With this problem I have never found a solution that to clean url_rewrite table every some time and regenerate. You understand that this is not a solution because regenaration takes time. And also breaks a lot of our urls
Anybody has an idea or a solution to that problem?
Now we are in magento 2.4.7-p3 and still we have same problem
