does max_connect_errors not check Aborted_connects?

Question

In MySQL (5.5) which status var reflects the current number of connection errors that are compared against max_connect_errors by the server? I thought it was Aborted_connects but in my testing I can still connect to server after driving aborted_connects (in succession) well above max_connect_errors...

Asked differently, does mysql server check aborted_connects against any threshold variable? Is any behavior dependent on value of that status variable (or is just FYI for DBAs)?

EDIT 1

to clarify, I am less interested in how to reach max_connect_errors, and more in whether having a high aborted_connects will have any negative consequences.

For context: we're using a layer 4 (TCP) load balancer to distribute JDBC connections of appservers' db connection pools across a cluster of MySQL read-only slaves. The load balancer (AWS ELB) has a TCP-based health check feature - it periodically checks health of the db servers by establishing a TCP connection with db server's mysql port. Since the LB does not speak 'JDBC', literally all it does is create a TCP connection and immediately disconnect. Each of these connections, however, increment aborted_connects on mysqld. I see no other side-effects, but wonder whether running up aborted_connects will eventually have negative consequences (for example, mysqld will ban the LB's IP, thus causing health check failures)

Thank you, -nikita

Answer 1

max_connect_errors is on a per-host basis, so I doubt that it checks a status variable such as Aborted_connects.

This variable probably does not apply if you are testing using 'localhost' (which does not use TCP), for what happens if you lock localhost out? No one could connect (assuming a single root user and not being able to log in root remotely)!

So I felt like digging into the source on this one. In the file 'sql/hostname.cc' is a function ip_to_hostname. This function has the following comments (emphasis mine):

Resolve IP-address to host name.

This function does the following things: - resolves IP-address; - employs Forward Confirmed Reverse DNS technique to validate IP-address; - returns host name if IP-address is validated; - set value to out-variable connect_errors -- this variable represents the number of connection errors from the specified IP-address.

NOTE: connect_errors are counted (are supported) only for the clients where IP-address can be resolved and FCrDNS check is passed.

There is also this comment with regards to localhost (or loopback ip like 127.0.0.1):

*connect_errors= 0; /* Do not count connect errors from localhost. */

Furthermore, the only time connect_errors is incremented is in the function 'inc_host_errors' of the same sql/hostname.cc file. This is only called from the sql/sql_acl.cc file. It looks like these are some of the situations:

1) There is an error with the packets (packet lengths don't match). This is also probably why the documentation says:

If you get the Host 'host_name' is blocked error message for a given host, you should first verify that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

2) Invalid native password (function is native_password_authenticate)

3) Invalid old-style password (function is old_password_authenticate)

I might have missed a few things, but ultimately, max_connect_errors has nothing to do with aborted connections, but rather networking issues or invalid passwords.

---

For completion sake, I will mention I was looking at the Percona 5.5.15-rel21 source code.

---

So, what does Aborted_connects have to do with it? Well, according to this documentation, it gets incremented anytime:

• A client does not have privileges to connect to a database.

• A client uses an incorrect password.

• A connection packet does not contain the right information.

• It takes more than connect_timeout seconds to get a connect packet.

So, Aborted_connects is just an indicator for a DBA to know there might be a problem (eg: if it increases dramatically over a period of time, someone might be attempting to hack into the server, but you should be protected by max_connect_errors in this case).

If yours increases steadily from the healthchecks, you can still use it if it spikes abnormally.