Database Error Updating Magento to version 2.4.3 on setup:upgrade

[burgh8wp]

After updating Magento from the version. 2.4.2-p1 to 2.4.3 during setup:upgrade command we see below error:

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

The problem is this table holds around 20,000 records, and looks to be hundreds, potentially over 1000 instances of a duplicate ID for url_rewrite_id which stems from Magento 2.4.3 looking to force a primary key onto the database table catalogu_url_rewrite_product_category

Preconditions (*)

1. Update Magento to version 2.4.3
2. Error appears when running php bin/magento setup:upgrade command

Steps to reproduce (*)

1. Error appears when running php bin/magento setup:upgrade command after changing composer.json to Magento 2.4.3 version

Expected result (*)

1. Upgrade Magento to version 2.4.3 and complete setup:upgrade query

Actual result (*)

php bin/magento setup:upgrade
Cache types config flushed successfully
Cache cleared successfully
File system cleanup:
/home/storm/sites/sitename/public/generated/code/Laminas
/home/storm/sites/sitename/public/generated/code/Magento
/home/storm/sites/sitename/public/generated/code/Psr
/home/storm/sites/sitename/public/generated/code/Symfony
The directory '/home/storm/sites/sitename/public/generated/metadata/' doesn't exist - skipping cleanup
Updating modules:
Cache cleared successfully
Schema creation/updates:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '106852' for key 'PRIMARY', query was: ALTER TABLE `catalog_url_rewrite_product_category` ADD CONSTRAINT  PRIMARY KEY (`url_rewrite_id`)

[ ] Severity: S0 _- Affects critical data or functionality and leaves users without workaround.

** Changing back to 2.4.2-p1 does not give this error output and setup:upgrade completes fine.

[m2-assistant]

Hi @burgh8wp. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, please, review the Magento Contributor Assistant documentation.

Please, add a comment to assign the issue: @magento I am working on this

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

🕙 You can find the schedule on the Magento Community Calendar page.

📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, please join the Community Contributions Triage session to discuss the appropriate ticket.

🎥 You can find the recording of the previous Community Contributions Triage on the Magento Youtube Channel

✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[Thundar]

I had the same issue and tried to remove the duplicated entries (same versions for update). I have a similar issue again after saving a category with different url in store scope: eg:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '174976976' for key 'PRIMARY', query was: INSERT INTO catalog_url_rewrite_product_category (url_rewrite_id,category_id,product_id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)
I think this should be solved in an upgrade. Introduced by ece2528 , issue was #9581

@ihor-sviziev predicted it #25746 (review) for backward compatibility, I'm adding the issue for different scoped attribute.

[ihor-sviziev]

Hi @burgh8wp @Thundar,
You should remove duplications in the catalog_url_rewrite_product_category table before doing an upgrade to 2.4.3.
Unfortunately, I don't have such an issue, so I can't provide you a good solution now.

[burgh8wp]

Hi @ihor-sviziev appreciate your reply. But in this instance, they have been there for years with no issue. What has changed in version 2.4.3 specifically where this is no longer acceptable? There are literally thousands of duplications in this table on this 1 site, where there are circa 20,000 individual records. So going through to manually delete is not an option. Also the data is there for a reason, so deleting will break the data on site somewhere. So a better solution is definitely needed. Even a script to run manually once to change/update the ID number? But is this ID number linked from other database tables?

[Thundar]

@ihor-sviziev done, but that's not enough (unfortunately). I could upgrade removing all the duplicates (manually, they were 70K, I don't think this can be a solution for everybody), but I couldn't update the category url in a store scope later, as from the INSERT query above.

[Thundar]

@magento give me 2.4-develop instance

[magento-deployment-service]

Hi @Thundar. Thank you for your request. I'm working on Magento instance for you.

[magento-deployment-service]

Hi @Thundar, here is your Magento Instance: https://85b60892c241a3d87d464e439dff008f-2-4-develop.instances.magento-community.engineering
Admin access: https://85b60892c241a3d87d464e439dff008f-2-4-develop.instances.magento-community.engineering/admin_81f4
Login: 5f67abc3
Password: 3759b6fe684d

[Thundar]

I'm investigating the second problem (the insert) as I'm not being able to reproduce it in a fresh installation.

[burgh8wp]

@ihor-sviziev done, but that's not enough (unfortunately). I could upgrade removing all the duplicates (manually, they were 70K, I don't think this can be a solution for everybody), but I couldn't update the category url in a store scope later, as from the INSERT query above.

Did you create a script to remove the duplicates? If so, how did you handle the ID number change or did you just delete them?

[ihor-sviziev]

@burgh8wp, sorry, I don't have this issue, can't help you with fixing that :(