Get this error upon accessing the forum page with an anonymous user...
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS r ON n.nid=r.nid INNER JOIN term_node AS t ON n.nid = t.nid I' at line 33 query: SELECT n.title AS topictitle, r.title AS replytitle, r.time timestamp, r.type AS replytype, n.type AS topictype, n.nid AS topicid, t.tid, r.cid AS cid, r.uid, u.name FROM node AS n INNER JOIN ( (SELECT title, created AS time, nid, uid, type, 'cid' AS cid FROM node ) UNION (SELECT subject, timestamp, nid, uid, 'comment', cid FROM comments INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner') OR (na.gid = 1 AND na.realm = 'term_access'))) AND ( comments.status = 0 ) ORDER BY time DESC ) AS r ON n.nid=r.nid INNER JOIN term_node AS t ON n.nid = t.nid INNER JOIN users AS u ON r.uid = u.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner') OR (na.gid = 1 AND na.realm = 'term_access'))) AND ( n.status = 1 ) GROUP BY tid; in /var/www/vhosts/qotion.sg/httpdocs/includes/database.mysql.inc on line 172.

Comments

placebo333’s picture

placebo333 commented

My users´are reporting similar errors since alpha 6. Its just that they are full members and logged in. The error only appears on the main forum Overview page :

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS r ON n.nid=r.nid INNER JOIN term_node AS t ON n.nid = t.nid INN' at line 33 query: SELECT n.title AS topictitle, r.title AS replytitle, r.time timestamp, r.type AS replytype, n.type AS topictype, n.nid AS topicid, t.tid, r.cid AS cid, r.uid, u.name FROM node AS n INNER JOIN ( (SELECT title, created AS time, nid, uid, type, 'cid' AS cid FROM node ) UNION (SELECT subject, timestamp, nid, uid, 'comment', cid FROM comments INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'forum_access'))) AND ( comments.status = 0 ) ORDER BY time DESC ) AS r ON n.nid=r.nid INNER JOIN term_node AS t ON n.nid = t.nid INNER JOIN users AS u ON r.uid = u.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'forum_access'))) AND ( n.status = 1 ) GROUP BY tid; in database.mysql.inc on line 172.

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Active » Fixed

Fixed, though probably not in the way people want. It turns out that the rewrite function can't handle subqueries. So I took it back out again. Which means that if you give people access to view a forum and the last post in it happens to be one that they don't have access to, they will see the title of it. Since I don't use nodeaccess, this is all the more it's going to be fixed unless someone wants to rewrite the query for me. I've spent enough time on this function and I'm done with it.

Michelle

Anonymous’s picture

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

Automatically closed -- issue fixed for two weeks with no activity.

mantyla’s picture

mantyla commented

Since there was a similar post in the Nodeaccess issue queue (http://drupal.org/node/238083), forwarded there from here, I feel I should clarify a couple things.

It should be clear that the problem is with the db_rewrite_sql function and its lack of support for subqueries. That function is part of Drupal core, and it is not something that module maintainers can do anything about - it just happens that the core function collects information from all installed access control modules and inserts that information into the rewritten query. These modules have nothing to do with the reason the query breaks.

Also, there is work in progress to fix the subquery support. See here: http://drupal.org/node/151910.