Frequent words I heard as a kid was reminding me of my household chore– “TAKE OUT THE TRASH!!!”
I’m finally getting around to doing it regularly.
Even with Akismet running on this blog, I have seen it accumulate more than 1000 trapped spams per day, and by the time the count hit 10k, the WordPress interface hung on how long it too to empty the spam odored trash. The only way to clean out was to jump into the database via phpMyAdmin, and manually run an SQL command.
I finally decided to set up a cron script to do this, and here is how I did it via my Reclaim Hosting cpanel (I await some technowiz to tell me how bad my approach is). I am essentially doing a command line database query to empty the comments marked as spam (the ones that Akismet IDs as spam).
To do this you will need info that is stored in your wp-config.php file on your server- they will look something like:
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', 'mygroovydatabase'); /** MySQL database username */ define('DB_USER', 'trickyusername'); /** MySQL database password */ define('DB_PASSWORD', 'X4somehiglhycryptic8password');
- the name of your wordpress database (below referred to as DB_NAME)
- the user name that logs into your wordpress database (DB_USER)
- a password for that database user (DB_PASSWORD)
The thing you are looking for in your cpanel is Cron Jobs down in the Advanced Section
When you add a new cron job, you need to choose how often it will run. Mine is every day at 4am; but a more reasonanle setting might be as shown below, every other day at 3am
The command is the big deal here. Mine looks like this, you will have to fill in your own values (no quotes) for DB_NAME, DB_NAME, and DB_PASSWORD (and make it all one one line, I broke mine up so you can see it)
mysql -u DB_USER -pDB_PASSWORD --database=DB_NAME -Bse "DELETE FROM wp_comments WHERE comment_approved = 'spam'"
Note that there is no space after “-p” and your database password, so for my fake example above, it would look like
mysql -u trickyusername -pX4somehiglhycryptic8password --database=mygroovydatabase -Bse "DELETE FROM wp_comments WHERE comment_approved = 'spam'"
So far, I am finding it is keeping the number of flagged comments to a more reasonable number.
I did get more powerful solution from Brad Emerson that essentially blacklists IPs at the server level, and am hoping to sort that out eventually.
For now, I just wanted something to keep the database from bloating from all the trash that piles up.
I do not fault anyone else for making the choice, but I refuse to turn off comments. Call me a masochist. And sometimes… sometimes.. it is mildly entertaining to see what floats through. It’s kind of like occasionally lifting your floor to scan the sludge for curiosity of how life can exist down there,
The post "Automated WordPress Blog Comment Spam Emptying" was originally squeezed out of the bottom of an old rusted tube of toothpaste at CogDogBlog (https://cogdogblog.com/2014/10/take-out-the-trash/) on October 6, 2014.