A couple weeks ago I looked at my blog and discovered roughly 7000 trackbacks. Given that I hadn't written anything in a month or more, and that I was pretty sure not *that* many people were linking to my Continuous Integration post, it was clear the spammers had me.

I had to turn off trackbacks for a while because I was getting new spam faster than I could delete them. So I turned it off and left it alone until tonight, when I decided I would stop neglecting my poor blog for the billionth time.

Anyway, SubText is pimp and all, but dumping a mass amount of trackbacks that wasn't marked as spam is pain in the admin console, so I took the 8 hours required to load up my host's shared SQL server and jumped in my database to see if I could take care of this all at once.

The first query you are going to need is:

SELECT * FROM subtext_feedback WHERE feedbacktype = 2

this gets you trackbacks only. (FYI comments are feedbacktype=1)  Now looking through you will probably notice a good bit of the spam is only linked to a couple of entryid values, all 7k of mine were linked to only 3 separate entries, so that made the job of deleting them en masse easy.  Your mileage may vary.

Next step is to update your Pingback/Trackback count appropriately so it displays right on your blog page. Mine after the deletes was a paltry 7 (which is lame I know, but not every link back ends up as a trackback. I really think trackbacks fail as a technology). So your next stop is:

UPDATE subtext_config SET pingtrackcount = $whatever_it_should_be$

And then you're all set.  Spam gone.