Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save diazwatson/2cce8a9e1fe9bb32828d38c9d9079b07 to your computer and use it in GitHub Desktop.
Save diazwatson/2cce8a9e1fe9bb32828d38c9d9079b07 to your computer and use it in GitHub Desktop.
Deletes orphaned records from a Magento 1.x database.

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:

Any comments, corrections, additions or suggestions are welcomed.

As always, backup, backup, backup first.

-- 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`);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment