Bug #49939 max_user_connections unclear
Submitted: 26 Dec 2009 10:32 Modified: 25 Feb 2010 17:13
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1 - likely any recent OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[26 Dec 2009 10:32] Peter Laursen
Description:
"max_user_connections" is referred to in two contexts: 1) in context of GRANT syntax (where it inserts to mysql.user) I believe and and as a SESSION|GLOBAL server varaible.

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_user_connec...

The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means “no limit.” 

This variable has both a global scope and a (read-only) session scope. The session variable has the same value as the global variable unless the current account has a nonzero MAX_USER_CONNECTIONS resource limit. In that case, the session value reflects the account limit.

http://dev.mysql.com/doc/refman/5.1/en/user-resources.html

One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a nonzero value. However, this limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. In addition, this method is strictly global, and does not allow for management of individual accounts. Both types of control are of interest to many MySQL administrators, particularly those working for Internet Service Providers.

I cannot make sense out of it.  To me the two pages have contradictory content. 

How to repeat:
See above.

Suggested fix:
Not sure if it is only a documentation issue. Maybe only the same name is used for different things?
[26 Dec 2009 10:33] Peter Laursen
I meant: "max_user_connections" is referred to in two contexts: 

1) in context of GRANT syntax (where it inserts to mysql.user I believe)
2) and and as a SESSION|GLOBAL server variable.
[26 Dec 2009 10:48] Peter Laursen
btw: why is there both a 'max_user_connections' and a 'max_connections' in mysql.user? 

The questions that I am not able to answer are among others:

Which one controls what? 
How do they inter-operate with SET .. ?
Only 'max_user_connections' is a variable (and can be SET) and 'max_connections' is not?
[26 Dec 2009 12:16] Peter Laursen
Also I found this link:
http://dev.mysql.com/doc/refman/5.1/en/grant.html

"The MAX_USER_CONNECTIONS count option limits the maximum number of simultaneous connections that the account can make. If count is 0 (the default), the max_user_connections system variable determines the number of simultaneous connections for the account."

It describes the situation "If count is 0" - but what "If count is not 0". Is the *implicit* meaning here that then it will have no effect to SET it? Will it raise an error or warning then? Also the other pages linked to do not mention that "If count is not 0" then SET has no effect (if it has not and if it should be understood like that).

Additionally I think it is inconsistent that there is a 'max_user_connections' variable but not a 'max_connections' or 'max_connections_per_hour' variable.
[26 Dec 2009 12:20] Peter Laursen
If I understand correctly now this is one more example of 'information fragmention' in MySQL documentation.
[26 Dec 2009 14:17] Valeriy Kravchuk
Peter,

Let me try to explain. There is a server variable, max_user_connections, "The maximum number of simultaneous connections allowed to any given MySQL account." It sets the highest possible value for the number of concurrent connections for every user account that does NOT have this resource limit set EXPLICITLY with a GRANT (MAX_USER_CONNECTIONS resource limit). So, by default any user can not open more than this number of connections concurrently.

Now, for individual user, say, user2@localhost, you can redefine this highest possible value with GRANT (as shown http://dev.mysql.com/doc/refman/5.1/en/user-resources.html).

Let's assume we have MySQL server with 3 user accounts, user1@localhost, user2@localhost, user3@localhost. Global server variable max_user_connections is set to 5. For user2@localhost this resource limit is set to 100 with GRANT. For user3@localhost this resource limit is set to 1 with GRANT.

As a result, you can have not more than 3 concurrent connections of user1@localhost, up to 100 concurrent connections of user2@localhost and at most one connection of user3@localhost.

What is NOT clear in the above, and what from the above contradicts any our manual page or can not be inferred from our manual?
[26 Dec 2009 14:19] Valeriy Kravchuk
Please, replace 3 with 5 in my previous comment. My mistake, not related to any deficiencies in our manual, even if you see them there.
[26 Dec 2009 15:19] Peter Laursen
This is also what conclusion I reached after reading all the 3 manual pages I linked to (several times). I find it very hard to read. I had to experiment with a user account to be sure I understood how different settings interacted in all situations.

What is not clear is in my opinion this 
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_user_connec...
.. does not tell clearly enough how (global) max_user_connections interacts with settings for users in mysql.user table.

You may say that this "..unless the current account has a nonzero MAX_USER_CONNECTIONS resource limit .." is the explanation, but the term 'resource limit' is a term I do not find elsewhere in manual except for http://dev.mysql.com/doc/refman/5.1/en/user-resources.html(not here http://dev.mysql.com/doc/refman/5.1/en/grant.html for instance) and I was not able to understand in the first place that 'resource limit' should be understood as GRANTs-WITH for the user). A link to GRANT-page and/or Limiting Account Resources-page or another term maybe?  

I also think that this page: http://dev.mysql.com/doc/refman/5.1/en/user-resources.html could be much more structured and also have links to other relevant pages.

You can do as you like with this.
[26 Dec 2009 15:44] Valeriy Kravchuk
These parts of our manual can surely benefit from more cross-references and implementation of other your suggestions presented in the last comment.
[23 Feb 2010 21:26] Paul DuBois
In answer to an earlier question:

mysql.user.max_connections stores the MAX_CONNECTIONS_PER_HOUR value from GRANT.

mysql.user.max_user_connections stores the MAX_USER_CONNECTIONS value from GRANT.

Neither of these can be set with the SET statement.

It looks like one thing that may have contributed to the confusion is that I had a few instances of max_connections and max_user_connections linked to descriptions for the system variables of those names, when the references really were to the mysql.user columns of those names.
[25 Feb 2010 17:13] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Revised sections:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_user_connec...
http://dev.mysql.com/doc/refman/5.1/en/user-resources.html
http://dev.mysql.com/doc/refman/5.1/en/grant.html