Okay... So I don't know whether this module is the cause or whether it's from the forum_access module (I enabled them at exactly the same time)... But now my logs seem to be filling up with errors about the event module;

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 'HAVING (event_start >= '2008-03-01 00:00:00' AND event_start <= '2008-03-31 23:5' at line 1 query: SELECT DISTINCT(n.nid), n.uid, n.title, n.type, e.event_start, e.event_start AS event_start_orig, e.event_end, e.event_end AS event_end_orig, e.timezone, e.has_time, e.has_end_date, tz.offset AS offset, tz.offset_dst AS offset_dst, tz.dst_region, tz.is_dst, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_start_utc, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_end_utc, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_user, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_user, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_site, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_site, tz.name as timezone_name FROM node n INNER JOIN event e ON n.nid = e.nid INNER JOIN event_timezones tz ON tz.timezone = e.timezone 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') OR (na.gid = 4 AND na.realm = 'forum_access'))) AND ( n.status = 1 AND ((e.event_start >= '2008-02-29 00:00:00' AND e.event_start <= '2008-04-01 23:59:59') OR (e.event_end >= '2008-02-29 00:00:00' AND e.event_end <= '2008-04-01 23:59:59') OR (e.event_start <= '2008-02-29 00:00:00' AND e.event_end >= '2008-04-01 23:59:59')) HAVING (event_start >= '2008-03-01 00:00:00' AND event_start <= '2008-03-31 23:59:59') OR (event_end >= '2008-03-01 00:00:00' AND event_end <= '2008-03-31 23:59:59') OR (event_start <= '2008-03-01 00:00:00' AND event_end >= '2008-03-31 23:59:59') )ORDER BY event_start ASC in /var/www/drupal/htdocs/sites/all/modules/event/event_database.mysqli.inc on line 102.

It looks to me though the node_access rebuilding is inserting html codes rather than the actual symbols they represent? Or is that normal? I have no idea...

Thanks,

Pobster

Comments

pobster’s picture

pobster commented

Oops, obviously the html codes have been converted into html symbols so you can't see what I mean... Try this;

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 &#039;HAVING (event_start &gt;= &#039;2008-03-01 00:00:00&#039; AND event_start &lt;= &#039;2008-03-31 23:5&#039; at line 1 query: SELECT DISTINCT(n.nid), n.uid, n.title, n.type, e.event_start, e.event_start AS event_start_orig, e.event_end, e.event_end AS event_end_orig, e.timezone, e.has_time, e.has_end_date, tz.offset AS offset, tz.offset_dst AS offset_dst, tz.dst_region, tz.is_dst, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_start_utc, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_end_utc, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_user, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_user, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_site, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_site, tz.name as timezone_name FROM node n INNER JOIN event e ON n.nid = e.nid INNER JOIN event_timezones tz ON tz.timezone = e.timezone INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view &gt;= 1 AND ((na.gid = 0 AND na.realm = &#039;all&#039;) OR (na.gid = 2 AND na.realm = &#039;forum_access&#039;) OR (na.gid = 4 AND na.realm = &#039;forum_access&#039;))) AND ( n.status = 1 AND ((e.event_start &gt;= &#039;2008-02-29 00:00:00&#039; AND e.event_start &lt;= &#039;2008-04-01 23:59:59&#039;) OR (e.event_end &gt;= &#039;2008-02-29 00:00:00&#039; AND e.event_end &lt;= &#039;2008-04-01 23:59:59&#039;) OR (e.event_start &lt;= &#039;2008-02-29 00:00:00&#039; AND e.event_end &gt;= &#039;2008-04-01 23:59:59&#039;)) HAVING (event_start &gt;= &#039;2008-03-01 00:00:00&#039; AND event_start &lt;= &#039;2008-03-31 23:59:59&#039;) OR (event_end &gt;= &#039;2008-03-01 00:00:00&#039; AND event_end &lt;= &#039;2008-03-31 23:59:59&#039;) OR (event_start &lt;= &#039;2008-03-01 00:00:00&#039; AND event_end &gt;= &#039;2008-03-31 23:59:59&#039;) )ORDER BY event_start ASC in /var/www/drupal/htdocs/sites/all/modules/event/event_database.mysqli.inc on line 102.

At least now you can compare the two! ;o)

Pobster

salvis’s picture

salvis
he/his
commented
Priority: Minor » Normal
Status: Active » Postponed (maintainer needs more info)

What you're showing is this:

SELECT DISTINCT(n.nid), n.uid, n.title, n.type, ...
  FROM node n 
    INNER JOIN event e ON n.nid = e.nid 
    INNER JOIN event_timezones tz ON tz.timezone = e.timezone 
    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') 
        OR (na.gid = 4 AND na.realm = 'forum_access'))) 
    AND ( n.status = 1 
      AND ((e.event_start >= '2008-02-29 00:00:00' AND e.event_start <= '2008-04-01 23:59:59') 
        OR (e.event_end >= '2008-02-29 00:00:00' AND e.event_end <= '2008-04-01 23:59:59') 
        OR (e.event_start <= '2008-02-29 00:00:00' AND e.event_end >= '2008-04-01 23:59:59'))
  HAVING (event_start >= '2008-03-01 00:00:00' AND event_start <= '2008-03-31 23:59:59') 
    OR (event_end >= '2008-03-01 00:00:00' AND event_end <= '2008-03-31 23:59:59') 
    OR (event_start <= '2008-03-01 00:00:00' AND event_end >= '2008-03-31 23:59:59') )
  ORDER BY event_start ASC

The problem is a missing closing parenthesis in front of HAVING.

What version of Event are you using?

BTW, knowing that you're on MySQL is good information.

pobster’s picture

pobster commented

Hiya!

Thanks for replying - everything is the most recent version of everything using the 6.x-2.x-dev version of event (2008-Mar-11 according to the update status page), do you think it's the event module that's responsible? It seemed to work fine (more or less) before I rebuilt my permissions?

Pobster

pobster’s picture

pobster commented

See now I'm not so sure that it *is* event modules fault here... As I mentioned before, it worked fine before I enabled the ACL+Forum Access modules - and there isn't a missing closing parenthesis? The HAVING clause is correctly closed as it's part of the previous AND clause and so is closed at the end before the ORDER BY statement.

...The only thing I did wonder is why some of the "event_start" and "event_end" tables are prefixed and some aren't? If that anything to do with it? I'm thinking not as the calendar displays correctly for user 1 (superuser) it's everyone else that gets the error? Which as I suspect, is a permissions error?

Thanks,

Pobster

pobster’s picture

pobster commented

Okay... So on a whim, I've disabled both the acl and forum_access modules (only disabled, I haven't done an 'uninstall' and removed any schema) and lo, yep my event calendar works just fine again?

I'm really hoping this will shed some light on what's going on?

I noticed on another issue (forum_access) that the module has been altered to include ALL node types not just forum topics? Well... Can I roll this back? I only allow forum topics to be posted to my forums and if my event access stays unaltered then everything should be okay? Right...? I dunno... I'm not entirely sure what's wrong with it... All I know is that I can't have both a calendar and private forums, it's one or the other...

Pobster
PS. Would it be possible perhaps to add a tab to the forum access settings to select which nodetypes to alter (via checkboxes) rather than just assume all of them?

salvis’s picture

salvis
he/his
commented

Thank you for the additional information. The way it works is that as soon as you enable any node access module, all the queries in your installation that retrieve lists of something, are rewritten to take the node access modules into account. I'm pretty sure that you could add any other node access module in place of ACL and FA, and you'd get the same problems.

I have to look into Event before I can say where the problem really is, but I don't think it's in ACL/FA, and rolling back/options/whatever would not help. I'll get back to you when I know more...

salvis’s picture

salvis
he/his
commented
Title: node_access rebuild causes errors to event module? » Event module causes SQL errors!
Category: bug » support
Status: Postponed (maintainer needs more info) » Active

Well, I found the missing closing parenthesis: it's at the very end (before ORDER BY). The problem is that the HAVING clause is inside the pair of parentheses opened in AND ( n.status = 1 .

This is a duplicate of #222128: Event module's use of HAVING is incompatible with db_rewrite_sql(), but I'll keep it active here as a reminder until Event is fixed.

killes@www.drop.org’s picture

killes@www.drop.org commented
Status: Active » Closed (duplicate)

http://drupal.org/node/222128