magento2: Database Error Updating Magento to version 2.4.3 on setup:upgrade

@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 https://github.com/magento/magento2/issues/33770#issuecomment-908245196

It looks like this issue was caused by https://github.com/magento/magento2/issues/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;

1. Apply as a patch changes from the following PR: https://github.com/magento/magento2/pull/34791

I have yet the same issue in production. When adding or removing products from certain categories, using csv import or category smart rules, it gives me Integrity constraint violation: 1062 Duplicate entry 'xxxxx' for key 'PRIMARY

This patch for me, after upgrading Magento with this query https://github.com/magento/magento2/issues/33770#issuecomment-898520221 (run in maintenance mode).

Patch file on magento/module-catalog-url-rewrite:

--- a/Model/ResourceModel/Category/Product.php	2021-09-27 09:49:30.000000000 +0200
+++ b/Model/ResourceModel/Category/Product.php	2021-09-27 09:49:30.000000000 +0200
@@ -53,12 +53,12 @@
     {
         $connection = $this->getConnection();
         if (count($insertData) <= self::CHUNK_SIZE) {
-            return $connection->insertMultiple($this->getTable(self::TABLE_NAME), $insertData);
+            return $connection->insertOnDuplicate($this->getTable(self::TABLE_NAME), $insertData);
         }
         $data = array_chunk($insertData, self::CHUNK_SIZE);
         $totalCount = 0;
         foreach ($data as $insertData) {
-            $totalCount += $connection->insertMultiple($this->getTable(self::TABLE_NAME), $insertData);
+            $totalCount += $connection->insertOnDuplicate($this->getTable(self::TABLE_NAME), $insertData);
         }
         return $totalCount;
     }

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?

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

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?

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.

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

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 https://github.com/magento/magento2/commit/ece2528a6f09da1486d63ddd794f746501984413 , issue was https://github.com/magento/magento2/issues/9581

@ihor-sviziev predicted it https://github.com/magento/magento2/pull/25746#pullrequestreview-359746861 for backward compatibility, I’m adding the issue for different scoped attribute.

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;

@ihor-sviziev My Pullrequest https://github.com/magento/magento2/pull/34791 does something similar like @antondkv approach - no clue what’s better…

I realized today, that https://github.com/magento/magento2/issues/33770#issuecomment-898520221 is just dropping all the contraints as a side-effect, so it is no suprise that they key violation vanish after that.

@sidolov, I added P2 priority, as this issue prevents Magento upgrade.

@Thundar fix unfortunately didn’t resolve issue for me. But I’ve found solution which rollback changes from https://github.com/magento/magento2/commit/ece2528a6f09da1486d63ddd794f746501984413 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

@amenk I use this to prevent duplicates from both category save and csv import: https://github.com/magento/magento2/issues/33770#issuecomment-924837177

I faced the same issue in 2.4.3 during catalog importing for multi-stores. The duplication appears in \Magento\CatalogUrlRewrite\Model\ResourceModel\Category\Product::saveMultiple method on insertMultiple step (56 and 61 rows).

I tried to find out the root of the problem but have not enough time for now, so as a temporary solution I just replaced the insert function with insertOnDuplicate one and it get working.

@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)

Hey @Thundar

in this way new table does not contains foreign keys, and than we get next error: “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,”

to prevent this just use following solution:

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

@victor-v-rad: could you have a look at the remark from @magenest-dev above? Thanks! 🙂

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

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

https://github.com/magento/magento2/blob/7c6b6365a3c099509d6f6e6c306cb1821910aab0/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L2800-L2816

@maghamed what do you think about it?