Bug #65104 max_user_connections with processlist empty

• Submitted: 25 Apr 2012 18:51
• Modified: 13 Aug 2012 16:10
• Reporter: Olivier Doucet
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S2 (Serious)
• Version: 5.5.23, 5.5.25
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, regression

[25 Apr 2012 18:51] Olivier Doucet

Description:
When a specific user has a max_user_connections value set to != 0 (default), this value is not always respected by MySQL Server.

In some case, MySQL refused user login with ERROR 1226 (42000): User 'XXX' has exceeded the 'max_user_connections' resource (current value: 4000) ; at the same time, processlist is really empty, and moreover, max_connections was set to 510, so hitting a limit at 4000 is really not expected.

After reading source code, it appears there is a missing case in sql/sql_acl.cc around line 9480 where a failed connection does not decrease thd->user_connect.

How to repeat:
Gor Martsen wrote a reproductible case and I give him full credits for this.

Case is described in the following bug report (in Percona Server, but MySQL Server do have this bug too) : 
https://bugs.launchpad.net/percona-server/+bug/893348/comments/5

Suggested fix:
Following patch seems to fix the problem (patch was written by Gor Martsen) : 

--- sql/sql_acl.cc.orig	
+++ sql/sql_acl.cc	
@@ -9486,6 +9486,11 @@
     mysql_mutex_unlock(&LOCK_connection_count);
     if (!count_ok)
     {                                         // too many connections
+      if (thd->user_connect)
+      {
+        decrease_user_connections(thd->user_connect);
+        thd->user_connect= 0;
+      }
       my_error(ER_CON_COUNT_ERROR, MYF(0));
       DBUG_RETURN(1);
     }

[25 Apr 2012 19:23] Sveta Smirnova

Thank you for the report.

Verified as described. Bug is not repeatable with version 5.1

[25 Apr 2012 19:24] Sveta Smirnova

Verified using test case from Percona bug

[1 Aug 2012 8:29] Olivier Doucet

Can you please commit the patch provided by Gor Martsen ? He signed the OCA three months ago and this bug is really critical for large installations.
Thank you.

[10 Aug 2012 10:16] Simon Mudd

I second that. hit by this again on production systems which forces me to remove the "per user limits".
5.5.27 was released a short while ago and doesn't have this patch applied. this is a critical problem in the sense that MAX_USER_CONNECTIONS is used to prevent overload of systems and if we use that option now (I do) then when you hit the configured ceiling you lock your application out until you change the limit to 0.  That's really not helpful.

[10 Aug 2012 10:48] MySQL Verification Team

i do see the internal patch has been applied in the 5.5.27 tree.  Have not actually tested it though, so can't say if it really works or not.

[10 Aug 2012 11:28] Simon Mudd

That's a start anyway. I didn't see this mentioned in the announcement by Hery Ramilison, but if the fix is supposedly there then someone can at least try to confirm if the issue seems resolved.  I'm not going to get a chance at least in the next few days.

[13 Aug 2012 16:10] Paul DuBois

Noted in 5.5.26, 5.6.6 changelogs.

If an account had a nonzero MAX_USER_CONNECTIONS value, that value
was not always respected.