Node's asscociated categories don't show up on node and category page

I encountered a similar thing while trying to upgrade my 5.x site. Category pages are OK, but I get nothing on Container pages, and the links on nodes (in place of Taxonomy links, with wrapper module used) are mostly missing - I say mostly, because some nodes DO have links, to entirely wrong categories (i.e. other than stored in database, even from entirely different Container). Both category_node and term_node database tables are OK.

I'm trying to figure it out, so this post is also a reminder to myself, to post the findings here... Stay tuned...

Hm, at least I managed to get the links right on my site. It turned out to be a problem with revision numbers (vid) in my category_node table:

There's probably some bug in the 5.x version of Category (I've just a suspicion, didn't check yet), which caused most of my nodes to have 'vid' identical to 'nid' in the 'category_node' table, while in the main 'node' table they are different. This is the case of nodes, where a new revision was never created (so only one exists), but still the 'vid' changed during later edits - the new 'vid' wasn't updated in 'category_node' table. The nodes with more revisions (about 300 out of 3000 nodes on my site) are OK, and nodes created post-upgrade on 6.x are OK too. Even some of the oldest nodes (created on 4.7.x) seem to be OK, but I can't track the exact flow these went through.

The random category-links I saw were due to the wrong 'vid' sometimes matching a correct 'vid' of another node.

So I needed to fix bad 5.x data in my database. After a while, I came up with this flow: We need to identify all entries, where only one revision is present, and 'vid' is equal to 'nid'. These are suspicious, and unless categories on the node changed heavily across revisions, they are also safe to reset. Then, we copy the latest 'vid' from 'node' table, which is correct (for nodes with only one revision surely).

I ended with this SQL:

CREATE TEMPORARY TABLE `category_node_fix` (`nid` int(10) unsigned, `cid` int(10) unsigned, `vid` int(10) unsigned);
INSERT INTO `category_node_fix` SELECT nid, cid, vid FROM `category_node` GROUP BY nid, cid HAVING COUNT(nid) = 1 AND nid = vid;
ALTER TABLE `category_node` DROP PRIMARY KEY;
UPDATE `category_node` cn INNER JOIN `node` n ON cn.nid = n.nid SET cn.vid = n.vid WHERE CONCAT_WS('-', cn.nid, cn.cid) IN (SELECT CONCAT_WS('-', nid, cid) FROM `category_node_fix` WHERE 1);
ALTER TABLE `category_node` ADD PRIMARY KEY (`cid`,`vid`);

A temporary table is created, and filled with a list of node+category pairs matching the conditions for later update. (Unfortunately, MySQL don't like this subquery directly in later UPDATE...) The primary key is removed for a while, because there might be random matches between old and new vid's during the update, causing the query to crash on a duplicate key. Then 'vid' is copied from 'node' table for the rows indicated by the temporary table, and primary key restored.

After this, correct categories re-appeared on all my nodes.

TODO: I need to convert this into a real update function, update also the (mirror) 'term_node' table, and roll a patch. Now it's too late night.

I did a bit more research, and I feel quite stupid now - of course 5.x version had NO revisions, just like Taxonomy didn't. All the 'vid' data in my database came from category_update_6000() - i.e. the bug is in the 5.x->6.x upgrade process. (The other differences I observed were due to unrelated experiments with category_resave post-upgrade, which I forgot about - clearly it WAS too late night for me yesterday!)

So, my problem boils down to the query in category_update_6000() and it's difference from the one in system_update_6001() where Taxonomy got a similar change (without such problems). I've rolled a patch, adding the fix as a second update function (because big monster-updates like category_update_6000() are always in danger of php time-limit, if you've thousands of categorized nodes like I have), plus a bit of cleanup along the way.

Leaving at "needs work" status, because I DIDN'T FULLY TEST THIS, YET. I hope to continue tomorrow.

I tested my upgrade fix carefully with real site's data (having 5 different containers with a bunch of categories and about 3000 nodes assigned - not that small set of data), and it works fine for me. So setting "Needs review", I believe this is ready to go.

BTW, despite very similar outcome, I now got a feeling that the initial poster had a different problem (probably the links on nodes just disabled for the container in question?), but the upgrade bug needs to be fixed all the same, and it's already posted here, so I'm leaving it as is.

There are more problems with the 5.x->6.x upgrade, so I created a new issue for that: #465442: Upgrade problems 5.x->6.x That new issue now have an improved patch, less intrusive to the already existing update function, and with another fix included.

Resetting this issue to previous status. Sorry for the confusion, but I feel like the initial problem here may be different and unrelated to my fix.

There's a setting whether the links should be shown or not. I don't know exactly (as I'm out of home now, without my usual data set), but I would check settings for the container (i.e. edit the container node, open the Container information fieldset, and see what's in there - beware: some settings are hidden, and only show if you enable other settings, so read the descriptions). Maybe the category_display is required for that, but I'm not sure - still I would try to enable category_display if the setting wansn't there first time. If that doesn't help, I would look also to settings on category nodes (although this is not the right place most probably), and whether the Taxonomy wrapper is installed or not (a tab under admin->content->category) - it might be part of the problem too (if I'm not mistaken, Taxonomy wrapper will emulate the links as "taxonomy", that might be what your theme looks for).

For me, the links work fine, without any patches needed.

I quite agree about the default - actually it's a bit similar to #464678: Category pages not showing nodes, so if time permits, I'll roll a single patch for both (and see if there are more defaults to be adjusted). But still, the step of enabling category_display will be necessary. That might go into INSTALL.txt or something.

I addressed the default at #484624: Fix all broken category/container previews, and add category_display defaults now (and .txt files update at #484084: Update README.txt and friends), so reverting this issue back to a fixed support request.