0

From time to time, I'm getting an error "mysqli_connect(): (HY000/1203): User already has more than 'max_user_connections' active connections." I checked the MySQL max_connection variable, and it's at 150. What's odd is that when I check for Max_used_connections, it says 41, which (I think) implies that MySql never reached 150 simultaneous connections. The per-user Max_user_connections variable is 0, so it should effectively be the same as max_connections. Am I misunderstanding what these variables mean, or is something odd going on with MySql?

I use PHP to connect to MySql. The MySql version is 5.7.40

Here are the results of show status like '%connec%'; and show variables like '%connections%';

| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_connects                              | 776                 |
| Connection_errors_accept                      | 0                   |
| Connection_errors_internal                    | 0                   |
| Connection_errors_max_connections             | 0                   |
| Connection_errors_peer_address                | 0                   |
| Connection_errors_select                      | 0                   |
| Connection_errors_tcpwrap                     | 0                   |
| Connections                                   | 8899838             |
| Locked_connects                               | 0                   |
| Max_used_connections                          | 41                  |
| Max_used_connections_time                     | 2022-10-25 13:57:07 |
| Performance_schema_session_connect_attrs_lost | 0                   |
| Ssl_client_connects                           | 0                   |
| Ssl_connect_renegotiates                      | 0                   |
| Ssl_finished_connects                         | 0                   |
| Threads_connected                             | 1                   |
+-----------------------------------------------+---------------------+

+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| max_connections      | 150        |
| max_user_connections | 2147483647 |
+----------------------+------------+

Also, I doubt I'm using up all connections by serving web pages; I'm not doing complicated queries, and the data sets are relatively small. Can someone point me to how to find what process/user maxes out these resources?

1 Answer 1

1

Max_used_connections is a "high water mark".

max_user_connections -- Maximum simultaneous connections permitted for each user account. There are exceptions, such as for 'root' and when the value is 0.

If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit. -- Check this value, too. SHOW CREATE USER "..."@"...";

A typical PHP application will [or at least should] use at most 1 connection per web page, but a user can run through lots of pages rather fast. While they are flipping through pages, the "used" connections is going up and down by one. That one user is not contributing more than 1 to the "used". Meanwhile, a hidden counter is rapidly increasing toward his max_user_connections.

If "everyone" has the same login, then "user" is somewhat meaningless. More likely, the user connects to a web page, then then uses some common db connection. That is, all users end up incrementing the counter for that one login name.

5
  • The query select User,max_user_connections from mysql.user; shows this user's max_user_connections is set to 0. Back to my question, could you clarify more why I reached MAX_CONNECTIONS without incrementing MAX_USED_CONNECTIONS to 150? If I got to 150 MAX_CONNECTIONS, wouldn't MAX_USED_CONNECTIONS also be at 150? Commented Nov 29, 2022 at 21:04
  • Analogy... Going to a store. Max_used_connections is peak number of people are in the store at one time. MAX_USER_CONNECTIONS is how many times you went to the store.
    – Rick James
    Commented Nov 29, 2022 at 21:20
  • See also SELECT User, Host, User_attributes, max_connections, max_user_connections, user() FROM mysql.user;
    – Rick James
    Commented Nov 29, 2022 at 21:21
  • You misunderstood the question. I'm not asking why max_useR_connections isn't 150. I'm asking why max_useD_connections is at 41--not 150. Commented Nov 29, 2022 at 21:29
  • The 41 is the max since the last restart of mysqld.
    – Rick James
    Commented Nov 29, 2022 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.