Like many people, I use the Jetpack plugin to have a contact form on one of my sites. It’s  a nice thing to have there, even though nobody ever uses it. Well, at least I thought nobody was using it – I just ran a backup of the database and though it was taking rather longer than I expected. It turns out that I have actually been getting rather a lot of feedback, but all of it spam: tens of thousands of spam messages.

Now, the fact that the spam filters hid me from this is great – that’s exactly what they’re there for. But now they’ve built up and are taking up a large chunk of space in my database, I want rid of them. So, I set to. It turns out that’s not so straightforward.

Apparently, you can’t simply delete all your spam messages, you have to go through them a page at a time and move them into your trash. Once they’re in the trash, you can just click on the ‘Empty Trash’ button. Getting them into the trash however, was proving a little tedious.

The default number of items per page is 20. Trashing 20 at a time was going to prove slow progress. So, I upped the number per page to 500, still a lot of pages to get through, but should be faster. No such luck. Deleting 500 at a time results in a “URL too long error”. Good grief, have these people never heard of POST?

So, 100 items per page. Yes, that works, but boy is it tedious. After three pages I gave up. Can’t I just delete them from the database?

Yes, they’re all in the wp_posts table, and easy to identify. Except there’s a catch. They also have corresponding entries in the wp_postmeta table. That’s a bit more complicated to deal with. But then I realised, if I can just change them from ‘spam’ to ‘trash’ then I can get rid of them in one fell swoop with the ‘Empty Trash’ button. That will also then clean up all those loose ends in the wp_postmeta table.

So, one simple SQL statement is all I needed:

update wp_posts set post_status = 'trash' where
  post_status = 'spam' and post_type = 'feedback'

That finds all feedback items marked as spam, and moves them into the trash. Then, back over to the Feedback admin page and click on ‘Empty Trash’ and it’s all gone.

It really should have been easier than that though.

[Important: if you try this for yourself, do take a backup of your database first. Things always go wrong when you’re least prepared for them.]