Skip to content

Instantly share code, notes, and snippets.

@dfelton
Last active May 24, 2021 19:23
Show Gist options
  • Star 14 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save dfelton/042e15a51b9795b25cad0235a1bf9122 to your computer and use it in GitHub Desktop.
Save dfelton/042e15a51b9795b25cad0235a1bf9122 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`);
@fwolfst
Copy link

fwolfst commented Apr 5, 2017

Awesome, thank you a lot. I have a whole lot of more violated constraints (some probably from plugins, not sure), should I dump them here?

@j0um
Copy link

j0um commented Jan 31, 2018

Thanks a lot! Much appreciated!

@sajidunnar
Copy link

Thanks a lot! for sharing

@kozie
Copy link

kozie commented Aug 15, 2018

I needed the above for a Magento CE installation so i used the following to generate the necessary queries:

SELECT 
  CONCAT(
  	'DELETE FROM `', TABLE_NAME, '` ',
  	'WHERE `', COLUMN_NAME, '` NOT IN (',
  	'SELECT `', REFERENCED_COLUMN_NAME, '` FROM `', REFERENCED_TABLE_NAME, '`',
  	');'
  )
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE
  REFERENCED_TABLE_NAME IS NOT NULL
AND 
  CONSTRAINT_SCHEMA = '__PUT_DATABASE_NAME_HERE__'
  ;

@dfelton
Copy link
Author

dfelton commented Feb 24, 2019

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?

@bp612
Copy link

bp612 commented Jul 9, 2020

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.

@dfelton
Copy link
Author

dfelton commented Jul 9, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment