Deletes orphaned records from a Magento 1.x database.

Magento 1.x Orphaned Records Cleanup.md

Purpose

The purpose of this SQL script is to clean up a Magento 1.x database by deleting orphaned records from database tables which cause foreign key contraint failures. This happens when at some point in time records where deleted from the database while FOREIGN_KEY_CHECKS = 0 and the person performing the delete operations failed to delete all related records pertaining to related tables of the primary table.

This script can be helpful when encountering foreign key constraint failures using the Data Migration Tool to migrate your Magento 1 database to Magento 2.

This list was accumulated by querying database tables for their foreign keys and evaluating the results. For example, the "Delete orphaned product data" was written based off the results this query:

SELECT 
  TABLE_NAME, 
  COLUMN_NAME, 
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE
  REFERENCED_TABLE_NAME = 'catalog_product_entity' \G;

Versions:

• Magento EE 1.14.2.0

Any comments, corrections, additions or suggestions are welcomed.

As always, backup, backup, backup first.

magento-ee-1.14.2.0.sql

-- TODO: Finish documenting the constraint names for each query.

-- ---------------------------------------------------------------
-- Delete orphaned eav attribute data                            -
-- ---------------------------------------------------------------

-- FK_CATALOG_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID
DELETE FROM `catalog_eav_attribute` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_product_entity_datetime` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_product_entity_decimal` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
-- FK_CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID
DELETE FROM `catalog_product_entity_int` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_product_entity_text` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_product_entity_varchar` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_category_entity_datetime` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_category_entity_decimal` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_category_entity_int` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_category_entity_text` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `catalog_category_entity_varchar` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_eav_attribute` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_eav_attribute_website` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_entity_datetime` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_entity_decimal` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_entity_int` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_entity_text` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `customer_entity_varchar` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
DELETE FROM `eav_attribute_option` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);
-- FK_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID
DELETE FROM `eav_entity_attribute` WHERE `attribute_id` NOT IN (SELECT `attribute_id` FROM `eav_attribute`);

-- ---------------------------------------------------------------
-- - Delete orphaned customer entity data                        -
-- ---------------------------------------------------------------

DELETE FROM `customer_address_entity` WHERE `parent_id` NOT IN (SELECT `entity_id` FROM `customer_entity`);
-- FK_ENT_CSTRBALANCE_CSTR_ID_CSTR_ENTT_ENTT_ID
DELETE FROM `enterprise_customerbalance` WHERE `customer_id` NOT IN (SELECT `entity_id` FROM `customer_entity`);
-- FK_ENTERPRISE_REWARD_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID
DELETE FROM `enterprise_reward` WHERE `customer_id` NOT IN (SELECT `entity_id` FROM `customer_entity`);
-- FK_WISHLIST_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID
DELETE FROM `wishlist` WHERE `customer_id` NOT IN (SELECT `entity_id` FROM `customer_entity`);


-- ---------------------------------------------------------------
-- Delete orphaned product entity data                           -
-- ---------------------------------------------------------------

DELETE FROM `catalog_product_bundle_option` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_datetime` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_decimal` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_gallery` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_int` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery_value` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery_value_to_entity` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_text` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_tier_price` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_varchar` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_link` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_option` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_relation` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_attribute` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_link` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_category_product` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_category_product_index` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_compare_item` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_bundle_option` WHERE `parent_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_bundle_price_index` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_bundle_selection` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_enabled_index` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_datetime` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_decimal` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_gallery` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_group_price` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_int` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_text` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_tier_price` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_url_key` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_varchar` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_index_eav` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_index_eav_decimal` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_index_group_price` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_index_price` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_index_tier_price` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_link` WHERE `linked_product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_link` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_option` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_relation` WHERE `child_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_relation` WHERE `parent_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_attribute` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_link` WHERE `parent_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_link` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_website` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `cataloginventory_stock_item` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `cataloginventory_stock_status` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalogrule_product` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalogrule_product_price` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalogsearch_result` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `core_url_rewrite` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_catalogpermissions_index_product` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_giftcard_amount` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_giftregistry_item` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_staging_product_unlinked` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_targetrule_index` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_targetrule_index_crosssell` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_targetrule_index_related` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_targetrule_index_upsell` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_targetrule_product` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `enterprise_url_rewrite_redirect` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `product_alert_price` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `product_alert_stock` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `report_compared_product_index` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `report_viewed_product_aggregated_daily` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `report_viewed_product_aggregated_monthly` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `report_viewed_product_aggregated_yearly` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `report_viewed_product_index` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `sales_bestsellers_aggregated_daily` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `sales_bestsellers_aggregated_monthly` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `sales_bestsellers_aggregated_yearly` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `sales_flat_quote_item` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `tag_relation` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `weee_discount` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `weee_tax` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `wishlist_item` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_bundle_option` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_datetime` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_decimal` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_gallery` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_int` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery_value` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery_value_to_entity` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_text` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_tier_price` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_varchar` WHERE `row_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_link` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_option` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_relation` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_attribute` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
DELETE FROM `catalog_product_super_link` WHERE `parent_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);

-- wtf??? this mixture of row_id and entity_id for the same column doesn't make sense. Hold on this...
-- DELETE FROM `downloadable_link` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
-- DELETE FROM `downloadable_link` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);
-- DELETE FROM `downloadable_sample` WHERE `product_id` NOT IN (SELECT `row_id` FROM `catalog_product_entity`);
-- DELETE FROM `downloadable_sample` WHERE `product_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity`);

I haven't looked at this for some time, almost forgot I wrote it. Thanks for the positive feedback everyone.

@fwolfst if they where from tables provided by Magento, by all means. If they were from custom extensions I'd prefer this gist be strictly for tables native to Magento.

@kozie I like where you're head is at. Great example of "work smarter not harder" 👍 Did it yield successful results for you?

This is written for Enterprise, correct? is Kozie's answer the best choice for Magento Open Source 1.9.x?

FYI, for anyone experiencing errors with the Data Migration Tool, some of my errors were solved by re-indexing. Apparently I had some old Attributes in the catalog_product_index_eav table that were orphaned. Re-indexing cleared that up.

This is written for Enterprise, correct? is Kozie's answer the best choice for Magento Open Source 1.9.x?

FYI, for anyone experiencing errors with the Data Migration Tool, some of my errors were solved by re-indexing. Apparently I had some old Attributes in the catalog_product_index_eav table that were orphaned. Re-indexing cleared that up.

@brianpittvps yes, this was for enterprise 1.14.x. While I have not ran kozie's code myself, it certainly appears like it would work on any database to clean up orphaned records (that have foreign keys). Not just a Magento database. Of course back up back up back up database before running such things.