Error!: SQLSTATE[42000] [1226] ‘max_user_connections’ resource (current value: 30), but max_user_connections is configured to 1000

Question

Visitors get a Mysql error when a mysql user has exceeded the max_user_connections, returning the current value as 30, but max_connections and max_user_connections are set to 1000. When the problem occurs, the CPU reaches almost 98 %.

On mysql error logs we received a lot of access denied errors from another user, around 5000 denied connections. My problem is not why the PHP script takes all these connections, but to know why the configured variables, max_user_connections and max_connections are not applied. Those are configured to 1000, but the error message returns 30. How it is possible ?

I activated log_warnings=2, to get more information, but we don't get an extra information. Any idea why this behavior ? or How to audit mysql to find the source of this problem ?

The error message received is :

Error!: SQLSTATE[42000] [1226] User ‘some_user’ has exceeded the ‘max_user_connections’ resource (current value: 30)

select @@session.max_user_connections, @@global.max_connections;
+--------------------------------+--------------------------+
| @@session.max_user_connections | @@global.max_connections |
+--------------------------------+--------------------------+
|                           1000 |                     1000 |
+--------------------------------+--------------------------+` 

 show global variables like '%connections%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |
| max_connections       | 1000  |
| max_user_connections  | 1000  |
+-----------------------+-------+

 show status like '%connected%';
 +-------------------+-------+
 | Variable_name     | Value |
 +-------------------+-------+
 | Threads_connected | 4     |
 +-------------------+-------+

 select user,max_user_connections from mysql.user where          host='localhost'\G

 user: some_user
 max_user_connections: 0
 user: another_user
 max_user_connections: 0`

The error seems to be : Error: 1226 SQLSTATE: 42000 (ER_USER_LIMIT_REACHED)

Message: User '%s' has exceeded the '%s' resource (current value: %ld)

and not :

Error: 1203 SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS)

Message: User %s already has more than 'max_user_connections' active connections

We are using MariaDB, version :

  select version(); 
  +------------------------+
  | version()              |
  +------------------------+
  | 5.5.44-MariaDB-cll-lve |
  +------------------------+

I would start by checking that each script only connects once and not multiple times per script. — RiggsFolly, Apr 26 '16 at 16:34
If the error messages identify the script thats running, show us that script — RiggsFolly, Apr 26 '16 at 16:45
The problem is that we know the script that cause the problem, we manage the server for the customer. — nandoC, Apr 27 '16 at 13:36
Well its likely that that script was badly written and has many connects when only one is neededd — RiggsFolly, Apr 27 '16 at 13:58
I am sure that is not written correctly, but the problem is why the error max_user_connections is at 30 if this value is set at 1000 globally and locally. I found the problem, see the solution. — nandoC, Apr 27 '16 at 18:43

nandoC · Answer 1 · 2016-04-28T04:19:35.793

Solution :

You can reproduce the error with the following command :

mysqlslap -a --concurrency=500 --number-of-queries 5000 --iterations=500 --engine=innodb --debug-info -utest -p

The problem was caused by Governor, we have Cloudlinux installaed on the server, but this option was set OFF by default, but in this server was set to abusers. If the CPU is higher that 400 Gobernor set the max_user_connections for the user at 30.

You can check the logs on /var/log/dbgovernor-restrict.log

The solution si to set correctly this value or set off

dbctl --lve-mode off

/etc/container/mysql-governor.xml

<lve use="abuser"></lve>
<restrict level1="60s" level2="15m" level3="1h" level4="1d"     
timeout="1h" log="/var/log/dbgovernor-restrict.log"          

user_max_connections="30"></restrict>

<statistic mode="on"></statistic>
<default>
<limit name="cpu" current="400" short="380" mid="350" long="300">      
</limit>

Well, not DDOS attacks, but if you have a shared hosting with a lot of customers you can limit the mysql resources for each user. — nandoC, May 02 '16 at 12:46

Error!: SQLSTATE[42000] [1226] ‘max_user_connections’ resource (current value: 30), but max_user_connections is configured to 1000

1 Answers1