Duplicate entry in DB after upgrading to 2.4.3

Question

I am having this strange issue when trying to upgrade magento 2.4.2p1 to 2.4.3

In bin/magento setup:upgrade

I get:

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

I found entry in table

catalog_url_rewrite_product_category

entry

543741

and deleted it. But I still can't setup:upgrade

I also tried to empty url.rewrite table but again no luck

I am stuck. Any help much appreciated!

Answer 1

This solution worked for me (DO NOT RUN THIS ON PRODUCTION!)

delete from catalog_url_rewrite_product_category
    where url_rewrite_id in (select url_rewrite_id from
        (select * from
               (select url_rewrite_id, count(*) as c from catalog_url_rewrite_product_category
group by url_rewrite_id) as q1 having c > 1) as q2);

For production use this one: (First check on your test environment)

CREATE TABLE tmp_catalog_url_rewrite_product_category_backup AS SELECT * FROM catalog_url_rewrite_product_category;
TRUNCATE catalog_url_rewrite_product_category;
INSERT INTO catalog_url_rewrite_product_category SELECT * FROM tmp_catalog_url_rewrite_product_category_backup GROUP BY url_rewrite_id, category_id, product_id;
DROP TABLE tmp_catalog_url_rewrite_product_category_backup;

Answer 2

Problem solved. It seems that when you upgrade to 2.4.3 Magento discovers duplicates in catalog_url_rewrite_product_category table. I haven't figure out from where they came as in 2.4.2 version all worked fine. No errors anywhere. I am suspecting that these came from migration. My store was migrated from version 1.

To solve this issue I created a new temporary table in database running this query:

CREATE TABLE copy_of_table SELECT DISTINCT url_rewrite_id, category_id, product_id FROM catalog_url_rewrite_product_category;

With this the new table which created has removed all the duplicates.

So then I added the same relations from catalog_url_rewrite_product_category , deleted table catalog_url_rewrite_product_category and renamed the temporary table.

With this

setup:upgrade

worked fine.

I am still looking if this solution didn't created any other issues but for now all seem fine

Caution: Don't do this in production instance and always take backup the database before.

Answer 3

A neater solution would be to use:

ALTER IGNORE TABLE catalog_url_rewrite_product_category ADD UNIQUE KEY (url_rewrite_id, category_id, product_id)

This forces MySQL to delete the duplicate entries on the table, after which you can delete the new index if you wish too.

Answer 4

It's a bug in Magento 2.4.3 upgrade procedure, see GitHub for details:

https://github.com/magento/magento2/issues/33770

You can apply on of the workaround posted here.

Answer 5

You can just use this script here https://takeoff.digital/2022/09/15/duplicate-primary-key-error-in-magento-2/
Please read the explanations in the above url if it doesn't make sense for you as this will generate a couple of text files which you will then have to execute in your mysql editor.

<?php
$servername = "localhost";
$username = "username";
$password = "pass";
$dbname = "yourdb";
$deletefile = "path/del.txt";
$insfile = "path/ins.txt";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error)
{
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT url_rewrite_id  FROM database_name.catalog_url_rewrite_product_category group by url_rewrite_id having count(*) > 1 order by url_rewrite_id desc;";
$result = $conn->query($sql);

if ($result->num_rows > 0)
{
    while ($row = $result->fetch_assoc())
    {
        $roid = $row["url_rewrite_id"];
        $line = "DELETE FROM database_name.catalog_url_rewrite_product_category where url_rewrite_id = '$roid';" . "\n";
        file_put_contents($deletefile, $line, FILE_APPEND | LOCK_EX);
        $query2 = "SELECT * FROM database_name.catalog_url_rewrite_product_category WHERE url_rewrite_id = '$roid' limit 1;" . "\n";
        $data = $conn->query($query2)->fetch_assoc();
        $urid = $data["url_rewrite_id"];
        $catid = $data["category_id"];
        $pid = $data["product_id"];
        $insquery = "INSERT INTO database_name.catalog_url_rewrite_product_category (url_rewrite_id, category_id, product_id) VALUES('$urid', '$catid','$pid');" . "\n";
        file_put_contents($insfile, $insquery, FILE_APPEND | LOCK_EX);
    }
}
$conn->close();
?>

Answer 6

If you are trying to update your store the last Magento version (once this post was written 2.4.3 is latest), and you are getting the following error message:

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

Then this is because you are having duplicated url rewrites on your database. So, to solve this out, you will need to remove those useless and duplicated entries. To reach this out successfully, i suggest performing the following steps gettng logged into your database:

CREATE TABLE catalog_url_rewrite_product_category_nodup AS SELECT * FROM catalog_url_rewrite_product_category;
truncate table catalog_url_rewrite_product_category_nodup;
insert into catalog_url_rewrite_product_category_nodup (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_nodup);