July 10, 2020

cm-mini

Thinking Magento

Migrating to Magento 2 - Orphan Record ID

We've all been there. You run the Magento 2 migration tool and your 5000 year old Magento 2 database returns [ERROR]: Foreign key (FK_CATALOG_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID) constraint fails on source database. Orphan records id or some other similar message about there being orphaned rows in the database. Here's a cheatsheet for getting rid of most of those entries quickly.

INTEGRITY CHECK FIXES

DELETE FROM `sales_flat_order_item` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_order_item`.`order_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_order_payment` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_order_payment`.`parent_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_order_status_history` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_order_status_history`.`parent_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_order_address` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_order_address`.`parent_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_order_tax_item` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order_item` WHERE `sales_order_tax_item`.`item_id` = `sales_flat_order_item`.`item_id`);


DELETE FROM `sales_flat_shipment` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_shipment`.`order_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_shipment_item` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_shipment` WHERE `sales_flat_shipment_item`.`parent_id` = `sales_flat_shipment`.`entity_id`);
DELETE FROM `sales_flat_shipment_comment` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_shipment` WHERE `sales_flat_shipment_comment`.`parent_id` = `sales_flat_shipment`.`entity_id`);
DELETE FROM `sales_flat_shipment_track` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_shipment` WHERE `sales_flat_shipment_track`.`parent_id` = `sales_flat_shipment`.`entity_id`);

DELETE FROM `sales_flat_invoice` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_invoice`.`order_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_invoice_item` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_invoice` WHERE `sales_flat_invoice_item`.`parent_id` = `sales_flat_invoice`.`entity_id`);
DELETE FROM `sales_flat_invoice_comment` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_invoice` WHERE `sales_flat_invoice_comment`.`parent_id` = `sales_flat_invoice`.`entity_id`);
DELETE FROM `sales_flat_invoice_grid` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_invoice` WHERE `sales_flat_invoice_grid`.`entity_id` = `sales_flat_invoice`.`entity_id`);


DELETE FROM `sales_flat_creditmemo` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_order` WHERE `sales_flat_creditmemo`.`order_id` = `sales_flat_order`.`entity_id`);
DELETE FROM `sales_flat_creditmemo_item` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_creditmemo` WHERE `sales_flat_creditmemo_item`.`parent_id` = `sales_flat_creditmemo`.`entity_id`);
DELETE FROM `sales_flat_creditmemo_comment` WHERE NOT EXISTS (SELECT 1 FROM `sales_flat_creditmemo` WHERE `sales_flat_creditmemo_comment`.`parent_id` = `sales_flat_creditmemo`.`entity_id`);


DELETE FROM `catalog_eav_attribute` WHERE NOT EXISTS (SELECT 1 FROM `eav_attribute` WHERE `catalog_eav_attribute`.`attribute_id` = `eav_attribute`.`attribute_id`);
DELETE FROM `catalog_product_entity_int` WHERE NOT EXISTS (SELECT 1 FROM `eav_attribute` WHERE `catalog_product_entity_int`.`attribute_id` = `eav_attribute`.`attribute_id`);
DELETE FROM `eav_entity_attribute` WHERE NOT EXISTS (SELECT 1 FROM `eav_attribute` WHERE `eav_entity_attribute`.`attribute_id` = `eav_attribute`.`attribute_id`);

DELETE FROM `cataloginventory_stock_item` WHERE NOT EXISTS (SELECT 1 FROM `catalog_product_entity` WHERE `cataloginventory_stock_item`.`product_id` = `catalog_product_entity`.`entity_id`);

DELETE FROM `catalog_category_entity_int` WHERE NOT EXISTS (SELECT 1 FROM `catalog_category_entity` WHERE `catalog_category_entity_int`.`entity_id` = `catalog_category_entity_int`.`entity_id`);

DELETE FROM `catalog_category_entity_text` WHERE NOT EXISTS (SELECT 1 FROM `catalog_category_entity` WHERE `catalog_category_entity`.`entity_id` = `catalog_category_entity_text`.`entity_id`);

DELETE FROM `eav_attribute_group` WHERE NOT EXISTS (SELECT 1 FROM `eav_attribute_set` WHERE `eav_attribute_set`.`attribute_set_id` = `eav_attribute_group`.`attribute_set_id`);

DELETE FROM `eav_entity_attribute` WHERE NOT EXISTS (SELECT 1 FROM `eav_attribute` WHERE `eav_attribute`.`attribute_set_id` = `eav_entity_attribute`.`attribute_id`);

DELETE FROM `report_viewed_product_index` WHERE NOT EXISTS (SELECT 1 FROM `catalog_product_entity` WHERE `catalog_product_entity`.`entity_id` = `report_viewed_product_index`.`product_id`);

DELETE FROM `catalog_category_entity_varchar` WHERE NOT EXISTS (SELECT 1 FROM `catalog_category_entity` WHERE `catalog_category_entity`.`entity_id` = `catalog_category_entity_varchar`.`entity_id`);