Although I noted yesterday that my own technical gaffs had erase all of our blog comments going back to September 2004, I did comb through the last database dump from early March 2005 and sifted out the legit comments for Sept 2004 – March 3, 2005, so the loss was the last month and a half.

It was fairly trivial with BBEdit to semi-manually sift out all the spam roach poop. There were a total of 1947 rows in the comment table of the database (for about 6 blogs, mostly inactive) and out of those, I deleted 1662 spammies, easily identified by their repeated patterns, url encrusted comments, and general stench. There were sequences of more than 150 in quick succession to a dormant blog (which is now fenced off).

And the captcha security code on the comment form is working like a dream, perfection, baby.

Share this barking on social media

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon

Uncategorized

The Dog Ate My Comments

CogDog The Blog on April 13, 2005

Yesterday I was trying to clean out a swath of comment spam on a blog we set up for one of our college’s sites, wiping directly from the database, e.g.:

DELETE FROM mt_comments WHERE comment_blog_id=XX AND comment_author like “%poker%”
DELETE FROM mt_comments WHERE comment_blog_id=XX AND comment_text like “%cialis%”
DELETE FROM mt_comments WHERE comment_blog_id=XX AND comment_email like “%mail.ru%”

It’s kind of fun watching them go wooshing down the drain in batches like that.

Apparently one of my commands was a little too aggressive (where I took all out above a certain ID and forgot to restrict it to the one blog), and I’ve munged all comments since September 2004! Oh well, that’s why I have hourly database backups…

Back we go into the depths of the database, armed with a wrench and a grease gun to patch things up….

Update: Gulp. My backup scripts were dying over the last 2 months. Now they are fixed. But alas, my comments going back the last 8 months was flushed. Oh well. Not important, eh? How about adding some new comments?

Share this barking on social media

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon

Comments

blalor says:

April 13, 2005 at 12:04 pm

Nothing like a disaster to show you your disaster recovery scripts are broken. 🙂

I really do hate the captcha (?) you’ve got going on here… At least the numbers are fairly legible…

I use the following script to back up my MySQL databases; I stuck it in /etc/cron.daily/01MySQLBackup on my Fedora box:

#!/bin/bash

BACKUP_DIR=”/var/backup/mysql”

STAMP=$(date +’%Y%m%d’)

if [ ! -e ${BACKUP_DIR} ]; then

mkdir -p ${BACKUP_DIR}

fi

find ${BACKUP_DIR} -ctime +7 -exec rm {} \;

for db in $(mysql -Bse “show databases”); do

mysqldump ${db} | bzip2 –best –stdout > ${BACKUP_DIR}/${db}.${STAMP}.bz2

done

(bet *that* gets mangled…)
Alan Levine says:

April 13, 2005 at 12:26 pm

Yes, I am discovering my technical goofups on a regular, daily basis. I am human and truth be known, pretty much a sloppy programmer.

My backup script was fine- it was those pesky file permissions when I moved the directory where the dumps are stored to a new location. Our backups do a bit more. There are hourly saves of everything in one backup file, and than on a daily basis, all databases are backed up as separate dump. Furthermore, we archive the previous month’s data just in case.

And why exactly do people get so bent our of shape on captchas? I have seen the ones where you have to squint a bit to get ’em right, but when you compare the extra task of mimic typing in numbers to a total denial of 1000s of Porn, Pills, and Casino attacks, it is a small price to pay.

Unless anyonew wants to defend the posting of betiality and incest photo sites to an educational web site??
James Farmer says:

April 13, 2005 at 4:55 pm

Dude… c’mon, come over to WP, you know you want to, c’mon now…

Comments are closed.