I was trying to upgrade to upgrade from 2.3.3 to 2.4.3 in my test environment. While trying to run bin/magento setup:upgrade,
The below error appeared:
Cache cleared successfullySchema creation/updates:SQLSTATE: Integrity constraint violation: 1062 Duplicate entry '2902914' for key 'catalog_url_rewrite_product_category.PRIMARY', query was: ALTER TABLE `catalog_url_rewrite_product_category` ADD CONSTRAINT PRIMARY KEY (`url_rewrite_id`)
When I checked DB, looks like 3264 of duplicate rows are there:
SELECT url_rewrite_id, COUNT(url_rewrite_id) FROM catalog_url_rewrite_product_category GROUP BY url_rewrite_id HAVING COUNT(url_rewrite_id) > 1;
3264 rows in set (0.02 sec)
1. What is the reason for this?
2. How can we solve this issue?
Also is there any official tool from Magento for running database sanity checks before upgrades?
Hope you are doing well!
I am not getting the actual reason behind this, but I will let you know once I will get it. So, for now, you can try the following solution.
NOTE: Kindly take a backup of your database and check in the dev site first.
CREATE TABLE copy_of_table SELECT DISTINCT url_rewrite_id, category_id, product_id FROM catalog_url_rewrite_product_category;