MySQL Bugs: #23921: random failure of user

Bug #23921	random failure of user_limits.test
Submitted:	3 Nov 2006 8:39	Modified:	18 Aug 2008 17:16
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any (*)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	pushbuild, sporadic, test failure

Description:
See the following for an example of this test failure.

https://intranet.mysql.com/~knielsen/pb/getlog.pl?dir=mysql-5.1-new-maint&entry=cmiller@zi...

user_limits                    [ fail ]

Errors are (from /dev/shm/var-n_stm-7/log/mysqltest-time) :
mysqltest: At line 143: query 'connect  muca2, localhost, mysqltest_1,,' failed: 1203: User mysqltest_1 already has more than 'max_user_connections' active connections
(the last lines may be the most important ones)
Result from queries before failure can be found in r/user_limits.log

This test fails randomly.  Here are the test runs recorded in PushBuild where it fails:

mysql> select tfl_psh_tree tree, tfl_psh_name push, tfl_bld_platform platform, tfl_tst_type type from test_failure where tfl_name = 'user_limits' and tfl_text like "%already has more than 'max_user_connections' active connections%";
+---------------------+-----------------------------------------------+---------------+--------+
| tree                | push                                          | platform      | type   |
+---------------------+-----------------------------------------------+---------------+--------+
| mysql-5.1-new-maint | vshebordaev@ted.mysql.internal-20061011053311 | sol10-sparc-a | n_stm  | 
| mysql-5.1-new-maint | kpettersson@naruto.-20061011110827            | sapsrv2       | n_stm  | 
| mysql-5.0-maint     | msvensson@neptunus.none-20061023142045        | sol10-sparc-a | n_stm  | 
| mysql-5.0-maint     | rkalimullin@mysql.com-20061023102407          | maint1        | ps_stm | 
| mysql-5.1-new-maint | msvensson@neptunus.none-20061024181534        | sol10-sparc-a | ps_row | 
| mysql-5.1-new-maint | msvensson@neptunus.none-20061025093641        | sol10-amd64-a | ps_row | 
| mysql-5.1-new-maint | igalarza@amd64.-20061026215355                | maint1        | n_stm  | 
| mysql-5.1-new-maint | cmiller@zippy.cornsilk.net-20061102235724     | rhas4-ia64    | n_stm  | 
+---------------------+-----------------------------------------------+---------------+--------+

How to repeat:
See PushBuild pages for 5.0-maint and 5.1-new-maint.

Suggested fix:
n/a

This test has not failed one single time this year, see

https://intranet.mysql.com/secure/pushbuild/xref.pl?startdate=&enddate=&dir=&plat=&testtyp...

Closing the bug report.

The bug does not appear to be fixed in 6.0.
The test was disabled for a long time in 6.0, today I tried to enable it and got
a random failure again:

main.user_limits               [ fail ]

mysqltest: At line 108: query 'connect  muc3, localhost, mysqltest_1,,' failed: 1226: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 2)

The result from queries just before the failure was:
< snip >
select * from t1;
i
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_connections_per_hour' resource (current value: 2)
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_connections_per_hour' resource (current value: 2)
drop user mysqltest_1@localhost;
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 2;
flush user_resources;
select * from t1;
i
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 2)

More results from queries before failure can be found in /tmp/var-n_mix-100/log/user_limits.log

Stopping All Servers
Restoring snapshot of databases
Resuming Tests
https://intranet.mysql.com/secure/pushbuild/getlog.pl?dir=mysql-6.0-runtime&entry=kosipov@...

Seeing this for ER_TOO_MANY_USER_CONNECTIONS and ER_USER_LIMIT_REACHED; this is always caught on the first retry in my tests.

catching race @ line 108 on attempt 1 for error #1226:42000 (ER_USER_LIMIT_REACHED)
(per-user max-user-connections/max-connections-per-hour/max-questions/max-updates, of which the latter two are irrelevant for our case and the 2nd takes intent to trigger)

catching race @ line 143 on attempt 1 for error #1203:42000 (ER_TOO_MANY_USER_CONNECTIONS)
(global max-user-connections)

http://dev.mysql.com/doc/refman/5.1/en/user-resources.html
cf. sql/sql_connect.cc

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/49751

2662 Tatiana A. Nurnberg	2008-07-15
      Bug#23921: random failure of user_limits.test
      
      mysqltest disconnect/connect-combo could be so
      quick that connect would hit the server before
      it had processed the disconnect. Since that
      resulted in one more concurrent connection than
      we meant to have, global or per-user
      max-user-connections could be exceeded.
      This could lead to "random" failures in tests
      that set those limits.

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/50105

2668 Tatiana A. Nurnberg	2008-07-21 [merge]
      Bug#23921: random failure of user_limits.test
      
      mysqltest disconnect/connect-combo could be so
      quick that connect would hit the server before
      it had processed the disconnect. Since that
      resulted in one more concurrent connection than
      we meant to have, global or per-user
      max-user-connections could be exceeded.
      This could lead to "random" failures in tests
      that set those limits.

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/50109

2731 Tatiana A. Nurnberg	2008-07-21 [merge]
      Bug#23921: random failure of user_limits.test
      
      mysqltest disconnect/connect-combo could be so
      quick that connect would hit the server before
      it had processed the disconnect. Since that
      resulted in one more concurrent connection than
      we meant to have, global or per-user
      max-user-connections could be exceeded.
      This could lead to "random" failures in tests
      that set those limits.

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/50164

2731 Tatiana A. Nurnberg	2008-07-21 [merge]
      Bug#23921: random failure of user_limits.test
      
      mysqltest disconnect/connect-combo could be so
      quick that connect would hit the server before
      it had processed the disconnect. Since that
      resulted in one more concurrent connection than
      we meant to have, global or per-user
      max-user-connections could be exceeded.
      This could lead to "random" failures in tests
      that set those limits.

Pushed into 5.1.26-rc

Still failing in 6.0-bugteam (prepared statements tests on 'sles10-ia64-a'. Disabling the test again

Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:2)

Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)

Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)

Noted in 5.1.26, 6.0.7 changelog.

mysqltest was changed to be more robust in the case of a race
condition that can occur for rapid disconnect/connect sequences with
the server. The account used by mysqltest could reach its allowed
simultaneous-sessions user limit if the connect attempt occurred
before the server had fully processed the preceding disconnect. 
mysqltest now checks specificaly for a user-limits error when it
connects; if that error occurs, it delays briefly before retrying.

Pushed into 6.0.7-alpha  (revid:azundris@mysql.com-20080721102344-esynwn5hc5rv7dpx) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)