More Mysql Syntax errors
chaos21in - June 8, 2006 - 06:03
| Project: | Event Views |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
Description
Hi i just installed this module on my drupal 4.7.2 and used it for creation of a custom page for my event type taxonomy term. After configuration of that particular view when i see the page e.g. http://www.website.com/drupal/taxonomy/term/4/9 it gives these error, when i am logged in as admin.
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(1149746012))) in /home/user/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)) >= DAY(from_UNIXTIME(1149746 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.event_start AS event_event_start, event.timezone AS event_timezone 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(1149746012))) ORDER BY event.event_start ASC LIMIT 0, 60 in /home/user/public_html/drupal/includes/database.mysql.inc on line 120and when i am anonymous
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 INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'simple_access') OR (na.gid = 7 AND na.realm = 'simple_access') OR (na.gid = 1 AND na.realm = 'term_access') 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 = 1 AND na.realm = 'tac_lite') OR (na.gid = 3 AND na.realm = 'tac_lite') OR (na.gid = 2 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite'))) AN in /home/user/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)) >= DAY(from_UNIXTIME(1149746 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.event_start AS event_event_start, event.timezone AS event_timezone FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN event event ON node.nid = event.nid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'simple_access') OR (na.gid = 7 AND na.realm = 'simple_access') OR (na.gid = 1 AND na.realm = 'term_access') 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 = 1 AND na.realm = 'tac_lite') OR (na.gid = 3 AND na.realm in /home/user/public_html/drupal/includes/database.mysql.inc on line 120any idea whats wrong??
regards,
--rafi
#1
You're using some kind of node access control, maybe more than one (looks like simple access and tac_light). There have been problems with the node access module re-writing the sql in a way that invalidates it.
This is really a views issue, related to one posted earlier. Anyway, I'm going to move this thread over there and see if merlin has any ideas.
#2
Actually, this is really a node access issue that affects views.
#3
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
#4
By the way i am using taxonomy_access module as well..
regards,
--rafi
#5
KarenS is correct.
At the moment it's basically a bug in Drupal's node_access feature that was, at one time, fixed and then got unfixed somewhere along the way.
#6
The reason you didn't see it until you added an event field into your view is that it only affects date searches that use the FROM_UNIXTIME sql. That means it affects most event fields, filters, and arguments, but it also affects other date fields like some of those that use node and comment dates. It just depends on which fields, filters and arguments you use.
This is going to require a patch for the node access, if anyone can figure out how to get it to behave properly.
Your error should go away if you disable simple access and tac lite, but if that's not an acceptable solution (and I assuming it's not), you're probably stuck until we find a fix for node access.
#7
I'm also going through the event views module to see where I can eliminate that sql, but it many cases it is the only way to get the right results.
#8
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
#9
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
#10
You could really help us debug this if you say exactly which fields, filters, and arguments worked, and which ones seemed to create errors.
Thanks!!
#11
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 120If 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 120if 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
#12
Try something else for me if you would. I can see you are selecting the current date as the filter date. Try one of those filters that isn't working, but set it to some specific month instead of the current date. Maybe it is just the way I handled the current day that is creating problems. If so, that's probably fixable. That's the only thing I can think of that is different between the queries that are working and those that are not...
#13
What's actually going wrong is that node_access is rewriting the LEFT JOIN erroneously, seemingly due to the from_unixtime. When I have some time, I will try to reduce it to fundamentals and submit it as a bug to Drupal, and it may get fixed in a patch release.
KarenS: Since this impacts you and you are more available than I am right now, it seems, if you would like, what I would suggest is this: Get *just* the query that the view generates, or one close enough to trigger the error, and set up a db_rewrite_sql call that can reproduce the results on a stock Drupal with no other modules installed.
Submit that against node.module as a bug.
If you don't, it's fine; I'll get to it when I can. =)
#14
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 120when 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 120When "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
#15
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, 60So 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
#16
I committed a change that I *think* actually works around this; please try the CVS version of views (with views.module v 1.130 or higher, please) and see if that changes anything.
#17
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
#18
Since i am still getting errors so i am puting it back as active bug.
--rafi
#19
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 120http://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
#20
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
#21
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
#22
Are you using the very latest version of views? This might be a bug that was just recently fixed in views (http://drupal.org/node/66928).
#23
Have you tried the following :
- empy your cache (for instance by simply re-submitting your 'modules' page, or with devel.module's "empty cache")
- re-save the incriminated view(s), maybe after checking / redefining the "filters" part
#24
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
#25
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
#26
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
#27
Looks like this is an event_views error, so I'm moving this issue. I thought I had checked that I was using terms that worked in early versions of mysql, but I must have missed this one. I'll get it fixed soon.
#28
Subscribing, so I know when this is fixed... I am seeing errors too, although they don't seem to break anything. Thanks for the fix, when it comes.
#29
OK, hopefully this is now fixed in both cvs and 4.7. Let me know if it does not work.
#30
Forgot to say thanks chaos21in for the debugging effort. It saved me a lot of time!
#31