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.

[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 https://github.com/magento/magento2/issues/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 https://github.com/magento/magento2/pull/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 TABLEcatalog_url_rewrite_product_categoryADD CONSTRAINT PRIMARY KEY (url_rewrite_id), ADD CONSTRAINTCAT_URL_REWRITE_PRD_CTGR_PRD_ID_CAT_PRD_ENTT_ENTT_IDFOREIGN KEY (product_id) REFERENCEScatalog_product_entity(entity_id) ON DELETE CASCADE, ADD CONSTRAINTFK_D2BB00D30043A5342877FA74E0638ADEFOREIGN KEY (url_rewrite_id) REFERENCESurl_rewrite(url_rewrite_id) ON DELETE CASCADE, ADD CONSTRAINTCAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_IDFOREIGN KEY (category_id) REFERENCEScatalog_category_entity(entity_id) ON DELETE CASCADE, ADD INDEXCATALOG_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 https://github.com/magento/magento2/pull/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 - https://github.com/magento/magento2/pull/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
https://github.com/magento/magento2/blob/2e45de7acefbd665ffa90c5e24dda6a5286c36ee/app/code/Magento/CatalogUrlRewrite/Observer/CategoryProcessUrlRewriteSavingObserver.php#L123-L124

[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.