Drupal 5 clean install, Xampp 1.5.2, Windows XP
Organic Groups 5.x-1.x-dev, and many many other contributed modules

The following errors appear in the error log after logging out of any user account:

Unknown column 'n.moderate' in 'where clause' query: SELECT DISTINCT(n.nid) FROM node_comment_statistics n INNER JOIN node_access na ON na.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 0)) AND (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND ( n.comment_count > 0 ) ORDER BY n.last_comment_timestamp DESC LIMIT 0, 10 in C:\Internet\xampp\htdocs\drupal\includes\database.mysql.inc on line 167.

Not unique table/alias: 'n' query: SELECT DISTINCT(c.nid), n.title, COUNT(c.nid) AS comment_count, MAX(c.timestamp) AS the_time FROM comments c LEFT JOIN node n ON n.nid = c.nid LEFT JOIN node n ON c.nid = n.nid INNER JOIN node_access na ON na.nid = c.nid WHERE ((n.moderate != 1 OR n.uid = 0)) AND (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND ( n.status = 1 AND c.status = 0 ) GROUP BY c.nid ORDER BY the_time DESC LIMIT 10 in C:\Internet\xampp\htdocs\drupal\includes\database.mysql.inc on line 167.

I thought this might be a conflict with Organic Groups but disabling it did not eliminate the errors.

Comments

pwolanin’s picture

pwolanin commented

Hmm, looks like something funny with the hook_db_rewrite_sql().

It's certainly at least partly due to modr8, but can you identify which module is making the original query? Is OG the only contrib module you have installed?

mjohnq3’s picture

mjohnq3 commented

I have a local test installation with over a dozen contributed modules installed. If I get some time i'll disable all of them and then re-enable them one at a time to see if I can identify the module causing the problem.

pwolanin’s picture

pwolanin commented

With the first error, it looks as though db_rewrite_sql is being called in a way that's confusing one or more modules that (certainly including modr8) since node_comment_statistics is being used as the primary table, but the API docs say that valid primary tables are only "comments, forum, node, menu, term_data, vocabulary".

Maybe the problem is here: http://api.drupal.org/api/HEAD/function/comment_get_recent

Embarassingly, that code came from one of my core patches...

The second error seems to be due to this repeated part of the query:
"LEFT JOIN node n ON n.nid = c.nid LEFT JOIN node n ON c.nid = n.nid "

And one of those 2 LEFT JOINS is probably from modr8.

pwolanin’s picture

pwolanin commented

ok, here's an issue for the core part of it: http://drupal.org/node/111830

mjohnq3’s picture

mjohnq3 commented

I created another clean install of Drupal 5 and installed three contributed modules in the following order: 1) modr8 v5.x-2.1; 2) views v5.x-1.5 (required by OG); and 3) OG v5.x-1.0. After activating the Recent Comments Block I began to see the unknown column error on logging in and out of user accounts.

Then on a hunch I installed the Live discussions module v5.x-1.0-beta and after enabling the Block I received both errors on login and logout.

I hope this information helps.

pwolanin’s picture

pwolanin commented

The patch at: http://drupal.org/node/111830 should fix the first problem (I test it last night).

So, it sounds like Live Discussions is also doing access control of some sort?

Prometheus6’s picture

Prometheus6 commented

So, it sounds like Live Discussions is also doing access control of some sort?

No, Live Discussions as it stands doesn't select on at the node table, it joins it to the comment table.

mjohnq3’s picture

mjohnq3 commented

I submitted and Issue for Live Discussion concerning this error.

http://drupal.org/node/112749

pwolanin’s picture

pwolanin commented

Can you post the original query from the Live Discussions module?

Prometheus6’s picture

Prometheus6 commented

This is the query.

      $query = "SELECT c.nid, n.title, COUNT(c.nid) AS comment_count, MAX(c.timestamp) AS the_time 
FROM {comments} c
LEFT JOIN {node} n ON n.nid = c.nid 
WHERE n.status = 1 AND c.status = 0 $select_blog 
GROUP BY c.nid ORDER BY the_time DESC LIMIT $commented_limit";

But I think this is the problem.

      $query_result = db_query(db_rewrite_sql($query, 'c'));

The error message that was passed along says it can't find the alias 'n' because it's not there. The alias, 'c', is the correct one for this query.

The 5.x dev release has a different queries that may work better for you.

pwolanin’s picture

pwolanin commented

I think this is really a bug with that rewrite query- since the query is already being joined to the node table, the 'c' parameter should not be passed IMHO.

Prometheus6’s picture

Prometheus6 commented

Without the db_rewrite_query() it gets links to comments on nodes the user has no access to. In particular, Organic Groups would have problems.

http://drupal.org/node/27331

pwolanin’s picture

pwolanin commented

I'm sorry, maybe I wasn't clear. the only suggestion is to change the last function call to:

$query_result = db_query(db_rewrite_sql($query));
Prometheus6’s picture

Prometheus6 commented

I understood...the patch attached to the linked issue ADDS the 'c'. That's ALL it does.

#1 submitted by chx on July 23, 2005 - 13:37

Not just og -- any node access module that clears the (all,0) grant will immediately make this query fail.

pwolanin’s picture

pwolanin commented

Ok, obviously there's some conflict between my understanding of hook_db_rewrite_sql and chx's, which means I'm probably wrong...

The code in modr8 is completely modeled off this smaple code in the develpoer docs:

http://api.drupal.org/api/HEAD/function/hook_db_rewrite_sql

If you follow this example code , passing in the 'c' breaks it...

pwolanin’s picture

pwolanin commented

@Prometheus - I don't think the comments on http://drupal.org/node/112749 apply for Drupal 5. I just tested the livediscussions module with OG under Drupal 5.1-dev, and it works fine without passing the extra parameter (I had to patch the module see: http://drupal.org/node/112749)

pwolanin’s picture

pwolanin commented

oops- make that the comments on http://drupal.org/node/27331

pwolanin’s picture

pwolanin commented
Status: Active » Closed (fixed)