"Unique constraint violation found" when removing .html suffix

Question

When I try to remove the .html suffix from category, Magento 2.2.0 gives me this error.

Has anyone else ran into the same problem? How can I resolve it?

Thank you

Answer 1

This can be caused by duplicate entries for the same product in url_rewrite database table.

You can follow the below steps to solve this issue:

• Backup your current Magento2 database

• Delete all product records from table url_rewrite. You can run the following SQL command:

   DELETE FROM url_rewrite WHERE entity_type = 'product';
  

• Login to your Magento 2 backend

• Re-save all your Categories

• Clean the Magento2 cache

• Now, you should be able to save/update the Product URL Suffix in the Configuration Settings.

Answer 2

The other answers here kind of circle around the exact issue without answering it directly. The issue here is not duplicate existing records in the url_rewrite table. The issue is existing rewrites that match what the product URL would be after the .html is removed.

So for example, if you have a product at /acme-product.html and also a CMS page at /acme-product, you'll get this error because the product URL will conflict with the CMS page after the change.

While the other currently proposed solutions may solve the issue, they are either too narrow (solving only certain cases) or overly aggressive (having the potential to lose redirects that should be kept).

Instead, I suggest running the following query to find the specific conflicts. You can then delete them (if manual redirects) or update the corresponding URL keys (if categories or CMS pages).

SELECT r2.*
FROM url_rewrite r1
JOIN url_rewrite r2 ON r2.request_path = REPLACE(r1.request_path, '.html', '')
WHERE r1.entity_type = 'product';

Note that this same error can appear when removing the .html suffix from category URLs. To find the conflicts for that, just change 'product' above to 'category'. And for other types of changes to product/category URLs other than removing .html, you can similarly adjust the REPLACE() arguments above to generate the new URL structure you're trying to achieve.

Answer 3

Adding to Mukesh's answer.

After i ran the suggested step from Mukesh to delete the url_rewrites, my products where then showing as a really long format:

/catalog/product/view/id/2221/name-of-the-product

and i wanted: /name-of-the-product

So i used the following extension to regenerate the url rewrites: https://github.com/olegkoval/magento2-regenerate_url_rewrites

Also ensure URl rewrites are on: Store > Configurations > General > Web > Search Engine Optimization and set Use Web Server Rewrites to Yes

Answer 4

I'm not sure if anyone else is still having this issue, but here is what worked for me. Upgraded from 1.9.x to 2.3.3. The issue is duplicate entries in the url_rewrite table.

The issue was on removing .html suffix on just products. After backing up the url_rewrite table I ran

UPDATE `url_rewrite` SET `request_path` = REPLACE(`request_path`, ".html", "")

I wanted to remove the .html manually. This however couldn't complete but did highlight which fields were the problem. For me, it was auto-generated 301 redirects causing the issue.

In phpmyadmin I then used and deleted all 301 redirects. I could then remove the .html value via Magento Admin.

SELECT * FROM `url_rewrite` WHERE `redirect_type` = 301

Hope this helps someone!

Answer 5

Guys its not solution but can be work around instead to leave empty just place / and save it will work. second option can be change it in DB (core_config_data)

Answer 6

Deleting all 301 type redirects or deleting the 'URL_Rewrite' table will also remove category redirects and other necessary redirects which will lead to loss of all SEO rankings so instead run this SQL Command

DELETE FROM url_rewrite WHERE redirect_type = 301 and request_path NOT LIKE '%.html%'

This will only delete related redirect entries which were generated while applying suffix.

than run the following command UPDATE url_rewrite SET target_path = REPLACE(target_path, ".html", "")

UPDATE url_rewrite SET request_path = REPLACE(request_path, ".html", "") WHERE redirect_type = 301

DELETE FROM url_rewrite WHERE redirect_type = 301 and request_path NOT LIKE '%.html%'

Then check remove .html manually

Answer 7

No need to do through database. This situation can be handled from the admin.There are 2 cases 1.Product suffix and 2.Category suffix Before following these please install this extension and test if it is working fine with your installation : https://github.com/olegkoval/magento2-regenerate_url_rewrites

If the extension is working fine then go ahead you are ready to solve your problem.

1. Product Suffix issue : Go to admin->Marketing->URL Rewrites Then apply a filter with these conditions : Redirect type = 301 and Request Path = products it will now list only the products urls. Select all and delete.

2. Category case : Go to admin->Marketing->URL Rewrites Then apply a filter with these conditions : Target path = catalog/category/view it will list only category rewrites. Select all and delete.

You are now able to remove the suffix from Catalog->Catalog->Search Engine Optimization.

Now you need to regenerate all the urls for fast generation of products and categories urls follow me

php bin/magento ok:urlrewrites:regenerate --entity-type=product --no-reindex --no-cache-clean php bin/magento ok:urlrewrites:regenerate --entity-type=category --no-reindex --no-cache-clean

php bin/magento indexer:reindex php bin/magento cache:flush

That's it