MySQL Bugs: #8350: Limiting Account Resources does not work

Bug #8350	Limiting Account Resources does not work
Submitted:	7 Feb 2005 1:39	Modified:	27 Mar 2005 15:11
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.22	OS:
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
I'm forwarding this bug report as-is from the Debian bug reporting system:
------------------------
From: "Rene Konasz" <oe4@gmx.at>

Hello,

I tried limiting the account resources according to 
http://dev.mysql.com/doc/mysql/en/User_resources.html , also in phpmyadmin 
setting the limits using the interface.

Case A (for testing):

WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5

The limit is set in db mysql, table user. After the limit is reached, I get 
on a new connect:

ERROR 1226: User 'test' has exceeded the 'max_connections' resource (current 
value: 5)

Seems ok, but after 1 hour with no logins the access ist still not possible, 
also after 2, 3, ....10 hours. The same for max_questions, max_updates limit 
not tested manually. Only reloading the user table reactivates the account.

Case B:

WITH MAX_QUERIES_PER_HOUR 90000
MAX_UPDATES_PER_HOUR 30000
MAX_CONNECTIONS_PER_HOUR 10000

The server is running for 6 hours after a complete restart and mysqld only 
had about 1000 connections, 15000 questions and 1200 updates/inserts with 
~10 users over night. One account is locked for reaching the limits above, I 
had no possibility to wait 1 or more hours for testing, so reloaded the user 
table.

Best regards, René

How to repeat:
-

Suggested fix:
-

Same here with max queries per hour limit

OS: Red Hat Enterprise server 3
Release: 4.0.23 (binary rpms)

Verified with 4.0.24-debug-log

The per-hour connection limit counter was never being reset, so it was acting effectively as a hard per-user connection limit.

Fix pushed, will be in 4.1.11.

Noted in 4.1.11 changelog.

Forwarding from http://bugs.debian.org/285044 from "Rene Konasz" <oe4@gmx.at>:
Please comment.
-----------------------------------------
thank you for forwarding my report to upstream, after upgrading to 
mysql-server 4.0.24-2 MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR work as expected and both reset after an hour, but MAX_CONNECTIONS_PER_HOUR still 
does not reset even after a couple of hours. I have no problem with this as the both working limits are enough for my purposes. I also found no remark in the mysql.com changelog for 4.0.24 (and 4.0.25 yet) mentioning a change concerning the limits, seems this has only been fixed in 4.1.11.
--------------------------------------------

Because this was not deemed a critical bug, this was only fixed in the 4.1 (and later) branch.

The patch was originally prepared against 4.0, however, and can be downloaded from http://lists.mysql.com/internals/21679