Magento: Solving problems with Category tree database information

First take backup of your database before run this script.

Did you ever had a problem where you can not see all available categories in “Categories” tab while editing a product? Does your Indexer process hangs when updating “Catalog URL rewrites”? At the same time your category tree looks just fine on “Manage Categories” menu and you can see assigned products in each category?

This issue can happen for a number of reasons, but it usually happens when you are using some bad script for importing category information from some other 3rd party system (like switching your shop from osCommerce, for example). Well, look no further, here’s the solution.

Category tree is stored as a database column called “path” in “catalog_category_entity” table. Each entry in that table represents one category and each “path” column value represents the exact position of a category in the category tree.

So, we obviously have a healthy category tree (“path” column) and messed up “level” and “children_count” columns. Luckily, we can use data in “path” column to fix this. Since this is a one time operation, I will stick with using raw SQL queries.

Update “level” column values

UPDATE catalog_category_entity SET level =
(SELECT LENGTH(path)-LENGTH(REPLACE(path,'/','')) AS tmpl
FROM (SELECT * FROM catalog_category_entity) AS table1
WHERE catalog_category_entity.entity_id = table1.entity_id);

Update “children_count” column values

UPDATE catalog_category_entity SET children_count =
(SELECT COUNT(*) FROM
(SELECT * FROM catalog_category_entity) AS table2
WHERE path LIKE
CONCAT(catalog_category_entity.path,"/%"));

After executing these queries, you just have to re-index and you’re done. Clearing the cache and Logging out and back in the admin area and see.