Bug #22863 Unable to set MAX_USER_CONNECTIONS with GRANT USAGE.
Submitted: 30 Sep 2006 19:56 Modified: 1 Oct 2006 5:49
Reporter: Alan Frisch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.20 OS:Linux (CentOS 4.1)
Assigned to: CPU Architecture:Any

[30 Sep 2006 19:56] Alan Frisch 
Description:
Attempting to limit MAX_USER_CONNECTIONS using the following command:

GRANT USAGE ON *.* TO 'user'@'localhost' WITH MAX_USER_CONNECTIONS 10;

...for a user results in the following error:

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_USER_CONNECTIONS 10' at line 1

This occurs if you attempt to specify specific DBs/tables as well.

How to repeat:
Using the above syntax, attempt to limit connections on an account with the GRANT command.
[30 Sep 2006 19:58] Alan Frisch 
The above command was copied verbatim from the MySQL documentation.
[30 Sep 2006 20:04] Bryan Taylor 
Setting MAX_USER_CONNECTIONS manually; the query is sucessful but allows more than the specified connections:

mysql>use mysql;
mysql>update user SET max_connections='2' where User='sysloguser';
Query OK, 1 row affected (0,00 sec)

term 1
#mysql -usysloguser -p
<enter pass>
mysql>

term 2
#mysql -usysloguser -p
<enter pass>
mysql>

term 3
#mysql -usysloguser -p
<enter pass>
mysql>

I Will provide more information if needed.
[30 Sep 2006 20:09] Paul DuBois 
Regarding previous comment: Directly updating
the grant tables has no effect without FLUSH
PRIVILEGES.
[30 Sep 2006 20:16] Benoit St-Jean 
You should have issued a FLUSH PRIVILEGES after changing the grants on this user.
[30 Sep 2006 20:27] Bryan Taylor 
Yes that did work after manually editing the mysql db, although the GRANT query does not work?
[30 Sep 2006 20:44] Paul DuBois 
For GRANT, the MAX_USER_CONNECTIONS option was implemented
in MySQL 5.0.3. If you are using MySQL 4.1.20, that option is
unavailable.

http://dev.mysql.com/doc/refman/5.0/en/grant.html
[1 Oct 2006 5:49] Valeriy Kravchuk 
So, it is not a bug, but a documented behaviour (no such clause in 4.1.x). Read http://dev.mysql.com/doc/refman/4.1/en/grant.html.