PowWeb Forums - The Perfect Community for the Perfect Host  

Register now to interact with over 11,000 members! Registered users have Posting Privileges, free access to Private Messaging, Email Notifications and more.

Go Back   PowWeb Community Forums > The PowWeb Platform > MySQL
User Name
Password
Register FAQ Members List Search Today's Posts Mark Forums Read

Closed Thread
 
Thread Tools
Old 6-13-05, 12:30 PM   #1
aji
Guest
 
Posts: n/a
User has exceeded the 'max_questions' resource (current value: 72000)

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
 
Old 6-13-05, 12:36 PM   #2
alphadesk
 
alphadesk's Avatar
 
Join Date: Dec 2001
Location: Gulfcoast, TX
Posts: 6,911
Reputation: 102
What's the url and type of cms you are using?
__________________
Thanks,
AlphaDesk

Those who can read and don't are no better off than those who can't. - Sam Clemens
alphadesk is offline  
Old 6-15-05, 01:45 AM   #3
aji
Guest
 
Posts: n/a
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
 
Old 6-15-05, 01:47 AM   #4
tbonekkt
 
Join Date: Dec 2002
Location: TX
Posts: 12,382
Reputation: 248
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.
tbonekkt is offline  
Old 6-15-05, 01:56 AM   #5
aji
Guest
 
Posts: n/a
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
 
Old 6-15-05, 10:15 AM   #6
tbonekkt
 
Join Date: Dec 2002
Location: TX
Posts: 12,382
Reputation: 248
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.
tbonekkt is offline  
Old 6-15-05, 11:42 AM   #7
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by 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
The 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 )
__________________
Regards - Richard
RTH10260 is offline  
Old 6-20-05, 04:52 AM   #8
aji
Guest
 
Posts: n/a
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
 
Old 6-20-05, 06:45 PM   #9
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by aji
Consider the above statement,
Please explain me
1. how to check for connection errors and switch to the next connecton
The coding solution has just been shown once again by mitchind here:
http://forum.powweb.com/showthread.p...873#post310873

My slightly enhanced version that checks thru the whole list looks like this: NOT TESTED MAY HAVE CODING ERRORS
PHP Code:
// 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 rand0, ( count($un) -) ); // 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$usr1);
  
$pw array_splice($pw$usr1);
} while ( 
count($un) > );
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' "); 
__________________
Regards - Richard
RTH10260 is offline  
Old 6-20-05, 07:10 PM   #10
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by aji
2. how to switch the users with out releasing the connecton
I 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.
__________________
Regards - Richard
RTH10260 is offline  
Old 6-21-05, 09:17 AM   #11
Pig
foo
 
Pig's Avatar
 
Join Date: Jan 2003
Location: Seattle-ish
Posts: 2,597
Reputation: 106
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.
__________________
webhead.cc
Pig is offline  
Old 6-21-05, 11:50 AM   #12
mitchind
Older not wiser
 
mitchind's Avatar
 
Join Date: Nov 2003
Location: Calgary, AB
Posts: 2,472
Reputation: 205
Quote:
Originally Posted by 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.
Amen to that! Especially if you're on the same database server as me.
mitchind is offline  
Old 6-22-05, 03:37 AM   #13
eclipse
Guest
 
Posts: n/a
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.
 
Old 6-22-05, 04:21 AM   #14
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by 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.
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 ?
__________________
Regards - Richard
RTH10260 is offline  
Old 6-24-05, 10:03 AM   #15
Shanti
 
Join Date: May 2002
Location: WI
Posts: 107
Reputation: 5
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:

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?

Code:
<?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.'"); ?>
__________________
Shanti
It is better to light one candle...

RoK World
Shanti is offline  
Old 6-24-05, 11:10 AM   #16
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by Shanti
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 Code:
<?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 rand0, ( count($un) -) ); // 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$usr1);
  
$pw array_splice($pw$usr1);
  
// run the loop again to choose among the remaining users
} while ( count($un) > );

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).
__________________
Regards - Richard
RTH10260 is offline  
Old 6-24-05, 11:15 AM   #17
Shanti
 
Join Date: May 2002
Location: WI
Posts: 107
Reputation: 5
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.
__________________
Shanti
It is better to light one candle...

RoK World
Shanti is offline  
Old 6-24-05, 11:18 AM   #18
tbonekkt
 
Join Date: Dec 2002
Location: TX
Posts: 12,382
Reputation: 248
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.
tbonekkt is offline  
Old 6-24-05, 11:20 AM   #19
Shanti
 
Join Date: May 2002
Location: WI
Posts: 107
Reputation: 5
Quote:
Originally Posted by 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.
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?
__________________
Shanti
It is better to light one candle...

RoK World
Shanti is offline  
Old 6-24-05, 11:22 AM   #20
tbonekkt
 
Join Date: Dec 2002
Location: TX
Posts: 12,382
Reputation: 248
Yes, you could do that too. But that's not what the code above is doing.
tbonekkt is offline  
Old 6-24-05, 11:25 AM   #21
Shanti
 
Join Date: May 2002
Location: WI
Posts: 107
Reputation: 5
Quote:
Originally Posted by 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

Code:
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
It is better to light one candle...

RoK World
Shanti is offline  
Old 6-24-05, 11:36 AM   #22
Shanti
 
Join Date: May 2002
Location: WI
Posts: 107
Reputation: 5
Quote:
Originally Posted by tbonekkt
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?
__________________
Shanti
It is better to light one candle...

RoK World
Shanti is offline  
Old 6-24-05, 11:52 AM   #23
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by 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.
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) )
__________________
Regards - Richard
RTH10260 is offline  
Old 6-24-05, 12:08 PM   #24
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by Shanti
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.
__________________
Regards - Richard
RTH10260 is offline  
Old 6-24-05, 12:09 PM   #25
RTH10260
 
Join Date: Jan 2005
Location: Switzerland
Posts: 2,938
Reputation: 126
Quote:
Originally Posted by Shanti
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.
__________________
Regards - Richard
RTH10260 is offline  
Closed Thread

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 07:19 AM.


Contents ©PowWeb, Inc. ~ vBulletin, Copyright 2000-2007 Jelsoft Enterprises Limited.