MySQL 'max_user_connections' exceeded

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Suggested Hosts
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
Pseudonym
Registered User
Posts: 173
Joined: Mon Jan 26, 2004 8:37 am

MySQL 'max_user_connections' exceeded

Post by Pseudonym » Fri Jul 18, 2008 3:50 am

Over the last 6-8 weeks, my website has been having the following error message pop up periodically...
General Error
SQL ERROR [ mysql4 ]

User mywebsite_phpbb already has more than 'max_user_connections' active connections [1203]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.
Initially, the webhost company helpdesk, said it was someone else. Then they said it was me. They turned on a couple of SQL query logs, but couldn't see anything suspicious.

This is the latest from them:
I can't see any obvious queries in the log that would cause your tables to be locked and your connections used up. The number of user connections is limited to 8. As this is shared hosting, you may like to consider getting a Virtual Private Server (VPS). A VPS would allow you to configure MySQL so you could support a greater number of maximum connections.

We'll continue to monitor the MySQL 5 server and I'll let you know if we track anything down.


Now, as far as I'm aware, there's no reason for this to happen. I don't use persistent connections. It happens randomly - not during particularly high traffic times. According to my stats mod, when it died just now there were "20 users online :: 9 registered, 2 hidden and 9 guests (based on users active over the past 15 minutes)"


This is a phpBB2 board, upgraded to 3.0.1.

It has the following mods installed:
Genders - v1.0.0
http://www.phpbb.com/community/viewtopic.php?t=736135

Welcome PM on First Login – v1.0.0
http://www.phpbb.com/community/viewtopi ... &sk=t&sd=a

Prime Post Revisions – v0.0.4
http://www.phpbb.com/community/viewtopi ... &sk=t&sd=a

'Prime Trash Bin' Version 0.2.17
http://www.phpbb.com/community/viewtopi ... 0&t=571551

[BETA] Video BBCODE (Youtube, GVideo, DailyMotion,Etc) – v0.1.0
http://www.phpbb.com/community/viewtopi ... &sk=t&sd=a

Activity Stats – v1.0.0, with order-by-caps patch applied (see topic below)
http://www.phpbb.com/community/viewtopi ... &sk=t&sd=a

[BETA] Prime Ban to Group v0.0.1a
http://www.phpbb.com/community/viewtopi ... &sk=t&sd=a

Plus a lot of my own modifications. Any SQL queries go through the proper phpBB functions though.


I don't know where to go next. Any suggestions?

User avatar
Phil
Former Team Member
Posts: 10403
Joined: Sat Nov 25, 2006 4:11 am
Name: Phil Crumm
Contact:

Re: MySQL 'max_user_connections' exceeded

Post by Phil » Fri Jul 18, 2008 4:47 am

Your host stated that there is a maximum of 8 users running queries at a time -- this is a rather low limit. The error simply means that more than 8 users have attempted to query the database at the same time ;) (e.g. loading a page). The solution for this is to ask your host to raise the limit, but as they seem to indicate that they've no intention of doing this, your best bet would be to find a new host.
Moving on, with the wind. | My Corner of the Web

bbrunnrman
Registered User
Posts: 80
Joined: Sun Dec 24, 2006 9:19 pm

Re: MySQL 'max_user_connections' exceeded

Post by bbrunnrman » Sat Jul 19, 2008 5:45 am

While the setting of max_user_connections = 8 may seem small, it was probably quite adequate for your phpBB2 board. This kind of error happens to a lot of sites after upgrading from phpBB2 to phpBB3, and nobody seems to be really interested in solving it. It happened in my site, and I've posted a detailed analysis in response to a previous thread at http://www.phpbb.com/community/viewtopi ... 5#p6134185 . If anybody is seriously interested in attacking the problem, my observations in that earlier post may be a useful starting point. (Meanwhile on our site, we have, for now, reverted to phpBB2. And since I'm not holding my breath waiting for somebody to make phpBB3 robust enough to run stably on hosts like ours, I think we'll probably try changing our host before trying the upgrade to phpBB3 again.)

Pseudonym
Registered User
Posts: 173
Joined: Mon Jan 26, 2004 8:37 am

Re: MySQL 'max_user_connections' exceeded

Post by Pseudonym » Mon Jul 21, 2008 10:58 am

Thanks for the replies, guys. bbrunnrman, thanks for your thoughts - however we've invested so much time customising phpBB3 there's no way we could roll back to v2 now!

After having terrible trouble with the problem a few weeks ago, it seemed to disappear for about a week. Then over the weekend it popped back up again (hence my support request here). When I spoke to my webhost's helpdesk today, they told me this weekend's issue was "network traffic related". So it's kind of hard to tell what's my problem and what's theirs.

Hopefully I won't have to move to a VPS. Apart from this issue, I'm pretty happy with the current setup. Also, I'm a programmer (sort of), not a Linux server admin, and I'm much happier to leave that sort of thing to the experts. Still, I guess that's a conversation I need to have with my webhosts.

The only thing is, I've had more people online before, and had no issue. That makes me wonder: Is it really just a matter of throwing more capacity at the problem? Or is increasing the max_user_connections value just applying a band-aid fix to the underlying issue?

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: MySQL 'max_user_connections' exceeded

Post by digbydo » Wed Aug 20, 2008 6:14 am

We are having similar problems, hence my search for the solution and noticed your posts here..

We converted to 3 in June and are on 3.0.1 / PHP 5.2.5 / Mysql 4.1.2 (I think - not sure where to look but in php info under mysql it says client api version) AND WE ARE ON VPS. MySQL is configured for 250 connections - maximum users so far 37. We still have problems :(

Hardly any mods installed:

Forum Sponsors (not used yet)
Ad Management 1.0.2 (not activated yet)
both working fine on test server (which is different to the live one)

SO:
First we installed http://deflate.medialayer.com/ on the advice given in another thread (sorry can't find this whilst posting... search my posts under user profile thing if you need to).
It works great! as I said we have VPS and installed this solution server wide. My technical assistance tells me:
The script is set to receive email alerts when banning IP's, but so far seems to be working just fine just yesterday banned 3-5 IP's with more then 150 connections which were attacks via apache.
"Yesterday" was about 5 days ago. All running smoothly :)


THEN:
Last night same error: too many connections! Board is down for a couple of hours whilst I contact technical and they reply:
You barely had any connection to your server when this happened (~10 connections), again Mysql has 250 tables in Locked state and this caused the full connection + load on our hardware node .. since we can exclude an attack now all it's left is the website scripts ... it needs optimizing/changing or contacting their developers and asking why their script locks tables as mysql is unable to free them as long the script keeps them in lock state.

In the light of this information I am now searching this board for a solution and/or similar problems. Obviously a prod in the right direction from someone on the development team/admin or more technical knowledge than ourselves would be greatly appreciated :)

Cheers Team!

eboehnisch
Registered User
Posts: 9
Joined: Sat Aug 23, 2008 12:12 am

Re: MySQL 'max_user_connections' exceeded

Post by eboehnisch » Sat Aug 23, 2008 1:38 am

Hi everyone,

We have the same problem. Since switching to phpBB3 we reach the limit of our MySQL server share pretty often, something we never had before with phpBB2. Does phpBB3 use 'pconnect's more often? When the MySQL server goes down our host provider says that there are many 'locked' queries from phpBB that hook up the server.

Eric.

bbrunnrman
Registered User
Posts: 80
Joined: Sun Dec 24, 2006 9:19 pm

Re: MySQL 'max_user_connections' exceeded

Post by bbrunnrman » Sat Aug 23, 2008 5:03 am

eboehnisch,

As far as I know, neither phpBB3 nor phpBB2 ever uses the 'pconnect' method. Also, phpBB3 always closes its database connections--assuming that the scripts terminate normally. My analysis indicates that the problems are triggered by random internal server errors. When such errors occur, the scripts fail to terminate normally, so may leave open database connections.

Still, this isn't the whole story. On our site (which I've reverted to phpBB2 for now), I find that random internal server errors occur in in phpBB2 as well as phpBB3. However, these errors are fatal (i.e., bring down the board with MySQL errors) only in phpBB3.This suggests that the internal server errors leave open database connections in phpBB3 but not in phpBB2.

See my detailed analysis at http://www.phpbb.com/community/viewtopi ... 5#p6134185 and more recent comments at http://www.phpbb.com/community/viewtopi ... 5#p6737705

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: MySQL 'max_user_connections' exceeded

Post by digbydo » Sat Aug 23, 2008 12:24 pm

The logs from the latest outage shows 250 locked queries, almost every one of them similar to this:
Query | 2887 | Locked | SELECT p.post_id
FROM phpbb_posts p
WHERE p.topic_id = 15317
AND p.post_approved = 1


Over 200 identical (except for topic_id number) lines in the log.

Any ideas anyone?
Full log available to any support member that cares to assist...

Thanks.

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: MySQL 'max_user_connections' exceeded

Post by digbydo » Sun Aug 24, 2008 2:05 pm

ok, so I've been asked to bump a topic if I didn't receive an answer within 6 hours. I don't see the same bump facility on here (down at the bottom) that I've got on our standard forum so what or where to next?

This problem needs resolving.....

BUMP

User avatar
Phil
Former Team Member
Posts: 10403
Joined: Sat Nov 25, 2006 4:11 am
Name: Phil Crumm
Contact:

Re: MySQL 'max_user_connections' exceeded

Post by Phil » Sun Aug 24, 2008 3:44 pm

As I mentioned earlier, this sort of thing happens whenever, in your case, 8 concurrent users are trying to query the database -- if one post is particularly popular, the same query can easily lock it up ;) Your best bet is to probably contact your host about it.
Moving on, with the wind. | My Corner of the Web

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: MySQL 'max_user_connections' exceeded

Post by digbydo » Mon Aug 25, 2008 5:39 am

Thanks for your input iWisdom.

In our case the number of connections is not limited = we are now set at 250 = and can increase as we wish.
We started at 100, went to 200, now 250. The board crashes less frequently but still crashes. The answer, surely, is NOT to increase further but to find out why the code is not terminating queries correctly.

We are trying to get to the bottom of the queries being locked. Our maximum number of users online according to the board is 37 including guests.

More info here: http://www.phpbb.com/community/viewtopi ... &t=1140905

naive
Registered User
Posts: 43
Joined: Sun Apr 15, 2007 3:32 pm

Re: MySQL 'max_user_connections' exceeded

Post by naive » Fri Sep 05, 2008 3:02 pm

I have the same problems. It occurred several times a day.
I tried to repair mysql tables and fixed the overheads using phpmyadmin. After that, this kind of errors stop to appear again.
I have to observe for a few days more.

sickmusic
Registered User
Posts: 24
Joined: Fri Oct 25, 2002 1:22 pm
Location: London, UK
Contact:

Re: MySQL 'max_user_connections' exceeded

Post by sickmusic » Thu Sep 11, 2008 12:47 pm

My site has been down for the last 3 weeks with the same error message.

Code: Select all

SQL ERROR [ mysql4 ]

User username already has more than 'max_user_connections' active connections [1203]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.
I have been browsing the numerous threads regarding the issues and am dissappointed with the way its been handled.

It clearly is a problem with the new system, as I have never had this problem with phpbb2. I believe this point needs to be taken seriously and not so easily dismissed by the support team.

Its not an issue regarding the number of maximum users set by the host, as its got to a point on my board where it is very unlikely that I'd be getting more than 50 hits constantly for the last few weeks. Id like to think we're a popular site, but to increase in popularity by such a large percentage only by upgrading to phpbb3 is very unlikely :lol: Well when looking at my stats, its not the case at all, and I highly doubt it is the case with the other loyal members who have reported this problem.

I personally would not have an idea where to look in the code, or what to look out for in my server logs.. But there have been several members who have provided an in depth analysis of what they perceive the problem to be.

Again, I do not feel they have been taken seriously or treated fairly.

I understand that phpbb is open source, and what not, etc etc etc... but I don't recall ever having issues of passing blame between hosts and phpbb comunity in the good ole phpbb2 days.. especially since in my honest opinion its a bug in phpbb3.

If I can be of any assistance regarding my setups, or if any of the support team want's full access to my cpanel, db, ftp.. I am happy to provide the logins.

User avatar
Phil
Former Team Member
Posts: 10403
Joined: Sat Nov 25, 2006 4:11 am
Name: Phil Crumm
Contact:

Re: MySQL 'max_user_connections' exceeded

Post by Phil » Fri Sep 12, 2008 12:43 am

Again, this is a server-related setting, and no fault of phpBB's. The only possible cause for this, given what you've said, is the script is terminating early, and is not able to close it's database connections. As the default MySQL time-to-live is 3600 seconds (one hour), 50 connections within this hour that are not terminated will cause the limit to be hit -- if more people keep attempting to view pages and the script terminates, it will essentially be stuck in an eternal state of this issue.

That being said, I've yet to reproduce this issue on numerous different setups, and given the infrequency of it it seems as if it may be a host issue, instead of a phpBB issue. As I've said before, there's really nothing you can do besides contact your host -- if nothing else, they may be able to give you more information as to what is causing the issue via MySQL server logs.
Moving on, with the wind. | My Corner of the Web

eboehnisch
Registered User
Posts: 9
Joined: Sat Aug 23, 2008 12:12 am

Re: MySQL 'max_user_connections' exceeded

Post by eboehnisch » Fri Sep 12, 2008 5:49 am

iWisdom wrote:As I've said before, there's really nothing you can do besides contact your host -- if nothing else, they may be able to give you more information as to what is causing the issue via MySQL server logs.
And this is part of the problem, e.g., for me. We are using a shared host for our MySQL database and our host is unwilling to reset the whole server every two weeks when this problem strikes again. We also used phpBB2 for years without any problems at all on the very same setup. But, when the problem occurs I will ask them for the MySQL server logs and post them here.

Eric.

Locked

Return to “[3.0.x] Support Forum”