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 :(

[Thundar]

@burgh8wp i strongly suggest NOT to do this in a production environment.
START TRANSACTION; CREATE TABLE catalog_url_rewrite_product_category_temp SELECT DISTINCT * FROM catalog_url_rewrite_product_category; ALTER TABLE catalog_url_rewrite_product_category RENAME catalog_url_rewrite_product_category_backup; ALTER TABLE catalog_url_rewrite_product_category_temp RENAME catalog_url_rewrite_product_category; DROP TABLE catalog_url_rewrite_product_category_backup; COMMIT;

Edit: this query solved also the INSERT issue I quoted before.
Edit2: backup table dropped as for #33770 (comment)

[MajorKuprich]

@Thundar fix unfortunately didn't resolve issue for me.
But I've found solution which rollback changes from ece2528 by remove primary constraint.

etc/db_schema.xml

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="catalog_url_rewrite_product_category">
        <constraint xsi:type="primary" referenceId="PRIMARY" disabled="true">
            <column name="url_rewrite_id"/>
        </constraint>
    </table>
</schema>

Next generate whitelist: bin/magento setup:db-declaration:generate-whitelist
Finally command bin/magento setup:db-data:upgrade should be executed without errors. :)

Reference: https://magento.stackexchange.com/questions/294038/magento-2-3-remove-constraint-from-db-schema-xml-file

[Thundar]

@MajorKuprich i suggest you not to remove the constraint. Instead, you should find out what is blocking your table, and the cause of the ids duplicated.
My query removes duplicates only where a rewrite_id is associated to the same category_id and product_id in different rows. If it doesn't work for you, maybe you have one rewrite_id related to two different categories and/or products: that could be risky for your project.
Or, you could have something that is adding duplicates over and over: that would be interesting to investigate.

[Munktells]

I can confirm this issue too, trying to upgrade from 2.4.2-p1 to 2.4.3.
I'm using MariaDB in a multistore setup.

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

@Thundar trick didn't work for me either, after running the SQL statement, I got a new error when running the command php bin/magento setup:upgrade

SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint dev/CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID to system tables, query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id), ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE, ADD CONSTRAINT FK_BB79E64705D7F17FE181F23144528FC8 FOREIGN KEY (url_rewrite_id) REFERENCES url_rewrite (url_rewrite_id) ON DELETE CASCADE, ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES catalog_category_entity (entity_id) ON DELETE CASCADE, ADD INDEX CATALOG_URL_REWRITE_PRODUCT_CATEGORY_CATEGORY_ID_PRODUCT_ID (category_id,product_id), COMMENT=url_rewrite_relation, DEFAULT CHARSET=utf8, DEFAULT COLLATE=utf8_general_ci

[mahmoudbeikhit]

I have the Same Error after upgrade Magento form 2.4.1 to Magento 2.4.3
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '17369' for key 'PRIMARY', query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id)
And i removed all Duplicated and it work

[ihor-sviziev]

@mahmoudbeikhit you can use solution like this #33770 (comment)

[ihor-sviziev]

BTW, it feels like the declarative schema missing the feature removing duplicates like in regular MySQL connection during adding unique index

magento2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php

Lines 2800 to 2816 in 7c6b636

	while ($cycle === true) {
	try {
	$result = $this->rawQuery($query);
	$cycle = false;
	} catch (\Exception $e) {
	if (in_array(strtolower($indexType), ['primary', 'unique'])) {
	$match = [];
	// phpstan:ignore
	if (preg_match('#SQLSTATE\[23000\]: [^:]+: 1062[^\']+\'([\d.-]+)\'#', $e->getMessage(), $match)) {
	$ids = explode('-', $match[1]);
	$this->_removeDuplicateEntry($tableName, $fields, $ids);
	continue;
	}
	}
	throw $e;
	}
	}

@maghamed what do you think about it?

[jsyvanne]

I can confirm this issue too, trying to upgrade from 2.4.2-p1 to 2.4.3.
I'm using MariaDB in a multistore setup.

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

@Thundar trick didn't work for me either, after running the SQL statement, I got a new error when running the command php bin/magento setup:upgrade

SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint dev/CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID to system tables, query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id), ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE, ADD CONSTRAINT FK_BB79E64705D7F17FE181F23144528FC8 FOREIGN KEY (url_rewrite_id) REFERENCES url_rewrite (url_rewrite_id) ON DELETE CASCADE, ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES catalog_category_entity (entity_id) ON DELETE CASCADE, ADD INDEX CATALOG_URL_REWRITE_PRODUCT_CATEGORY_CATEGORY_ID_PRODUCT_ID (category_id,product_id), COMMENT=url_rewrite_relation, DEFAULT CHARSET=utf8, DEFAULT COLLATE=utf8_general_ci

This is probably due catalog_url_rewrite_product_category_backup table left over from the transaction in Thundar's trick. It has foreign keys set with the same names as the magento update tries to add.

I ended up dropping the backup table and got setup to go through.

[m2-assistant]

Hi @engcom-Delta. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[Thundar]

@jsyvanne you are right. I'll edit my snippet to drop that table then.

[magenest-dev]

This issue can happen if the instance previously used the Data migration tool to import M1 data. Duplicate url_rewrite_id rows under catalog_url_rewrite_product_category but only one row has matching product_id with entity_id on url_rewrite table.

Irrelevant duplicated rows can be removed from catalog_url_rewrite_product_category with the following (backup this table first) :

DELETE c FROM catalog_url_rewrite_product_category c INNER JOIN (SELECT * FROM url_rewrite WHERE entity_type = 'product' ) s WHERE c.url_rewrite_id = s.url_rewrite_id AND c.product_id <> s.entity_id;

[WebHostingAce]

I simply emptied this table catalog_url_rewrite_product_category .

Then you can simply re-generate this table by disabling and enabling Anchor for the category.

If you have many categories. This might not be the solution.

[Thundar]

@amenk I use this to prevent duplicates from both category save and csv import: #33770 (comment)

[ihor-sviziev]

It looks like this issue was caused by #34210 + in 2.4.3 was added a unique key on this table.

The correct fix will be following:

1. Execute the following SQL before running php bin/magento setup:upgrade command:

START TRANSACTION; 
CREATE TABLE catalog_url_rewrite_product_category_backup SELECT * FROM catalog_url_rewrite_product_category;
TRUNCATE TABLE catalog_url_rewrite_product_category;
INSERT INTO catalog_url_rewrite_product_category SELECT DISTINCT * FROM catalog_url_rewrite_product_category_backup; 
COMMIT;

After the successful upgrade and making sure everything works fine, remove the backup table

DROP TABLE catalog_url_rewrite_product_category_backup;

2. Apply as a patch changes from the following PR: Delete existing URL rewrite IDs in After Replace #34791

[burgh8wp]

Our catalog_url_rewrite_product_category appears to be corrupt and above queries which previously worked, appear to no longer be working. Hitting errors on duplicate keys as before, then when we truncate the data, we get errors relating to foreign keys and inability to run database table altering commands through setup:upgrade

Any advice/pointers on where to go to repair the database or regenerate? The core issues will have stemmed from the data (database) being migrated from M1 to M2 circa 4 years ago we believe. The site operated fine up to version 2.4.3 of Magento though. The solutions from @Thundar and @ihor-sviziev listed above previously worked for us earlier this year, but no longer working.... still using Magento version 2.4.3

Looks like this has been fixed in 2.4.4 ? https://devdocs.magento.com/guides/v2.4/release-notes/open-source-2-4-4.html Referencing below part:

"Data patches can no longer ignore a table’s unique constraints and insert duplicate values into a MySQL database table. Previously, patches could insert duplicate values, which corrupted the database."

[hostep]

@burgh8wp: the section you reference in the release notes is not the same as what is being discussed here (it has to do with #32283). So don't expect this to be fixed in 2.4.4 (at least not by what you reference).

In this issue here, an attempt is made to try to make the url_rewrite_id unique in the catalog_url_rewrite_product_category table (which was previously allowed on the database level). The thing you reference in release notes has to do with tables that already had a unique key set, but with a very obscure opportunity to ignore those unique rows during bin/magento setup:upgrade. So it's definitely not the same.

I'm afraid that in your case, you'll probably want to manually search for duplicated url_rewrite_id in that particular table and try to figure out which ones you can delete to make them unique (or write a script to do it in case you have hundreds/thousands of occurrences like this).

Also, maybe check out the solution from #34791, it sounds related to this issue. But it looks like this one isn't included in Magento 2.4.4 yet. So it's probably scheduled for Magento 2.4.5.

[burgh8wp]

Ok, thanks for your reply @hostep In our case, there isn't actually any duplicated ID's. When you search by the ID number it references in the table, there is only 1 entry with that ID. It's the lack of Primary key in the table that seems to be the initial issue. We can truncate the table, then add the primary key, but on import of the data back into the table, we get the foreign key error. I suspect it's just an issue with our instance and the migration from M1 causing something wrong there. So really just looking for a solution to be able to delete the table and regenerate it correctly. Which seems to be tricky. Any ideas appreciated for that?

[hostep]

Which foreign key gives you problems? It might reference product id's, or category id's or url_rewrite id's that no longer exist maybe?

Also, if you don't care about potential SEO impact, you can try to run this tool to find potential incorrect data in your categories & products, which you then need to fix somehow, and then to run a tool like this to regenerate all url rewrites (which can also automatically fix category url_path issues the first tool might show you). Maybe that fixes your issues once and for all 🤞

[burgh8wp]

Thanks @hostep feedback appreciated. Will look into those tools. Have deciphered it is definitely a lingering issue from M1 to M2 migration.

SQLSTATE[HY000]: General error: 1005 Can't create table `table_name`.`catalog_url_rewrite_product_category` (errno: 121 "Duplicate key on write or update"), query was: CREATE TABLE `catalog_url_rewrite_product_category` (
`url_rewrite_id` int UNSIGNED NOT NULL   COMMENT "url_rewrite_id",
`category_id` int UNSIGNED NOT NULL   COMMENT "category_id",
`product_id` int UNSIGNED NOT NULL   COMMENT "product_id",
CONSTRAINT  PRIMARY KEY (`url_rewrite_id`),
CONSTRAINT `CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`)  ON DELETE CASCADE,
CONSTRAINT `FK_D2BB00D30043A5342877FA74E0638ADE` FOREIGN KEY (`url_rewrite_id`) REFERENCES `url_rewrite` (`url_rewrite_id`)  ON DELETE CASCADE,
CONSTRAINT `CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`)  ON DELETE CASCADE,
INDEX `CATALOG_URL_REWRITE_PRODUCT_CATEGORY_CATEGORY_ID_PRODUCT_ID` (`category_id`,`product_id`)
) ENGINE=innodb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci COMMENT="url_rewrite_relation"

The old M1 database had a prefix for all tables, and I think it has muddled up the foreign keys somewhere along the lines, but it just seems to be this catalog_url_rewrite_product_category table only that is affected with any issue.

[burgh8wp]

@hostep you asked earlier for foreign key error which we have been able to replicate again today.

If we truncate the table, and run setup:upgrade we get below error output:

SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint 'table_name/CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID' to system tables, query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id), ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE, ADD CONSTRAINT FK_D2BB00D30043A5342877FA74E0638ADE FOREIGN KEY (url_rewrite_id) REFERENCES url_rewrite (url_rewrite_id) ON DELETE CASCADE, ADD CONSTRAINT CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES catalog_category_entity (entity_id) ON DELETE CASCADE, ADD INDEX CATALOG_URL_REWRITE_PRODUCT_CATEGORY_CATEGORY_ID_PRODUCT_ID (category_id,product_id), COMMENT='url_rewrite_relation', DEFAULT CHARSET='utf8', DEFAULT COLLATE='utf8_general_ci'

Then went back and manually added Primary key to url_rewrite_id field, and error is same as above without that part mentioned. Then changed the Character set and Collation to what it's trying to do in above query, that part saves fine. On setup:upgrade the error is as above, without those parts mentioned.

When we manually try to run that query directly on the DB, we get below error:

1005 - Can't create table table_name.catalog_url_rewrite_product_category (errno: 121 "Duplicate key on write or update")

Now stumped on why that's occurring......?

[burgh8wp]

@hostep we are making progress here, but just wanted to clarify if this issue will continue in 2.4.3 or 2.4.4 after we have fixed.

In 2.4.4 will Magento still create duplicate url_rewrite_id in the catalog_url_rewrite_product_category table?

[hostep]

Hi @burgh8wp, sorry for the lack of feedback but it's a complicated issue you are having and I don't have the time to dive into this.

I have no idea if in Magento 2.4.4 you can still have insertion of duplicated entries. But as mentioned before, take a look at #34791, which sounds related, unfortunately that fix is not included yet in Magento 2.4.4 and will probably get included in 2.4.5, but maybe if that fix helps in your case, you can try to apply it as a patch? Again: I'm not sure if that will resolve your issue, this is only guessing.

[burgh8wp]

Appreciate the reply. It is the same issue, but don't see a patch there? Quite a lengthy article though, but can't see the actual solution..... Will just continue to manually fix as best we can until released in 2.4.5 I think

[hostep]

You can click on the Files Changed tab in that Pull Request, to see the code changes 😉

[burgh8wp]

Thanks @hostep but that patch as I mentioned doesn't look to be a solution for the (latest) issue we are experiencing. We cleaned up the duplicated, managed to complete setup:upgrade again. But the issue continues, and the core reason seems to be that Magento is creating the url_rewrite_id for an ID that is already in the table, and is not generating new ones starting off from the previous latest one.

So for example, it should be auto incrementing around 118669 as the next new ID to go in, but is trying to add various that start 117xxx which suggests it has wrong stamp on where to start the ID? As far as I can see, the patch doesn't address any of this?

[hostep]

What database table are you talking about now? catalog_url_rewrite_product_category or url_rewrite?

If it creates new entries with a lower id than the highest one that exist in url_rewrite table, then maybe the AUTO_INCREMENT on that table is not correctly set? (which would be very weird and unexpected). In that case, try to increase it manually somehow?

It should be one higher than the highest id that already exist:

(well unless you've specified the @@auto_increment_increment variable to something higher than 1 in your app/etc/env.php file, but that probably won't be the case unless you work with Magento Cloud)

Not sure if this is really helpful as this is pretty basic database knowledge that you probably also already have? 🙂

[burgh8wp]

It's the catalog_url_rewrite_product_category I was referring to, the url_rewrite table already has "Auto Increment" enabled. But the former table doesn't. Looked through all our Magento installs including default blank install, and seems catalog_url_rewrite_product_category always has "Auto Increment" disabled. But would make sense to have it enabled? Will test on dev site shortly, but any issues changing this? Will a future Magento upgrade change it back?

[hostep]

No, you definitely do not want an auto increment on catalog_url_rewrite_product_category because its url_rewrite_id column is a foreign key to the url_rewrite_id column of the url_rewrite table. So those id's should move together.

[burgh8wp]

Ok, I see that structure now. If understanding the patch correctly here - #34791

All it's doing is essentially allowing duplicates to be created for url_rewrite_id for the catalog_url_rewrite_product_category table? That's it? Assuming that is not going to cause other further issues down the line?

[levelzero-magento]

Hi try this solution and works for me:

For me the problem was that the afterReplace function in Magento\CatalogUrlRewrite\Model\Category\Plugin\Storage added duplicate url rewrites to the catalog_url_rewrite_product_category table. Since a while there is a primary key on that table so this gives an error.

The solution is to delete the existing url_rewrites first, the function removeMultiple already exists.

Overwrite Magento\CatalogUrlRewrite\Model\Category\Plugin\Storage and add this line:

$this->productResource->removeMultiple(array_column($toSave, 'url_rewrite_id'));
before the saveMultiple between lines 63 - 64.

So it becomes like this:

if (count($toSave) > 0) {
    $this->productResource->removeMultiple(array_column($toSave, 'url_rewrite_id'));
    $this->productResource->saveMultiple($toSave);
}

can someone more experienced than me verify?

[rangerz]

For my solution to rebuild catalog_url_rewrite_product_category table

1. SQL to run TRUNCATE TABLE catalog_url_rewrite_product_category;
2. Rebuild catalog_url_rewrite_product_category

$om = \Magento\Framework\App\ObjectManager::getInstance();
$categoryCollectionFactory = $om->get(\Magento\Catalog\Model\ResourceModel\Category\CollectionFactory::class);
$urlRewriteHandler = $om->get(\Magento\CatalogUrlRewrite\Observer\UrlRewriteHandler::class);
$urlRewriteBunchReplacer = $om->get(\Magento\CatalogUrlRewrite\Model\UrlRewriteBunchReplacer::class);
$categoryCollection = $categoryCollectionFactory->create()->addAttributeToSelect('*')->addIsActiveFilter();

foreach ($categoryCollection as $category) {
    echo "Category [" . $category->getId() . "] Processing ...\n";

    // for $category->dataHasChangedFor('is_anchor') === true
    $category->setOrigData('is_anchor', !$category->getData('is_anchor'));

    $productUrlRewriteResult = $urlRewriteHandler->generateProductUrlRewrites($category);
    $urlRewriteBunchReplacer->doBunchReplace($productUrlRewriteResult);
}

Ref: vendor/magento/module-catalog-url-rewrite/Observer/CategoryProcessUrlRewriteSavingObserver.php

magento2/app/code/Magento/CatalogUrlRewrite/Observer/CategoryProcessUrlRewriteSavingObserver.php

Lines 123 to 124 in 2e45de7

	$productUrlRewriteResult = $this->urlRewriteHandler->generateProductUrlRewrites($category);
	$this->urlRewriteBunchReplacer->doBunchReplace($productUrlRewriteResult);

[abvdveen]

Same issue here, upgrading from 2.3.7-p3 to 2.4.5: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2918522' for key 'catalog_url_rewrite_product_category.PRIMARY', query was: ALTER TABLE catalog_url_rewrite_product_category ADD CONSTRAINT PRIMARY KEY (url_rewrite_id)

[WouterSteen]

Problem here in our case was that our customer had duplicate entries in the url_key field from products.

This issue needs to be fixed, so no duplicate urls can exist. This is what we did:

• installed: composer2 require elgentos/regenerate-catalog-urls
• bin/magento regenerate:product:url

With output let the customer fix there url keys

Obv we did this on a test env, and after the customer fixed the issues with the url keys, on production.

[Aditya-Prakash-Talluru]

@magento I am working on it

[Calvin495]

This issue can happen if the instance previously used the Data migration tool to import M1 data. Duplicate url_rewrite_id rows under catalog_url_rewrite_product_category but only one row has matching product_id with entity_id on url_rewrite table.

Irrelevant duplicated rows can be removed from catalog_url_rewrite_product_category with the following (backup this table first) :

DELETE c FROM catalog_url_rewrite_product_category c INNER JOIN (SELECT * FROM url_rewrite WHERE entity_type = 'product' ) s WHERE c.url_rewrite_id = s.url_rewrite_id AND c.product_id <> s.entity_id;

@magenest-dev We also have a similar issue. looks like the two same url_rewrite_id in the catalog_url_rewrite_product_category table but when we try to find the associated records in the url_rewrite table we only find one record for a particular url_rewrite_id. this seems weird. when we tried to delete the duplicates using your query it deleted all records from the url_rewrite table.