By divrom on

Aaarrgh!

I have a site going live on Tuesday for a large non-profit and the events and privacy_by_role have started clashing. This has happened on another site I'm working on, but I could turn privacy off for that. However, the non-profit site needs privacy.

Someone at civicspace suggested (for the other site): 'My guess is that you recently upgraded (or your host did) to a new version of MySQL or changed into a stricter mode in MySQL.' That strikes me as pretty likely as I know they've just been upgrading (to PHP 5, etc.).

Here's the specific error message:

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')) AND n.status = 1 AND n.moderate = 0 AND e.event_start >= 1145003415 ORDER BY event_start LIMIT 0, 6 in /home/sites/felixweb.co.uk/public_html/current/doc/includes/database.mysql.inc on line 66.

Are there any solutions to this? (What great timing when everyone's in holiday mode from now till when the site goes live!) I might even throw in a free Easter egg to whoever can help me! :-)

⋅ Categories: Drupal 4.6.x

Comments

rjl’s picture

rjl commented

Change the SQL of the query.

In the from clause

change
USING (nid)

to
INNER JOIN n.nid=e.nid

I imagine this query is in the event module, but am not posititive. If so, it would be good to post an issue with the event.module project.

The query in the module will not include the node_access table but will be something like SELECT ...fields... FROM {node} n INNER JOIN {event} e USING (nid) WHERE ... .

This query is passed to the db_rewrite_sql() function

The db_rewrite_sql() function adds the node_access table to the JOIN clause and adds the appropriate criteria expressions to the WHERE clause. The nid column now appears in 3 tables (node, event and node_access) and just stating nid is ambiguous. Changing USING to ON allows you to explicitly state which nid columns to evaluate.

The use of USING (some_field) is great for simple queries, but IMHO should be disallowed from drupal, as it can create problems when modules attempt to interact together.

divrom’s picture

divrom commented

These are changes to the event.module (i.e. the php file)? Not a new sql command thru phpmyadmin?

I'll try and follow the directions you gave in the other thread. Thanks.

If things work, I'll report back.

rjl’s picture

rjl commented

Sorry, this is a little confusing with the two threads...

Yes, change the php code in the event.module file.

In the other post I noted this 

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);

change the USING (nid) to ON n.nid=e.nid

looking over the other post, it appears there are 3 other queries that could potentially cause problems and haven't been addressed here, but have in the other post http://drupal.org/node/43735

divrom’s picture

divrom commented

That change (USING (nid) to ON n.nid=e.nid) gives me an error:

warning: Cannot modify header information - headers already sent by (output started at /home/sites/....co.uk/public_html/.../modules/event/event.module:1) in /home/sites/....co.uk/public_html/.../includes/common.inc on line 192.

rjl’s picture

rjl commented

Hmm? Curious? I don't think that change would cause that error.

Is there anything appearing in the event module code after the closing php tag ?> (the last line of the file)? including any white space or line returns? If so remove that as that can sometimes cause that error.

Otherwise I am at a loss.

divrom’s picture

divrom commented

I couldn't fix it, for some reason.

What I've done now is delete the events module but kept 'event' as a flexinode type (wasn't using the calendar anyway). I'm then using a php snippet, in a block, to call a list of the latest events.

The only problem with that is that I'm scheduling the events to be unpublished when the events is over, but the snippet doesn't recognise the change in status. Ah well, guess I can live with that.

Thanks for ur help.

divrom’s picture

divrom commented

The last problem was solved easily enough. I just needed to add:

AND n.status = 1

Now, I need to go and figure and how not to display the block title if there are no items in the block!