Too many connections

[pitzerwm]

Recently I have started getting this database error email. It starts at 10:30PM and last about 20-30 minutes after which all is normal.

This is the first of the error:

Database error in vBulletin :

mysql_connect(): Too many connections
/hsphere/local/home/xxxxxxxx/forum.autocareforum.com/includes/class_core.php on line 311

The ip is from China 180.76.5.64

Night before last the host terminated service and this was one of the issues:

->Query summary
Time: 24% (3696 sec) Amount: 20% (31 queries) Rows Examined/Sent: 557 / 502 Avg. Query Exec/Lock Time: 119 / 43 sec.
Used databases: [pitzerw_vb]
Query example: use pitzerw_vb; SELECT * FROM datastore WHERE title IN ('','options','bitfields','attachmentcache','forum cache','usergroupcache','style cache','languagecache','products','pluginlist','cr on','profilefield','loadcache' ,'noticecache','bbcodecache');
|id table rows Extra
|1 datastore 15 Using where

They turned it back on 7 hours later and suggested that it was an optimization issue, so I ran repair and optimization and it said all was fine.

I am running 3.8.4 patch 2

Thanks

[Lynne]

"Too many connections" means the server has maxed out the number of MySQL connections it allows. You can try turning persistent connections off by adding this to your config.php:
$config['MasterServer']['usepconnect'] = 0; But, if you still have the problem you will have to talk to your host about raising that limit. Also, see this Common MySQL Error Messages (https://www.vbulletin.com/docs/html/troubleshoot_mysql_errors)

[pitzerwm]

Thanks, I'll give it a try.

--------------- Added 1325485530 at 1325485530 ---------------

Lynne, That didn't solve it. What's with the "attack" at 10:18 PST each day.

Any other options?

Thanks

--------------- Added 1325530072 at 1325530072 ---------------

Is there a way to put their IP somewhere that would stop them on the "outside"?

[pitzerwm]

This is what I got from my host:

Our administration team is monitoring network 24/7 and we block external addresses if we detect abusive activity however if someone is overloading your site using some script from your side which generates too many SQL queries it can be very hard to detect because that address does not generate too much traffic. We recommend rechecking your scripts to prevent that from your side. Our administration team told us that it is possible that someone is abusing your application if the problem occurs at exact same time every day but we don't have any evidence of that, please check your HTTP connection logs available inside /logs/ folder in your hosting plan. You can add any address to deny list in .htaccess file, here are some examples:
http://www.htaccess-guide.com/deny-visitors-by-ip-address/

I added this to my root directory of the forum:

order allow,deny
deny from 213.186.12.
deny from 180.76.6.
allow from all

Do you think that this is the solution or a possible solution?

Thanks

[Lynne]

I don't know if that will work (and actually doubt it). I am guessing the host has the max_connections set low for you - did you ask them what it is set to?

[pitzerwm]

Update, well, it happened again but the 2 IPs that I put in the .htaccess were not the same, of course, there were about 10 from the normal unwanted places. The host says" that this is from a "script" on my end". Any idea what they are talking about, or a solution.

I asked about the number of connections, no answer yet.

[Lynne]

Of course it's from a script. If you had no pages on your site, then you would never get any errors!

Like I asked before, what do they have your max_connections set to?

You may want to see this - List of questions a user should ask a new host (https://www.vbulletin.com/forum/showthread.php/377753-List-of-questions-a-user-should-ask-a-new-host)

[pitzerwm]

I'm still waiting for their answer on the number of connections. My question is if this "too many connections" issue cause the long Query times, which is what they terminated service over?

Is there some way to automatically turn off my site from 10:18 to 10:40 or would that really help. Would they just adjust their "script".

Thanks

[Lynne]

You may want to see if they have any sort of caching available to you. You could use the vbulletin datastore_cache (set in the config.php file) and see if that helps.

[pitzerwm]

Thanks. Can you give me the code that would go in the config file? When I put something in the config file, do I need to cycle/reboot something or is it read constantly?

Do you think that these "too many connection" events is causing the Query time issues?

Bill

[Lynne]

Too many connections is separate from the query issue.

You can read about the datastore cache here in the manual - The vBulletin Datastore (https://www.vbulletin.com/docs/html/main/datastore)

[pitzerwm]

thanks

--------------- Added 1325748986 at 1325748986 ---------------

Update: IX gives me 50 connections, its a shared server. They suggested Throttle Policy, set 30/15sec, which I did (their 2nd suggestion to deny whole countries.

The attack still occurred. I can live with this, it started a month ago, why I don't know, but I think/hope that it will someday move on, when they accomplish nothing.

If you happen to think of something, please let me know

Thanks for your help.

[pitzerwm]

Update, Tonight at 10:15 I turned off the site, at 10:19 the "too many connections" emails started?

This isn't what I expected, any suggestions?

Thanks

[Lynne]

People are coming to the site even though it is off.

Have you looked at your access_logs to see what pages they are hitting at the time you get the error?

[Boofo]

Sounds like spiders are hitting the site hard.

[Max Taxable]

Get CloudFlare. Set security to medium, then disallow China.

The attacks would then hit CloudFlare and not you, and as a CDN, CF will also speed up your load times..

You can add any country, or any custom IP ranges in your CF settings.

[pitzerwm]

Lynne, The pages are random, just post/threads in the forum

Boofo, any other solution that you feel will work?

Max, I looked at CF and see there is a free one, do you just sign up and you or they apply it to the forum root?

Thanks for your help.

[Max Taxable]

Max, I looked at CF and see there is a free one, do you just sign up and you or they apply it to the forum root?Nope you'll change your nameservers to the ones they assign you and do one file edit to make your visitor IP addresses still come out right.

https://vborg.vbsupport.ru/showthread.php?t=259173

[pitzerwm]

Max, I read all of the info, from the other forum/s and it looks like a solution. Did you have any issues? I see where you change the db in the config file and I tested the FTP change, what is the other change that you need to make that they talk about. Is the DNS the actual db "location"?

Lynne, have you seen any issues with CF? Apparently, they have some working relationship with many host, but I didn't see IX in their list.

Thanks again for every ones help

[Max Taxable]

Max, I read all of the info, from the other forum/s and it looks like a solution. Did you have any issues? I see where you change the db in the config file and I tested the FTP change, what is the other change that you need to make that they talk about. Is the DNS the actual db "location"?All I did was edit the file, then assign my domain the nameservers CF provided. Than in CF you go through all the DNS settings to make sure it picked everything up. On mine, it did no problem.

I have had zero problems with CF and it's been great to have.

You are not editing the config file, you edit class_core.php like this:

1. open includes/class_core.php

2.

Find: function fetch_ip() { return $_SERVER['REMOTE_ADDR']; }

replace with:

function fetch_ip() { if(isset($_SERVER['HTTP_CF_CONNECTING_IP'])) { return $_SERVER['HTTP_CF_CONNECTING_IP']; } return $_SERVER['REMOTE_ADDR']; }


Find:

function fetch_alt_ip() { $alt_ip = $_SERVER['REMOTE_ADDR'];

if (isset($_SERVER['HTTP_CLIENT_IP'])) { $alt_ip = $_SERVER['HTTP_CLIENT_IP']; }


Replace with:

function fetch_alt_ip() { $alt_ip = $_SERVER['REMOTE_ADDR'];

if (isset($_SERVER['HTTP_CF_CONNECTING_IP'])) { $alt_ip = $_SERVER['HTTP_CF_CONNECTING_IP']; } else if (isset($_SERVER['HTTP_CLIENT_IP'])) { $alt_ip = $_SERVER['HTTP_CLIENT_IP']; }


3. upload and overwrite

[pitzerwm]

Thanks again Max, where do you "assign my domains"?

Do you use the challenge page?

[Max Taxable]

Thanks again Max, where do you "assign my domains"?

Do you use the challenge page?The challenge page, it only comes in when CF thinks a bad actor is trying to access your site. It gets its data from Project Honey Pot and is in fact, a product of Project Honey Pot.

I just use the default page, I didn't customize it. But, there's option to do so.

"assign the domain" means, CF will give you new nameservers to assign to your domain, through your host instead of the nameservers you have now.

[pitzerwm]

Thanks max, I guess, I'll give it shot tomorrow.

[Boofo]

It sounds a lot more confusing than it really is once you get going on it, trust me. Max is the man to go to for any advice. ;)

[Max Taxable]

It sounds a lot more confusing than it really is once you get going on it, trust me. Max is the man to go to for any advice. ;)And Boofo can now personally vouch for CF's effectiveness!

[pitzerwm]

Max, Do I use my main domain to register with CF, or just the forum's subdomain?

i.e. mydomain.com or forum.mydomain.com

Thanks

[Max Taxable]

Max, Do I use my main domain to register with CF, or just the forum's subdomain?

i.e. mydomain.com or forum.mydomain.com

ThanksOffhand I would advise use 'mydomain' then make sure all subdomains you want covered by CF are included in the DNS settings in CF.

[pitzerwm]

Thanks again Max for your time.

I'm in the process now of setting it up and I'm at the point that it shows what will be protected/routed to them, and while the main domain and the forum domain have the orange cloud the actual NS (DNS) has the grey cloud. Did I screw something up?

[Max Taxable]

Thanks again Max for your time.

I'm in the process now of setting it up and I'm at the point that it shows what will be protected/routed to them, and while the main domain and the forum domain have the orange cloud the actual NS (DNS) has the grey cloud. Did I screw something up?You'll have the option of turning any of the grey clouds ON by clicking on them.

[pitzerwm]

Thanks again Max. Ok, I finally got that step completed (CF said that the NS shouldn't be routed that way, KEEP grey clouds.

I have changed the DNS servers at IX, "wait 24 hours" When do I change the code in class_core?

When this "flips" will my site be down until I make that code change?

I went to the class_core.php file and (3.8.4 patch 1) it doesn't have those lines of code to replace

[Max Taxable]

Thanks again Max. Ok, I finally got that step completed (CF said that the NS shouldn't be routed that way, KEEP grey clouds.

I have changed the DNS servers at IX, "wait 24 hours" When do I change the code in class_core?

When this "flips" will my site be down until I make that code change?

I went to the class_core.php file and (3.8.4 patch 1) it doesn't have those lines of code to replacePut your class_core.php file here as a attachment please.

The change is needed so IPs of you, members and visitors won't all be resolving to cloud flare in online.php and elsewhere on the board.

[pitzerwm]

ok, Thanks again for your help.

[Max Taxable]

This is the file out of /includes folder?

???

includes/class_core.php is the file needed editing.

Per here:

http://www.cloudflare.com/wiki/VBulletin_3.8.4

[pitzerwm]

Yes, Max that is what I have there, and I double checked and that's what is on the site.

I looked at the files on 3.8.6 that I didn't put up, and it also doesn't have those lines.

Ok, I figured it out, its broken up so find replace doesn't catch it. line 2257

Thanks again for your time and energy.

[Max Taxable]

Yes, Max that is what I have there, and I double checked and that's what is on the site.Lines 2257-60 in the file you attached. I verified this with mine as well:

function fetch_ip()
{
return $_SERVER['REMOTE_ADDR'];
}

EDIT: I saw after I posted you figured it out. haha

[Boofo]

Max, you also might want to tell them about the the other option of installing mod_cloudflare on the server if they are using Apache to avoid the file edits.

[Max Taxable]

Max, you also might want to tell them about the the other option of installing mod_cloudflare on the server if they are using Apache to avoid the file edits.I did but didn't have the details handy, even mentioned you!:D

[Boofo]

I just had my host install it. Never had to touch anything. ;)

[pitzerwm]

Thanks again, I trust that I have it right. I guess, I made the "spiders" mad, yesterday they attacked at 6:30 in the AM and about 6:30 in the PM. Today they didn't do that.

[Max Taxable]

Thanks again, I trust that I have it right. I guess, I made the "spiders" mad, yesterday they attacked at 6:30 in the AM and about 6:30 in the PM. Today they didn't do that.Look in /online.php, "Who's Online" check the IP addresses.

[pitzerwm]

Well, CF did not fix the "too many connections" problem.

This is the message:

Database error in vBulletin :

mysql_connect(): Too many connections
/hsphere/local/home/xxxxxxxx/forum.autocareforum.com/includes/class_core.php on line 311

MySQL Error :
Error Number :
Request Date : Wednesday, January 11th 2012 @ 01:18:37 AM
Error Date : Wednesday, January 11th 2012 @ 01:18:37 AM
Script : http://forum.autocareforum.com/forumdisplay.php?f=9
Referrer : http://forum.autocareforum.com/
IP Address : 70.27.47.178
Username :
Classname : vB_Database
MySQL Version :


Any other ideas, starts at 10:19 runs until 10:40 +/-

[Max Taxable]

Are you on a shared server? It could be someone else's website that's causing the problem. Have you contacted your host?

it is what the error message says: the database cannot accept more connections, all connection slots are in use otherwise. Several things to check:
-- is your own application OK? Does your application open persistent connections, which aren't closed? Use mysql_connect() instead of mysql_pconnect() unless you have a good reason to do so.
-- have you got more users then ever? I can't see stats when I go to your board. Are you being flooded with bots?
-- can you increase the max_connections system variable, or has it been increased up to its end already (its default value is 100)?
-- Have you looked in the manual for further details:
[dev.mysql.com...]

If it is your own server, then you can look into these things and try to fix and correct.
If you are on shared hosting, you need your host to fix this -- perhaps other shared hosting customers started to eat up more connections than they should ... (common hosting error: too many customers on one box ...) This can be the result of a DDoS attack either on you or a site that is sharing the hosting with you.

Cloud Flare can block the attacks if they are coming to you. First thing I would do (and did) is block China - this is where the Baidu bots come from, you don't need them indexing your site, and their behavior is quite aggressive. They show up with 100s of bots at a time and stay for hours. They totally ignore robots.txt. You can block others too, you should start watching the traffic in online.php for floods. Block bad actors.

Here's what your board looks like at Web Page Test:

http://www.webpagetest.org/result/120111_78_2TNP4/

[Boofo]

Are you with hostgator, by chance? They have the max connections set at 25. Most shared servers have low max connections, IIRC.

[Disasterpiece]

maybe you should think about a protection mechanism like cloudflare. Then you will have better options to block or analyse possible threats.

//e: nvm, didn't see the last pages...

[pitzerwm]

Boofo: No I am with IX, shared server, with 50 connections.

Max: I have an ongoing dialog with the host.

-- is your own application OK? Does your application open persistent connections, which aren't closed? Use mysql_connect() instead of mysql_pconnect() unless you have a good reason to do so.

Where would I change the above? My site is for the car wash industry, at this time a night, I don't have more than 10 people at any time.

I'm not smart enough to understand dev.mySQL. I am with IX, not my own server.

I blocked China, Russia, Ukraine with .htaccess.

It does make sense that since I'm on a shared server, that its someone else. I have nothing happening at anytime. This started a month or so ago.

Thanks again

[kh99]

Where would I change the above?

In the file includes/config.php, find $config['MasterServer']['usepconnect'] and make sure it's set to 0.

[pitzerwm]

kh99: I checked that and it is at 0

--------------- Added 1326350381 at 1326350381 ---------------

In the file includes/config.php, find $config['MasterServer']['usepconnect'] and make sure it's set to 0.

This is the way it is.

Use mysql_connect() instead of mysql_pconnect() unless you have a good reason to do so.

where would I change this?

[pitzerwm]

More info:

Looking at "Who is online". I see that some of the "guest" are "viewing archives" with the link.

This is the link on one of them: /archives/index.php/t-4390.html so I tried

http://forum.autocareforum.com/archive/index.php/t-4390.html which goes to a place that I've never seen before.

Is there a way to block these? I would think that CF would do this automatically.

[Max Taxable]

More info:

Looking at "Who is online". I see that some of the "guest" are "viewing archives" with the link.

This is the link on one of them: /archives/index.php/t-4390.html so I tried

http://forum.autocareforum.com/archive/index.php/t-4390.html which goes to a place that I've never seen before.

Is there a way to block these? I would think that CF would do this automatically.The archive exists for search engine bots to use. It's always been there.

[pitzerwm]

Since I have tried to block all bots with robots.txt and obviously not working, is there another way to block this?

[Max Taxable]

Since I have tried to block all bots with robots.txt and obviously not working, is there another way to block this?You don't want to block all bots, most of them are your friends. Like Google, Yahoo, MSN and etc, these obey robots.txt. You just want to do away with the bad actors. Like Baidu, for one.

Robots.txt is merely instructions for the bots, it doesn't block anything.

Here's a Mod that lets you block spiders by user agent:

https://vborg.vbsupport.ru/showthread.php?t=264932

Here's advanced spider detection, and guest visitor tracking - identifies spiders for you in online.php and also tracks all guests visits. Very handy:

https://vborg.vbsupport.ru/showthread.php?t=201214

--------------- Added 1326398877 at 1326398877 ---------------

Is there a way to block these? I would think that CF would do this automatically.No, in CF it is blocking and challenging known bad actors, using the Project Honey Pot database as reference. For custom blocking, you can enter that information by IP, IP range, or even by country.

"China" is the first one you need to block, this wipes out Baidu for ya.

[pitzerwm]

Thanks Max