max_connect_errors
Fabian Thylmann
I hope this is the right list to make this suggestion in or discuss this.
I have had multiple problems with max_connect_errors, especially at the
default setting of 10. Although I fully agree such a setting must be
there, and I actually also agree that the setting of 10 is ok if
implement correctly, I totally disagree to the way it was implemented in
mySQL.
As far as I see it, the main reason max_connect_errors exists is to help
against DOS attacks for example which could totally hinder mySQL to do
its job. A malicious attacker could continuously connect to mysql and
drop again, causing mysql to work up its connections and get swamped
with connect errors which are very low work for the attacker but
annoying to mySQL.
Also, an attacker could go and try brute forcing a password to get into
mySQL and max_connect_errors would simply lock them out quickly.
This all makes sense. As far as I understand any of the things listed
here will cause the counter to be increased:
http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
That might be wrong and it only is effected by the errors listed there
that happen during the connection phase, so that limits them somewhat.
The problem remains though. And to finally get to the problem, here it is:
Imagine a server setup where the mySQL server has a somewhat bad
connection to the servers that connect to it. This might be because they
are far apart, or traffic on the network is high or similar things. Now
of course this would cause connect errors to happen now and then.
The problem now is that mySQL does not care, at least I see no evidence
for it doing so, how far apart connect errors actually occur. So,
theoretically, they could be an error, a fully legitimate one, every 2
days, and after 20 days of uptime the connection to the database is
totally broken and has to be manually fixed via a flush-hosts command.
This is obviously not the goal of the max_connect_errors setting.
So, what I suggest is to add another setting to mySQL, lets call it
something like: connect_error_deduction. This setting could work in 2 ways:
1) Simply allow someone to set how much the connect_errors counter
should be deducted in a specific timeframe, for example every 10 minutes.
2) Or, what might be a better solution, a setting for how much the
connect_errors counter should be deducted for every 100 (for example)
GOOD connections that occur. For example, setting this to 10, would
cause the counter to be deducted by 1 every 10 successfully closed
connections that did not cause any errors.
This would keep the connect_errors counter clean and would not cause
FULL connection failures because of bad connections between servers and
them being totally locked out.
Again, I hope this was the right place to post this, would like to hear
what everyone thinks.
Fabian
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=mysql-i...@freebsd.csie.nctu.edu.tw
Elliot Murphy
The idea of reducing the connect_errors counter over successful
connections (your idea #2) seems reasonable to me.
If you were to propose a patch we could attempt to get this into 5.2
(5.1 is already past feature freeze). Otherwise writing a feature
request on bugs.mysql.com would be a way to prevent this idea from
getting lost.
cheers,
-elliot
my...@karsites.net
I think it's a very good idea Fabian.
I hope it gets implemented in the next release.
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Thu, 23 Mar 2006, Elliot Murphy wrote:
> To: fab...@toomuchmedia.com
> From: Elliot Murphy <ell...@mysql.com>
> Subject: Re: max_connect_errors
Fabian Thylmann
Looks like this is already in 5.1.... from 5.1.7 file sql/sql_parse.cc
line 922:
if (connect_errors)
reset_host_errors(&thd->remote.sin_addr);
Resets connect_errors to 0 after a successful handshake. Looks like what
I was talking about, no?
Fabian
Too Much Media LLC
Elliot Murphy
source. Glad that this is in 5.1, keep the ideas coming.