I use this module on 2 other Drupal Sites, without issues. But on one Install I get:

user error: You have an error in your SQL syntax near 'on n.nid = c.nid  where c.status = 0 group by c.nid order by the_time desc LIMIT' at line 1

query: SELECT c.nid, n.title, count(c.nid) as comment_count, max(c.timestamp) as the_time FROM comments c JOIN node n on n.nid = c.nid  where c.status = 0 group by c.nid order by the_time desc LIMIT 5 in /var/www/html/includes/database.mysql.inc on line 125.

Thanks for ANY feedback.

Comments

Prometheus6’s picture

Prometheus6 commented

What version of Drupal and MySQL are you using?

czarphanguye’s picture

czarphanguye commented

Thanks for a reply:

Drupal v4.5.1

- But, I have been using Drupal since v4.4.0. The reason I bring this up, After doing some additional research I think this issue has something to do with the comment problem. I have NOT used a patch, nor anything else.. just the update.php

I'm not sure what SQL version I'm use... How would I check that?

Prometheus6’s picture

Prometheus6 commented

If you use phpMyAdmin, just connect and the home page will tell you what version you're running. On the command line give the -V or --version switch.

I am new to Drupal development and never really coded againt 4.4. I didn't know there was a comment issue for upgraders. Were the two sites that had no problems clean installs or upgrades? At any rate in the next day or two I'll probably load up 4.4, upgrade it and see how that works.

czarphanguye’s picture

czarphanguye commented

Dear Prometheus6,

Thank you for your reply.

MySQL version is 3.23.56. Both sites, that Live Discussion work are fresh installations of Drupal v4.5.1.

czarphanguye’s picture

czarphanguye commented

P.S.

In /admin/settings/live_discussions the "Show comment count per post" selection refuses to tick. It started in the off position and remains.

Prometheus6’s picture

Prometheus6 commented

Okay, I have a copy of the 4.4 archive that predates that thread about the comment update issue. I'll see if I can duplicate your problem by doing the install and update...that much I can get back to you on by tomorrow.

Prometheus6’s picture

Prometheus6 commented
Assigned: Unassigned » Prometheus6

Okay, I was able to duplicate the checkbox thing but not the sql error message., but I did find a coding error that I think could cause the sql error message under some conditions.

I've committed some changes I think will fix the whole lot. Give the system a couple of hours to repackage the download and try it again.

czarphanguye’s picture

czarphanguye commented

Thanks, I tried out the new module (mod date the 12th).

Same MySQL error. ;-/

Prometheus6’s picture

Prometheus6 commented

I may be stumped...and I you have no idea how much I hate saying that. I've done upgrade from 4.4 to 4.5 and 4.5.1 and get no SQL errors at all.

The query in the error message:
SELECT c.nid, n.title, count(c.nid) as comment_count, max(c.timestamp) as the_time FROM comments c JOIN node n on n.nid = c.nid where c.status = 0 group by c.nid order by the_time desc LIMIT 5
…is the right one. Can you run it successfully in phpMyAdmin?

Prometheus6’s picture

Prometheus6 commented
Priority: Critical » Normal

Can not duplicate

jmstriegel’s picture

jmstriegel commented
Status: Closed (fixed) » Active

I was able to duplicate on media temple's hosting service. mysql --version gives:
mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)

It apparently doesn't like the JOIN syntax for some reason. If you change to INNER JOIN it appears to work correctly.

Prometheus6’s picture

Prometheus6 commented

MySQL pre-version 4 doesn't like my join syntax it seems, though I still am not clear why it would complain. I recently got a rewritten module on another project of mine (rather than a patch, which is why I haven't looked at it yet) that the submitter said "now works" with this very version of MySQL.

Thanks for finding that. I'll probably run through all my GPL modules looking for similar stuff.

Prometheus6’s picture

Prometheus6 commented
Status: Active » Closed (fixed)

Updated 4.6 and HEAD.