Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Too many connections with connection_control plugin and proxysql as client

Description

Hello

I have observed a problem causing my server du be stuck in "too many connections"

I configured the server with connection_control plugin and:

I added a user for several hosts:

Try to connect with:

allowed host -> get a connection

host not configured: -> ERROR 1045 (28000): Access denied for user 'alice'@'client2'

so far so good, then I configured proxysql on this 'client2' wich alice as monitoring user

Normally I would assume proxysql would get the same 1045 Error, but here the connections get stuck in 

and stay there for connection_control_max_connection_delay time and such connects then exhaust the max_connections on the server.

proxysql.log: [ERROR] Server server:3306 is returning "Access denied" for monitoring user

So 2 things get not checked when proxysql tries to connect to the server:

  • host the connection comes from (there is no alice@client2 line in myslq.user)

  • the max_user_connections (set to 10 here)

I'm not sure what proxysql does different to connect than the mysql client which gets 1045 but the behaviour is not what I expected.

 

If I add a alice@client2 I get a normal connect from proxysql visible in show processlist;

 

without the connection_control plugin and no user alice@client2 configured

proxysql gets  [ERROR] Server server:3306 is returning "Access denied" for monitoring user again, but no alice@cient2 pile up in proccesslist.

 

Environment

PS8 8.0.27

mysql 8.0.27 client on client 1 and client 2

proxysql 2.3.2
on ubuntu 20.04 systems

Smart Checklist

Activity

Aaditya Dubey October 19, 2023 at 2:20 PM

Hi ,

Thank you for the reports and updates.
This is actually being tracked here at https://jira.percona.com/browse/PS-8847 as feature and this is not actually a bug, this is how this plugin is designed. However there is a possible improvement we see for the connection_control plugin. Upstream report also there https://bugs.mysql.com/bug.php?id=111877 but it is private now so may not accessible to you. Closing the report, Please subscribe to to get notified whenever there is updates.

Hubertus Krogmann June 15, 2022 at 11:58 AM

Hello

I also opened Bug #107570: connection-control may lead to DOS of MySQL Instance at bugs.mysql.com 
as this probably is a feature of the mysql community server and not special to percona server.

Hubertus Krogmann May 12, 2022 at 6:49 AM

Hello 
I need to correct myself, mysql as a client behaves the same way if I try more than connection_control_failed_connections_threshold from a not configured host it starts collecting threads in Waiting in connection_control plugin state way beyond the allowed max_user_connection settings.

So this is not a proxysql related connection problem (apart from retrying automatically and triggering the problem)

Clearly the task of connection_control is to prevent brute force attacks against passwords ... but the way it keeps threads "alive" makes a denial of service possible as long as you can reach the port. You don't need a valid user or come from a valid hosts to exhaust the max_connections setting. But even if connection_control would reject sessions earlier if user and/or host are not vaild it would make it easier to find valid usernames because they behave differently... and the denial of service would still be possible from a valid user@host if you just keep giving the wrong password.

Perhaps connection_control (or probably more accurate mysql itself?) would need a "2nd thread pool/thread type" to keep connections waiting but preventing a denial of service. Which probably will get into other problems if you try to fill up that with waiting connections (RAM/CPU Usage ...). 

Problem for the admin in this situation is that even root@localhost is not able to connect, perhaps mysql need a number of connects for superusers. (I have root configured using auth_socket if this makes any difference). 

 

 

 

 

Hubertus Krogmann May 11, 2022 at 10:44 AM

a global configured set global max_user_connections = 100; is also not helping in this situation.

Duplicate

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Created May 11, 2022 at 10:02 AM
Updated March 6, 2024 at 10:10 AM
Resolved October 19, 2023 at 2:22 PM