When does the duplicate primary key error occur ?
When you do a php bin/magento setup:upgrade ,a duplicate primary key error is the last thing you want, as you may manually go into mysql and fix it by removing the duplicate only to find out that the next error would be about the same thing but with a different/new key. This is a result of a corrupted database or it could be because of a mysqldump and mysqlimport operation which has resulted in duplicate records. This is quite bad as you can’t really run any single mysql queries to fix this common magento database issue and going in manually to delete dupes it can be very time consuming as you might get thousands of duplicate records.
How did we fix it ?
Going through the same thing ourselves in some of our Magento migrations and upgrades we have created a php script which connects to your mysql database.
- The script will find all your dupes and create a text file with all of the delete queries needed, so you can just copy and paste them into MySQL Workbench or any mysql editor you use.
- The script will create the insert queries necessary so you won’t be loosing any data (it will add back the previous records that were duplicated).
- Make sure you execute the delete queries first and the inserts after.
<?php
// Takeoff Digital Magento 2
// https://takeoff.digital
$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();
?>
Create a directory and copy and paste this code.
Replace:
- – database_name with your magento database name
- – username with your magento database username
- – pass with your magento database password
- – yourdb with your magento database name
- – path with your directory path, if you are in linux you can use pwd in terminal to display your current path
You have to be in terminal mode to execute this script which will create two text files.
Copy and paste the contents of del.txt and execute them following the ins.txt
After doing this try doing php bin/magento setup:upgrade again.
