Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
Work Around: Error SQL server exceeded max questions
Zachariah
Join Date: Feb 2002
Posts: 2,167

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

Canoga Park, CA
by Zachariah Zachariah is offline 10 Feb 2009
Rating: (1 vote - 5.00 average)

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.

Edit:
includes/config.php

Find:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Replace with:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.

Last edited by Zachariah; 24 Feb 2009 at 14:12..
Views: 11170
Reply With Quote
Comments
  #2  
Old 11 Mar 2009, 20:43
Sweeks's Avatar
Sweeks Sweeks is offline
 
Join Date: Jul 2008
Anyone confirm this to work fine on vbulletin 3.8.1?
________
Lesbian Scissoring

Last edited by Sweeks; 06 Apr 2011 at 13:34.
Reply With Quote
  #3  
Old 12 Mar 2009, 12:50
Zachariah's Avatar
Zachariah Zachariah is offline
 
Join Date: Feb 2002
Location: Canoga Park, CA
Yep, no poblems.

With a few changes the concept should work with any php program that accesses mySql.
__________________
http://www.szone.us |

Facebook | My:Hacks @ vBulletin.org
Reply With Quote
  #4  
Old 12 Mar 2009, 20:53
Sweeks's Avatar
Sweeks Sweeks is offline
 
Join Date: Jul 2008
Can this work around this error?


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Hope it can avoid this if possible.
________
BOX VAPORIZER

Last edited by Sweeks; 06 Apr 2011 at 13:35.
Reply With Quote
  #5  
Old 14 Mar 2009, 00:13
Zachariah's Avatar
Zachariah Zachariah is offline
 
Join Date: Feb 2002
Location: Canoga Park, CA
It should in theory.

max_user_connections - 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.
__________________
http://www.szone.us |

Facebook | My:Hacks @ vBulletin.org
Reply With Quote
  #6  
Old 22 Mar 2009, 11:35
bokmade bokmade is offline
 
Join Date: Feb 2002
Real name: Mansour
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.
Reply With Quote
  #7  
Old 23 Mar 2009, 19:16
Zachariah's Avatar
Zachariah Zachariah is offline
 
Join Date: Feb 2002
Location: Canoga Park, CA
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 .
__________________
http://www.szone.us |

Facebook | My:Hacks @ vBulletin.org
Reply With Quote
  #8  
Old 30 Mar 2009, 11:39
Frondy's Avatar
Frondy Frondy is offline
 
Join Date: Feb 2009
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):


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

After the first 3 comment lines, add the following:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

and replace them with:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

4. Check your edits, the complete block should look something like:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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)


.
Reply With Quote
  #9  
Old 21 Apr 2009, 19:58
MyChemicalSelf's Avatar
MyChemicalSelf MyChemicalSelf is offline
 
Join Date: Oct 2008
Real name: Jordan
Thanks dude im using this
Reply With Quote
  #10  
Old 26 Apr 2010, 05:53
thinkfast thinkfast is offline
 
Join Date: Mar 2010
is this work for vbulletin 4?
__________________
My Forums
Forex Forums
Reply With Quote
  #11  
Old 16 May 2010, 00:41
Zachariah's Avatar
Zachariah Zachariah is offline
 
Join Date: Feb 2002
Location: Canoga Park, CA
Originally Posted by thinkfast View Post
is this work for vbulletin 4?
Yes this should work the same.
__________________
http://www.szone.us |

Facebook | My:Hacks @ vBulletin.org
Reply With Quote
  #12  
Old 01 Jun 2010, 10:11
Alecsmith's Avatar
Alecsmith Alecsmith is offline
 
Join Date: Sep 2009
Real name: Nick
first of though this will make forum loading fast but after i adding more Mysql users but it made forum loading very slow
__________________

Reply With Quote
  #13  
Old 19 Dec 2010, 02:46
Poker Face Poker Face is offline
 
Join Date: Sep 2010
Originally Posted by Zachariah View Post
Replace with:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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';

Last edited by Poker Face; 19 Dec 2010 at 03:09.
Reply With Quote
  #14  
Old 21 Feb 2011, 20:02
combs combs is offline
 
Join Date: Aug 2007
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!
Reply With Quote
  #15  
Old 22 Feb 2011, 02:11
Videx's Avatar
Videx Videx is offline
 
Join Date: Feb 2007
Originally Posted by combs View Post
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).
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Article Options

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 On
HTML code is Off
Forum Jump


New To Site? Need Help?

All times are GMT. The time now is 10:59.

Layout Options | Width: Wide Color: