Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How are you testing? Is it localhost (unix socket) or over tcp (127.0.0.1 or other) ? –  Derek Downey Nov 14 '12 at 19:20
    
'telnet remoteHost 3306' and CTRL+C - more times than max_connect_errors; then do a proper connect using mysql. –  Nikita Nov 14 '12 at 19:21
    
try connecting with incorrect login info for more than max_connect_errors. –  Derek Downey Nov 14 '12 at 19:26
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
thanks so much for sleuthing. Sounds like aborted_connects is basically just a statistic - not used by mysqld to determine server behavior. I clarified the intent of the question - would love to know if you change your answer after reading. –  Nikita Nov 15 '12 at 21:28
    
@Nikita I added a bit more, but it is basically a status variable that indicates one of the above 4 issues. Which you may or may not find useful :) –  Derek Downey Nov 15 '12 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.