Skip to content

Instantly share code, notes, and snippets.

@tmotyl
Last active August 29, 2015 14:23
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tmotyl/d2c26fcc2f3776f99b68 to your computer and use it in GitHub Desktop.
Save tmotyl/d2c26fcc2f3776f99b68 to your computer and use it in GitHub Desktop.
Magento EAV health check
#Check if product eav tables contain values only for product entity_type_id =10 (no results == OK)
SELECT 'catalog_product_entity_datetime', entity_type_id, attribute_id, value FROM catalog_product_entity_datetime
where entity_type_id <> 10
union SELECT 'catalog_product_entity_decimal', entity_type_id, attribute_id, value FROM catalog_product_entity_decimal
where entity_type_id <> 10
union SELECT 'catalog_product_entity_int', entity_type_id, attribute_id, value FROM catalog_product_entity_int
where entity_type_id <> 10
union SELECT 'catalog_product_entity_text', entity_type_id, attribute_id, value FROM catalog_product_entity_text
where entity_type_id <> 10
union SELECT 'catalog_product_entity_varchar', entity_type_id, attribute_id, value FROM catalog_product_entity_varchar
where entity_type_id <> 10;
#Check if product eav tables contain values only for category entity_type_id =9 (no results == OK)
SELECT 'catalog_category_entity_datetime', entity_type_id, attribute_id, value FROM catalog_category_entity_datetime
where entity_type_id <> 9
union SELECT 'catalog_category_entity_decimal', entity_type_id, attribute_id, value FROM catalog_category_entity_decimal
where entity_type_id <> 9
union SELECT 'catalog_category_entity_int',entity_type_id, attribute_id, value FROM catalog_category_entity_int
where entity_type_id <> 9
union SELECT 'catalog_category_entity_text',entity_type_id, attribute_id, value FROM catalog_category_entity_text
where entity_type_id <> 9
union SELECT 'catalog_category_entity_varchar',entity_type_id, attribute_id, value FROM catalog_category_entity_varchar
where entity_type_id <> 9;
#fix
delete from catalog_category_entity_datetime where entity_type_id <> 9;
delete from catalog_category_entity_decimal where entity_type_id <> 9;
delete from catalog_category_entity_int where entity_type_id <> 9;
delete from catalog_category_entity_text where entity_type_id <> 9;
delete from catalog_category_entity_varchar where entity_type_id <> 9;
#Check if customer_address eav tables contain values only for entity_type_id =2 (no results == OK)
SELECT 'customer_address_entity_datetime', entity_type_id, attribute_id, value FROM customer_address_entity_datetime
where entity_type_id <> 2
union SELECT 'customer_address_entity_decimal', entity_type_id, attribute_id, value FROM customer_address_entity_decimal
where entity_type_id <> 2
union SELECT 'customer_address_entity_int', entity_type_id, attribute_id, value FROM customer_address_entity_int
where entity_type_id <> 2
union SELECT 'customer_address_entity_text', entity_type_id, attribute_id, value FROM customer_address_entity_text
where entity_type_id <> 2
union SELECT 'customer_address_entity_varchar', entity_type_id, attribute_id, value FROM customer_address_entity_varchar
where entity_type_id <> 2;
SELECT 'customer_entity_datetime', entity_type_id, attribute_id, value FROM customer_entity_datetime
where entity_type_id <> 1
union SELECT 'customer_entity_decimal', entity_type_id, attribute_id, value FROM customer_entity_decimal
where entity_type_id <> 1
union SELECT 'customer_entity_int', entity_type_id, attribute_id, value FROM customer_entity_int
where entity_type_id <> 1
union SELECT 'customer_entity_text', entity_type_id, attribute_id, value FROM customer_entity_text
where entity_type_id <> 1
union SELECT 'customer_entity_varchar', entity_type_id, attribute_id, value FROM customer_entity_varchar
where entity_type_id <> 1;
#-----------------------
#orphan attribute values (no eav_attribute entry for attribute_id used in value table)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment