By suit4 on

I get a database user warning:

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(td.tid), COUNT(tn.nid) as nodes FROM term_data td LEFT JOIN term_node t' at line 1 query: _glossary_get_terms SELECT td.name, td.description, DISTINCT(td.tid), COUNT(tn.nid) as nodes FROM term_data td LEFT JOIN term_node tn USING(tid) LEFT JOIN forum_access fa ON td.tid = fa.tid LEFT JOIN acl acl ON acl.name = td.tid AND acl.module = 'forum_access' LEFT JOIN acl_user aclu ON aclu.acl_id = acl.acl_id AND aclu.uid = 2 WHERE ((fa.grant_view >= 1 AND fa.rid IN (2, 5)) OR fa.tid IS NULL OR aclu.uid = 2) AND (td.tid IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','26','28','29','30','31','32','33','34','35','36','37','38','39', '40','41','42','44','45','46','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','70','71','72','73','74','75','76','77', '78','79','80','81','82','83','84','86','87','88','89','90','91','94')) AND ( td.vid=2 ) GROUP BY td.tid, td.name, td.description ORDER BY LENGTH(td.name) DESC in /var/www/localhost/htdocs/testsite/includes/database.mysqli.inc on line 151.

I *guess* the reason is, that the DITINCT() is not directly after the SELECT.
The problem is, I can't track to the source of this error.

The error only appears to non-admin users.

I already recreated the permissions table and reinstalled acl module, but with no success.

Any ideas or help to push me into the right direction very welcome.

[Heine - moved to the SUPPORT forum]

⋅ Categories: Drupal 5.x

Comments

Miszel’s picture

Miszel commented

If I were you, I would try to place the following line:

debug_print_bac­ktrace()

just above line 151 in database.mysqli.inc . This will give you a list of functions (and their locations) that had been called before and may help you track the source of this error.

Personal Website
Company Website

heine’s picture

heine commented

The query on line 994 (in _glossary_get_terms() in glossary.module) should read 

SELECT td.tid, td.name, td.description, COUNT(tn.nid) as nodes FROM {term_data} td LEFT JOIN {term_node} tn USING(tid) WHERE td.vid=%d GROUP BY td.tid, td.name, td.description ORDER BY LENGTH(td.name) DESC

--
The Manual | Troubleshooting FAQ | Tips for posting | How to report a security issue.

suit4’s picture

suit4 commented

As far as I got by now, the query does not work, if it reads
SELECT td.name, td.description, DISTINCT(td.tid), COUNT(tn.nid) ...

but it does work, if it reads
SELECT DISTINCT(td.tid), td.name, td.description, COUNT(tn.nid) ...

It's a bit problematic to debug this, as far as the error only occurs, when the cache was cleared.

_glossary_get_terms() uses db_rewrite_sql(), which again runs db_distinct_field().

An extra DISTINCT comes in from module forum_access, so the request is rewritten from
SELECT td.name, td.description, td.tid, COUNT(tn.nid)

to
SELECT td.name, td.description, DISTINCT(td.tid), COUNT(tn.nid)

with the DISTINCT in the wrong place.

@Heine you are absolutely right. Changing lin 994 in glossary.module does the trick.

Thanks a lot!

I think, I need to file a bug report for glossary.module ...
--------------------------------------
http://www.human-aspects.de