Mysql errors when accessing any event

Through "devel module" i found the exact mysql query which is producing that error, it is:

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, 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 19800 SECOND AS event_start_site, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 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 WHERE n.status = 1 HAVING (event_start >= '2007-06-01 00:00:00' AND event_start <= '2007-06-30 23:59:59') OR (event_end >= '2007-06-01 00:00:00' AND event_end <= '2007-06-30 23:59:59') OR (event_start <= '2007-06-01 00:00:00' AND event_end >= '2007-06-30 23:59:59')

at event_get_events_event . The weired part is this error is NOT coming if user is admin, for everyone else the error is coming irrespective of whether person logged in or not, and what role he/she has.

--rafi

HTH

--rafi

Hi,

Can somebody please fix this problem, as it is freaking me out now. The main part of my site is these event management and now that is screwed, even the main event page http://www.example.com/drupal/event is giving such mysql errors. If i run same mysql queries directly in database using phpmyadmin or if user logged in is admin then it works fine. I dont see any permission related problem here as before or after upgradation i have done no changes of that sort.

Please somebody fix it as soon as possible.

PS: i made a big mistake and not taken backup of my database before upgradation.

--rafi

Ok i finally figured out where exactly is the problem. It is indeed a badly formed sql query. I gave permission to run devel module to a non-admin user and saw what are the sql queries it is querying. And then run the same sql through phpmyadmin, and was surprised to see it is indeed giving syntax errors there as well. The erroneous sql query it was trying to run is:

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, 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 19800 SECOND AS event_start_user, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 SECOND AS event_end_user, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 SECOND AS event_start_site, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 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 = 4 AND na.realm = 'tac_lite') OR (na.gid = 6 AND na.realm = 'tac_lite') OR (na.gid = 5 AND na.realm = 'tac_lite') OR (na.gid = 4 AND na.realm = 'tac_lite') OR (na.gid = 6 AND na.realm = 'tac_lite') OR (na.gid = 5 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite') OR (na.gid = 2 AND na.realm = 'term_access') OR (na.gid = 6 AND na.realm = 'term_access'))) AND ( n.status = 1 AND ((e.event_start >= '2007-06-30 00:00:00' AND e.event_start <= '2007-08-01 23:59:59') OR (e.event_end >= '2007-06-30 00:00:00' AND e.event_end <= '2007-08-01 23:59:59') OR (e.event_start <= '2007-06-30 00:00:00' AND e.event_end >= '2007-08-01 23:59:59')) HAVING (event_start >= '2007-07-01 00:00:00' AND event_start <= '2007-07-31 23:59:59') OR (event_end >= '2007-07-01 00:00:00' AND event_end <= '2007-07-31 23:59:59') OR (event_start <= '2007-07-01 00:00:00' AND event_end >= '2007-07-31 23:59:59')) 

but due to wrong placement of one of the closing of brackets it is giving syntax error. The closing of totally last bracket should be closing just before "Having" and not at the end. So the correct sql statement should be (in my opinion) :

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, 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 19800 SECOND AS event_start_user, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 SECOND AS event_end_user, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 SECOND AS event_start_site, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 19800 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 = 4 AND na.realm = 'tac_lite') OR (na.gid = 6 AND na.realm = 'tac_lite') OR (na.gid = 5 AND na.realm = 'tac_lite') OR (na.gid = 4 AND na.realm = 'tac_lite') OR (na.gid = 6 AND na.realm = 'tac_lite') OR (na.gid = 5 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite') OR (na.gid = 2 AND na.realm = 'term_access') OR (na.gid = 6 AND na.realm = 'term_access'))) AND ( n.status = 1 AND ((e.event_start >= '2007-06-30 00:00:00' AND e.event_start <= '2007-08-01 23:59:59') OR (e.event_end >= '2007-06-30 00:00:00' AND e.event_end <= '2007-08-01 23:59:59') OR (e.event_start <= '2007-06-30 00:00:00' AND e.event_end >= '2007-08-01 23:59:59'))) HAVING (event_start >= '2007-07-01 00:00:00' AND event_start <= '2007-07-31 23:59:59') OR (event_end >= '2007-07-01 00:00:00' AND event_end <= '2007-07-31 23:59:59') OR (event_start <= '2007-07-01 00:00:00' AND event_end >= '2007-07-31 23:59:59') 

I run this query in phpmyadmin and it is running perfectly giving same set of results what admin is getting. (Note that all events are accessible to all users according to my user permission settings)

Now please some one fix this bug and updat the cvs so that i can download.

--rafi

Perfect ...It works absolutely fine with me...Thanks a lot Killes for this patch....

:)

--rafi