SQL Connection Errors

[Bobber]

I still seem to be having a slight problem with SQL connection errors, whereby the site for some reason reaches it's maximum number of connections allowed. This has something to do I think with code not releasing resources properly. I'm looking into this, but don't really know where to look in thousands of lines of code and hundreds of files that make up this site.

Please be patient when this happens. Hopefully I can find the cause soon.

[Bobber]

OK, so I think I know what might be causing the problem, however I don't know yet where it is. In order to determine this, I'm going to temporarily "disable" the font end of the site that I use, and just go with a plain phpBB3 message board. This will give me time to go through the code of the front end, and also determine if it's the font end causing the problem.

If you still get SQL Connection errors after using the plain site without the front end, please let me know about it by either sending me an e-mail to bobber@fish-hawk.net, or responding here once you are able to get back in.

Thanks.

[Bobber]

OK, so I've gone through several thousand lines of code to try and find the place where the connections are not being cleared and I did manage to find a few places that looked like there was an issue. I've correct them (I think), and have put back the front end to see if there are any more problems.

I did not get any problems (knock on wood) with just the plain vanilla board, so we'll see if it continues with these new modifications.

Cheers,

[toobinator]

I have no idea what you just said. Is there a head scratching icon?

[Bobber]

I have no idea what you just said. Is there a head scratching icon?

Well funny you should ask....there are a few of them.

[cprince]

Just got the SQL error when clicking on the FH logo to go to the /index.php page from my PM box.

Craig

[Bobber]

OK, thanks Craig. I've gotten about 2 of them in the last 48 hours....which is far less than I was getting. I may have gotten most of the open connections, but there may be a couple lingering....I'll keep at it.

Thanks for letting me know.

[Bobber]

So it still seems that we're getting some SQL errors. This is not an easy task to find out where.....

Are there any "techies" out there...who may know how to create a script that will show you the "open database" connections, on a designated database, along with "what files" initiated the open? I would need to execute this from the client side (ie...www.fish-hawk.net/showopendbs.php) which would talk to my database once I configure it into the file.

No worries if there's not....just thought I"d take a shot at it. I've been unsuccessful thus far in finding a solution that would show me this. If I do get something like this, then that will pinpoint where the problems may be.

I also have calls into the programmer of both Primetime font end and phpBB3....so far, no response though.

cheers,

[JasonT]

I was getting SQL errors last week but today its working ok.

The problem is not the script, it is the database setting in the server config.

(on my server) In the /etc/my.cnf, you can set the value like this:

max_connections=500
max_user_connections=12
Max connections is the maximum connections that can be made to the Mysql server at one time and max_user connection is the maximum a single user can make to the Mysql server.

[cprince]

New one for you:
Parse error: syntax error, unexpected T_VARIABLE in /hermes/waloraweb079/b427/pow.mlilly/htdocs/includes/db/mysqli.php on line 98

Just caught that when clicking from this thread into the forum.

Craig

[Bobber]

New one for you:
Parse error: syntax error, unexpected T_VARIABLE in /hermes/waloraweb079/b427/pow.mlilly/htdocs/includes/db/mysqli.php on line 98

Just caught that when clicking from this thread into the forum.

Craig

Yeah, that's fixed....I was playing trying to find a way to identify where the open connections are. oops, my bad.

[Bobber]

OK, a little update for you all......

I think we managed to find the culprit(s) that are causing all of the connections to be used up.....I've been working with my host provider Dan, who's been very helpful, finally...in figuring out what the problem may be....and have taken steps to rectify it.....

It's interesting that you're getting both errors now, and not just the max_connections error. The max_questions error is regarding the number of queries hitting your database in a given hour - Our limit is 75,000 and it looks like you're hitting it. Looking at your site traffic this month, I'm not sure I agree that your board isn't *that* busy. I'm seeing that you have over half a million page loads and just under a million hits total. The month isn't even half over and considering your board was down for some time, that traffic is nothing to scoff at! Those numbers could easily translate to 75,000 queries in one hour during peak traffic and depending on how long some queries can take, I could see you hitting the 5 concurrent connection limit as well.

While an abundance of traffic is typically a good thing for a growing site, after looking at your stats a little more closely, it looks like a large percentage of your traffic can be traced back to a couple of Chinese-based IP addresses. At the risk of sounding like I'm making assumptions, the amount of traffic coming from just those two IPs indicates an automated bot scanning your site for something (what, I don't know).

At this point, I'd recommend taking a look at the IPs hitting your site the most and then performing a WHOIS search on them to see if you can determine their legitimacy. Once you see who owns the IP you should be able to draw some conclusions based on that information. At that point, you'll want to decide whether to block the IP or not, which can be done via the htaccess Editor in your vDeck control panel.

I did look at the IP Addresses that have been visiting, and have blocked a large number of "known" SPAM IP Addresses, from China, Ukrane, Luxembourg, and other areas that I don't believe have any vested interest in this site. Hopefully this will fix the problem and we won't see this error any longer.

If I've made a mistake and for some reason a regular user cannot access the site....sorry about that....but someone will have to let me know and I'll fix that right away.

Fingers crossed.....

Regards,

[unmanaged]

I got some more this morning, we are not out of the woods yet...

[Bobber]

OK, thanks. I had another look at the IP Addresses hitting the site and banned a few more from overseas. This is turning into a war. There "has" to be some way to block entire countries from poking around like they do and causing all these hits. Hopefully this will make it better. At this rate, I'm going to have to make this check a regular thing.

Thanks for letting me know.
Cheers,

[unmanaged]

Pretty sure you can block BOTS...

http://www.thesitewizard.com/apache/blo ... cess.shtml