2.0.12: spam_cron: deleting trackback gets stuck

Found the source of the problem: I apparently have invalid entries in the spam_tracker table. There are 6 entries where id = 0 (all where source = 'list' and probability = 1 and all have the same hash). I've dropped those entries and will be checking back to see if the problem goes away.

This means that spam.module needs to have a sanity check in spam_cron() to make sure that results from the spam_tracker query actually exist in their respective tables, and if not, to clear the spam_tracker entry without going on to attempt to delete a nonexistent comment/trackback.

To be complete, I also cleared the line with id = 321.

A few cron passes have gone by now, and the errors have disappeared. Those entries appear to have been the problem.

Well, I'm not sure how the entries got in there originally (though I do have a vague memory of having tampered with spam.module and lists some time ago), but they definitely weren't being removed. Those lines I posted at the start appeared in the logs at every single cron pass. I suspect that the trackback discrepancy may have come from my having deleted a trackback_received entry by hand (via psql) at one point, though I couldn't tell you for sure. Possibly it was a trackback that I deleted from inside Drupal during a time when I had spam.module disabled.

Eh, well, now that I've dumped the corrupt entries, I'm pretty sure it *won't* happen again. I still think a sanity check on the spam_tracker table is a good idea, but it's obviously not a high priority. Changing priority to minor with this.

I'm pretty sure I know how this is happening now. It happens when someone (so far, always a spammer) sends a trackback with a site, name, subject, url, or excerpt longer than the database constraints allow. The spam_tracker insert goes through fine, but then the trackback_received insert fails, leaving a spam_tracker entry pointing to a nonexistent trackback_received entry.

I *suspect* that this is a Postgresql-specific problem, and that MySQL is silently truncating the data to fit in the field. I haven't tested it with a MySQL system to be sure.

I created a view to track the problem more precisely with:

CREATE VIEW trackback_check (sid,id,trid,prob,name,subject) AS
   SELECT spam_tracker.sid, spam_tracker.id, trid, probability AS prob, name, subject
   FROM spam_tracker
   LEFT JOIN trackback_received ON spam_tracker.id = trackback_received.trid
  WHERE spam_tracker.source::text = 'trackback'::text;

In MySQL, you'll probably have to perform the select fresh each time, adding a search for where trid is null, as I don't think it supports views. In Postgres, that shows the pattern of holes quite nicely. I'm up to over 10,000 holes now, not counting the ones I've manually deleted, just from the last four or so months.

I still think that a check at the delete stage for this problem (so that spam_tracker entries not associated with trackback_received entries can simply be automatically deleted) would be a good sanity check to have, because this is immensely hard and time consuming to fix with PostgreSQL 7.x. (8.x added a proprietary extension USING to the DELETE syntax which I could use combined with a view to take care of it periodically, but alas, I cannot upgrade at this time.)