0

We have an issue where we get data from a client. They are copying and pasting from websites so things are a mess atm.

So we have attributes with sku values in them. I did try and delete them manually but Magneto admin just crashes and the values i have deleted comes back.

is there away to find these values in the DB?

enter image description here

Edit: I get this error when i try and delete the values that is not supposed to be there. SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: DELETE FROM eav_attribute_label WHERE (attribute_id ='83')

2
  • can you share the screenshot of each tab for that attribute? Jan 24 at 12:25
  • just added the photo, if i understand you coreectly Jan 24 at 12:39

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.