How to track down the source of Aborted_connects
Yesterday I helped someone who was seeing a lot of "server has gone away" error messages on his website. While investigating this problem, I noticed several things amiss, which appeared to be related but really weren't. The biggest measurable sign was
-
[percona@server ~]$ mysqladmin ext | grep Abort
-
| Aborted_clients | 14835 |
-
| Aborted_connects | 15598 |
These two status variables are actually unrelated (see the manual page that explains them). The first was related to the errors the client was seeing: the server was closing inactive connections after a while, and I fixed it by increasing the wait_timeout configuration variable.
The second error does not indicate that an active connection is closed at all. Rather, it shows that a connection cannot be made for some reason. Perhaps it's networking, or perhaps there's an issue with permissions or something else. The first thing I did was look for packet loss between the database server and the web server; the network appeared to be working fine.
With that ruled out (at least, to my satisfaction) I turned to tcpdump to see what was happening with these connections. I ran the following command in one window of my screen session, so I could see when a connection was aborted:
-
[percona@server ~]$ mysqladmin ext | grep Abort | grep -v 0
And then I started tcpdump in another window:
-
[percona@server ~]$ tcpdump -s 1500 -w tcp.out port 3306
After I saw an aborted connection, I cancelled tcpdump and looked at the resulting file. Inspecting the session with tcpdump -r showed that there was a complete TCP session; nothing bad was happening at that layer. So I used the strings utility to look at the text sent in the packets:
-
[percona@server ~]$ strings tcpdump.out
-
Host 'XXX.XX.XX.XXX' is not allowed to connect to this MySQL server
I've anonymized the offending IP address. However, I checked the server's grant tables and indeed. that IP address (which is a machine in the local network) is not allowed to connect.
I don't actually use tcpdump much, but this was a fun little exercise that I thought I'd share with you.
del.icio.us
digg4 Comments
If you were to dig out one of my earlier Audit patches for MySQL, you could have hooked a plugin on the aborted connect event and no need to mess about with tcpdump.
Comment :: August 23, 2008 @ 12:36 pm
It’s too bad it’s not in the server! There’s no possibility of patching the server in most cases like this.
Comment :: August 23, 2008 @ 12:44 pm
Many things that are not being built in/for proxy and enterprise monitor should be direct server features or plugins.
That not being the case is not a technical decision but one of product marketing. And I *DO NOT LIKE IT*.
IMHO the “but we need to make money” argument is a wussy cop-out. It’s just so that people don’t have to use their imagination a bit more. What a pity. Such good things could come of looking for real opportunities.
Comment :: August 24, 2008 @ 4:38 am
Baron, Antony
Indeed Audit extensions are must to have in MySQL.
At least very basics it would be good to split Aborted_connects to network related and Authentication_Failures as these are very different events.
Aborted_clients may also benefit splitting somehow because unexpected aborts (network related) are not the same as aborts when client exists without closing connection or because wait_timeout seconds passed.
Comment :: August 24, 2008 @ 10:04 am