Fixing duplicate primary key problem during Magento upgrade

In a recent Magento upgrade project for one of our merchants, I encountered a problem with the production database during the setup:upgrade process.

The setup:upgrade process failed with the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '131451' for key 'PRIMARY', query was: ALTER TABLE `catalog_url_rewrite_product_category` ADD CONSTRAINT  PRIMARY KEY (`url_rewrite_id`), DEFAULT CHARSET='utf8', DEFAULT COLLATE='utf8_general_ci'

I checked the content of the catalog_url_rewrite_product_category database table and realized it contains a few hundred duplicate IDs.

While searching for a solution, I read several blog posts that suggested various strategies for addressing similar issues, but none of them worked for my specific situation. After some further research, I discovered a method of solving the problem using a temporary table. I modified the approach to fit my needs, and here's what I implemented.

First, we copy all the data into a temporary table:

CREATE TABLE tmp_catalog_url_rewrite_product_category_backup AS SELECT * FROM catalog_url_rewrite_product_category;

Next, we truncate the original table:

TRUNCATE catalog_url_rewrite_product_category;

And now comes the crucial part: we copy the data from the temporary table back to the original table but make sure to group the data by the field url_rewrite_id, category_id, product_id as this will filter out any duplicates:

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;

Finally, we delete the temporary table:

DROP TABLE tmp_catalog_url_rewrite_product_category_backup;

That method worked quickly and reliably, and afterward, the setup:upgrade process ran smoothly.