Work Around: Error SQL server exceeded max questions
Zachariah
Join Date: Feb 2002
Posts: 2,125
AS Electronics
Experance in PHP, HTML, JavaScript, DHTML, Flash, XML, MySQL, Photoshop, more.
Electronics I, II, III, Microprocessors, AC and DC Electronics, Boolean Algebra, Trig / Math Analysis, Chemistry / AP Physics
- All mySQL is stopped for like 5 min then all is ok.
- The server it self auto temp bans the mysql account in use.
I am sure others may have ran into this issue. I have ran into many webhosts that because of "server loads" have a cap on max questions mySQL server can be given in a time frame. (1 hour in my case) This becomes a huge problem when doing maintenance of "Update Counters" in the AdminCP.
One workaround is to create multiple mysql users in your hosting account. In config.php you randomly pick one of these users to connect to the database. This will spread the questions between users and since the limit is per user. This is not a perfect solution for if an account maxes out the script will stop, but you can usually overcome the problem.
I set up 7 accounts in mySQL up on 1 database all using the same password.
I ran 236,968 queries without a hiccup.
Edit:
includes/config.php
Find:
PHP Code:
//***** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
//***** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
$dblogins = array();
"Too many connections" is not the same as "User x already has more than 'max_user_connections' active connections"
to many connections, indicates that the mysql server itself has 0 available slots for mysql connections. Where max_user_connections indicates that a specific mysql user has reached the maxed limit.
To many connections is a semi-hard limit defined in the my.ini/my.cnf file
max_user_connections can be imposed at several areas, one of which is my.cnf/my.ini, but additionaly in the mysql user table specificly.
It is funny, sometimes i see single site servers setup with a max_connection limit of 100, so mysql allocates enough resources to have 100 connections. But they limit their one and only mysql user to something like 30 connections. If you only want mysql to handle 30 connections, only give it 30.
Now I am ranting
Though, 9 years working with vB, and 7 of those in support, has tought me many things.
02-09-2009, 10:00 PM
05-25-2011, 01:24 AM
