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.

Checking what we have

The first thing we have to notice is whether your category tree looks OK. If you imported it from some other system using a custom script, chances are that everything looks just fine. It’s relatively easy to check that since it’s visible even on the front-end – if your shop’s categories are right, then your category tree is healthy.

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. The problem we encountered in one of our recent projects is that the categories were imported from osCommerce a while ago and whoever wrote the import script forgot to update the level of the categories as well as the number of children each category has. That caused indexer failure on “Catalog URL Rewrite” process as well as the issues with displaying available categories on the Product edit page in the Magento’s admin area.

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.

Important note: Use these queries on your own discretion. Do not apply to a live system directly and always test on your local/development machines before deciding to apply it on a live environment.

Updating “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);

What we have done here is reading the number of category delimiter characters (“/”) in the path column and adding a number of them to the “level” column. That will reliably tell us the exact level of the category.

Updating “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,"/%"));

This query is a bit more complicated, since it runs through whole table and collect how many rows have the same sub-string (which is equal to the “path” column value of the observed row) on the start of the “path” column value.

If you know exactly how many children one category has and you want to check if this query did the trick, you can do it with this query:

SELECT COUNT(*) FROM catalog_category_entity
WHERE path LIKE '1/15/%';

… where the category with ID = 15 is the one you are checking.

Post execution tasks

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 is also a good idea.

I will not go deeper into explaining the logic behind the scripts. My recommendation is, however, that you consider learning the magic behind MySQL. Trust me, it will be useful in many situations where you need to fix some system that you haven’t built yourself.

Cheers