Database profeesionals. What does this MySQL error mean

Mtazamaji

Mtazamaji

JF-Expert Member
Feb 29, 2008
5,937
1,437
MyBB has experienced an internal SQL error and cannot continue.
SQL Error:1203 - User bbafrica_forum1 already has more than 'max_user_connections' active connectionsQuery:[READ] Unable to connect to MySQL server Please contact the MyBB Group for support.


Nimekuwa natembealea webiste ya bigbrother na kukubamna na ujumbe huu mara kwa mara.


kabla sija google nilipenda kuwauliza wataalam wenye practical experince na MySQL database engine.

  • Je hiyo maximun active user connection inayoongolewa ni user wangapi?
  • Is there a work arround to this problem?

 
First off MySQL DBA is not my expertise so what I will say may not be very specific.

Either the forum you are connecting to has reached it's maximum number of allowed concurrent connectopns as designated by the DBA/ Webmaster, or there is a lock that is not releasing logged off users, making the count higher with every new logon.

In the first case the DBA/ Webmaster / host can increase the number of concurrent connections, depending on the sites capacity and any possible license issues. If this is a hosting related issue you may need to talk to your host.

In the second case they will need to restart the SQL service and or count.

Also try to see if the host supoorts persistent connections, it could be as easy as turning the persistent connection from TRUE to FALSE.

All these are server side issues and you cannot do much (other than reporting) unless you are the server side admin.
 
Kiranga said:
First off MySQL DBA is not my expertise so what I will say may not be very specific.

Either the forum you are connecting to has reached it's maximum number of allowed concurrent connectopns as designated by the DBA/ Webmaster, or there is a lock that is not releasing logged off users, making the count higher with every new logon.

In the first case the DBA/ Webmaster / host can increase the number of concurrent connections, depending on the sites capacity and any possible license issues. If this is a hosting related issue you may need to talk to your host.

In the second case they will need to restart the SQL service and or count.

Also try to see if the host supoorts persistent connections, it could be as easy as turning the persistent connection from TRUE to FALSE.

All these are server side issues and you cannot do much (other than reporting) unless you are the server side admin.
Click to expand...

Kiranga, mimi kama wewe, MySQL is not my expertise.

But would like to point that, Mtazamaji is asking this from Mtazamaji's (website visitor's) standpoint, well, at least that is how I understood him/her. Hivyo basi, explanation yako kama nilivyo isoma ina cover angle ya webadmin/master badala ya normal visitor to a site, albeit, valid.

Kwako ndugu Mtazamaji, kwa kuzingatia maelezo ya Kiranga; mimi nadhani tatizo hilo linaweza kutokana na shared IP address. Yawezekana IP unayotumia iko shared na watu wengi. Na katika kujaribu kuzuia denial of service attacks, hao wenye website ya big brother wameweka limitation ya number of concurrent requests zinazo originate kutoka kwenye IP moja (yenu). Au pia kama hao jamaa wenye site wanakuwa wana detect malicious activity kutoka kwenye kundi la IP fulani hivi, kama vile kurecord show etc.. wanaweza kuwa wameban IP.

Maximum user - maelezo ya kiranga yanatosheleza.
Work around upande wako kama visitor- kama ni site ya ku-login, jaribu kulog out then log back on, au restart computer. Pia kama hilo halifanyi kazi, jaribu kujua kutoka kwa ISP wako kama IP yako ni shared. Kama ni shared na tatizo linaendelea, yawezekana jamaa wa-bba wame implement black-list na kuban access kwa IP zenu au kuweka limit ya hits kutoka IP hiyo (maelezo ya Kiranga). Unaweza pia kujaribisha kutembelea site hiyo kupitia komputer ya pahala pengine, i.e. IP tofauti. Good luck.
 
All these are server side issues and you cannot do much (other than reporting) unless you are the server side admin.
Click to expand...
Kiranga, sorry... your last line went unnoticed. Kumbe ulishalishema. My bad!
 
Thank you mkuu kiranga na Steve dii kwa maelezo safi . Pia nina little knowlenge ya MySQL server an Micrsoft SQL server engines.

Sikujua kama Databaser server kama MySQL ambayo ni Open souce inaweza kuwa na Limitation ya connections. Sasa najiuliza hili tatizo upande wa database server. I thouth hii inaweza kuwa ni case kwa Microsoft SQL server

  • Sasa hii number ya concurent connection inayofanya iwe maximum ni ngapi? I mean napenda nijue on the back ground/ server side what is real happening Technically at that MQSQL server engine.
  • Inawezekana hii server ina run kwenye Limited number of connection katika level ya ambayo ndo imefikia maximum?
Yaani niko curious kujua hata nikirepot kwa DBA wa hiyo site watafanya nini? As a junior IT professional napenda kujua.

NB:
kwenye hii site nakuwa natembealea kama unregistered visitor. Home page inafunguka vizuri ila baada ya hapo ndo makasheshe hayo.
 
Mtazamaji said:
Hao watu wengi ndo nilipenda kujua MYSQL limitation yake ni cocueent conection ngapi?
Click to expand...

Mkuu, sidhani kama kuna limitation. Inategemea na ukubwa wa server yako tu.

Kuna sites nimecheki kujaribu kuchunguza swali ulilouliza. Nyingi zinaonesha kuna default values za 10 - 250 and sometimes 500. Lakini access to db tables can be altered to suit particular need and in line with your server resources.

Nita nukuu hapa chini, just in case those sites ever become defunct:


Increase MySQL connection limit
MySQL's default configuration sets the maximum simultaneous connections to 100. If you need to increase it, you can do it fairly easily:

For MySQL 3.x:

# vi /etc/my.cnf
set-variable = max_connections = 250
For MySQL 4.x and 5.x:

# vi /etc/my.cnf
max_connections = 250
Restart MySQL once you've made the changes and verify with:

echo "show variables like 'max_connections';" | mysql
WHOA THERE: Before increasing MySQL's connection limit, you really owe it to yourself (and your server), to find out why you're reaching the maximum number of connections. Over 90% of the MySQL servers that are hitting the maximum connection limit have a performance limiting issue that needs to be corrected instead.


This entry was posted on 24/01/2007 (Wednesday) at 11:21 am and is filed under Posts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
21 Comments »

jamtech338
27.01.10 at 06:51
Hello,

I have MYSQL version: Mysql Ver 14.12 Distrib 5.0.51a, for suse-linux-gnu (i686)

and I need to increase my connection limit from 100 to 250-500 and I wanted to know which command to type.

This is urgent!
Thanks

Major Hayden
27.01.10 at 06:57
jamtech338 - Did you actually read the post above?

Open your MySQL configuration file in your favorite text editor and set the variable "max_connections" to the value you need. Once that's done, restart your MySQL daemon with /etc/init.d/mysqld restart (the init script path may differ on your system).

jamtech338
27.01.10 at 09:45
Hi Major Hayden,

Thanks for your quick response.

Truth is, I don't know anything about MYSQL however, I need to change the default max connection of 100 to something else.
From the command line using PuTTY to connect to MySQL which is on Linux box. I typed both the following:

# vi /etc/my.cnf
max_connections = 250

max_connections = 250

and both returned and error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_connections = 250' at line 1

With that said, can you please tell me how to open MYSQL in notepad because I dont have a G.U.I. tool; I install both mysql-workbench-oss-5.1.18a-win32 and mysql-gui-tools-5.0-r17-win32 on my Windows XP computer and I am unable to connect successfully due to 'access denied errors - I use the same password to connect with PuTTY.

Jamtech

Major Hayden
27.01.10 at 09:54
jamtech338 - I would highly recommend getting in contact with a linux systems administrator who can help you. There may be some underlying issues that may not be fixed by the max_connections adjustment (and the adjustment may make things worse).

Chris S
27.01.10 at 10:08
jamtech338 - is this under the [mysqld] section of your my.cnf?

You may be better copying and pasting your my.cnf at Pastebin.com - #1 paste tool since 2002!, and popping the link on here so we can give it a once over.

Chris

Sam
27.01.10 at 10:37
Hi, I was sent this way by @standaloneSA.

Based on the error and the "at line 1" it sounds to me as if you are typing these commands at a MySQL prompt (that is to say, at the prompt of the interactive MySQL query tool) rather than a command line prompt.

Can you repeat the procedure you use to connect, and then type 'show databases;' (without the quotes, with the semi-colon) and report back what is returned? If you get a "box" showing you all of the databases on your machine then you are in a MySQL prompt.

The quick and easy way to edit your configuration file from here is to type 'system bash' (again, without quotes). This will put you on a proper command line, from which you will be able to run the `vi` command to edit your configuration file.

Beware, on some Linux distributions (certainly Debian-based_distros such as Ubuntu) the command should be `vi /etc/mysql/my.cf`.

When you are done with editing `/etc/init.d/mysql restart` (Debian-based) or `/etc/rc.d/init.d/mysql restart` (most RPM-based, RHEL, Centos, etc.) will restart MySQL.

Of course, you should discover *why* you need to increase these concurrent connections. If whatever is failing now fails again, only after a little more time, you're likely to have an application that is leaking database connections.

jamtech338
27.01.10 at 11:03
Hi Sam,

Many Thanks for your input.

You are correct I was in fact in the MYSQL prompt.
I think I am now at the correct command line

jamtech338
27.01.10 at 11:06
Sam,

When I type 'vi /etc/my.cnf ;

It gives me options to Open, Edit, Recover, Delete, Quit and Abort

Sam
27.01.10 at 11:22
IIRC that is usually because a previous in-progress edit was interrupted (system reboot, terminal connection ended, etc, etc.).

If you'll excuse my crude way of putting it, I'm wary of giving you enough rope to hang yourself with here. By recovering the file (from the "backup" copy vi kept when it was nuked before) and then saving this over a copy that has been edited in the meantime you may regress a previous change or completely bork your MySQL configuration. Or everything might be fine.

That said, if I were you I would:

`cp /etc/my.cf $HOME/my.cf.backup.20100127`

to create a backup copy of the existing configuration file in your home directory. Then edit the file in vi (discarding the changes held in the recovery version of the file), save, restart. this page may help you decide what is most appropriate to do at the vi prompt.

If you manage to bork MySQL in any way then:

`cp $HOME/my.cf.backup.20100127 /etc/my.cf` and a restart of MySQL should get you out of trouble.

Finally, pride aside, please evaluate how valuable this system and anything that hangs off of it is against your knowledge and confidence in making these changes. I don't want to put you off, but then again I don't want to help you hose something you don't quite understand!

Major Hayden
27.01.10 at 11:24
jamtech338 - I can't emphasize how important it is that you need to understand the changes you're making prior to making them.

jamtech338
27.01.10 at 11:30
Sam,

From the command line I typed:

vi /etc/my.cnf
set-variable = max_connections = 200

and it took me to a line 120,1 with the '#' in green
#bdb_max_lock = 100000

is this the where I need to make the change from 100000 to 200000?

Jamtech338

Major Hayden
27.01.10 at 11:34
jamtech338 - You may be comfortable using 'nano' rather than vi since it's a little easier for a beginner to navigate. However, "set-variable" is only relevant for old versions of MySQL. You would be able to just use this in your configuration:

max_connections = 200

It's extremely important to find out why you need more connections. You may end up pushing your server into its swap memory and it will become highly unstable (or crash). Consult with a sysadmin/DBA and review:

MySQL: ERROR 1040: Too many connections | Racker Hacker

rjamestaylor
27.01.10 at 11:37
This is not going to end well. @jamtech338 - please take RackerHacker's advice and get expert help before making changes to your MySQL configuration.

Sam
27.01.10 at 11:57
At this point I would have to agree with Major and rjamestaylor. You are better off getting professional help.

jamtech338
27.01.10 at 12:47
Major Hayden,

THE REASON FOR INCREASING THE CONNECTIONS

I work in a call center and Monday we experienced a high volume of calls approx. 100 in the Que and Agents were getting an error stating that: 'MySQL connect ERROR: Too many connections' once the call volume died down normality was immediately restored.

So in the future should we get a high volume of calls then there will be enough connections to go around.

from the command this is what I did:
1. nano> Enter
2. max_connections = 200

what would be my next step?

Jamtech338

jamtech338
27.01.10 at 13:01
Okay Guys,

I am taking your advice, anyone here does this sort of thing as a professional/consultant?

I am now looking to hire someone.

Jamtech338

Arun
27.01.10 at 13:02
If you're using debian, open (use an editor you're comfortable with) /etc/mysql/my.cnf. Find the line starting with max_connections. Copy the line and comment it out - add a # at the beginning. Paste the line below the commented line and change the value or max_connections to something appropriate for your environment. Save the file. Restart mysql - /etc/init.d/mysql restart.

To see if the changes, login to MySQL monitor, and type

show variables like 'max_connections';

That should show you the new value.

jamtech338
27.01.10 at 13:13
Arun,

I am not using Debian in fact, I am using:

MYSQL version: Mysql Ver 14.12 Distrib 5.0.51a, for suse-linux-gnu (i686)

Jamtech338

Sam
27.01.10 at 13:46
jamtech338,

As Major says:
It's extremely important to find out why you need more connections. You may end up pushing your server into its swap memory and it will become highly unstable

Increasing the number of connections will require more resources to service. Unless you have a clear understanding of the consequences then this may be detrimental to your database server and/or application server performance.

It might be more appropriate, for example, to change the connection pooling settings of your call center software to, for example, terminate idle connections more quickly, or delay rather than refuse connections until the pool has idle connections.

In short: there is more involved here than making a configuration file change.

If the software that has the problem has a vendor I would call them in the first instance. If you don't have a maintenance contract, now might be the time to start one as the rates may be comparable to one-off consultancy fees and you'll get on-going support. Failing that, you need to hire someone to analyze the problem and suggest a solution. Whilst you could hire someone to do this remotely, experience says you're better off finding someone local to you and have them come in to do the work. That way you can also quiz/learn from them too.

I'm sorry you're not getting the quick fix you had hoped for, but having been in this game for nigh-on a decade I can confidently say that understanding the problem in detail is preferable to replacing one problem with another with a "simple" change.

Best of luck with your endeavors.

Sam.

Arun
27.01.10 at 23:19
One more thing to be concerned about is the wait_timeout. It's 8hrs by default IIRC. Reduce it to a sane value according to your needs, else you'll find a lot of lingering connections that'll eat up your max_connections.

Arun
27.01.10 at 23:29
Jamtech338,

Google tells me that on Suse, my.cnf resides in /etc, so edit /etc/my.cnf and change the values. Don't change things blindly as it'll eat up resources on your server quickly and lead to thrashing. Use something like mysql-tuner.pl (google for it) to see if the values are safe. But mysql has to be running for at least a week, before the script can tell you if the current values are safe.
Click to expand...
Source: Increase MySQL connection limit | Racker Hacker

gobeyond View Beta Profile
WHT Addict

Join Date: Oct 2005
Location: Minneapolis, MN
Posts: 146
MySQL Concurrent Connection Limit
Hi,

I know the Apache has a default 150 concurrent connections limit. Is there such a limit for MySQL server? If so, how to increase it? Thanks!

__________________
Minneapolis SEO & Web Design
Web Design, Website Development - Minneapolis, St Paul, Minnesota
Minneapolis SEO Search Engine Optimization Minnesota
Search Engine Optimization (SEO), Internet Marketing - Minneapolis, St Paul, Minnesota


Sponsored Links
SSL Certificates 4 Less
Certs 4 Less Offers Discounted SSL Certificates from VeriSign, RapidSSL, GeoTrust, Thawte.

Get a FREE Web Hosting Quote today!
Get FREE custom web hosting quotes from up to 4 hosting companies with no obligation! Save time and money finding web hosting. Get a free quote now!

110MB.com - Free & Premium Web Hosting
Searching for a web hosting provider? Look no further! Over 900,000 websites are hosted with 110mb.com! Use coupon code "SAVE10" to save 10% on premium hosting!

#2
09-12-2006, 10:05 AM
david510 View Beta Profile
At the Cliff


Join Date: Oct 2004
Location: India
Posts: 4,245
In the /etc/my.cnf, you can set the value like this.

max_connections=500
max_user_connections=12
Max connections is the maximum connections that can be made to the Mysql server and max_user connection is the maximum a user can make to the Mysql server.
Any thing else dude?


#3
09-12-2006, 11:05 AM
gobeyond View Beta Profile
WHT Addict

Join Date: Oct 2005
Location: Minneapolis, MN
Posts: 146
Hi David,

Thanks for the help! I looked at the my.cnf file and it doesn't have these two entries:

max_connections
max_user_connections

Should I add them? What would be the default value if I don't add these entries?

__________________
Minneapolis SEO & Web Design
Web Design, Website Development - Minneapolis, St Paul, Minnesota
Minneapolis SEO Search Engine Optimization Minnesota
Search Engine Optimization (SEO), Internet Marketing - Minneapolis, St Paul, Minnesota


Sponsored Links
SSL Certificates 4 Less
Certs 4 Less Offers Discounted SSL Certificates from VeriSign, RapidSSL, GeoTrust, Thawte.

Get a FREE Web Hosting Quote today!
Get FREE custom web hosting quotes from up to 4 hosting companies with no obligation! Save time and money finding web hosting. Get a free quote now!

110MB.com - Free & Premium Web Hosting
Searching for a web hosting provider? Look no further! Over 900,000 websites are hosted with 110mb.com! Use coupon code "SAVE10" to save 10% on premium hosting!

#4
09-12-2006, 02:00 PM
besty View Beta Profile
Web Hosting Master

Join Date: Mar 2006
Posts: 644
Quote:
Originally Posted by gobeyond
Hi David,

What would be the default value if I don't add these entries?

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

__________________
Live Your DreamZ
~Besty


#5
09-12-2006, 11:52 AM
david510 View Beta Profile
At the Cliff


Join Date: Oct 2004
Location: India
Posts: 4,245
yeah, You can add them or it will be using the default values.


#6
09-15-2006, 08:35 AM
gobeyond View Beta Profile
WHT Addict

Join Date: Oct 2005
Location: Minneapolis, MN
Posts: 146
In my.cnf file, is this what I need to set these variables:

set-variable = max_connection=500
set-variable = max_user_connections=10

Does it matter where I put these two lines? In my.cnf file, there are:
[mysqld]

[mysql.server]

[mysqld_safe]

Not sure where I should put it. Thanks!

__________________
Minneapolis SEO & Web Design
Web Design, Website Development - Minneapolis, St Paul, Minnesota
Minneapolis SEO Search Engine Optimization Minnesota
Search Engine Optimization (SEO), Internet Marketing - Minneapolis, St Paul, Minnesota


#7
09-15-2006, 09:10 AM
mellow-h View Beta Profile
Web Hosting Master

Join Date: Nov 2005
Location: /etc/fstab
Posts: 975
You need to add the two line after [mysqld]. Like this:

Code:
[mysqld]

set-variable = max_connection=500
set-variable = max_user_connections=10

__________________
█ Mellowhost - Affordable Cpanel and WHM Reseller Hosting
R1Soft, RVSitebuilder, RVSkin, Softaculous, Fantastico, Domain Reseller and many more ...
█ Learn Hosting


#8
09-16-2006, 03:33 PM
Bilco105 View Beta Profile
Web Hosting Master

Join Date: Oct 2002
Location: Manchester, UK
Posts: 1,164
Quote:
Originally Posted by hadrick
You need to add the two line after [mysqld]. Like this:

Code:
[mysqld]

set-variable = max_connection=500
set-variable = max_user_connections=10
Without the set-variable.

__________________
Rob Greenwood
RedHat Certified, Unix Consultant
Rob Greenwood - United Kingdom | LinkedIn


#9
07-02-2007, 10:08 PM
gila View Beta Profile
Newbie

Join Date: Nov 2006
Posts: 15
Hi, i try set to max_user_connections=1800 but that became my server down, got any solutions for it?

Thank you.


#10
07-02-2007, 11:24 PM
Crucial Web Host View Beta Profile
Corporate Member


Join Date: Apr 2006
Location: Phoenix, AZ, USA
Posts: 515
Quote:
Originally Posted by gila
Hi, i try set to max_user_connections=1800 but that became my server down, got any solutions for it?

Thank you.
That's a lot of connections per user.

Are you sure that's what you want to do?

One user will easily bring your system to a quick halt.

But you guys are probably right - just not the way I would do it.

__________________
█ Crucial Web Hosting
█ Magento Professional Partner specializing in business class web hosting
█ Join our new Developer Network and check out the updated Site Showcase!


#11
07-03-2007, 02:21 AM
gila View Beta Profile
Newbie

Join Date: Nov 2006
Posts: 15
Hi,my server OS is window server 2003 and got 500-900 user online at the same time. The website often got max_user_connection problem,i also dunno how to slove it.....

Can anyone give me suggestion?

Thank You.


#12
07-04-2007, 01:44 AM
MaximSupport View Beta Profile
Web Hosting Guru

Join Date: Feb 2006
Location: Pakistan/India/USA
Posts: 322
Re:
Dear gila,

I am sure you are using single user to connect your website. For this purpose you need to remove max_user_connections from your MySQL Configuration File and restart MySQL. If you use max_user_connections it will not allow MySQL User to use more then described connections.

e.g

max_user_connections = 10

It will not allow 11th connection from same MySQL User.

Best Regards.

__________________
Tom F - Red Hat Certified Technician & Red Hat Certified Engineer
Maxim Support - Hosting Solutions & Server Management
Email : tom@maximsupport.com
Web : http://www.maximsupport.com
Click to expand...

Source: MySQL Concurrent Connection Limit - Web Hosting Talk

Note: quotes contain ads and links to other sites, to be edited.
 
You must log in or register to reply here.

Similar Discussions

Back
Top Bottom
Forums
New posts
Post thread
Back