MySQL Bugs: #41147: main.user_limits test fails with user exceeding the 'max

Bug #41147	main.user_limits test fails with user exceeding the 'max_questions' resource
Submitted:	1 Dec 2008 9:38	Modified:	11 Feb 2009 3:52
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	6.0-bzr	OS:	Any
Assigned to:	Matthias Leich	CPU Architecture:	Any
Tags:	pushbuild, test failure

Description:
Example test output from mysql-6.0 bzr branch, 2008-11-26 15:58:00, sles10-ia64-a-2 platform:

--- --- ---

main.user_limits               [ fail ]

mysqltest: At line 30: query 'select * from t1' failed: 1226: User 'mysqltest_1' has exceeded the 'max_questions' resource (current value: 2)

The result from queries just before the failure was:
drop table if exists t1;
create table t1 (i int);
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2;
flush user_resources;
select * from t1;
i
select * from t1;

More results from queries before failure can be found in /dev/shm/var-ps_stm_threadpool-141/log/user_limits.log

Stopping All Servers
Restoring snapshot of databases

--- --- ---

Possibly related bugs: 

Bug#36970 (Closed, main.max_user_connections_func.test fails in pushbuild)
Bug#23921 (Closed, random failure of user_limits.test)
Bug#33771 (Closed, -mysqld=--thread-handling=pool-of-threads causes the tests to fail)

How to repeat:
Repeatable when thread pooling is enabled (configure options include --with-libevent):

./mtr --mysqld=--thread-handling=pool-of-threads user_limits

The failure occurs in 5.1 as well.

mysql-6.0-bugteam last changeset Jan 2009
./mysql-test-run.pl user_limits
....
TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.user_limits               [ pass ]            109
-------------------------------------------------------
...

./mysql-test-run.pl --mysqld=--thread-handling=pool-of-threads user_limits
...
TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.user_limits               [ fail ]

mysqltest: At line 30: query 'select * from t1' failed: 1226: User 'mysqltest_1' has exceeded the 'max_questions' resource (current value: 2)

So the error around line 30 of the scripts
seems to be caused by the server startup option
"--thread-handling=pool-of-threads".

Neither 
  http://dev.mysql.com/doc/refman/6.0/en/user-resources.html
not
  http://dev.mysql.com/doc/refman/6.0/en/connection-threads.html
mention an impact of "pool-of-threads" on the resource
limit management in case of max_questions.

The second problem
   User 'mysqltest_1' has exceeded the 'max_user_connections'
   resource (current value: 2)"
is most probably caused by heavy load on the testing which
leads to too slow completion of line 107 "disconnect muc1;".
See also 
  http://dev.mysql.com/doc/refman/6.0/en/user-resources.html
...
For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections allowed to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection will once more be allowed.

Proposed solution:
1. Bad effect 1 is caused by either a server or 
   a documentation bug which needs to be reported.
 . Anyway "user_limits" needs an adjustment.
2. Bad effect 2 is a weakness within the test.
3. I will also look more thorough on the test
   around session handling (connect/disconnect)
   and fix any additional weakness I detect.

Places to fix per xref output (search pattern
user_limits.test and main.user_limits.test):
5.0/5.1/6.0
grep -i line xref.pl.html | sort -u

1. At line 100: query 'connect  muc2, localhost, mysqltest_1,,' failed:
   1226: User 'mysqltest_1' has exceeded the 'max_user_connections'
   resource (current value: 2)
2. At line 108: query 'connect  muc3, localhost, mysqltest_1,,' failed:
   1226: User 'mysqltest_1' has exceeded the 'max_user_connections' resource 
  (current value: 2)
3. At line 140: query 'connect  muca1, localhost, mysqltest_1,,' failed:
   1203: User mysqltest_1 already has more than 'max_user_connections'
   active connections
4. At line 143: query 'connect  muca2, localhost, mysqltest_1,,' failed:
   1203: User mysqltest_1 already has more than 'max_user_connections'
   active connections
5. At line 97: query 'connect  muc1, localhost, mysqltest_1,,' failed:
   1226: User 'mysqltest_1' has exceeded the 'max_user_connections'
   resource (current value: 2)

6.0
At line 30: query 'select * from t1' failed: 1226: User 'mysqltest_1' has exceeded the 'max_questions' resource (current value: 2)

I will start the fix in MySQL 5.0 because of the following reasons:
- The code of user_limits.test is equal in 5.0 and 5.1
  = They share the same weaknesses.
- The last PB failure in 5.0 occured ~ 2006 (~ 12 times,
  numbers are not accurate) is most probably caused by
  - lower frequency of PB runs in 5.0
  - maybe influence of server properties
- Keeping the code in sync between 5.0 and 5.1 makes
  any future bug fixes in user_limits.test starting
  with 5.0 easier

The problem around "max_user_connections" was fixed
with the patch for
   Bug#23921 random failure of user_limits.test
pushed into 5.1 and 6.0 around Aug 2008.
It was not fixed in 5.0 but there the test is disabled.
This (disabling in 5.0 + fix in 5.1 and 6.0) explains
why we have no PB failure of user_limits with
"max_user_connections" in 5.0,,5.1, 6.0 since Sep 2008.
Therefore I will change the target version to 6.0.

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/commits/64252

2983 Matthias Leich	2009-01-28
      Fix for Bug#41147 main.user_limits test fails with user exceeding the 'max_questions' resource
      + replace error numbers by error names

Ok to push.

Added a note to Bug#42384 as a reminder that they must alter user_limits.test once that bug is fixed (just as an extra precaution)

Pushed to mysql-6.0-bugteam

Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:davi.arnaut@sun.com-20090129184519-mgp2vr1rna9nk8li) (merge vers: 6.0.10-alpha) (pib:6)

Test case changes. No changelog entry needed.