More Mysql Syntax errors

Hi ,

Thanks for looking into it. But that error only comes when i use/choose any field of event_views module while creating any "view". It was not there before, and views was working perfectly with both simple_access and tac_lite.

Anyway i dont have any in depth knowledge about it, but please let me know if it can solved (maybe temporarly) by removing simple_access module. As evenif it is installed i am not using it. Where as i need event_views module very much. tac_lite i cannt afford to loose, as i need it too.

regards,
--rafi

By the way i am using taxonomy_access module as well..

regards,
--rafi

Thanks Karen and Merlin for all the info and advise you provided. I just tried disabling simple_access module only , keeping tac_lite as it is and glad to find that it did work. Simple_access presently i am not using as it is not working on my site since i upgraded my drupal from 4.6.0 to 4.7.0. Even if i give view permission to only one group created by simple_access, the page is accessible by everyone. So now it become useless to me. So all role based accesses are working but group based not.

Also i dont know if one of my other problem is also related to this issue, so just let me spell out the problem and i leave it into experts hand to decide what exactly is the problem. The problem is whenever admin (user_id=1) do something which is high privilage activity (which only admin suppose to do), like accessing logs, editing a page, access admin's my account etc...everything works fine except that i get a log message of "access denied" saying the same activity is just tried by an anonymous user (which i am sure is not the case, i confirmed it by many trials).

anyway my problem is temporarly solved by removing simple_access module only, so maybe it will help you figure out what exactly is the problem. Looking forward to have a patch soon..Good luck..and Thanks again for all the efforts and support.

regards,
--rafi

No sorry sorry!! removing simple_access module does NOT "fully" solves the problem as i thought it has. Now what is happening is after doing some trials and errors:

1. if simple_access is there then in which ever field i use event_views module option it gives error

2. When i remove simple_access module, everything works perfectly if i use options of event_views in the fields of "Page=>'View type'", "Fields", "Sort criteria". But when i use event_views options in "Filters" i again start getting mysql warning messages.

i didnt try removing tac_lite module, as it is important for my website.

regards,
--rafi

Ok here is some trials i conducted with some combinations of fields and options of event_views module in views: (remember i disabled simple_access module only, tac_lite is working)

1. "Page=>View Type" --->Here whichever option i choose, views works.
2. "Fields" --->Here also whichever options i choose, views works
3. "Filters" (when page=>'view type', Fields, sort criteria has event_views modules options) --->"Event start date", "Event End date" worked perfectly...But "Event Start year" gives error:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (YEAR(from_UNIXTIME(event.event_start)) >= YEAR(from_UNIXTIME(1149866082))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

"Event Start Month" gives error:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (MONTH(from_UNIXTIME(event.event_start)) > MONTH(from_UNIXTIME(1149866488))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

"Event Start Day" Gives errors:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(1149866647))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(11498666 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.timezone AS event_timezone, event.event_end AS event_event_end, event.event_start AS event_event_start FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(1149866647))) LIMIT 0, 60 in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

4. "Sort Criteria"---> Works perfectly with all event_view options, except the "filter" i mentioned above which gives errors.

--------------------------------------------------------------------------------

If i disable tac_lite module too along with disabling simple_access...and choosing Filter as "Event start year" ...it gives:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (YEAR(from_UNIXTIME(event.event_start)) > YEAR(from_UNIXTIME(1149867062))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

If use "Event start month"

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (MONTH(from_UNIXTIME(event.event_start)) > MONTH(from_UNIXTIME(1149867321))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

if use "Event start day" if gives:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(1149867415))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(11498674 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.timezone AS event_timezone, event.event_end AS event_event_end, event.event_start AS event_event_start FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > DAY(from_UNIXTIME(1149867415))) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

HTH
regards,
--rafi

Hi KarenS, As per your request..

After disabling simple_access, but tac_lite working ...
when i try "Event: Start Year" -"Is Greater Than" - "2006"...The error is:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (YEAR(from_UNIXTIME(event.event_start)) > 2006) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

when i try "Event: Start Month" -"Is greater than" - "August"- ...The error is:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (MONTH(from_UNIXTIME(event.event_start)) > 8) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

When "Event start day" - "Is greater than" - "26" ...The error is:

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 'DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON nod query: SELECT count(*) FROM node node LEFT JOIN term_node term_node ON DISTINCT(node.nid) = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > 26) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) > 26) ORDER BY event.event_ query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) > 26) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

Is this what you were looking for? ...if not please explain a little more...
Let me know if you want me to try any other combination of options, will be glad to help, whatever i can.

regards,
--rafi

Hi,

I have "Devel" module installed, so i know all the mysql queries in bottom of everpage if i want. When i choose "Event: start date"--"Is Greater than"--"2006-08-10 08:30:00" (NOTE that this is a working option field) the mysql query which has "DISTINCT" in the query is:

SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (event.event_start > 1155216600 + 0) ORDER BY event.event_start ASC LIMIT 0, 60

So this query is working perfectly...I noticed also that there is no query which involves UNIXTIME in this type of field option.

I dont know if my this finding make any difference or help you in anyway, just trying to help whatever i can =).

regards,
--rafi

Hello Merlin,
Sorry for the late reply. I just checked this posting. Thank you for your great effort i just installed the latest version from cvs 1.141 for views module. I tried all the options which were giving error earlier. "Event Start Year" and "Event Start Month" working perfectly along with others, but "Event Start Day" still gives following errors:

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 '(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151339 query: SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151339408))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151339 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151339408))) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

regards,
--rafi

Since i am still getting errors so i am puting it back as active bug.

--rafi

I installed cck module and then found few more default views. Now I am getting more errors in views modules..When using default view "event_date" and "event_calendar" at http://www.website.com/drupal/event/date and when click year "2006" it goes to
http://www.website.com/drupal/event/date/2006 and gives..

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 'DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (n query: SELECT count(DISTINCT(MONTH(from_UNIXTIME(event.event_start+0)))) FROM node node LEFT JOIN event event ON DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (node.type IN ('event')) AND (YEAR(from_UNIXTIME(event.event_start+0)) = 2006) in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

http://www.website.com/drupal/event/date/2006/1 gives:

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 '(from_UNIXTIME(event.event_start+0)))) FROM node node LEFT JOI query: SELECT count(DISTINCT(DAY(from_UNIXTIME(event.event_start+0)))) FROM node node LEFT JOIN event event ON DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (node.type IN ('event')) AND (YEAR(from_UNIXTIME(event.event_start+0)) = 2006) AND (MONTH(from_UNIXTIME(event.event_start+0)) = 1) in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start+0)) AS event_day, count(DISTIN query: SELECT DAY(from_UNIXTIME(event.event_start+0)) AS event_day, count(DISTINCT(node.nid)) as num_nodes FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('event')) AND (YEAR(from_UNIXTIME(event.event_start+0)) = 2006) AND (MONTH(from_UNIXTIME(event.event_start+0)) = 1) GROUP BY DAY(from_UNIXTIME(event.event_start+0)) LIMIT 0, 10 in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

same type of error if i go to event_calendar view.

event_select view gives:

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 '(from_UNIXTIME(event.event_start)) >= 1)' at line 1 query: SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('event')) AND (YEAR(from_UNIXTIME(event.event_start)) = YEAR(from_UNIXTIME(1151344958))) AND (MONTH(from_UNIXTIME(event.event_start)) >= MONTH(from_UNIXTIME(1151344958))) AND (DAY(from_UNIXTIME(event.event_start)) >= 1) in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) >= 1) LIMIT 0, 10' at line query: SELECT DISTINCT(node.nid) FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('event')) AND (YEAR(from_UNIXTIME(event.event_start)) = YEAR(from_UNIXTIME(1151344958))) AND (MONTH(from_UNIXTIME(event.event_start)) >= MONTH(from_UNIXTIME(1151344958))) AND (DAY(from_UNIXTIME(event.event_start)) >= 1) LIMIT 0, 10 in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

"event_js" view is working fine..

regards,
--rafi

Hi ,

In the above 3 postings i was still using old version of event_view module. When i tried the latest from cvs yesterday i started geting fatal errors:

Warning: theme_event_views_display(event_views_api.inc): failed to open stream: No such file or directory in /home/website/public_html/drupal/modules/event/contrib/event_view/event_views.module on line 167

Warning: theme_event_views_display(): Failed opening 'event_views_api.inc' for inclusion (include_path='.:/home/website/public_html/drupal/modules/civicrm/:/home/website/public_html/drupal/modules/civicrm//packages:.:/usr/lib/php:/usr/local/lib/php') in /home/website/public_html/drupal/modules/event/contrib/event_view/event_views.module on line 167

Fatal error: Call to undefined function: event_views_get_calendar() in /home/website/public_html/drupal/modules/event/contrib/event_view/event_views.module on line 168

--rafi

Sorry i didnt notice event module also upgraded yesterday so was still using old version. When i tried today with recent event module. Now i am getting only

Warning: Invalid argument supplied for foreach() in /home/website/public_html/drupal/includes/form.inc on line 637

when adding "Event: Start Day" as one of filters while adding in a event type view. and after saving when tried to see the page organised by view, it gives:

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 '(from_UNIXTIME(event.event_start)) >= 1)' at line 1 query: SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (event.event_start >= (1151381681 + 0)) AND (DAY(from_UNIXTIME(event.event_start)) >= 1) in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) >= 1) ORDER BY event.event_ query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (event.event_start >= (1151381681 + 0)) AND (DAY(from_UNIXTIME(event.event_start)) >= 1) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/website/public_html/drupal/includes/database.mysql.inc on line 120

regards,
--rafi

Hi, Just to be sure i downloaded the CVS version of views, event, event_view, cck, modules and upgarded my website successfull again. Deleted catche by both devel module and also submiting the modules page. Used update.php also (latest cvs version). Also upgraded all .inc files in includes directory which are changed in last 2 weeks (that includes form.inc as well). I also deleted all the filters and recreated it. But unfortunately still getting same error.

when used "Event start day" in filter option, even before saving it gives error:
Warning: Invalid argument supplied for foreach() in /home/website/public_html/drupal/includes/form.inc on line 637

after saving when i goto http://www.website.com/drupal/taxonomy/term/4/9

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 '(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151427 query: SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151427179))) in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

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 '(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151427 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151427179))) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/thinkind/public_html/den/includes/database.mysql.inc on line 120

any idea whats wrong.

regards,
--rafi

Hi,

I think i got the problem and probable solution. I tried the whole sql query in my phpMyAdmin, i.e.

SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAY(from_UNIXTIME(event.event_start)) >= DAY(from_UNIXTIME(1151429788)))

I got the same error message which i was getting on my website , with syntax error at DAY(from_UNIXTIME(event.event_start). I tried some constant values instead of from_UNIXTIME(event.event_start) like '2006-06-27 12:06:06' with a simple query, which is

select DAY('2006-06-27 12:06:06');

i got again sytanx error even in so simple code. Later i tried :

select DAYOFMONTH('2006-06-27 12:06:06');

which worked perfectly. Ironicaly evenif DAY() is a synonym for DAYOFMONTH() according to http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html but its not working the same way. So later i replaces the query to :

SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND ((term_node.tid = '4')) AND (DAYOFMONTH(from_UNIXTIME(event.event_start)) >= DAYOFMONTH(from_UNIXTIME(1151429788)))

which worked perfectly in phpMyAdmin and gave result "2".

HTH

--rafi

DAY() is a synonym for DAYOFMONTH() and It is only available as of MySQL 4.1.1, but my MySQL version is 4.0.27-standard, thats why probably its not working.

Can you please correct it so that it works with all versions..

regards,
--rafi