Error with node access modules and mysql 5

Samm - do you know which mode your mysql5.0 database was in?

It could be that this is a problem with "strict" mode

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

--
Wanderlusting Travelers
Knaddison Family

I've been a little confused by hook_db_rewrite_sql and have been trying to figure it out, because it's a pretty fascinating hook. So I found your post interesting.

As far as I can tell, node_db_rewrite_sql (the node modules implementation of hook_db_rewrite_sql) appears to be the function that inserts the the node_access table in the JOIN clause. What I find interesting is that it also appears to rewrite the entire FROM clause after the first table.

The event.module calls db_rewrite_sql 4 times (note line #'s may not be same as yours but should be close by).

line 624
$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 '), $stamp, $endstamp, $stamp, $endstamp, $stamp, $endstamp);
line 673
$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 '), $stamp, $endstamp, $stamp, $endstamp, $stamp, $endstamp);
line 913
$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);
line 1593
$result = db_query(db_rewrite_sql("SELECT n.nid, n.title, n.type, n.status, n.moderate, e.event_start FROM {node} n INNER JOIN {event} e USING (nid) WHERE n.status = 1 AND n.moderate = 0 AND e.event_start >= %d ORDER BY event_start"), $time);

If you look at an error message that has been noted in regards to the event module.

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 ...

Compare the SELECT and FROM clauses in the error to any of the SELECT and FROM clauses passed to the function, I think it is fairly clear that the 4th call made in the event module (line 1593) is not at issue here, but any of first 3 are a match. Note how event e INNER JOIN node n gets changed to event e INNER JOIN node_access na. So the FROM statement was re-written after the first table.

It appear that all the reported errors indcate one of those first 3 calls. No one appears to have reported an error that would indicate the last call (line 1593).

The image.module calls db_rewrite_sql 4 times, (note line #'s may not be same as yours but should be close by).

line 407
$last = db_fetch_object(db_query_range(db_rewrite_sql('SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN (%s) AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC'), implode(',', $descendant_tids), 0, 1));
line 413
$result = pager_query(db_rewrite_sql("SELECT n.nid FROM {term_node} t INNER JOIN {node} n ON t.nid=n.nid WHERE n.status=1 AND n.type='image' AND t.tid=%d ORDER BY n.sticky DESC, n.created DESC"), variable_get('image_images_per_page', 6), 0, NULL, $tid);
line 575
$result = db_query_range(db_rewrite_sql("SELECT nid FROM {node} n WHERE n.type='image' AND n.status=1 ORDER BY RAND()"), 0, $count);
line 587
$result = db_query_range(db_rewrite_sql("SELECT nid FROM {node} n WHERE n.type='image' AND n.status=1 ORDER BY changed DESC"), 0, $count);

Here's a reported error in regards to the image module

user error: Unknown column 'n.nid' in 'on clause' query: SELECT COUNT(*) FROM term_node t INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n 

This one is tricky, because of COUNT(*), as none of the SQL strings passed has COUNT(*) in the SELECT clause, but the second one, line 413, is a pager query which would send a COUNT(*). Note the FROM clause passed FROM {term_node} t INNER JOIN {node} n gets changed to FROM term_node t INNER JOIN node_access na. Again the FROM statement was re-written after the first table.

It appears that all the reported errors in regard to the image module point to the second call (line 413) and no one seems to have reported an error that would indicate any of the other 3 calls.

So what's the problem then... I am going to hazard a guess, now that I have seen the SQL statements together. I think the problem may be fixed if when using db_rewrite_sql function, the first table listed in the FROM clause was the NODE table.

So for the event module change the first 3 calls from SELECT n.nid, e.event_start FROM {event} e INNER JOIN {node} n ON n.nid = e.nid to SELECT n.nid, e.event_start FROM {node} n INNER JOIN {event} e ON n.nid = e.nid . And for the image module, change the second one (line 413) from SELECT n.nid FROM {term_node} t INNER JOIN {node} n ON t.nid=n.nid to SELECT n.nid FROM {node} n INNER JOIN {term_node} t ON n.nid=t.nid

Now if that works, I really can't yet explain why exactly, and would love to hear the reason.

I wish I had access to MySQL 5 so I could test my theory, but maybe it will help you. Please let me know if you try it and if it works, or maybe you have already tried it.

This seems like a good a place post my findings on the subject. Just to recap, the problem is db_rewrite_sql() is easy to use incorrectly, and msql 5.0.12 and up has changed some of the join rules to conform to the sql2003 standard. (http://dev.mysql.com/doc/refman/5.0/en/join.html, the section that starts with "Note: Beginning with MySQL 5.0.12").

I have found issues in event.module, image.module & taxonomy.module. Three come from not having the $primary_table match the FROM table. This can be fixed by either swapping the join order, or by actively setting the $primary_table parameter of db_rewrite_sql (it defaults to 'n').
Also, there is one case where a USING needs to be changed to an ON clause.

Here are my findings & fixes:

#1: Broken - from event_calendar_data, event_calendar_rss & event_calendar_ical: Unknown column 'n.nid' in 'on clause'

Query: SELECT * FROM {event} e INNER JOIN {node} n ON n.nid = e.nid WHERE n.status = 1. Returned Row Count: 2

Rewritten Query: SELECT * 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 CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.status = 1. Returned Row Count:

Fix

Query: SELECT * FROM {node} n INNER JOIN {event} e ON n.nid = e.nid WHERE n.status = 1. Returned Row Count: 2

Rewritten Query: SELECT * FROM {node} n INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {event} e ON n.nid = e.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.status = 1. Returned Row Count: 2

#2: Broken & Throwing Errors - from event_block_upcoming: Column 'nid' in from clause is ambiguous

Query: SELECT * FROM {node} n INNER JOIN {event} e USING (nid). Returned Row Count: 2

Rewritten Query: SELECT * 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')). Returned Row Count:

Fix

Query: SELECT * FROM {node} n INNER JOIN {event} e ON n.nid = e.nid. Returned Row Count: 2

Rewritten Query: SELECT * FROM {node} n INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {event} e ON n.nid = e.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')). Returned Row Count: 2

#3: Broken - image_page: Unknown column 'n.nid' in 'on clause'

Query: SELECT n.nid FROM {term_node} t INNER JOIN {node} n ON t.nid=n.nid WHERE n.status=1 AND n.type='image' ORDER BY n.sticky DESC, n.created DESC. Returned Row Count: 2

Rewritten Query: SELECT DISTINCT(n.nid) FROM {term_node} t INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {node} n ON t.nid=n.nid WHERE (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 n.type='image' ORDER BY n.sticky DESC, n.created DESC. Returned Row Count:

Fix

Query: SELECT n.nid FROM {node} n INNER JOIN {term_node} t ON t.nid=n.nid WHERE n.status=1 AND n.type='image' ORDER BY n.sticky DESC, n.created DESC. Returned Row Count: 2

Rewritten Query: SELECT DISTINCT(n.nid) FROM {node} n INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {term_node} t ON t.nid=n.nid WHERE (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 n.type='image' ORDER BY n.sticky DESC, n.created DESC. Returned Row Count: 2

#4: Broken - taxonomy_term_count_nodes (twice): Unknown column 'n.nid' in 'on clause

Query: SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 GROUP BY t.tid. Returned Row Count: 1

Rewritten Query: SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM {term_node} t INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {node} n ON t.nid = n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.status = 1 GROUP BY t.tid. Returned Row Count:

Fix

Query: SELECT t.tid, COUNT(n.nid) AS c FROM {node} n INNER JOIN {term_node} t ON t.nid = n.nid WHERE n.status = 1 GROUP BY t.tid. Returned Row Count: 1

Rewritten Query: SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM {node} n INNER JOIN {node_access} na ON na.nid = n.nid INNER JOIN {term_node} t ON t.nid = n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.status = 1 GROUP BY t.tid. Returned Row Count: 1