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 |
  +------------------------+

Answer 1

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>