CogDogBlog Alan Levine barks about and plays with stuff here

Uncategorized

The Dog Barfed Up Some Comments

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.

Profile Picture for Alan Levine aka CogDog
An early 90s builder of the web and blogging Alan Levine barks at CogDogBlog.com on web storytelling (#ds106 #4life), photography, bending WordPress, and serendipity in the infinite internet river. He thinks it's weird to write about himself in the third person.

Uncategorized

The Dog Ate My Comments

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?

Profile Picture for Alan Levine aka CogDog
An early 90s builder of the web and blogging Alan Levine barks at CogDogBlog.com on web storytelling (#ds106 #4life), photography, bending WordPress, and serendipity in the infinite internet river. He thinks it's weird to write about himself in the third person.

Comments

  1. 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…)

  2. 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??

Comments are closed.