I periodically have broken data from importing product data from my live site to a test site causing duplicate entries in attribute values or labels so I have had to figure out where in the database to look and clean.
First Are you trying to delete the whole attribute or an attribute option?
When you say "sku values in them" I assumed you meant people have added options you don't want in them, but your error message SQL seems to suggest it was deleting the whole attribute, not sku values.
For attributes you'd have to look into eav_attribute, eav_attribute_set, eav_attribute_label, catalog_eav_attribute. The data is in catalog_product_entity_XXXX where xxxx will depend on the type of attribute.
For attribute options you are looking for eav_attribute_option and eav_attribute_option_value and eav_attribute_option_swatch - the values per product would be in catalog_product_entity_int or catalog_product_entity_varchar depending on whether single select or multi select.
BEFORE YOU DELETE AN ATTRIBUTE OPTION
Have a look at what products have this attribute value assigned, as you will need to decide what value they need to have. You should be able to do that from the admin product grid rather (enabling Use in Filter Options and Add to Column Options in attribute properties) than the database, but if you can't, the table catalog_product_entity_int (or varchar for multiple) is where you query.
If you need to do it from database. all options for an attribute:
SELECT a.option_id, a.sort_order, o.value, o.value_id, o.store_id FROM `eav_attribute_option` as a
inner join eav_attribute_option_value as o on a.option_id=o.option_id
WHERE a.`attribute_id` = 83
ORDER BY `a`.`sort_order` ASC
use this to figure out which option id to delete from eav_attribute_option
Note: duplicate entries in eav_attribute_option_value will cause errors and can happen if people are importing or migrating data. It's probably not your problem but it has been mine many times ;)
BEFORE YOU DELETE AN ATTRIBUTE
I think your attempt to delete an attribute fails because there's a cascading chain that locks the database. This might be avoided if you do some of these stages in advance:
be certain those attributes aren't used in any third party modules (search, stock etc.), catalog rules, templates etc. - if yes remove them from those configuration
be certain the data in that attribute is unnecessary or captured elsewhere, else use product admin grid filtering (you might need to add the attribute to that) and "update attributes" mass action in the grid to get that data where it was supposed to be.
if you have a suspicion this attribute was used in conditions in price rules or any other place where there is rule-based product selection, have a quick look. Especially if you have a dynamic category or merchandising module!
Then start the steps to clean out the attribute from your live data:
- change the attribute settings to not be in any grid filtering, search results, search forms, price rules, layered navigation etc (this is all in the attribute configuration) and save AND
- remove this attribute from any attribute set and save each attribute set AND
-reindex, fully flush cache
Please let me know if you want more details on these steps, I assumed you knew how
Now browse around the site's features to make sure nothing is breaking - this is your chance to spot that some module or page is using that attribute while the data is still around (then the attribute can be put back in the attribute sets while you figure out what needs to be disentangled).
It might be that the attribute deletion works now.
If not you'll have to look into eav_attribute, eav_attribute_set, eav_attribute_label, catalog_eav_attribute.
The product data is in catalog_product_entity_XXXX where xxxx will depend on the type of the attribute.
Technically deleting the eav_attribute entry will clean the others but it's worth checking what's in each one for that attribute ID before you jump, and clean remnants after.
