I'm using spam.module version 2.0.12pre, and am seeing the following error upon occasion:

query: SELECT COUNT(*) FROM spam_tracker s INNER JOIN trackback_received tr WHERE s.id = tr.trid AND s.source = 'trackback' AND s.probability >= 80 in /usr/local/share/drupal-4.6.3/includes/database.pgsql.inc on line 62.

pg_query(): Query failed: ERROR: syntax error at or near "WHERE" at character 70 in /usr/local/share/drupal-4.6.3/includes/database.pgsql.inc on line 45.

query: SELECT tr.*, s.probability FROM spam_tracker s INNER JOIN trackback_received tr WHERE s.id = tr.trid AND s.source = 'trackback' AND s.probability >= 80 ORDER BY tr.created DESC LIMIT 50 OFFSET 0 in /usr/local/share/drupal-4.6.3/includes/database.pgsql.inc on line 62.

pg_query(): Query failed: ERROR: syntax error at or near "WHERE" at character 81 in /usr/local/share/drupal-4.6.3/includes/database.pgsql.inc on line 45.

I think you mean "ON s.id = tr.tid", not "WHERE s.id = tr.tid".

The latter entry is definitely from trackback.module (line 674), and I *think* the first one is originating from there as well, but I suspect it's a rewrite, as I can't find it in that form anywhere.

CommentFileSizeAuthor
#12 contrib-cvs.trackback.junyor.patch894 bytesjunyor

Comments

jeremy’s picture

jeremy commented

The code works with MySQL, but if I remember correctly you are using PostgreSQL. Does your suggested change solve the problem for you?

Zed Pobre’s picture

Zed Pobre commented

I'll need a bit more time to test it. I've taken down the iptables rules blocking spammers to give spam.module a better source of inputs now that I've applied the trackback.module patch, but it may be a bit before the next surge hits.

I'll check back in once I have more log info.

Zed Pobre’s picture

Zed Pobre commented

Whee! A very short time later, and I have filled several pages of logs with trackback spam. None of said pages contains the above error, however, so I think that might have been the only location that needed patching.

junyor’s picture

junyor commented

The column name *is* called trid, though.

Zed Pobre’s picture

Zed Pobre commented

Um, right, sorry. I meant "ON s.id = tr.trid", not "WHERE s.id = tr.trid".

junyor’s picture

junyor commented

Ahh, yes. I completely missed that you changed WHERE to ON. So, the bad query is:

"SELECT tr.*, s.probability FROM {spam_tracker} s INNER JOIN {trackback_received} tr WHERE s.id = tr.trid AND s.source = 'trackback' AND s.probability >= 80"

and the correct query is:

"SELECT tr.*, s.probability FROM {spam_tracker} s INNER JOIN {trackback_received} tr ON s.id = tr.trid WHERE s.source = 'trackback' AND s.probability >= 80"

The WHERE should be ON and the first AND should be WHERE.

Zed Pobre’s picture

Zed Pobre commented

Huh. Interesting... I missed changing the first AND to a WHERE, but it seems to have worked without error anyway with just the first change from WHERE to ON. I've just made the second WHERE change as well, and I'll post again if any other errors pop up, but that line looks correct, yes.

Zed Pobre’s picture

Zed Pobre commented

Okay, the second WHERE is WRONG. It causes massive problems with spam.module.

Incorrect (line 674):

SELECT tr.*, s.probability FROM {spam_tracker} s INNER JOIN {trackback_received} tr WHERE s.id = tr.trid AND s.source = 'trackback' AND s.probability >= 80

Correct:

SELECT tr.*, s.probability FROM {spam_tracker} s INNER JOIN {trackback_received} tr ON s.id = tr.trid AND s.source = 'trackback' AND s.probability >= 80

(Go figure. That looks wrong to me, but it's what seems to work.)

Zed Pobre’s picture

Zed Pobre commented

Okay, this is weird. Typing in the two different 'fixed' queries by hand generates exactly the same result each time. I think I may simply have triggered a spam.module bug while testing this. I'm putting the WHERE back in and will post back shortly.

Zed Pobre’s picture

Zed Pobre commented

The correct SQL query is in fact:

SELECT tr.*, s.probability FROM {spam_tracker} s INNER JOIN {trackback_received} tr ON s.id = tr.trid WHERE s.source = 'trackback' AND s.probability >= 80

My problem with that stemmed from a bug in spam.module, that I have now fixed.

Zed Pobre’s picture

Zed Pobre commented
Status: Active » Needs review

(Setting status)

junyor’s picture

junyor commented
Status: Needs review » Reviewed & tested by the community
StatusFileSize
new contrib-cvs.trackback.junyor.patch894 bytes

Here's a patch for HEAD. It should apply cleanly to 4.6, too.

ankur’s picture

ankur commented
Status: Reviewed & tested by the community » Fixed

Committed to HEAD, DRUPAL-4-6.

Thanks guys.

-Ankur

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)