Movable Type comment spam solutions

I wanted to apologise if you've recently had trouble leaving a comment on currybetdotnet, or indeed, even had trouble accessing the site. I've been suffering badly with Movable Type comment spam, and was finding that I had to restart either MySQL or Apache2 a couple of times a day. Obviously that situation was less than ideal, and even more so if you consider that in Greece I can be a bus ride away from an SSH session.

Dave tipped me off to Jeremy Zawodny's suggestion of using mod_security with Apache2 to try and head some of the spam off at the pass. It seems to have worked a treat - I've gone down from hundreds of spam comments a day to maybe getting ten through in a week - and made Andrew smile too.

One of the bits of spam to get through had been patiently done by hand to promote a naturist site - they evidently have some kind of Technorati feed search on the go to target anybody using the word 'nude' in a blog post - and the others were just there because they randomly matched my code word in part of their spammy URL.

I was moved to act because of an eventual catastrophic failure of MySQL, which corrupted the Movable Type comments table. It was only whilst I was poking around trying to repair it that I noticed the comments table had grown to be an order of magnitude greater than the table that actually holds all of the entries. Now, I know that there are over 1,000 published comments on currybetdotnet, but since the majority of them are along the lines of "you're a complete moron.", I reckoned that was an indication there was a problem.

And there was.

My Movable Type install settings suggest it should delete a comment after 24 hours if it has been marked as 'junk' and I haven't 'unjunked' it. That evidently hadn't been happening - as I discovered that I actually still had over 105,000 junk comments lurking in the database. No wonder it kept falling over every time you tried to perform an operation relating to comments.

Anyway, with the Movable Type interface it was going to take an age to clear all that. I could only do it 125 comments at a time, since I'm still using version 3.2, and it was only in 3.3 that Six Apart introduced a 'Clear All Junk Comments' button.

So, one quick crash course in MySQL command line syntax later, I took a deep breath, crossed my fingers, and just did a direct DELETE FROM mt_comment WHERE comment_junk_status = -1 command, and hopefully removed all 105,000 junk comments in one fell swoop.

It seems to have worked and - touch wood - Apache2 and MySQL have been up on the server without a hitch for a couple of weeks now.

In fact, the main implementation problem I had was spelling Alan Turing's name as Turning on the first iteration of the new templates. At least I never criticise anybody else about their spelling...d'oh!