Solving problems with Category tree database information
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.
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…
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
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.
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.
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
I have faced same issue in Magento version 22.214.171.124
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,”/%”));
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.
WoW!!! Thank you very much…. it works perfect. That was exactly my problem, so THANK YOU for share.
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:
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.
Thank you very much, I thought I was going insane! This has saved me so much time. Great stuff
This was the exact solution I needed. Thanks.
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 = (
FROM (SELECT * FROM catalog_category_entity) cc
WHERE cc.path LIKE CONCAT(c.path, ‘/%’)
AND cc.path NOT LIKE CONCAT(c.path, ‘/%/%’)
thanks a lot !! seriously you saved my life ! All my problems with the category tree disapear with two sql query.
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)
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
Thanks , Great Post……… Worked for me ……
Great post, we have solved it in a similar way at our german-blog-post (http://www.webguys.de/magento/fehler-in-der-kategorie-tabelle-beseitigen/) – never got some problems using that query.
Thanks for the feedback, Steve. I’m glad that you find it useful.
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.