Relation of attribute set with category

Question

Does magento attribute sets have any relation with categories ?

It's just that recently I removed all the attribute sets except "Default" attribute set(id=4), the mysql query of delete didn't tell anything about foreign key check issues and the query ran successfully.

But after that when I see in admin > Catalog > Manage Categories, I can't find the first three tabs of any category. Also I can't go to any category page in frontend, all redirect to 404 page. Don't understand why this happens.

Can anybody let me if I am on right track or guide me if I ain't ?

Answer 1

Attribute sets are used by other eav entities too, not just by products. So my guess is that you deleted all the attribute sets for all the eav entities (including categories).

You should restore from a backup that you made before the delete, and then either delete the attribute sets from admin, or add this condition to your query:

DELETE FROM eav_attribute_set WHERE entity_type_id = 4 AND attribute_set_id != 4;

Answer 2

Problem solved!

Here are the details on how to fix it should anyone stumble upon the same issue.

When you delete an entry from eav_attribute_set it will also delete foreign-key related entries from consequent tables based on entity_type_id which is set as a foreign-key for this matter. This deletion happens because Magento uses ON DELETE CASCADE rule which tells the Sql engine to also delete all FK related entries from other tables.

In my case, the deleted entry was the Default for categories, which is entity_type_id:3. So here are the steps towards the fix:

1) use this query to find out what tables are related on delete cascade rule

    USE information_schema;
SELECT table_name
FROM referential_constraints
WHERE constraint_schema = 'your_db_name' AND
     referenced_table_name = 'eav_attribute_set' AND
     delete_rule = 'CASCADE';

This resulted in 3 other tables:

'catalog_product_entity'
'eav_attribute_group'
'googleshopping_types'

2) the only table that might have been affected - in my case - was 'eav_attribute_group' since the products use entity_type=4 and that was not deleted.

3) to verify if this is the culprit, use the below query to find any values with attribute_set_id=3:

SELECT * FROM your_db_name.eav_attribute_group where entity_type_id=3;

4) results: 0 - that's bad I know

5) now you need to verify if this table also uses ON DELETE CASCADE rule, and something else might got deleted without you knowing.

So use the above query in step 1 but replace the table_name with eav_attribute_group.

This resulted in one other table:

eav_entity_attribute

6) run this query similar to step 3 to verify if any values exist with attribute_set_id=3, again replace the table to eav_entity_attribute.

SELECT * FROM your_db_name.eav_entity_attribute where attribute_set_id=3

7) results: 0 - scary!

8) what you see here before your eyes is, that values from two other tables were deleted with a delete of a single record from just one table eav_attribute_set.

You'll definitely want to add back those values exactly the way they were before. Use a backup copy if you have one, or use a dev/staging/production environement to mimic the values accordingly.

In my case we have a staging site set up so it was pretty easy to identify all 37 missing values for eav_entity_type=3 in both tables and everything suddenly got healed.

Of course don't forget to add back the originally deleted record in table eav_attribute_set.

Good luck