So, here’s the sad tale of woe and lamentations — all because of accursed comment spam — and operator incompetence.

A little more than a week ago, this site was chugging along just fine. I wasn’t posting much, but I did check every once in a while to see if all was well. Then on Monday the 8th, my site had the dreaded “your account has been suspended” boilerplate from the web host.

I scurried around trying to figure why it was suspended. My bills were paid up, and as far as I knew all the scripts were kosher. My host’s support staff only said (initially) that their server was running near capacity and that my site was hogging about 30% of the CPU cycles. Without any prior notification, they just shut me down.

I was pissed, of course. I complained, and they set about tracking down the source of the problem. I also tried everything to clear up the problem. Deleted the directories, reloaded the files, upgraded WordPress to 2.0.6, tried different browsers — all to no avail. Finally, I reluctantly decided to move the blog elsewhere. I was both frustrated and ticked at my hosts, and I was ready to leave them behind.

Fate intervened, however, and for the best, I believe.

The most economical path was to move the blog to www.wordpress.com, where you can host a WP blog for free. They have an import feature to permit bloggers to move their posts and comments to wordpress.com. To import a WP blog, you need to install a plugin that converts your datatables from SQL format to the WordPress WXR format. So, I set up a blog at wordpress.com and set about converting my datatables.

The plugin installed just fine, but my site was so slow that the export process would just abort. I asked the wordpress.com support staff for help. Mark there suggested that I export the tables directly using phpMyAdmin and send them to him to perform support-staff magic on them.

Which I did. That the tables amounted to 41 megabytes made no immediate impression on me, so consumed was I by frustration. Mark replied to my email submission, asking that I not send the entire database because it was too large. Still, my fevered brain (suffering from lack of sleep, no doubt) ignored the telltale evidence. I dutifully sent only the necessary tables to Mark and awaited his happy news of success with anticipation.

Meanwhile, my host had restored my account, so although I had no working sites, I at least had access to my email, the databases and FTP access. It was now Wednesday the 10th, and my site was still dead in the water. I sent Mark (who lives in the UK, as I found out) another message asking about his progress. He replied that the second set of tables I had sent were filled with spam and thousands of tags, and the process was going to take him a long time.

Finally, the clouds parted and the light shone down. My database had grown to an unmanageable size, so huge that my host’s database server could not handle the load and it was preventing the site from displaying within the timeout limit. As Homer says, DOH!

Impatient and somewhat sheepish now, I decided to clean up my own database to see if I could generate the WXR file myself. Since my site was still snail slow and would not complete the export process, I copied the datatables to my own computer, created a dummy WordPress site on a webserver at work, and slowly pared down the data to a reasonable size. My efforts paid off; I was able to successfully import the pared down tables, then export them in WXR format using the dummy WordPress site. Then, I uploaded the WXR file to my new site at wordpress.com with their import feature, thereby recreating this site at a new location. I breathed a sigh of relief — my work of one year was not lost forever.

Check these numbers: I had literally hundreds of comment spams and the Ultimate Tag Warrior plugin had created no less than 600,000 entries in my database! Not the plugins’ fault; they’re good. The fault was entirely mine. I did not configure them correctly.

So I have to eat crow. My hosts did what they had to do. My database was growing like the Blob, and was threatening to overwhelm their server. I was just too dense to realize what the problem was.
So, my penance for falsely accusing someone of incompetence and malfeasance was to straighten out the mess I had made. My immediate reward, after hours of deleting crap from the tables, was to see once again this blog in full operational glory, where it will stay for the foreseeable future.

I have also configured SpamKarma2 to nuke the spam within an hour of its arrival, and deactivated the Ultimate Tag Warrior plugin. So, I should not have any more outages, and coincidentally, I now have a mirror of my site for the last year at wheatdogg.wordpress.com.

But stick around here, if you don’t mind. I like the flexibility offered here. A free blog at wordpress.com is great, but I’d miss my theme and plugins.
——MySQL Developer’s Library (3rd ed.)

January 11th, 2007 | Tags: Technology | Category: General stuff | Subscribe to comments | Leave a comment | Trackback URL