Bug #9947 inaccurate error messages for exceeding user resource limits
Submitted: 15 Apr 2005 23:49 Modified: 7 Aug 2005 1:15
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0 OS:Any (all)
Assigned to: Jim Winstead CPU Architecture:Any

[15 Apr 2005 23:49] Timothy Smith
Description:

A few problems:

1)  Error for exceeding MAX_CONNECTIONS_PER_HOUR resource is inaccurate.  It should say 'max_connections_per_hour' instead of 'max_connections':

ERROR 1226 (42000): User 'bogus' has exceeded the 'max_connections' resource (current value: 2)

2)  The error for MAX_USER_CONNECTIONS uses ER_TOO_MANY_USER_CONNECTIONS; instead, that error code should be deprecated, and it should use ER_USER_LIMIT_REACHED to be consistent with the other user resource limit errors.

3)  Actually, it looks like the code should be refactored a bit, since the MAX_USER_CONNECTIONS resource isn't handled in the USER_CONN struct - it seems odd that it is so different from the other per-user resources.  I mean, it does not use USER_CONN->user_resources.max_user_connections; instead it uses a global max_user_connections variable, which seems inconsistent and error-prone.

How to repeat:

mysql> grant usage on *.* to ''@localhost with max_connections_per_hour 2;
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for ''@localhost;
+----------------------------------------------------------------------+
| Grants for @localhost                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 2 |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

11:15 ~/m/41/m$ mysql -ubogus test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> Bye
11:16 ~/m/41/m$ mysql -ubogus test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> Bye
11:16 ~/m/41/m$ mysql -ubogus test
ERROR 1226 (42000): User 'bogus' has exceeded the 'max_connections' resource (current value: 2)
11:16 ~/m/41/m$ 

Suggested fix:
--- sql/sql_parse.cc    Fri Apr  1 10:55:32 2005
+++ /tmp/sql_parse.cc   Sat Apr 16 11:47:24 2005
@@ -322,6 +322,9 @@
       max_user_connections < uc->connections)
   {
     net_printf(&(current_thd->net),ER_TOO_MANY_USER_CONNECTIONS, uc->user);
+    net_printf(&current_thd->net, ER_USER_LIMIT_REACHED, uc->user,
+              "max_user_connections",
+              (long) max_user_connections);
     error=1;
     goto end;
   }
@@ -329,7 +332,7 @@
       uc->user_resources.connections <= uc->conn_per_hour)
   {
     net_printf(&current_thd->net, ER_USER_LIMIT_REACHED, uc->user,
-              "max_connections",
+              "max_connections_per_hour",
               (long) uc->user_resources.connections);
     error=1;
     goto end;
[28 Apr 2005 0:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24424
[28 Apr 2005 0:07] Jim Winstead
1) Fixed by patch.

2) This is intentional. Although this is a per-user limit, it is not configurable per-user. It's not a typical user limit in that regard, so the special error message makes sense.

3) For the same reason as 2, this is handled differently.

(Maybe it would make sense to open a feature request about allowing the connection limit per user, as opposed to the connection limit per hour per user, to be set as a user-specific limit.)
[2 May 2005 19:07] Jim Winstead
Fixed in 4.1.12 and 5.0.6.
[18 May 2005 2:07] Paul Dubois
Noted in 4.1.12, 5.0.6 changelogs.
[22 Jun 2005 17:09] Denis Sokolov
Problem not solved

Problem not only in "inaccurate error messages for exceeding user resource limits" but in inaccurate working of this limits!

When using MAX CONNECTIONS PER HOUR problem appear.

So, if MAX CONNECTIONS PER HOUR not null (we have
max_connections_per_hour= 14400) after some working:

User 'username' has exceeded the 'max_connections_per_hour' resource (current value: 40)

We have global max_user_connections=40

So it seems, that problem is that instead of "max_connections_per_hour" mysql using
global "max_connections"

Configuration:

bash-2.05b# /usr/local/libexec/mysqld -V
/usr/local/libexec/mysqld  Ver 4.1.12 for portbld-freebsd4.10 on i386 (FreeBSD port: mysql-server-4.1.12)
bash-2.05b#

How to repeat:

1) In /etc/my.cnf
max_user_connections=2

2) GRANT USAGE ON * . * TO 'username'@ 'localhost' WITH MAX_QUERIES_PER_HOUR 14400 MAX_CONNECTIONS_PER_HOUR 14400 MAX_UPDATES_PER_HOUR 14400 ;

3)make 2 connections you will see:

bash-2.05b# bash-2.05b# mysql -p -u username username
Enter password:
ERROR 1226 (42000): User 'username' has exceeded the
'max_connections_per_hour' resource (current value: 2)

Sorry for bad English
[26 Jun 2005 21:14] Timothy Smith
Re-opening due to new comment which requires verification.
[28 Jun 2005 1:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26465
[2 Aug 2005 2:23] Jim Winstead
Fixed in 4.1.14 and 5.0.11.
[7 Aug 2005 1:04] Mike Hillyer
Documented in 4.1.14 and 5.0.11 changelogs:

<listitem><para>
<literal>max_connections_per_hour</literal> setting was being capped by unrelated <literal>max_user_connections</literal> setting. (Bug #9947) 
</para></listitem>