J_K9 @ Linux

An Excess Of SQL Queries And Account Suspension

December 5, 2006 on 4:32 pm | In This site |

The past couple of days have been a ride - no, really. I got back to my room after lessons at 12.30pm on Saturday to do some revision, check my email, game - the kind of things that most people do before exams. I signed into my Gmail account and waited for the little ‘Loading…’ sign to disappear. I had a quick look through the twenty new emails to see if there was anything important and I spotted an email from a reader, Jack. I opened it and the first line I read was:

Hi, I posted a question on your guide but when I try to access your reply it says that my account has been suspended:

http://box39.bluehost.com/suspended.page/ 

Wait a minute… It couldn’t be his account that had been suspended - it had to be mine. 

I quickly went back to my inbox to check if there was an email from Bluehost and indeed there was: my eyes fell on its title in disbelief. BLUEHOST.COM wolphination.com is deactivated. I cautiously opened the email only to have my horrors confirmed:

Message-Id: < [concealed]@www.bluehost.com>
Date: Fri,  1 Dec 2006 19:28:23 -0700 (MST)    

Attention Sir:

Your BlueHost.Com account for wolphination.com was deactivated. Your data may still be temporarily available even though your web site may not be functioning. If you feel this deactivation is in error, please contact support as soon as possible.

Thank you,

Bluehost Support
http://www.bluehost.com/
email: support@bluehost.com
(801) 765-9400
(888) 401-HOST

At this point I was swearing, cursing and beating my head with a hockey stick. My data may only be temporarily available, and this email had arrived in my inbox 10 hours ago?! This was a nightmare - what if all my blog posts were gone, just in a flash? I had backups, but they were a few weeks/months old..

 

Despite the cost of a long distance call from the UK to the US (ouch), I called them. Luckily, I got through to a member of the Tier 1 support team immediately, which I was quite impressed with. Anyway, I told him about the email I had received and I gave him my domain - he looked it up and couldn’t find any records. I gave him my name - he couldn’t find any records. At this point I was tearing my skin off and boiling inside at the same time - what the hell had they done? Before I could say anything (I was too dumbstruck to think of anything vaguely respectful to say) he told me he’d be right back, put me on hold and after a few minutes of waiting the line went dead. Damn. Back to square one.

The lunch bell had rung quite a while before the end of the phone call, so I sprinted downstairs, wolfed down a tuna sandwich and a yoghurt and then sprinted back upstairs to call them again. After several minutes of waiting in the queue, I got through to another member of the support staff. I told him my problem and gave him my domain name. He looked it up and told me exactly what the problem was and the reason for the deactivation of the account, saying that I must fix the problem as soon as possible after the reactivation. It’s odd that this person found the records and the other didn’t, but I asked him to reactivate it and I would do my best to fix the problem immediately. He then reactivated my account and sent me this email:

MySQL queries killing server as not terminating:6781 [concealed]_ localhost [concealed]_p 572 Query SELECT DISTINCT pagename, hits FROM wikilinks, hitcount WHERE (frompage=pagename AND topage=’R Literally hundreds of these are appearing for this user alone. He MUST fix this immediately upon reactivation

Thank you,

bluehost.com

Wiki? I don’t have a wiki.. Could it be a new Wordpress feature which I had missed and which was buggy?

I had to wait a few hours before I could go to the local Starbucks and hop onto an unrestricted network to find out what was causing this. Once I was there, I went through all of Wordpress’ core files looking for that query - there was no sign of it. Hmm… That’s odd. So, I sent the following email to Bluehost’s support team:

Hi,

I’ve taken a look, but I haven’t been able to find the source of all these queries. Is there any chance you could tell me which file is running this query? I might then be able to edit it to resolve this problem once and for all!

Thank you.

To which they replied (long after my Starbucks internet connection had expired, when I was sitting in my room “revising”):

You can find out what file is causing the slow queries by going to CPanel->File-Manager->/tmp/MysQL_Slow_Queries/date-of-mysqlslowquerry.log

Great! The only problem was that I couldn’t access my cPanel from the college network as HTTPS access it restricted to all sites but the few allowed, as is direct access to IP addresses, and both of these restrictions were stopping me from checking the log and fixing the problem. I emailed the network administrator to please unblock the IP address and HTTPS access to my cPanel and they were unblocked by the following afternoon! So much for fixing the problem immediately, eh?

 

With access to my cPanel, I follow the instructions which had been emailed to me and checked the log file - the file executing the queries was not displayed, but there were a lot of odd domain names and comments… Hmm - that was even more strange. There had to be a wiki somewhere on my site.

Sure enough, http://www.wolphination.com/wiki/ led to a wiki which spammers had been exploiting for quite some time, had been eating my CPU cycles and clogging up the hard drive (a single log file accounting for one hour’s worth of SQL logs was 6MB large). I went back to the File Manager, changed into the public_html/ directory and deleted the wiki/ directory.

Done and dusted! At least, that’s the end of the excess SQL query problem… For now

[edit] And many thanks to Jack for letting me know about this!