MT 3.2 issue: junk comment purging

h4. Summary of the issue:

“Movable Type 3.2”:http://www.movabletype.org has a bit of a problem. Not quite sure I’d call it a bug, rather a feature that can cause a problem. You may not be able to log in to your site at http://www.domain.com/path/to/mt/mt.cgi?__mode=menu&blog_id=1 (the blog’s main window) without getting an “500 Internal Server Error”

The cause? Runaway junk comments. MT 3.2 includes Spam Lookup which is a fantastic plug-in for dealing with junk comments and trackbacks. Set it and practically forget about it. That’s the problem. You can easily set junk comments to delete after a set amount of days by going to settings -> Feedback and select how long to hold junk:

https://i1.wp.com/www.momathome.com/viewfromhome/images/2005/11/junk-filter-thumb.gif

However, apparently MT 3.2 runs the purge *only* when you load the main blog page. If you only post entries through a 3rd party client and never log in, junk accumulates. And accumulates. And accumulates. I discovered this morning that “Eric’s”:http://www.9to5andotherwise.com blog had nearly 25,000 junk comments sitting there since June even though I thought it was clearing out every 30 days. The main blog page was timing out before it could complete the purge and hence the “internal server error”.

Lesson: If you use a 3rd party client to post to your blog, load the MT interface in your browser every once in a while. Maybe “Sixapart”:http://www.movabletype.org should have a way of purging the comments manually or remotely.

Update: “This is a known issue.”:http://www.sixapart.com/movabletype/docs/3.2/i_known_issues/#entry-5871 but you have to know where to look for known issues to know about it. Now you know. 🙂

In the meantime, I got the junk comments out with a little help from a “friend”:http://www.nslog.com. if you want to know how to fiddle in your MySQL database to manually purge the comments, read on…

So this morning “Eric”:http://www.9to5andotherwise.com mentions to me that he’s surprised to see how low his traffic is in “Opentracker”:http://www.opentracker.net despite the fact that some folks are mentioning that they read his blog. I suggest that folks may be subscribing to his feed which Opentracker won’t track. I offer to route his feed through “Feedburner”:http://www.feedburner.com so he can get a better picture of his site’s traffic.

I logged in to his site, and realized that I never got around to upgrading him to the release version of 3.2. He’s been running 3.2b4 all this time. Ooops. So I upgrade the blog as I’ve done many times before. Went well. No problem. Log in and got the dreaded “Internal Server Error” on the blog’s main page. I could get to every other page (entries, comments, settings, etc.) but just not the main page. I thought it had something to do with the upgrade, so I reinstalled MT. No difference. I sent in a support ticket to Sixapart. In the process of trying to troubleshoot I noticed that his database was 48MB which seemed awfully high for only 582 entries and 80 some odd comments.

A little later, I was chatting with “Erik”:http://www.nslog.com and mentioned the issue. He knows so much more about MySQL issues than I do.

He walked me through manually cleaning up the database in phpMyAdmin:

# Log in to phpMyAdmin
# This is a good time to back up your database if you haven’t done so recently. If you skip this step and something goes wrong, please don’t come yellling at me. Sixapart has an easy-to-follow tutorial on doing this “here.”:http://www.sixapart.com/movabletype/docs/3.2/01_installation_and_upgrade/mysql_backup_restore.html
# Click the mt_comment table on the left hand side
# Click the “SQL” tab along the top of the right hand side of the page. Should look something like this:
https://i0.wp.com/www.momathome.com/viewfromhome/images/2005/11/mysql1-thumb.gif
# Highlight over SELECT * FROM `mt_comment` WHERE 1 and change it to SELECT * FROM `mt_comment` WHERE comment_junk_status = '-1' and hit “Go”
# You should see all the junk comments listed. On Eric’s blog, there were 24,873 results! If that’s the case, (you’re running the “select” step only to confirm that you’ll be junking comments correctly) then go back to the SQL tab and change the line to DELETE FROM `mt_comment` WHERE comment_junk_status = '-1' hit “Go” and it’s done!
# Log back in to Movable Type and you should be able to easily load the main page again.

Before purge: 48 MB database. After purge (and optimizing the database): 10.1 MB

Oh, if you want to try out Feedburner but you don’t want to change your templates, simply copy all the code from your current RSS 2.0 template (index.xml) and create a new index template. Name it something descriptive (I called it “RSS Feedburner”), going to a file with a unique name ending in .xml (I called it “index-fb.xml”) and paste in the code from the index.xml file. So you’re duplicating your RSS feed. Set up Feedburner to burn that duplicated feed. Then you can simply edit (or create) your .htaccess file to redirect any requests for the old feed to the duplicated one. If you decide you don’t like going through Feedburner, just remove the redirect. Once you’re sure you like Feedburner, you can edit your templates so the only publicized feed is the Feedburner one (as I did here at “A View from Home”).

Advertisements

3 thoughts on “MT 3.2 issue: junk comment purging

  1. Thanks for the tip, saved me from having to create my own SQL string. I’d recommend running it from a shell rather than from phpmyadmin if possible (would hate to see it timeout and cause problems). Still works with the latest version of MT.

    Shell commands:

    mysql -u youruser -p

    use mt_yourdb;

    SELECT * FROM `mt_comment` WHERE comment_junk_status = ‘-1’ LIMIT 0 , 30;

    DELETE FROM `mt_comment` WHERE comment_junk_status = ‘-1’;

Comments are closed.