[drupal-devel] [bug] Ignore unpublished comments when determining last_post

Jeremy drupal-devel at drupal.org
Mon Jan 17 04:25:50 UTC 2005

 Project:      Drupal
 Version:      4.5.2
 Component:    tracker.module
 Category:     bug reports
 Priority:     normal
 Assigned to:  Morbus Iff
 Reported by:  Morbus Iff
 Updated by:   Jeremy at kerneltrap.org
 Status:       patch

Agreed, the updated patch seems to be working perfectly for me.  Now
these constant floods of spam don't render my tracker useless.  Thanks!

Jeremy at kerneltrap.org

Previous comments:

January 12, 2005 - 11:02 : Morbus Iff

The automated unpublishing is great for stopping spam from being
displayed, but it doesn't stop spam from corrupting another feature: my
"recent posts" (tracker.module). I'll regularly get bursts of 200
auto-unpublished spams which make the "recent posts" page useless - an
unpublished spam still affects the modification date of a node, which
makes "recent posts" display craploads of updates, even though there
really isn't any. Thus, the feature request would be:

 - treat an unpublished spam as a deleted spam
 - when a spam is automatically unpublished, revert the node
modification date to its previous value.

I'm not entirely sure how easily possible this is, as you'd have to
make sure the date is properly set when someone says "nah, this
unpublished spam was actual ham", and then republishes it.


January 12, 2005 - 14:41 : Jeremy at kerneltrap.org

This is a known and annoying bug.  As it affects me too, I assure you
I'll get to fixing this eventually... 

Any ideas on how to cleanly implement this are welcome.


January 13, 2005 - 16:27 : Morbus Iff

Would it seem to you that this behavior should be part of core? Ignoring
spam.module entirely, if a node is updated at 11:00, and then updated
with a comment at 12:00, the 12:00 is saved in there as the node's
modification time. But if a comment is unpublished, shouldn't the node
also be reverted back to its previous state (being 11:00)? How is the
deletion of data (permanently hiding it) different from merely
temporarily hiding it (ie., unpublishing it)?

Perhaps I should move request to Drupal.


January 13, 2005 - 16:32 : Jeremy at kerneltrap.org

Feel free to move it.  If I come up with a clean solution (at least as
far as spam is concerned), I'll update the issue wherever it is.


January 13, 2005 - 16:39 : Morbus Iff

Was talking about this with killes (who "frankly [doesn't] care too much
about comments"), and I'm now wondering if I'm going nutty with all this
talk about node and comment timestamps. We should investigate to see if
tracker.module looks at /unpublished/ comment timestamps instead of
just published ones. If it doesn't make a distinction, then it would
theoretically be a quickie patch to a SQL statement to only check
against published comment timestamps, not unpublished.


January 13, 2005 - 16:42 : Morbus Iff

With a quick look, it looks like tracker_page() only checks node.status,
and not comment.status. We should be able to put a patch in for
comment.status = 1 (unpublished being 2), and that should solve our
problem, right?


January 13, 2005 - 16:55 : Morbus Iff

Apologies... "AND c.status = 0". Modifying the two queries in
tracker_page() made things, seemingly, work as I desire. Can you


January 13, 2005 - 17:02 : Jeremy at kerneltrap.org

It didn't appear to help with regards to the spam module.  I had a
recent spam flood, and with or without the 'AND c.status = 0' I saw the
same posts, including those with recently unpublished spam comments.


January 13, 2005 - 17:08 : Morbus Iff

Odd - I had one unpublished piece of spam that I tested against (for
4.5.0, not CVS), and modifying tracker.module seemed to work as I
intended.  So, the following bit of SQL still shows you erroneous nodes
with unpublished comments?

SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name,
MAX(GREATEST(n.changed, c.timestamp)) AS last_post FROM node n LEFT
JOIN comments c ON n.nid = c.nid INNER JOIN users u ON n.uid = u.uid
WHERE n.status = 1 AND c.status = 0 AND '1' GROUP BY n.nid, n.title,
n.type, n.changed, n.uid, u.name ORDER BY last_post DESC LIMIT 10;

The above is what is actually run against my database with the proposed
tracker.module change. (This code is currently active on gamegrene.com,
so we can test live if need be. Submit a comment as an anonymous user
with the words "poker" or "texas hold'em", and it should be a)
unpublished automatically via your module, and b) not shown in the


January 14, 2005 - 07:52 : Morbus Iff

For what it's worth, I just woke up to another flood of 150 spams, and
no corruption of my recent posts.


January 14, 2005 - 08:05 : Jeremy at kerneltrap.org

Then it sounds like a patch is in order...  For the tracker module.


January 14, 2005 - 08:59 : Morbus Iff

Have you been able to confirm this though?


January 14, 2005 - 09:46 : Morbus Iff

Attachment: http://drupal.org/files/issues/tracker_0.patch (2.59 KB)

Patch attached for 4.5.0. Haven't tried applying it to CVS, but it needs
to go there too.


January 14, 2005 - 09:56 : killes at www.drop.org

I think it makes sense to only select published comments. I think this
bugfix should go in both 4.5 and cvs. +1


January 14, 2005 - 10:43 : Dries

Committed to DRUPAL-4-5.


January 15, 2005 - 13:55 : grohk

Reopening this issue.  I just upgraded to 4.5.2 and noticed that this
patch did indeed filter out the nodes with unpublished comments, but it
also seems to be causing a new problem in the tracker.  I have observed
that new posts do not show up in the tracker until they have been
commented on.  I rolled back the tracker.module installed on my site to
revision 1.100 and the problem was resolved, but the phantom comments
(unpublished spam) were back.  I am going to do some more
investigations, but I am rather inexperienced in this realm.  Anyone
have an ideas?


January 16, 2005 - 06:13 : Zed Pobre

Attachment: http://drupal.org/files/issues/tracker.diff (2.67 KB)

To fix this, instead of just checking c.status = 0, check (c.status = 0
OR c.status IS NULL).  I've attached a patch to do this.


January 16, 2005 - 10:45 : grohk

So far so good.  This patch seems to have fixed my tracker.  Thanks Zed,
I really appreciate it.

View: http://drupal.org/node/15500
Edit: http://drupal.org/project/comments/add/15500

More information about the drupal-devel mailing list