Bad interaction with MySQL 5 & Image & Event modules

i also have this issue with: event + node_privacy_byrole and event + simple_access with the calendar view

my log error is:
Unknown column 'n.nid' in 'on clause' query: SELECT DISTINCT(n.nid), e.event_start FROM event e INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n ON n.nid = e.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'simple_access'))) AND n.status = 1 AND ((e.event_start > 1136073600 AND e.event_start < 1138751999) OR (e.event_end > 1136073600 AND e.event_end < 1138751999) OR (e.event_start < 1136073600 AND e.event_end > 1138751999)) ORDER BY event_start in /sites-new/includes/database.mysql.inc on line 108.

event.module at line 945:
$result = db_query(db_rewrite_sql('SELECT n.nid, e.event_start FROM {event} e INNER JOIN {node} n ON n.nid = e.nid WHERE n.status = 1 AND ((e.event_start > %d AND e.
event_start < %d) OR (e.event_end > %d AND e.event_end < %d) OR (e.event_start < %d AND e.event_end > %d)) ORDER BY event_start '), $first, $last, $first, $last, $first, $last);

i cannot flip the INNER JOIN as you did, in that case na.nid is unknown

I get this same problem when I copied a complete website from a server running mySQL 4 (php 4.4.1) into my local server running the latest XAMPP server (MySQL 5.0.18 + PHP 5.1.1).

I get this message at the first attempt connecting to the new server (From 2:nd attempt and on I just get Error 404 message):

user error: Unknown column 'n.nid' in 'on clause'
query: SELECT DISTINCT(n.nid), e.event_start FROM rilcms_collab_event e INNER JOIN rilcms_collab_node_access na ON na.nid = n.nid INNER JOIN rilcms_collab_node n ON n.nid = e.nid WHERE (n.language ='en' OR n.language = '') AND (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.status = 1 AND ((e.event_start > 1136073600 AND e.event_start < 1138751999) OR (e.event_end > 1136073600 AND e.event_end < 1138751999) OR (e.event_start < 1136073600 AND e.event_end > 1138751999)) ORDER BY event_start in D:\Program files\XAMPP\xampp\htdocs\rilcms\collab\includes\database.mysql.inc on line 66.

"rilcms_collab_" is just my table-prefix.
node_privacy_by_role is active, (neither simple_access nor taxonomy_access)

The cause(?):
http://bugs.mysql.com/bug.php?id=13832

A question to the SQL experts out there. Since the 4.0.24 JOIN syntax is not compatible with 5.0.12 syntax, it causes the following query to cause an error (unknown column "n.nid" in 'on clause').

Works fine in MySQL 4.0.24:

query: SELECT DISTINCT(n.nid), e.event_start FROM event e INNER JOIN
node_access na ON na.nid = n.nid INNER JOIN node n ON n.nid = e.nid WHERE

Rewritten (proposal) for MySQL 5.0.12 According to the article; http://dev.mysql.com/doc/refman/5.0/en/join.html (search for "Before MySQL 5.0.12, the ON clause" on the page):

query: SELECT DISTINCT(n.nid), e.event_start FROM event e INNER JOIN
node n ON n.nid = e.nid INNER JOIN node_access na ON na.nid = n.nid WHERE

Q: Is the rewritten query:

1. semantically the same query as the above?
2. would it (e.g. "n.nid") run correctly on 5.0.12+ ?
3. correctly implementing the recommendation in the referred article?

If correct on all paragraphs, this might shed some more light over this issue.

Rolf, your solution seems to work - at least it does from the mysql command line. I'm not at all familiar with how Drupal builds it's SQL queries however, so I don't know how to implement the changes in the code. I imagine it's far from being a trivial challenge, but if anyone knows please let me know!

I'm having trouble with this too. Just want to track this...

user error: Column 'nid' in from clause is ambiguous

query: SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN node_access na ON na.nid = n.nid INNER JOIN event e USING (nid) WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0','term_access1')) AND n.status = 1 AND n.moderate = 0 AND e.event_start >= 1139057940 ORDER BY event_start LIMIT 0, 6 in /home/.makadisa/nynjtc/ramapo2007.org/includes/database.mysql.inc on line 66.

dreamhost just switched to 5

When I asked Dreamhost if MySQL 4 was available I got this reply:

Unfortunately no... we are upgrading all servers to mysql 5. I'm sorry this is causing you a problem.. mysql 5 has been released and stable for quite a while now, Drupal has had plenty of time to fix this problem.
The only reason it breaks programs is if those programs were not SQL99 compliant. It's up to Drupal to get an SQL99 compliant copy of their
product, and we really can't wait for them any longer. I'm very sorry.

There is definately an issue here, and a complex one at that. I've been trying to figure it out and I've made two lengthy comments to posts regarding similar problems.

The best that I have been able to deduce so far is that is has something to do with the core db_rewrite_sql() function (both the image and event modules use this function - many other modules do as well) and the related hook_db_rewrite_sql() function (which as far as I have seen is not a commonly implemented hook).

This error is not common and appears to rear its ugly head mostly with node access modules (such as node_privacy_byrole and taxonomy_access, etc.) because the core node module implements the hook to check node access (hence the inclusion of INNER JOIN node_access na ... in the sql strings). Because the error is seen in conjunction with node access modules, it appears to be a problem with the node access module itself, but I do not think it is.

I think that the db_rewrite_sql() function needs to be re-worked and then all modules using the function will need to be patched depending on what is done with the db_rewrite_sql() function. The fix that this implies is fairly substantial.

Whatever the problem, drupal ultimately needs to be able to work properly with MySQL 5.0.

Please read these two posts:
http://drupal.org/node/46864 see issue #12
http://drupal.org/node/43735

Let me know if you think I am on the right track here or way off base.

Does that mean that Node Priacy by Role should be working with MySQL5? As it isn't on my system... Are there any of the privacy type modules that do work with MySQL5?

Bookmarking, greetings, Martijn

#331651: db_rewrite_sql improperly rewriting a subquery