Bug #2778 max_connections not decrementing properly
Submitted: 13 Feb 2004 10:49 Modified: 18 Feb 2004 15:39
Reporter: Joe Lewis Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.16 OS:FreeBSD (FreeBSD)
Assigned to: Dean Ellis CPU Architecture:Any

[13 Feb 2004 10:49] Joe Lewis
I've waited to report this just in case someone else had entered it or even solved it, but to no avail.  I know this has been occuring in versions as old as 4.0.16, and haven't seen 4.0.17 or 4.0.18 bugs covering this.

I don't know if it is a race condition when processes are stopping, or if the max_connections values are just not getting decremented.  When trying to log in to MySQL 4.0.16, I can get the following transcript :

#mysql -p -u test
Enter password:
ERROR 1226: User 'test' has exceeded the 'max_connections' resource 
(current value: 100)

That is exactly what I'd expect to see, IF the user had 100 connections.  Instead,  when viewing the processlist, I get :

#mysqladmin -p processlist
Enter password:
| Id  | User | Host      | db | Command | Time | State | Info             |
| 469 | root | localhost |    | Query   | 0    |       | show processlist |

Calling FLUSH USER_PRIVILEGES causes the error to go away, so my interim solution is to have a cron flush the users privileges. until a bug-fixed version arrives.

The account is using a PHP-4.3-based shopping cart under Apache 2.0.47 (OS is FreeBSD 4.7).

How to repeat:
Open a high number of connections for a single user in rapid succession (possibly multiple connections at the same time), ensuring that you close each one.  Once the max_connections error begins, call SHOW PROCESSLIST, and note that it is empty.  After FLUSH USER_PRIVILEGES, everything will return to normal.

Suggested fix:
Use thread mutexes/locks in decrementing max_connections for a user to prevent race conditions from causing the above problem.
[18 Feb 2004 15:39] Dean Ellis
It sounds like the user is exceeding the user's max_connections setting, which restricts the number of connections per hour, not the number of concurrent connections, so check that and see if that is the problem.