In the process of moving from 4.7 to 5.1 all the forums (not just the group forums) disappeared. I saw no error messages. The nodes exist. I simply can no longer access them.

It seems in many ways to be a similar issue to http://drupal.org/node/123451 Though it appears that there was no resolution there.

Comments

rconstantine’s picture

rconstantine commented

I have no personal knowledge about the 4.7 version as that was before my time. Hopefully Darren Oh can comment and perhaps tell you how to upgrade.

Looking at the install file, nothing changed. So the database should be fine. This means that the update script will do nothing for this module.

Have you looked at the regular forum module as the possible problem? Or how about the taxonomy module? The forum module depends on that. And I think that OG has dependencies of its own. Have you met all dependencies? Have you rebuilt the access table? [There's a button somewhere to do that in the admin - not an og or og_forum feature]

Has anyone else gone through this process out there?

apratt’s picture

apratt commented

Thanks very much for the quick response. I tried rebuilding the access tables. Still the same behaviour.

Because there is no impact of og_forum on the database - I disable it and the forums work. Enable it and the forums as lists go away. I can get to individual posts. But I can't get the list of forum posts.

I click on a link example.com/forums/7. And nothing happens.

I had the thought that views might be interacting here in some way.

Could it be stalling trying to rebuild a cache of some sort? These are fairly massive forums - upwards of 3,000 entries each.

No error messages - no server log messages - just baffling...

apratt’s picture

apratt commented

Well one step forward.

I realized that indeed it wasn't a denied access problem. It was just a really slow load for the page. After enabling devel I was able to see that while there were some long load times for queries by far and away the longest was

taxonomy_get_tree at 213919.6 ms and 4152.4 ms for taxonomy_get_parents

I assume that 1000+ terms is hard on the system but how do I speed it up. Regular forums don't call it. Thus the one part is working. In fact I can only find one place in og_forum that is calling it and that is related to managing of the forums.

And once again I'm lost...

rconstantine’s picture

rconstantine commented

Interesting. I know another fellow that was having problems with the CCK_SSU module due to a taxonomy with over 2000 terms. No page would load with that taxonomy on it.

I'm not sure when I'll get a chance to look this over, but will when I can. Meanwhile, could you describe for me the taxonomy that is being called? Is that just the number of OG_Forums that you have? Or is it a regular taxonomy used for something else?

apratt’s picture

apratt commented

So I am pursuing the taxonomy tree angle on a couple of fronts.

I tried this suggestions from here Optimize taxonomy_get_tree (http://drupal.org/node/146688 ) first and it didn't help. I assume because I need to call the altered table rather than the original.
Optimize taxonomy_get_tree (http://drupal.org/node/146688 )

I'm in the process of trying this...

http://drupal.org/node/106015 [performance] Use Drupal caching mechanism for taxonomy_get_tree() and fix a taxonomy bug and notices.

To answer your question I only have four og groups and only one of them is somewhat large (ie several hundred nodes associated)

I'm removing one of the categories (a freetagging run riot) which will leave me with 150 and trying that.

apratt’s picture

apratt commented

So I suddenly realize that the node advice that I'm following is for Drupal 6... back to the darwing board.

Here is the query that I am seeing for taxonomy_get_tree 

SELECT DISTINCT(t.tid), t.*, parent FROM term_data t INNER JOIN term_hierarchy h ON t.tid = h.tid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 9258) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 9258)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 9258 WHERE ogu.is_active = 0)) AND ( t.vid = 1 ) ORDER BY weight, name

This isn't the query that is generated by taxonomy_get_tree (I don't think) Is there any way to limit the nesting of it... that seems to be where the time is eaten.

rconstantine’s picture

rconstantine commented

That is the key query to the whole module. It is critical. There isn't any nesting per se. See the comment at the beginning of the og_forum_db_rewrite_sql function which breaks down each part and how they are put together. og_term and og_uid both have indexes that should make the first SELECT query fast/acceptable. Same goes for the second and third SELECTS.

Does your host allow you to have the 'CREATE TEMPORARY TABLES' permission in your SQL install? What version of MySQL are you running? PHP version?

How many forums do you have?

apratt’s picture

apratt commented

PHP 5.1
MySQL 5.0

And the database user has create temporary tables...

65 forums of which 6 are permissioned - ie the rest are public

Can I break the query down into smaller pieces to try and figure out where the slowdown happens?

rconstantine’s picture

rconstantine commented

Can I break the query down into smaller pieces to try and figure out where the slowdown happens?

Sort of - you could take each piece and inside phpmyadmin or similar execute them one at a time and then build the whole query up. That's how I built it in the first place.

apratt’s picture

apratt commented

Another developer has suggested the following modification. Does it make sense to you?

The current query selects a subselect:

SELECT ogt.tid FROM og_term ogt 
    INNER JOIN og_uid ogu 
    ON ogt.nid = ogu.nid AND ogu.uid != 1

This query returns a million (pinky smile) rows, which are then selected from again from a (non-indexed) temporary table, requiring a huge table iteration. Messy.

However, if we try this instead:

SELECT distinct(ogt.tid) FROM og_term ogt 
    INNER JOIN og_uid ogu 
    ON ogt.nid = ogu.nid AND ogu.uid != 1

The result is a smaller set of only 1168 rows, which is kept in ram, and completes in under 2 seconds.

rconstantine’s picture

rconstantine commented

You are absolutely correct. I can't believe I didn't even think of that. You should be able to change the monster query into:

$return['where'] = 't.tid NOT IN (SELECT x.tid FROM (SELECT distinct(ogt.tid) FROM {og_term} ogt INNER JOIN {og_uid} ogu ON ogt.nid = ogu.nid AND ogu.uid != ' .$user->uid. ') x
WHERE x.tid NOT IN (SELECT distinct(ogt.tid) FROM {og_term} ogt INNER JOIN {og_uid} ogu ON ogt.nid = ogu.nid AND ogu.uid = ' .$user->uid. ')) 
AND t.tid NOT IN (SELECT distinct(ogt.tid) FROM {og_term} ogt INNER JOIN {og_uid} ogu ON ogt.nid = ogu.nid AND ogu.uid = ' .$user->uid. ' WHERE ogu.is_active = 0)';

and the smaller one into:

$return['where'] = "t.tid NOT IN (SELECT x.tid FROM
(SELECT distinct(ogt.tid) FROM {og_term} ogt INNER JOIN {og_uid} ogu ON ogt.nid = ogu.nid AND ogu.uid != ' .$user->uid. ') x
WHERE x.tid NOT IN (SELECT distinct(ogt.tid) FROM {og_term} ogt INNER JOIN {og_uid} ogu ON ogt.nid = ogu.nid AND ogu.uid = ' .$user->uid. ')) 
AND ogt.nid = $og_nid";
apratt’s picture

apratt commented

Thanks that seems to have totally resolved the issue.

rconstantine’s picture

rconstantine commented
Status: Active » Fixed

I've added this to my code, so it will be included in the next release whenever I can find time for it....

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)