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();
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
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 .
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):
PHP Code:
// ****** 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:
PHP Code:
$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:
4. Check your edits, the complete block should look something like:
PHP Code:
// ****** 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)
02-09-2009, 10:00 PM
03-11-2009, 06:43 PM
.
