Our blog

Our Blog

Usually coherent musings of pertinance.

 

Spam sucks

Spam sucks

I don't blog that much. I have always had these great ambitions to. I read Steven King's "On Writing" where he states that if you want to write you have to write. Daily. Without fail. This works about as well as my daily exercise routine or my daily meditation time. Boy do I ever have great intentions.

So imagine my surprise when I visit my personal site and find out that each post has 1,000s of spam comments. There was actually some legitimate ones too, but they were a bit hard to find. To make matters worse, I couldn't log in to the site as an administrator. All I could get was the "White Screen of Death" or WoD. This usually happens with Drupal when the database is overloaded or there are php memory errors, both of which is possible in this scenario.

Now, I sit there and ponder. I can't get in to delete comments or look at the log files. I could say that I racked my brain searching for an answer, but what that means in this day and age is that I whipped out my boolean logic sword and headed to Google.

The post I read suggested that I add some code to the index.php file to echo error messages to the page. This was something I could do. I added the following code directly after the opening php tag:

<?php

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

// $Id: index.php,v 1.94 2007/12/26...

Then, on page load it told me that the watchdog table was full. Now some actionable information. Next stop, phpmyadmin...

Now I know I could do this command-line, but then I would have to do more googling and that just makes me feel dumb.

Actually, I found out later I could have used drush to delete the watchdog messages very easy with the command "watchdog-delete".

I logged into phpmyadmin and found out that the drupal database was 1.2Gb. This is for a drupal site with 3 blog posts and an out-of-date portfolio section. Now, in retrospect, I could have done some real damage here. But I happenned to luck into the right way of clearing out the cache tables and comments. I first ordered all tables by size and "emptied" the really large cache ones, watchdog and all the comment tables. Make sure you don't "Drop" the tables as you would then have to recreate them with the right field structure.

I know that emptying comments is a bit drastic, as I also got rid of the legitimate ones, but I had no intention of selecting all the bad ones in groups of 30 from within Drupal admin. Again, this is war.

It worked! I now have a fast, clean site that no longer has any database or memory errors. All comments have been wiped and I am back in business. Next post, I will explore the various options that are available to combat spam with user registrations and comments.