MySQL Bugs: #20690: Flush privileges and max_user

Bug #20690	Flush privileges and max_user_connections
Submitted:	26 Jun 2006 7:13	Modified:	27 Jun 2006 17:12
Reporter:	Andrea Pantaleoni	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
When I change the value of max_user_connections in the table mysql.user the limitation of the max number of connections is not correct.

How to repeat:
1)CREATE A USER testuser
2)open a connection as testuser: you log in.
3)open a connection as root
3)As Root: update mysql.user set max_user_connections=1 where user='testuser';
4)As root: flush privileges;
5)try to open a new connection as testuser:unexpectedly you log in(testuser already has a connection opened)!!
6)Now try to open again a new connection as testuser:unexpectedly you get a error message the user testuser can open just one connection!!

Thank you for a problem report. I think, this is not a bug. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/user-resources.html):

"Resource-use counting takes place when any account has a non-zero limit placed on its use of any of the resources.
...
If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued."

First connect was performed BEFORE limit was set, so, it was BEFORE accounting was started. After you set limit everything worked as expected.