Free eBook by John Chow - How I Went From Zero to Over $100,000 a Month
 

Misbehaving Comment Relish Plugin

written by John Chow on June 15, 2007

Secure Affiliate Marketing

Today, I left a comment on Nate Whitehill’s blog for his post about what a header image says about you. A few minute after the comment I get this email from Nate.

Hi John Chow,

Thanks for taking the time to visit my blog. I hope you found some good information about business, blog, and web development If you did, please consider subscribing to my RSS feed (http://feeds.feedburner.com/NateWhitehill).

Let me know if you have any questions. I look forward to seeing you around the blogosphere. Take care!

Nate Whitehill

http://natewhitehill.com

My first impression upon receiving the email was, “OK, this is a plugin.” I emailed Nate back and asked him about it. He replied back saying the email was from a WordPress plugin call Comment Relish.

Comment relish is a WordPress plugin developed to send an e-mail message to users who comment on your website who have never commented before. The message dispatched to the user is defined within the plugin’s preferences. Numerous tags have been integrated to allow for information to be included in the message easily (I.E.: timestamp, author name, comment, ETC.).

Comment Relish sounds like a great plugin. However, there is one major flaw in it. The plugin goes through your comment database to find out if the reader have ever made a comment on your blog. If not, then it send the thank you email. This isn’t a problem when you only have a 100 comments or so. However, if your blog has 42,000 comments (like this one does) the plugin will consume all the server’s resources trying to find out if you have commented before. The instant I turned on the plugin, it crashed the MySQL database and maxed out the server load to 100! This was why this blog was unavailable for the last hour. We thought we were under a MySQL DOS attack.

Sending a thank you email to first time commentator is a great idea and I would have loved to use Comment Relish to do that. However, if your blog has a lot of comments, I don’t recommend using it unless you want to experience a big time server crash.

Did you enjoy this post? Get John Chow Dot Com updates via email...

Stay up to date with all of John Chow’s tips for making money online and blog posts by subscribing via email. Your email will be kept private and never shared with anyone.

{ 39 comments }

BlogsAndBucks June 15, 2007 at 5:14 pm

O ya, i was like what is going on with JohnChow.com? Look i even took a screenshot. Good to have to back.

http://blogsandbucks.com/images/johnchow_server_down.gif

blogcrowds June 15, 2007 at 5:34 pm

Quick work! I was curious about this as well, thought the blog was hacked with good old Denial of Service. Well, it kind of was…

There should be better way to implement the widget. ie instead of searching through everything, keep track of who the email has been sent to in the MySQL database and search through that. This MySQL table should be much smaller.

Mr. P | CASH for COMMENTS June 19, 2007 at 10:15 am

Wow. You guys are quick. It’s nice to know that JC has legions of followers to support him.

cashforcomments.com
CASH for COMMENTS | get paid to comment.

Aaron Cook dot Com™ June 15, 2007 at 6:40 pm

Yep, I took one too, just in case:

http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

I’m really glad it wasn’t a MySQL DOS attack from some rogue asshole…as I’m sure John is as well.

Alex June 15, 2007 at 8:19 pm

If John doesnt update to WP 2.2.1, he can be vulnerable of a DOS attack…

Aaron Cook dot Com™ June 15, 2007 at 8:47 pm

BTW, I had a real nasty shit-for-brains leave an antiJohnChow (and pro-hacking) comment on my blog this evening…
http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

But I put it to him/her pretty good! :twisted:

It’s a shame when people think that hacking into someone’s blogs is OK. I’m just glad John wasn’t actually hacked this time.

Walter Vos June 16, 2007 at 12:25 am

That’s so cool, the content links even work on error messages! :smile:

Steven June 15, 2007 at 5:15 pm

Welcome back online John (I didn’t notice you were offline at all ^_^)

Isn’t there a way to set the settings of comment relish to only scan say a month’s worth of comments or a week’s worth rather than your entire database?

Alex June 15, 2007 at 5:18 pm

Wtf, John is better not use all plugins than ppl recommend, a lot of plugins are dangerous….

Nathan Hutchinson June 15, 2007 at 5:20 pm

I wondered why this site was down. I had never seen it down before. :cool: Nice plugin, I might try it on my site!

A Tentative Personal Finance Blog June 15, 2007 at 5:20 pm

I was wondering what happened to this place. I thought you had finally packed up and went to that island in the middle of nowhere.

Ed June 15, 2007 at 5:22 pm

I haven’t looked at the plugin in depth, but my first thought is this could have been designed much better. Why is it searching comments and not a list of users? I’m assuming there’s a user table in Wordpress (been awhile since I looked) that you could just add a boolean column to in order to keep track of who has been sent an email. Should only require tweaking the SQL query the plugin uses in two spots, checking and setting the flag.

Robert June 15, 2007 at 5:26 pm

Comment Relish is primitive at best. It doesn’t support HTML tags so links don’t work. Also, there is no easy way to send yourself a test email.

I tried it a week ago and deleted it after 5 minutes.

Kumiko June 15, 2007 at 6:59 pm

All you need is another email address to send a test.

Ted June 15, 2007 at 5:30 pm

A smarter programmer than me would refine this plug in so it has a separate table for commenter’s email addresses. It should reduce the server load massively.

Chris Hope June 15, 2007 at 7:43 pm

Also never minding that 42k records isn’t really that many… you need to make sure the email address field is indexed.

Aaron Cook dot Com™ June 15, 2007 at 5:36 pm

Darn, then my MySQL DOS attack wasn’t really what caused it then? Just kidding. :lol:

Ah, so then that would explain it…

Where Did John Chow Go?
http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

The plugin is a great idea, but it obviously needs to be reworked so that is doesn’t use up so many resources. Crashing a server is utterly ridiculous.

Sammy Larbi June 15, 2007 at 5:51 pm

That’s strange. 42,000 records is not many by any stretch of the imagination for a DBMS, even if you have no indexes on the column it was searching through.

Good idea though.

Casey June 15, 2007 at 6:01 pm

As I was reading this post, I was about to go ahead and stop to download it, until I read it’s a pain for big blogs and can crash your mySQL.

Browie June 15, 2007 at 6:25 pm

Thanks for the information. I wasn’t worried about your website but I just installed that plugin. I think i’ll be ok since I have about 1,000x less comments than you do. :roll:

Wallace June 15, 2007 at 6:37 pm

nice plugin to change your visitors to the rss subscribe readers.

Steven Smethurst June 15, 2007 at 6:57 pm

Good idea,
But instead of looking thou the all your comments it should set up anther table of people that it has already sent emails too…
Searching that table would be alot quicker.

Drew June 15, 2007 at 7:10 pm

Sounds like a good concept for a plug-in. Despite the risk I may give it a try. Shame that the site went down for a bit though.

Thanks for the advice and the tip!

Eugene June 15, 2007 at 7:30 pm

Awesome! Never thought this plugin existed. Downloaded and installed it. Thanks John and Nate.

Richard Cai June 15, 2007 at 7:47 pm

The speed is still very slow.

webd360 June 15, 2007 at 8:12 pm

It is a nice plugin, Steven Smethurst’s idea seems like it would make it more efficient too so that blogs like yours can use it. When your blog was down I thought your server crashed or something…

Mason June 15, 2007 at 8:45 pm

i believe bob buskirk has the same plugin. when I replied to his blog I got an email as well. too bad his spam plugin banned me forever. :)

MillionDollarJourney.com June 15, 2007 at 9:04 pm

Thanks for the warning John, I was just about to download the plugin!

FT

Stephen Welton June 15, 2007 at 11:30 pm

On that note I think that I will turn it off even before I turn it on. I just recently added it to the blog but I certainly do not want to have a crash in the near future.

Thanks for sharing John

dotnetnuke June 16, 2007 at 12:30 am

I experienced the timeout error last night, welcome back again. :roll:

shaun June 16, 2007 at 12:33 am

Nice plugin John I will use this on my site. Im sure other bloggers will use this plugin as well :grin:

Wahlau.NET June 16, 2007 at 3:01 am

it is a pretty good idea to have this plugin, not sure if it improve a blog

Joy Anderson June 16, 2007 at 10:27 am

I think replying to the poster personally, is a much better idea. I guess however, if you have over 42,000 comments it can be a lot of work! I would still go for the personal touch, but that’s just me. Hope you have a great Father’s Day!

andre nosalsky June 16, 2007 at 10:10 pm

SOLUTION: Here’s how I think this can be solved. You need to add an INDEX to the email address. Go to your PPHPMyAdmin page and find your MySQL database and enter the following (if your DB is not modified)

ALTER TABLE wp_comments ADD INDEX ( comment_author_email )

Patches and hacks June 18, 2007 at 12:27 am

To make it even better, change the email field in cr_emailed table from TINYTEXT to VARCHAR(255) [ ALTER TABLE `wp_cr_emailed` CHANGE `email` `email` varchar(255) ] and add an index on that column too [ ALTER TABLE wp_cr_emailed ADD INDEX ( email ) ] . that way it will use indexes in both tables when it makes a query to find out where to send the messages and the query will be faster and less resource consuming.

andre nosalsky June 19, 2007 at 11:18 am

Good point, that will help it even more.

Ash Haque June 17, 2007 at 2:15 am

Has anyone else tried the plugin? Say if your site has around 500 or so comments, will it only be a problem the first time it’s activated? Or everytime a comment is made?

Michael June 17, 2007 at 3:16 pm

I considered using the plugin but I just don’t want to bother my readers with emails. Maybe it would help them come back but I just don’t like to bother people.

Patches and hacks June 18, 2007 at 4:00 am

I made some modifications to it and tested on 25000 comments, It takes about one second for it to figure out where to send the messages. You can read more details about comment relish optimisation here