0

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 ?

2

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;
  • I kept the "default" attribute set as it is, I didn't delete it at all, but the problem is I took backup of only eav_attribute_set table, even if I restore that table then also the category issue I mentioned above still persists. So can you let me know if there is problem in relation of table between eav_attribute_set and eav_entity_type then how can we solve it ? Provided if the category issue is due to relation, I saw there is relation between these two tables also set with cascade update/delete. – Vivek Feb 16 '15 at 15:30
  • Did you also keep the default attribute set for the categories, or only for products? How many rows did you have in your eav_attribute_set table after the delete? – Marina Vilcea Feb 16 '15 at 18:57
  • There is a foreign key set on the eav_attribute_group table, and after you deleted the sets the groups also got deleted, so restoring only the eav_attribute_set table would not suffice. You're not seeing the tabs on the category page because those correspond to the category groups that got deleted. – Marina Vilcea Feb 16 '15 at 19:01
  • After I deleted the attribute sets I still had 8 rows of "Default" attribute set in the table. – Vivek Feb 17 '15 at 7:19
  • Also I looked in the relation view, the foreign key set with "eav_entity_type" table, also checked that table in another magento site, the entries are same – Vivek Feb 17 '15 at 7:47
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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