Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+4 votes
50.9k views
in Q2A Core by
edited by

My server's error log says:

PHP Warning:  mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User db123456 already has more than 'max_user_connections' active connections in /qa-include/qa-db.php on line 66

This error appeared yesterday, 20-Oct-2012, from 18:50:17 to 18:51:01 (about 3 times per second), and 18:53:40 to 18:53:47 (about 3 times per second).

--

On stackoverflow they say it is "not a programming problem, just quantity of resources available".

But why haven't I got this error over the last 7 months?

--

Update 2012-12-14

My server logs showed me that a Chinese bot was going over a lot of tags, which obviously caused the error, here some lines:

[11-Dec-2012 18:58:39] PHP Warning:  mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User 123456 already has more than 'max_user_connections' active connections in /qa-include/qa-db.php on line 66

42.62.36.25 - - [11/Dec/2012:18:58:39 +0100] "GET /tag/schaubid HTTP/1.0" 200 6491 "http://www.mysite.de/" "Moziella/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9.1.2) Firefox/3.5.2"
42.62.36.25 - - [11/Dec/2012:18:58:39 +0100] "GET /tag/sattelpunkt HTTP/1.0" 200 647 "http://www.mysite.de/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9.1.2) Firefox/3.5.2"
42.62.36.25 - - [11/Dec/2012:18:58:39 +0100] "GET /tag/binomisch HTTP/1.0" 200 647 "http://www.mysite.de/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9.1.2) Firefox/3.5.2"
...

How to solve/prevent this?

Q2A version: 1.5.3
by
edited by
Just see that somebody else had a similar problem: http://question2answer.org/qa/14988/max-user-connections However, I have QA_PERSISTENT_CONN_DB = false.

I got only 600 visitors on my site yesterday, so the quantity of visitors cannot be the problem.

My provider wrote me: "Please check your scripts if they create a connection for each query".
by
edited by
Here is some information I found on the error 'max_user_connections':

"This is limit is rarely reached by a site in normal use. If it is exceeded, the cause is often related to one or more locked tables due to a change in structure, a repair or updating of indices carried out with the same account or effecting the same table.

A table that consists of many elements and that has never been purged might be the reason for the lock. Overfrequentation of a PHP script which makes long requests that are not optimised may also lead to accumulation of a large number of simultaneous users as related to response time and the number of requests.
In this case it will be necessary to add indices to the tables used by the request to optimise performance."

src: http://hosting.infomaniak.ch/support/faq/faq_home.php?language=english&faq=471
by
Can ask. How do you solve the problem? I have the same problem
by
got this error message again, not solved...
by
Alright, I checked my server log and updated the question above.

1 Answer

+3 votes
by
selected by
 
Best answer

it's a mySQL server error, because of overusing system resources. most shared hosts use this limitation to stop heavy resource consumtion of sites.

increase MAX_USER_CONNECTIONS and MAX_CONNECTIONS in your my.ini file or use this SQL command:

GRANT ALL * TO 'root'@'localhost' WITH MAX_USER_CONNECTIONS 1000;

syntax might be wrong but the idea worked before for me, check it yourself.

Update:

I figured more people may need it so here is picture guide to do it using phpMyAdmin:

phpMyAdmin>(1)Privileges>(2) choose edit privilege from users list >(3) in "Resource limits" panel in the right > increase all values we talked about or set all to ZERO to remove the limitation.

 

by
Thanks for the information, I contacted my provider to change the values.

You say I could do it manually by a mysql command? How/where to do this? Thanks towhid!
by
Hi Kei, if you have phpMyAdmin with root access in your hosting panel follow these steps:
phpMyAdmin>Privileges> choose edit privilege from users list > in "Resource limits" panel in the right > increase all values we talked about.
by
Thanks, unfortunately I have no root access but your information will at least help others with the same problem! Merry xmas :)

The default value for max_user_connections of my provider is "35". They say they do not change it. Checking my error logs, the Chinese bot has triggered 34 php warnings in one second, thus I guess it is doing about 70 requests in a very short time.

If the error appears again, I will block the specific IP.
...