Running delta migration gives the 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry' Error

Question

Magento 1.9.4 to Magento 2.4.2

We are migrating Magento 1.9.4 to Magento 2.4.2. I've ran the initial migration some time back and it worked. Now, about a month or so later I would like to run it again to transfer only the new products and categories added since the initial migration. I connect to the Magento 2.4.2 store via PuTTy and run the following command:

php bin/magento migrate:delta vendor/magento/data-migration-tool/etc/opensource-to-opensource/1.9.4.0/config.xml

it then comes up with the following:

In Mysql.php line 599:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-18414' for key 'CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID', query was: INSERT  INTO `catalog_product_link_att
  ribute_int` (`value_id`,`product_link_attribute_id`,`link_id`,`value`) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?
  , ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?
 ON DUPLICATE KEY UPDATE `value_id` = VALUES(`value_id
  `), `product_link_attribute_id` = VALUES(`product_link_attribute_id`), `link_id` = VALUES(`link_id`), `value` = VALUES(`value`)

In Mysql.php line 110:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-18414' for key 'CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID', query was: INSERT  INTO `catalog_product_link_att
  ribute_int` (`value_id`,`product_link_attribute_id`,`link_id`,`value`) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?
  , ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?,
  ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)
  , (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?) ON DUPLICATE KEY UPDATE `value_id` = VALUES(`value_id
  `), `product_link_attribute_id` = VALUES(`product_link_attribute_id`), `link_id` = VALUES(`link_id`), `value` = VALUES(`value`)


In Mysql.php line 91:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-18414' for key 'CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID'


migrate:delta [-r|--reset] [-a|--auto] [--] <config>

We may have changed some category names on the old system. Is this what is causing the issue? What can I do to proceed? What would happen if I run the full migration instead of the delta one? (I haven't done too much work on the new system so maybe this is a way to go?)

---

If I try to run it from within the bin folder - I get the following Error:

In Config.php line 70:

  Invalid config filename: vendor/magento/data-migration-tool/etc/opensource-to-opensource/1.9.4.0/config.xml

---

---

UPDATED: I tried to run the following command to just overwrite everything but it gives the Error below:

php bin/magento migrate:data -r -a vendor/magento/data-migration-tool/etc/opensource-to-opensource/1.9.4.0/config.xml

In Mysql.php line 182:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10963-1052' for key 'CAT_PRD_ENTT_MDA_GLR_VAL_TO_ENTT_VAL_ID_ENTT_ID'

---

---

UPDATED on July 12th, 2021:

I figured out that the biggest issue might have been that I was logged into just the account I was trying to do the delta migration into as opposed to being logged into ROOT (SSH via PuTTy). When I log into root, it starts running and then gives this 1 Error:

In Mysql.php line 182:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10963-1052' for key 'CAT_PRD_ENTT_MDA_GLR_VAL_TO_ENTT_VAL_ID_ENTT_ID'

Any ideas what this means and how to fix it?

Answer 1

Probably the delta migration fails because the initial migration you ran time ago wasn't done with the delta option

If you are able to clean the new site's database (truncate tables you want data to be migrated to) then you should run the full migration again

On the last site I migrated I only needed some tables, so when we were ready to migrate I just ran a full migration after truncating the needed tables to avoid keys issues

SET FOREIGN_KEY_CHECKS = 0;
#customers
TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;
ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
#orders
TRUNCATE TABLE `checkout_agreement`;
TRUNCATE TABLE `checkout_agreement_store`;
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;
TRUNCATE TABLE `reporting_orders`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;
TRUNCATE TABLE `salesrule_coupon_aggregated`;
TRUNCATE TABLE `salesrule_coupon_aggregated_updated`;
TRUNCATE TABLE `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;
SET FOREIGN_KEY_CHECKS = 1;