On Monday 01 April 2024 at 12:57:43, Marc via discuss wrote:

x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'

Have you tried setting max_connections to something higher than the default (on my machine this is 100) in /etc/mysql/mariadb.conf.d/50-server.cnf?

I don't want the connection limit to be on just an ip in a shared environment, but on the combination ip+accessed db.

I don't know whether that is even possible; maybe someone else here does.

Antony.

...

...

...

I don't want to have this limit on ip but on ip+database, how can I

change

...

...

...

this?

at the point at which connections are blocked the database the remote host wants to use is not even known yet, so this blocking mechanism can't take it into account.

afaik you have a connection requist with user/pass/db. So maybe it could be a good option to delay tcp blocking till after you get more info? I donk't think there is much overhead created using these variables that are being send. You don't query the backend looking up users in tables. I think this could be nice feature for design.

The related setting is max_connect_errors, but as far as I can tell it defaults to 100 for all MariaDB releases, and for MySQL, too, at least back to MySQL 5.7

https://mariadb.com/kb/en/server-system-variables/#max_connect_errors

https://dev.mysql.com/doc/refman/5.7/en/server-system- variables.html#sysvar_max_connect_errors

That's why I was asking for specific version information on a previous reply ...

I don't think it is that relevant currently. It takes me a bit of time to gather this. I can remember noticing starting to have these incidents after an upgrade.

PS: your quoting style is confusing, the ">" indentation marker is supposed to mark quoted text, one ">" per level of quoting, not your actual reply.

:) My reply is being quoted? I sort of thought this was a clear way of responding. I am usually annoyed by people not responding clearly, so I definitely want to prevent doing the same.

Hi Antony,

...

I am constantly getting abuse from azure, amazon, digital ocean cloud.

x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'

Do you mean you do not want ANY connections from those sources?

You mention "abuse" which makes me think that these are not legitimate connection attempts and Maria is only blocking them after they have tried NN times and failed.

That sounds like you have port 3306 open to the internet, which is generally considered a Very Bad Thing. (As it is for any port you don't need the random internet to connect to)

I would suggest you close this port to the internet immediately. If you do need connections from the internet, then adjust your firewall to restrict it to accept connections from them explicitly.

Apologies if I'm misinterpreting this, but if the above is true then I also suggest you review your basic computer security.

...

Apologies if I'm misinterpreting this, but if the above is true then I

also

...

suggest you review your basic computer security.

Not a bad idea in general, for most people :)

That would exclude 70% or so of people doing business on the internet ;) I can remember years ago complaining to some developer of an s3 client that they were having default read everyone rights on uploads. Then it starts to make sense when you read about data breaches where files are being downloaded. I have the impression the cloud mostly facilitates people that are not a 'professional'.

On 4/2/24 14:35, Marc via discuss wrote:

This is shared web server, so I need to allow what ever else is not being abused at that time.

with that you should never end up with blocked hosts though, a host only gets blocked when terminating before the initial handshake is complete, e.g. by connecting to port 3306 and then immediately disconnecting again without sending any data.

Hosts do not get blocked for e.g. repeatedly providing the wrong password, as for that the initial handshake gets completed. Completed with an "access denied" error, but completed nonetheless.

Main problem here though is that you seem to want to provide as little information as possible only, so this turned into a big guessing game.

And with that I'm out, I don't want to waste unpaid time on doing educated guesses with information only being reviled bit by bit instead of describing the full scope of the problem up front.

On 4/2/24 15:00, Marc wrote:

I think the initial post with error message is clear " blocked because of many connections"

so it was "too many connections" after all, not "too many connection errors"?

in that case you had us all lead on the wrong track, and the solution would be simple: increase max_connections

But my intial question was also if the blocking could be expanded to a match of ip+db and not just ip. I still think this could be an interesting change in design.

that again hints towards "too many connection errors"

All this mess could have been avoided by a clear copy+paste of the error message you are actually getting, instead of trying to be as vague as possible (e.g. not mentioning actual old and new version, even after repeated questions about that).

So back to "too many connection errors": as described in my last reply this error is about misbehaved clients that have repeatedly connected to the server, but then aborted before completing the login handshake.

And with that the IP is the only thing known at that point, user name and the optional up-front default database are only transferred later in the handshake.

So it is an interesting feature request, but one impossible to add to that specific feature.

PS: so you tricked me into replying once more, while again not really adding any useful information. Won't happen again though ...

Hi,

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

On 2024/04/02 15:00, Marc via discuss wrote:

...

Hosts do not get blocked for e.g. repeatedly providing the wrong password, as for that the initial handshake gets completed. Completed with an "access denied" error, but completed nonetheless.

I think the initial post with error message is clear " blocked because of many connections"

...

Main problem here though is that you seem to want to provide as little information as possible only, so this turned into a big guessing game.

And with that I'm out, I don't want to waste unpaid time on doing educated guesses with information only being reviled bit by bit instead of describing the full scope of the problem up front.

But my intial question was also if the blocking could be expanded to a match of ip+db and not just ip. I still think this could be an interesting change in design. _______________________________________________ discuss mailing list --discuss@lists.mariadb.org To unsubscribe send an email todiscuss-leave@lists.mariadb.org

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.

/usr/sbin/mysqld --verbose --help shows me the value I have configured in server.cnf at [mysqld]. However the show variables like '%max%connections%' shows me a different value. I should be able to put this in server.cnf not?

...

Did you restart mariadb after modifying the configuration?

yes

[mysqld] max_user_connections = 250 max_connections = 5000

Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '

using v10.5.

...

...

[mysqld] max_user_connections = 250 max_connections = 5000

Yes I am putting it exactly there. If I change max_connections there, I

see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '

...

using v10.5.

with 10.5 you can use

SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections';

to see what value is in effect, and from where (esp.: what .cnf file) it was fetched.

Thanks!

...

...

[mysqld] max_user_connections = 250 max_connections = 5000

Yes I am putting it exactly there. If I change max_connections there, I

see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '

...

using v10.5.

character encoding for that 103?  because 10 looks suspiciously like a prefix of 103 and given the default is 0 (unlimited) I can only think that you've got a space or some other invisible character between the 10 and the 3 that causes the parse to treat the string as 10 and not 103.

I have been testing with different numbers just to see if and how it would change, or if there was a relationship (maybe total amount of connections devided by something as a max for users). I think I also tested with 200. Maybe some update script did not execute fully/correctly. No idea where this 10 is coming from. I am going to check logs a bit and just wait for the next blocking ;)