Solving problems with Category tree database information

category_tree © backtrust@sxc.hu

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 🙂


19 comments

  1. i face some issue with manage category , category tree not appear in manage category so if i want to add any child category i am not able to do that, and also if i want to drag any child category to anther parent isn’t possible with current condition please help me…

  2. Hi, a weird problem happened: since yesterday everything was fine, then this morning I login to admin to check on some products but when I go to catalog>manage products, click on the product I want to change and then click on the ‘category tab’, magento loads the frontend page INSIDE the product edit page. So I have the admin panel with its buttons (go back, save, save and continue etc), the admin menu on top, the product tab on the left and INSIDE, where the category tree shoud be, I have the frontend homepage!!!

    Any idea how this could happen? How can I solve this? Thanks

  3. I was trying to use this tutorial. Had some issues looking quite similar. I am not sure but i think it does not work on a multistore where products are displayed in multiple categories (please correct me if i’m wrong, maybe ther is another reason this did not work but i ended up with values way to high). Work around could be to move products to a single category before runnig the script.

    Another thing that is worth sharing concerning this issue: The problems my site eventually seemed to have where not becasue off the database table being wrong. It was because a javascript or flash file being cached (did quite a large upgrad from 1.5 to 1.9). We where not able to see the categories when we tried to add the via products > categories. Manage categories > Category products was workin just fine.

    Clearing browser cache or clearing Magento’s cache was not enough to resolve this. Using an incognito tab are clearing the complete browser history did the trick.

  4. Hi, i got issue.. i integrated the Extension for category import. After importing the categories, and re indexing, all the categories are gone.. but available in catalog_category_entity table.

    i really stuck.. kindly help

  5. Hello guys

    I have faced same issue in Magento version 1.8.1.0

    Problem is \”catalog_category_entity\” table.

    Here is the solution to overcome this issue.

    Run following two SQL queries on database, it will re-solve this problem

    Note : Please backup your database before run Sqls

    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,”/%”));

  6. Thankyou so much Nikola.

    Even almost 2 years after posting it, it has just solved the problem I’ve been having. Fantastic. So pleased to have got it sorted.

    Thanks again

  7. I ran into this problem and this post was really helpful. Thanks!

    I wrote two queries to help me find all the problematic categories (before correcting them) and thought they might be useful to others:

    SELECT entity_id, LENGTH(path)-LENGTH(REPLACE(path,'/','')) AS correct_level, `level`
    FROM catalog_category_entity
    WHERE LENGTH(path)-LENGTH(REPLACE(path,'/',''))  != `level`
    SELECT p.entity_id, p.path, p.children_count, COUNT(c.entity_id) AS correct_children_count, COUNT(c.entity_id) - p.children_count AS child_diff
    FROM catalog_category_entity p
    	LEFT JOIN catalog_category_entity c ON c.path LIKE CONCAT(p.path,"/%")
    WHERE 1
    GROUP BY p.entity_id
    HAVING correct_children_count != p.children_count
  8. Hello,
    Thank you for your tutorials. By the way, I got 1 similar issue with Manage Category in Admin section. The problem is, it does not show the correct number of products on the category. For example, if I assign 4 product to category Test and if I delete these products via Manage products -> select products -> Delete action
    After that if I will go to Manage Category, the category Test was still showing the 4 items: Test (4)

    Do you know any solution to fix this bug please?

    Thank you in advance.

  9. Thank you very much, I thought I was going insane! This has saved me so much time. Great stuff

  10. The first query could be simplified by:

    UPDATE catalog_category_entity c
    SET c.level = LENGTH(c.path) – LENGTH(REPLACE(c.path, ‘/’, ”));

    I think the second one has to be extended by a second WHERE clause to just count the number of direct subcategories and not all subcategories of subcategories of …:

    UPDATE catalog_category_entity c
    SET children_count = (
    SELECT
    COUNT(*)
    FROM (SELECT * FROM catalog_category_entity) cc
    WHERE cc.path LIKE CONCAT(c.path, ‘/%’)
    AND cc.path NOT LIKE CONCAT(c.path, ‘/%/%’)
    );

  11. thanks a lot !! seriously you saved my life ! All my problems with the category tree disapear with two sql query.

  12. hi can any1 help, am new with sql, am required to write a code where i have i think 2 parameters 1 dependent on the other eg when i preview my report it has to show 1 parameter then i select a product group after then the other shows up so that i can select my product,

    productgrop->productname from table(dboproduct)

  13. The second query could be simplified by replacing
    SELECT COUNT(*) FROM
    (SELECT * FROM catalog_category_entity)

    with just one following line
    SELECT COUNT(*) FROM catalog_category_entity

  14. Brilliant tutorial for website users! Actually I first learn that using some bad script for importing category information from some other 3rd party system is general reason for Indexer hangs process when updating “Catalog URL rewrites”. Really it is crucial input though I appreciate the tutorial for me.
    http://www.brandretailers.com/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.