User has exceeded the 'max_questions' resource (current value: 72000)

[aji]

Recently I strated getting the error message "user has exceeded the 'max_questions' resource (current value: 72000)"

I tried 'randomly connect with mutiple users' and 'try another connection if one got failed'.

Both are not solving my problem and my site is down always.

Please help me to solve this problem

Thank you

[alphadesk]

What's the url and type of cms you are using?

[aji]

Our URL is www.allarabia.net

We developed our own CMS program and not using any opensource CMS program

It is a recruitment site and people are visiting every day to register their CV's and Search for Candidates, but now the site is locking always with the above error message. Please give me a solution

Thank you
Aji

[tbonekkt]

The solution is to optimize your scripts to use fewer queries. We limit each database user (three per database) to 72,000 queries per hour.

[aji]

We are using three database users randamly and it will not exceed 216000 per hour, Please give me a solution to solve this problem

Thnx
Aji

[tbonekkt]

The limit isn't 216,000 per hour; it's 72,000 per user per database per hour. As I already suggested, you should look into optimizing your scripts to use less queries.

[RTH10260]

We are using three database users randamly and it will not exceed 216000 per hour, Please give me a solution to solve this problemThe random choice doesn't guarantee that your database usernames remain below the limit. They spread the load, but Mr Murphy may be visiting your site :) and one username may be getting just a bit more traffic than the others.

You can enhance the random choice algorithm, by checking for the error, and switching among the other two usernames (again random) and if all things fail, use the third. Note that when you have acquired a connection, you can keep that connection and only switch the users rather than releasing the connection too (you may hit a connect limit too otherwise).

That your application seems to hit the limits even with the random choice of usernames could probably also be an indication that you are running a very successful website and that you may need to look for a hosting with more resources than Powweb offers.
(or as tbonekkt hinted, a poorly coded application ;) )

[aji]

Consider the above statement,
Please explain me
1. how to check for connection errors and switch to the next connecton

2. how to switch the users with out releasing the connecton


Thank you
Aji

[RTH10260]

Consider the above statement,
Please explain me
1. how to check for connection errors and switch to the next connectonThe coding solution has just been shown once again by mitchind here:
http://forum.powweb.com/showthread.php?p=310873#post310873

My slightly enhanced version that checks thru the whole list looks like this: NOT TESTED MAY HAVE CODING ERRORS// usernames defined in OPS
$usernames = array();
$usernames[] = 'username1';
$usernames[] = 'username2';
$usernames[] = 'username3';

// passwords corresponding to usernames
$passwords = array();
$passwords[] = 'password1';
$passwords[] = 'password2';
$passwords[] = 'password3';

$the_server = 'mysql??.powweb.com';
$the_database = 'some_database';

// initial database connection
$un = $usernames;
$pw = $passwords;
do {
$usr = rand( 0, ( count($un) -1 ) ); // random choice of a user
$db_connection = mysql_connect ($the_server,$un[$usr],$pw[$usr]);
if ($db_connection) break; // we have connection
if ( mysql_errno() != 1040 ) break;
// too many connections - choose among the remaining users
$un = array_splice($un, $usr, 1);
$pw = array_splice($pw, $usr, 1);
} while ( count($un) > 0 );
if (!db_connection) die("User friendly msg 'database server not available' ");

// link to database on the server
$db_link = mysql_select_db($the_database,$db_connection);
if (!db_link) die("User friendly msg 'database not available' ");

[RTH10260]

2. how to switch the users with out releasing the connectonI admit I stumbled over the documentation, the small print about 'deprecated' and 'removed from php' was missed on first reading of mysql_change_user :( .

IMO I would try following approach to squeeze the usage to the extreme:
- when performing a query, and receive the status code of max query for the user, then retry this statement with an alternate username, until all tried.
- don't close the connection, but open up a further connection for the next username in the list.
- only give up when all three usernames fail for a single query.
- this approach would not work where part of the logic is with transactions logic.
- it relies on the fact, that the limit is a rolling number and a free slot may appear.

[Pig]

If you are so close to the limit that you frequently getting errors and rotating users does not work for you, then powweb is probably not the best host for you. If your needs are that high, you should look into do deditcated hosting.

[mitchind]

If you are so close to the limit that you frequently getting errors and rotating users does not work for you, then powweb is probably not the best host for you. If your needs are that high, you should look into do deditcated hosting.
Amen to that! Especially if you're on the same database server as me.

[eclipse]

There seems to be something wrong on mysql10. I'm getting the same error with only 100 querys an hour.

When i refresh the page i get mysql error or everything is ok.

[RTH10260]

There seems to be something wrong on mysql10. I'm getting the same error with only 100 querys an hour.

When i refresh the page i get mysql error or everything is ok.There is also a max concurrent server connection limit that has a similar sounding error messsage, could it be you have seen that one ?
How do you know you only get 100 queries per hour ? Did you check with the access log if you don't have any unwelcome visitor (spider) that is generating those accesses ?

[Shanti]

I'm having this same problem with my phpBB message boards, mainly because a lot of my users are high school kids with nothing better to do than post all day long :P

Can someone help me modify the phpBB config.php file to fit the random username and password script that RTH10260 provided?

Here's the original code:

<?php


// phpBB 2.x auto-generated config file
// Do not change anything in this file!

$dbms = 'mysql';

$dbhost = 'server_name';
$dbname = 'database_name';
$dbuser = 'user_name';
$dbpasswd = 'password';

$table_prefix = 'phpbb_';

define('PHPBB_INSTALLED', true);

?>

Would this work to substitute it?

<?php

$dbms = 'mysql';

$dbhost = 'server_name';
$dbname = 'database_name';

$table_prefix = 'phpbb_';
define('PHPBB_INSTALLED', true);

// usernames defined in OPS
$usernames = array();
$usernames[] = 'username1';
$usernames[] = 'username2';
$usernames[] = 'username3';

// passwords corresponding to usernames
$passwords = array();
$passwords[] = 'password1';
$passwords[] = 'password2';
$passwords[] = 'password3';

// initial database connection
$un = $usernames;
$pw = $passwords;
do {
$usr = rand( 0, ( count($un) -1 ) ); // random choice of a user
$db_connection = mysql_connect ($dbhost,$un[$usr],$pw[$usr]);
if ($db_connection)
$dbuser = $un[$usr];
$dbpasswd = $pw[$usr];
break; // we have connection

if ( mysql_errno() != 1040 ) || ( mysql_errno() != 1226 )
// too many connections or requests per hour
// use array_splice to remove bad connection credentials from the array
$un = array_splice($un, $usr, 1);
$pw = array_splice($pw, $usr, 1);
// run the loop again to choose among the remaining users
} while ( count($un) > 0 );

if (!dbuser) die("User friendly msg 'Too many connections, try back later.'");

?>

[RTH10260]

Can someone help me modify the phpBB config.php file to fit the random username and password script that RTH10260 provided?
[...]May I suggest this modification (you have been omitting several curly {braces} that are required to keep some statements correctly together with IF:<?php

$dbms = 'mysql';

$dbhost = 'server_name';
$dbname = 'database_name';

$table_prefix = 'phpbb_';
define('PHPBB_INSTALLED', true);

// usernames defined in OPS
$usernames = array();
$usernames[] = 'username1';
$usernames[] = 'username2';
$usernames[] = 'username3';

// passwords corresponding to usernames
$passwords = array();
$passwords[] = 'password1';
$passwords[] = 'password2';
$passwords[] = 'password3';

// initial database connection
$un = $usernames;
$pw = $passwords;
do {
$usr = rand( 0, ( count($un) -1 ) ); // random choice of a user
$db_connection = mysql_connect ($dbhost,$un[$usr],$pw[$usr]);
if ($db_connection) {
$dbuser = $un[$usr];
$dbpasswd = $pw[$usr];
break; // we have connection
}
// only these error codes of inerest, drop out for any others
if ( mysql_errno() != 1040 ) && ( mysql_errno() != 1226 ) break;
// too many connections or requests per hour
// use array_splice to remove bad connection credentials from the array
$un = array_splice($un, $usr, 1);
$pw = array_splice($pw, $usr, 1);
// run the loop again to choose among the remaining users
} while ( count($un) > 0 );

if (!dbuser) die("User friendly msg 'Too many connections, try back later.'");
?>If phpBB has a nice way of notifying users of errors, use similar code in place of the DIE to drop out user friendly.

Be sure to test this code on a backup forum first (or in a time of low use).

[Shanti]

Thanks! It's hard for me to get out of ASP mode sometimes :)

I'll run this by the people at the phpBB community too and see if there's something here that might cause problems. Thanks for posting the code, it will be a big help once I get it working!


BTW, the break that you've added here:

if ( mysql_errno() != 1040 ) && ( mysql_errno() != 1226 ) break;

Would that break you out of the do/while statement completely? My thinking was that if the connection combination created a too many connections or too many requests SQL error, that it would remove that username/password combination from the array then run though the loop again with a different combination.

[tbonekkt]

The only problem I see with the code you're attempting to use is the password. All three MySQL usernames must have the same password for rotating users to work. And because of that, there's no need for a password array.

[Shanti]

The only problem I see with the code you're attempting to use is the password. All three MySQL usernames must have the same password for rotating users to work.

They do in my situation, so that's good. But wouldn't you be able to pull the corresponding password from the password array based on the index of the current username in the username array?

[tbonekkt]

Yes, you could do that too. But that's not what the code above is doing. ;)

[Shanti]

if ( mysql_errno() != 1040 ) && ( mysql_errno() != 1226 ) break;

Would that break you out of the do/while statement completely? My thinking was that if the connection combination created a too many connections or too many requests SQL error, that it would remove that username/password combination from the array then run though the loop again with a different combination.

Wait I've got that backwards here, don't I? Would it be better to use

if ( mysql_errno($dbconnection) ) {
// connection returned a sql error
// remove bad connection credentials from the array
$un = array_splice($un, $usr, 1);
$pw = array_splice($pw, $usr, 1);
// run the loop again to choose among the remaining users
}

?

[Shanti]

Yes, you could do that too. But that's not what the code above is doing. ;)

Ahh I thought that's what this was doing:

$dbuser = $un[$usr];
$dbpasswd = $pw[$usr];

Is there some other syntax that you'd have to use to get the parallel arrays?

[RTH10260]

The only problem I see with the code you're attempting to use is the password. All three MySQL usernames must have the same password for rotating users to work. And because of that, there's no need for a password array.There is the static $passwords array at the beginning that keeps passwords to corresponding $usernames. For actual work in the algorithm I copy them to $un and $pw to be manipulated. When I splice out the failed user out of $un, I also drop the corresponding password in $pw to keep the array members sync.

Any flaw ?
(I admit - it's PFMS time now (post full moon syndrom) :D )

[RTH10260]

BTW, the break that you've added here:

if ( mysql_errno() != 1040 ) && ( mysql_errno() != 1226 ) break;

Would that break you out of the do/while statement completely? My thinking was that if the connection combination created a too many connections or too many requests SQL error, that it would remove that username/password combination from the array then run though the loop again with a different combination.I break out of the loop in three cases:
- by BREAK when the connection is established
- by BREAK when I receive none of the expected error codes. MySql could be throwing several other codes too, but only the 1040 and the 1226 qualify for a repeated connect attempt. Check the test, it's the != operator and asks for the use of the && operator for a break.
- array is exhausted.

Depends what scripting language you use, but PHP doesn't have an ENDIF/IFEND to match an IF. PHP expects a single statement, so a compound statement goes into a pair of curly braces.

[RTH10260]

Ahh I thought that's what this was doing:

$dbuser = $un[$usr];
$dbpasswd = $pw[$usr];

Is there some other syntax that you'd have to use to get the parallel arrays?Check my above post to Tom.