I lowered the max_user_connections hoping it would keep other connections. Yet I am seeing the opposite. I have the impression that ' db: 'unconnected' user: 'unauthenticated' host:' is being counted, which they should not be.
-----Original Message----- From: Marc Sent: Monday, 8 July 2024 12:30 To: 'Jaco Kroon' <jaco@uls.co.za>; Hartmut Holzgraefe <hartmut@mariadb.com>; discuss@lists.mariadb.org Subject: RE: [MariaDB discuss] Re: is blocked because of many connection
Hi Jaco,
I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries
to db: 'unconnected' user: 'unauthenticated' host:
before these entries are logged:
Aborted connection 3077 to db: xxxx user: 'aaaaaa' host:
Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking?
[1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+
MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+------------ --------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+------------ --------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+------------ --------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
Kind regards, Jaco