Description:
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.
