MySQL version 5.0.81-community on lunarpages web host.

On calling /g2/initial/g

MySQL server version for the right syntax to use near 'DISTINCT(n.nid)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR ' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR n.language IS NULL) AND ( n.type = 'g2_entry' )GROUP BY n.status in /home/webel02/public_html/sites/all/modules/g2/g2.module on line 842.
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)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR ' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR n.language IS NULL) AND ( n.type = 'g2_entry' AND n.title like 'initial%' )GROUP BY n.status in /home/webel02/public_html/sites/all/modules/g2/g2.module on line 842.

BTW where (it at all) is the corresponding G2 view (or does it use another approach), i did not find G2 or similar in the views list.

Very glad for help on this, my client needs to make a decision soon on migrating our current Definition system, and this module seems very promising for our needs, as we need a highly customised node type for our Definitions (what you call G2 Entry type), and together with the wonderful Node Type module the conversion is not so painful.

Visit also: #673728: Create a normal string handler for taxonomy term names so that they can be used with glossary style views

CommentFileSizeAuthor
#7 dbrq.patch651 bytesfgm
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Category: bug » support
Status: Active » Postponed (maintainer needs more info)

Hi,

It seems you are having a db_rewrite_sql problem. These are typically due to access control modules, like og_access, book_access, taxonomy_access, tac_lite...

Regarding Views :
- there is no specific Views integration in the current G2 versions, although nothing prevents you from using g2_entry nodes in node-based views.
- the Views 2.x "glossary" mode is unrelated to either g2.module or glossary module : it is just a way to build an index on a View argument.

Can you reproduce the problem on a clean Drupal install without any contrib module except G2 ?

webel’s picture

webel CreditAttribution: webel commented

> Can you reproduce the problem on a clean Drupal install without any contrib module except G2 ?

I may have the opportunity to do this soon as a side-effect of some work with a client
(I happen to a fresh install running at the moment). Well get back to you on this.

Thanks for your reply, Webel

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Status: Postponed (maintainer needs more info) » Fixed

Reproduced by installing an access control module (book_access) on a clean install reproduces the problem, which is apparently due to a weakness in db_distinct_field() needing the primary field to come first.

Worked around the underlying core issue in today's version. No equivalent problem expected in D7 due to DBTNG.

webel’s picture

webel CreditAttribution: webel commented

Well done, and thanks for testing, yes I am using book_access (and need it).
BTW I was arranging to do a test w.r.t. book_access vs. Glossary2 on a clean install tomorrow.

My client remains very interested in this module, so glad for any updates for D6.

Sorry, DBTNG = ?
fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented

DBTNG is the usual tongue-in-cheek name for the D7 Database API: http://drupal.org/node/310069

As you can imagine, it's a pun on Star Trek : The Next Generation.

webel’s picture

webel CreditAttribution: webel commented

I tried 6.x-1.x-dev 2010-Jan-25.

It failed with a new error, apparently due to interaction with Killfile (6.x-1.0-beta1):

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)) cnt FROM node n LEFT JOIN killfile_nodes kfn ON kfn.nid = n.n' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n LEFT JOIN killfile_nodes kfn ON kfn.nid = n.nid WHERE (kfn.timestamp IS NULL OR kfn.timestamp > 1264656234) AND (n.language ='en' OR n.language ='' OR n.language IS NULL) AND ( n.type = 'g2_entry' )GROUP BY n.status in ../modules/g2/g2.module on line 842.

I disabled killfile, and get an new error, apparently due to languge modules:

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)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR ' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR n.language IS NULL) AND ( n.type = 'g2_entry' )GROUP BY n.status in .../modules/g2/g2.module on line 842.

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)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR ' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE (n.language ='en' OR n.language ='' OR n.language IS NULL) AND ( n.type = 'g2_entry' AND n.title like 'initial%' )GROUP BY n.status in .../modules/g2/g2.module on line 842.

I am using Internationalization 6.x-1.1. I turn off all language modules and get:

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)) cnt FROM node n WHERE n.type = 'g2_entry' GROUP BY n.status' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE n.type = 'g2_entry' GROUP BY n.status in .../modules/g2/g2.module on line 842.

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)) cnt FROM node n WHERE n.type = 'g2_entry' AND n.title like 'in' at line 1 query: SELECT n.status, COUNT(distinct DISTINCT(n.nid)) cnt FROM node n WHERE n.type = 'g2_entry' AND n.title like 'initial%' GROUP BY n.status in .../modules/g2/g2.module on line 842.

I remain very interested in this module, however it is wreaking havoc with the site I need to deliver in next days ! I can't continue disabling features like this before my client's eyes (however luckily I am migrating from development host to production host next week, so I will be able to perform tests (and wreak havoc) on the current development site, and I can leave off wanted modules (like killfile and internationalization) during tests. But only from next week or later.

I remain very interested in this (clearly experimental) module. Could you please in the meantime test this against MySQL further, and in combination
with other modules (which I can help you with, but only after I have a separate production and development versions of the site).

thanks for persistence,

Webel

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Status: Fixed » Needs review
FileSize
dbrq.patch651 bytes

You can try to apply the attached patch to see if it fixes the issue your are encountering: it is the same issue, due to db_rewrite_sql() in all three cases.

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Title: Search by initial fails on MySQL due to DISTINCT in SQL clause » Compatibility problem with node access modules: search by initial fails on MySQL due to DISTINCT in SQL clause
Status: Needs review » Fixed

Fix tested with locale, killfile and i18n enabled both separately and simultaneously with no visible problem.

Applied to today's dev version.

webel’s picture

webel CreditAttribution: webel commented

I downloaded and installed:

6.x-1.x-dev Download (30.94 KB) 2010-Jan-29

New 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)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' GROUP BY n' at line 1 query: SELECT COUNT(distinct DISTINCT(n.nid)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' GROUP BY n.status in .../modules/g2/g2.module on line 841.

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)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' AND n.titl' at line 1 query: SELECT COUNT(distinct DISTINCT(n.nid)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' AND n.title like 'initial%' GROUP BY n.status in .../modules/g2/g2.module on line 841.

Please persist and retest.

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented

You are apparently using the (incorrect) quick patch instead of the latest version. Please use the latest dev version and retest instead of using the patch from #8.

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Issue tags: +select distinct as distinct bug
webel’s picture

webel CreditAttribution: webel commented

I did not use any patch, I used http://ftp.drupal.org/files/projects/g2-6.x-1.x-dev.tar.gz, as clearly stated in my previous email, which says, with link to the download:

I downloaded and installed:

6.x-1.x-dev Download (30.94 KB) 2010-Jan-29

I will download the most recent and report back.

webel’s picture

webel CreditAttribution: webel commented

I just (re) downloaded and reinstalled, completely freshly (as before).

I have BookAccess and Content Access enabled (but not Killfile or internationalization modules, which provoked problems earlier).

I get this 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)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' GROUP BY n' at line 1 query: SELECT COUNT(distinct DISTINCT(n.nid)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' GROUP BY n.status in .../modules/g2/g2.module on line 841.

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)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' AND n.titl' at line 1 query: SELECT COUNT(distinct DISTINCT(n.nid)) cnt, n.status FROM node n WHERE n.type = 'g2_entry' AND n.title like 'g%' GROUP BY n.status in .../modules/g2/g2.module on line 841.

And I'm sure its the latest version, because it includes your initial display fix.

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented
Status: Fixed » Postponed (maintainer needs more info)

I'm afraid I cannot reproduce the problem: I have enabled both book_access and content_access and configured them, and do not see this error, which should have been fixed by the version you have anyway.

I have some G2 content, all G2 blocks enabled, and visited the by initial page, a non-G2 page, the G2 main page, and a G2 node page, without encountering the problem.

- What is the version number of g2.module ? It should be at least 1.19.2.10
- Can you describe steps to reproduce the problem from a clean core + g2 install ?

webel’s picture

webel CreditAttribution: webel commented

> What is the version number of g2.module ? It should be at least 1.19.2.10

// $Id: g2.module,v 1.19.2.10 2010/01/29 07:14:24 fgm Exp $

> Can you describe steps to reproduce the problem from a clean core + g2 install ?

The live site is running Drupal core 6.8.
I am planning to upgrade tonight to 6.15.
Before I proceed, what version of Drupal core are you using ?

Also, perhaps relevant, MySQL version: 5.0.81-community (bit old, can't be changed, is on a remote web host Lunarpages).

I will be able to create a fresh install with g2 only and test.

fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented

I'm using MySQL 5.1 and core 6.15, but the issue you describe is visibly related to db_rewrite_sql() generating incorrect SQL, which no sane DBMS will accept: SELECT COUNT(distinct DISTINCT(n.nid)) cnt.

It builds this based on the (incorrect) result from db_distinct_field(), which was fixed rather recently (Drupal 6.14) for a similar issue with Views (#284392: db_rewrite_sql causing issues with DISTINCT). Please confirm the results you obtain with 6.15 to ascertain whether you are in fact meeting this old core bug, or one in G2.

webel’s picture

webel CreditAttribution: webel commented

I'm delighted to report that the upgrade to Drupal 6.15 has overcome the problem. Thanks for your persistence in dealing with the matter.

Webel IT Australia

webel’s picture

webel CreditAttribution: webel commented
Status: Postponed (maintainer needs more info) » Closed (fixed)
fgm’s picture

fgm
Primary language French
Location Paris, France
CreditAttribution: fgm commented

Glad to hear that. Thanks for confirming.

However, please don't set issues to "Closed" yourself, because I (and others) might not see them at all, like this happened with the perms issue: rather set them to "Fixed" so they stay visible until the auto-closer applies.

webel’s picture

webel CreditAttribution: webel commented

read, thanks.