Work Around: Error SQL server exceeded max questions

[Zachariah]

vBulletin: 3.0x / 3.5x / 3.6x / 3.7x / 3.8x

"Error: SQL server exceeded 50,000 max questions"
- 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. :cool:

Edit:
includes/config.php

Find:

//***** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.

$config['MasterServer']['username'] = '';
$config['MasterServer']['password'] = '';

Replace with:

//***** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
$dblogins = array();

$dblogins['1']['user'] = 'user1';
$dblogins['2']['user'] = 'user2';
$dblogins['3']['user'] = 'user3';
$dblogins['4']['user'] = 'user4';
$dblogins['5']['user'] = 'user5';
$dblogins['6']['user'] = 'user6';
$dblogins['7']['user'] = 'user7';

srand((float) microtime() * 10000000);
$dbloginid = array_rand($dblogins);
while ($dblogins[$dbloginid]['user'] == '')
{
$dbloginid = array_rand($dblogins);
}
$config['MasterServer']['username'] = $dblogins[$dbloginid]['user'];

unset($dblogins, $dbloginid);

$config['MasterServer']['password'] = 'password';

1) Make sure and replace user1, user2, user3, user4, user5, user6, user7 with the correct user names made for the database.

2) Also replace password with the correct password.

[Sweeks]

Anyone confirm this to work fine on vbulletin 3.8.1?
________
Lesbian Scissoring (http://www.++++tube.com/categories/741/scissoring/videos/1)

[Zachariah]

Yep, no poblems.

With a few changes the concept should work with any php program that accesses mySql.

[Sweeks]

Can this work around this error?

User x already has more than 'max_user_connections' active connections
/home/xxx/public_html/includes/class_core.php on line 311

MySQL Error :
Error Number :


Hope it can avoid this if possible.
________
BOX VAPORIZER (http://boxvaporizers.com)

[Zachariah]

It should in theory.

max_user_connections (http://dev.mysql.com/doc/refman/5.0/en/user-resources.html) - this limits only the number of simultaneous connections made using a single account.

The number of queries that an account can issue per hour
The number of updates that an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server an account can have (as of MySQL 5.0.3)


This little bit of code takes multiple mysql user accounts (all with the same password) and spread the questions between users at random.

[bokmade]

Does It Work With web hosting limit connection ?If the limit 25 we can get 100 if four user ?

Does It Speed Up the Forum and Increase the server load ?

Thanks.

[Zachariah]

There is no harm in trying. I have not ran into this problem to test for an answer.

The number of simultaneous connections to the server an account can have are limits per account. If there are multi accounts, I would think each account has a limit of 25 vs. each database with a max limit.

Try it out and see if your problem goes away. Report back findings please :).

[Frondy]

1. Create additional user(s)
Create more MySQL user/password's and give these full permissions to your database.

2. Edit you config.php, and locate the following lines (they should alerady contain the user/password for your current setup):

// ****** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
$config['MasterServer']['username'] = 'root';
$config['MasterServer']['password'] = '';
After the first 3 comment lines, add the following:

$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
Edit mysql_username_X and mysql_password_X to have valid MySQL username/password combinations. If you need more then 3 combination, just duplicate the third line. If you are only using 2 combinations, remove the third combination.

3. Now edit the lines that configure your username password (they will probably already contain the info for the first MySQL user).
find:

$config['MasterServer']['username'] = 'root';
$config['MasterServer']['password'] = '';
and replace them with:

$config['MasterServer']['username'] = $mysql_user['user'];
$config['MasterServer']['password'] = $mysql_user['password'];
4. Check your edits, the complete block should look something like:

// ****** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
$config['MasterServer']['username'] = $mysql_user['user'];
$config['MasterServer']['password'] = $mysql_user['password'];
5. Save and upload your config.php. Finished.

Everytime a page is opened, 1 of the defined username/password combinations will be choosen at random, and by this reducing the number of connections for each user.

I found this quoted article on vb.com site, is it the same purpose variant? ('max_user_connections' issue)


.

[MyChemicalSelf]

Thanks dude im using this

[thinkfast]

is this work for vbulletin 4?

[Zachariah]

is this work for vbulletin 4?

Yes this should work the same.

[Alecsmith]

first of though this will make forum loading fast but after i adding more Mysql users but it made forum loading very slow :(

[Poker Face]

Replace with:

//***** MASTER DATABASE USERNAME & PASSWORD ******
// This is the username and password you use to access MySQL.
// These must be obtained through your webhost.
$dblogins = array();

$dblogins['1']['user'] = 'user1';
$dblogins['2']['user'] = 'user2';
$dblogins['3']['user'] = 'user3';
$dblogins['4']['user'] = 'user4';
$dblogins['5']['user'] = 'user5';
$dblogins['6']['user'] = 'user6';
$dblogins['7']['user'] = 'user7';

srand((float) microtime() * 10000000);
$dbloginid = array_rand($dblogins);
while ($dblogins[$dbloginid]['user'] == '')
{
$dbloginid = array_rand($dblogins);
}
$config['MasterServer']['username'] = $dblogins[$dbloginid]['user'];

unset($dblogins, $dbloginid);

$config['MasterServer']['password'] = 'password';

1) Make sure and replace user1, user2, user3, user4, user5, user6, user7 with the correct user names made for the database.

2) Also replace password with the correct password.

This isn't working for some reason... where does the database name 'username' get inserted or replaced, as in the original instructions?

My Database: $config['Database']['dbtype'] = 'mysqli';

[combs]

This is genius. I didn't want to change servers and Hostgator can be a real pain in the ass. I did this and the forum was INSTANTLY faster and the hundreds of errors in my email have stopped(for now..let it continue). Thanks a ton man!

[Videx]

This is genius.Only until they catch you. They've limited you to 25 connections per account, not per user (which would basically be unlimited connections).

[Zachery]

Some webhosts will ban you for this, find a REAL webhost.

[thincom2000]

Is there any other benefit from spreading the connections over multiple accounts (slight performance gain, maybe)? I just moved to a dedicated server, and am wondering if I even need this any more.

[Zachery]

Nope.

[SVTCobraLTD]

I am going to try this, hopefully it eliminates my errors:

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Too many connections
/home/xxxxxxx/forums/includes/class_core.php on line 311

[Zachery]

SVT, it wont in your case, your mysql server has reached the GLOBAL limit, not your user limit.

[Videx]

SVT, it wont in your case, your mysql server has reached the GLOBAL limit, not your user limit.I'll bite. How can you tell that from what he posted?

[SVTCobraLTD]

I'll bite. How can you tell that from what he posted?

I was wondering the same thing.

[Zachery]

"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 :p

Though, 9 years working with vB, and 7 of those in support, has tought me many things.

[yahsuah]

THANK YOU Zachery!