By Donovan on

I just upgraded my 4.5.2 site to Drupal 4.6.0 and immediately started receiving this error message:

user error: 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(c.nid), c.subject, c.pid, u.name FROM mmex_comments c
query: SELECT DISTINCT(c.cid), DISTINCT(c.nid), c.subject, c.pid, u.name FROM mmex_comments c INNER JOIN mmex_users u ON u.uid = c.uid WHERE c.status = 0 AND c.timestamp > 1113951559 ORDER BY c.nid, c.timestamp in /...my_website_path.../includes/database.mysql.inc on line 66.

My installation uses a database prefix of "mmex_".

The message is repeated 13 times each time it occurs -- I also happen to have 13 registered users.

I am pulling my hair out trying to identify the cause and potential fix for the problem. I suspect that there is a clue to the cause embedded in the error message, but so far it's elusive to this noob.

I have searched the forums for similar posts but have not found any. Any thoughts are appreciated. Thx.

Comments

autowitch’s picture

autowitch commented

It's the two DISTINCT() clauses that's causing the error.

What modules are you using? I looked through the basic drupal modules and found some similar queries in the comment module, but not the one you are looking at (fields in different order).

It's possible that something like a call to db_rewrite_sql() added the extra DISTINCT to the query.

-aw

Donovan’s picture

Donovan commented

Thanks autowich. I did some searching as well and could not find the code fragment either.

I have the following modules loaded (in addition to the Drupal 4.6.0 default selections):

  • Standard distribution - comments, page, story, aggregator, taxonomy, upload, forum, help;
  • Contributed - news_page, notify, organic groups, feedback, image.

    • "It always seems impossible, until it is done."
    - Nelson Mandela

    Donovan’s picture

    Donovan commented

    UPDATE: Problem was caused by Notify module. It turns out that the CVS version of the Notify module had a bug that caused the SQL errors. This is not a Drupal core bug or issue. Gerhard (Killes) will be posting an update to Notify shortly.

    "It always seems impossible, until it is done."
    - Nelson Mandela