Ugrade 2.4.2 to 2.4.3 error: Integrity constraint violation

Question

I was trying to upgrade from 2.4.2 to 2.4.3 in my test environment. While trying to run bin/magento setup:upgrade,

The below error appeared:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '249191' for key 'PRIMARY', query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id)

How to fix these issues

Answer 1

It seems that when you upgrade to 2.4.3 Magento discovers duplicates in

catalog_url_rewrite_product_category

table. I haven't figured out from where they came as in the 2.4.2 version all worked fine. No errors anywhere. I am suspecting that these came from migration. My store was migrated from version 1.

To solve this issue I created a new temporary table in the database running this query:

CREATE TABLE copy_of_table SELECT DISTINCT url_rewrite_id, category_id, product_id FROM catalog_url_rewrite_product_category;

With this, the new table created has removed all the duplicates. So then I added the same relations from

catalog_url_rewrite_product_category

and deleted

table catalog_url_rewrite_product_category

and renamed the temporary table with this setup:upgrade

Don't do this in production instances and always take backup of the database before.

Answer 2

Issue is resolved. When you upgrade to Magento 2.4.3, it appears that Magento finds duplicates in the catalog url rewrite product category table. Since everything was working properly in version 2.4.2, I'm not sure where they came from. No mistakes at all. These most likely came via migration, in my opinion. Version 1 of my store got upgraded.

To address this problem I made a new temporary table in the database and ran the following query:

delete from catalog_url_rewrite_product_category
    where url_rewrite_id in (select url_rewrite_id from
        (select * from
               (select url_rewrite_id, count(*) as c from catalog_url_rewrite_product_category
group by url_rewrite_id) as q1 having c > 1) as q2);

Use this one for production: (First, check the surroundings of your test)

CREATE TABLE tmp_catalog_url_rewrite_product_category_backup AS SELECT * FROM catalog_url_rewrite_product_category;
TRUNCATE catalog_url_rewrite_product_category;
INSERT INTO catalog_url_rewrite_product_category SELECT * FROM tmp_catalog_url_rewrite_product_category_backup GROUP BY url_rewrite_id, category_id, product_id;
DROP TABLE tmp_catalog_url_rewrite_product_category_backup;