[support] My site is haunted! SQL code gets changed by the Phantom of SQL Errors!

[Neil: esl-lounge.com]

The culprit was the Workflow module!

----- Original Message ----- 
From: "Gerhard Killesreiter" <gerhard at killesreiter.de>
To: <support at drupal.org>
Sent: Thursday, June 05, 2008 12:52 PM
Subject: Re: [support] My site is haunted! SQL code gets changed by the 
Phantom of SQL Errors!


> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Neil: esl-lounge.com schrieb:
>> This is a real head scratcher.
>>
>> I have two sites: mysite.com and it.mysite.com, the Italian version.
>>
>> I noticed this morning a ton of mysql errors on pages where I have a 
>> block called "Related Pages". The sql query for this block is:
>>
>> SELECT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = 
>> tn.nid WHERE tn.tid = $term->tid AND n.nid != $nid LIMIT 3
>>
>> it gets me a list of 3 related pages that share the same taxonomy terms 
>> and was working perfectly about 4 days ago the last time I checked. Now 
>> it gives me the error:
>>
>> user 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(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid 
>> WHERE tn.t' at line 1 query: SELECT n.title, DISTINCT(n.nid) FROM node n 
>> INNER JOIN term_node tn ON n.nid = tn.nid WHERE tn.tid = 59 AND n.nid != 
>> 200 LIMIT 3 in /home/mysite/public_html/includes/database.mysql.inc on 
>> line 172.
>>
>> I pasted the code into phpmyadmin and then noticed the difference between 
>> this and what I was getting on the Italian site which has identical code. 
>> For some reason, the DISTINCT was getting put into the code.
>>
>> So in block config, the code has been pasted as:
>>
>> SELECT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = 
>> tn.nid WHERE tn.tid = $term->tid AND n.nid != $nid LIMIT 3
>>
>> and yet the query is being sent as:
>>
>> SELECT n.title, DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON 
>> n.nid = tn.nid WHERE tn.tid = 59 AND n.nid != 200 LIMIT 3
>>
>> and it is this which is causing an error. In phpmyadmin, when I take away 
>> the DISTINCT(), it works like a dream.
>>
>> this is not happening on the Italian site despite identical block code. 
>> Now, I have been doing quite a bit of work with phpmyadmin in last few 
>> days although I can't think of anything I've changed on the english DB 
>> that may have created this result.
>>
>> Do I have to start calling the mediums and reaching for the garlic and 
>> crucifix?
>>
>
> No, you need to disable the node access module or to fix the node_access
> table if you don't use one.
>
> Cheers,
> Gerhard
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIR8Vyfg6TFvELooQRAtWJAKC4xiEgJpHfzBHS/7VIf4WMWbXfIgCfciqi
> fTbjbLBQz36m28G16JSvpkg=
> =b+UT
> -----END PGP SIGNATURE-----
> -- 
> [ Drupal support list | http://lists.drupal.org/ ]
>