Les Jones

Blogging > "Mister, here's your problem!" Blog hiccup revealed

Since the site was having trouble with comments hanging. I decided to upgrade the blogging software to the new version, which is reputed to be much quicker.

Installion of the new version kept quitting during the import of the old comments. Then I found a mention in the installation notes that large numbers of comments (usually spam comments) could cause the upgrade to fail.

So I checked and holy frijoles! I had 271,833 spam comments and 47,586 spam trackbacks. The built-in spam filter kept them from appearing on the blog, but they were still in the SQL database. Gory details of the cleanup after the jump.

Even after the cleanup the blog is having problems so I'm going ahead with the ugprade.

Long story short I used the instructions here to clear out the exorcise the demon spam comments and trackbacks from the holy blog. The relevant SQL commands are (COUNT to check for the number, DELETE to actually delete them)):

SELECT COUNT(*) `mt_comment` WHERE comment_junk_status = '-1'
DELETE FROM `mt_comment` WHERE comment_junk_status = '-1'

and

SELECT COUNT(*) FROM `mt_tbping` WHERE `tbping_junk_status` = '-1'
DELETE FROM `mt_tbping` WHERE `tbping_junk_status` = '-1'

I had to optimize to regain space the tables to regain the space.

I also deleted the log by clicking Activity Log from the right hand menu in the MT admin interface and clearing the entries.