MySQL DNS Details

MySQL manual section 7.5.6. How MySQL Uses DNS can be summarized in one sentence: A new thread looks for hostname in cache, if not present it tries to resolve it. Simple enough, however in cases where MySQL is primarily accessed over a network (such as a dedicated backend server), DNS resolution can become the cause of a very elusive type of performance degradation, sometimes to the point of complete MySQL failure.

How MySQL Really Uses DNS

If you're not a programmer, the difference between gethostbyaddr() and gethostbyname() as mentioned in the MySQL manual may not tell you what you should know. gethostbyaddr() performs a reverse DNS lookup; it tries to resolve an IP address to a hostname. gethostbyname() performs a forward DNS lookup; it tries to resolve a hostname to an IP address. When a network connection is made to MySQL it only knows the remote end's IP address. Since grant tables can specify privileges by hostname, MySQL has to resolve every IP address to its hostname. For added security MySQL then also resolves that hostname to an IP address to make sure this IP address matches the IP address of the remote end. In short, for every TCP/IP connection, MySQL does a reverse and forward lookup. For added performance, successful lookups are cached and not tried again until either MySQL is restarted or issued a FLUSH HOSTS; command.

DNS topics in the MySQL manual are largely based on working setups—were DNS resolves quickly and correctly. However, we know DNS isn't always quick or correct. In less common cases DNS doesn't even respond. The following looks at how MySQL handles these conditions, as well as a potential bug involving DNS which stops MySQL from authenticating network connections.

This document isn't geared toward MySQL source code hackers, but in case you do want to see the respective source code it can be found in sql/hostname.cc starting at line 196 through the end of the function, which is ip_to_hostname(), for version 4.0.22 on a Linux system. Numbers in brackets like [200] scattered throughout the document are source code line references.

Reverse Lookup

When a network connection is made to MySQL, all MySQL knows is the remote end (peer) IP. First MySQL resolves the peer IP to a hostname by calling the standard C library gethostbyaddr() [197]. Naturally, there are two possible return values: Either the lookup succeeds and returns a hostname, or it fails. If the reverse lookup succeeds MySQL goes onto the next step. If the reverse lookup fails, the failure is cached (remembered) [201], and MySQL will not try to lookup this IP address again until either MySQL is restarted or issued a FLUSH HOSTS; command. Failure can mean, basically, one of two things: Either DNS doesn't respond at all, or it responds with NXDomain (non-existent domain). In the first case MySQL waits at least 10 seconds for a response. Since MySQL uses standard C library resolver functions (gethostbyaddr() and gethostbyname()) the operation and limitations of DNS lookups is a factor of the C library, not MySQL. Therefore we have to look at how these resolver functions work.

Standard C library gethostbyaddr() uses /etc/resolv.conf. (For our purpose here we forget about /etc/hosts which might also be used.) It queries each listed nameserver twice in order, waiting 5 seconds for each one. For example, if /etc/resolv.conf lists 2 nameservers, the first is queried, then the second, then the first again, then the second again. Therefore the maximum wait time for gethostbyaddr() is 10 seconds times the number of nameservers.

For MySQL this means, if no nameserver in /etc/resolv.conf responds, it takes a minimum of 10 seconds to fail, but only for the first time for the same IP address because this failure will be cached. While MySQL is waiting for DNS, the status of this connection in the process list is shown like:

   +----+----------------------+------------------+------+---------+------+-------+-----------------------+
   | Id | User                 | Host             | db   | Command | Time | State | Info                  |
   +----+----------------------+------------------+------+---------+------+-------+-----------------------+
   |  4 | unauthenticated user | 192.168.0.3:1112 | NULL | Connect | NULL | login | NULL                  |
   +----+----------------------+------------------+------+---------+------+-------+-----------------------+
   

Where 192.168.0.3 is the peer IP address MySQL is trying to resolve. Usually this won't ever be a problem because what server would have non-responsive DNS?

The other most common kind of failure is a return value of NXDomain, which means the DNS server has no matching resource record, which would be a PTR record at this point. Each nameserver is queried twice in order, waiting 5 seconds for each one, and fails once any nameserver returns NXDomain.

If the reverse lookup succeeds by returning a hostname, MySQL then does a forward lookup on this hostname. (Technically, MySQL doesn't allow an empty hostname [203]. If the reverse lookup returns an empty hostname this is considered a failure.)

Forward Lookup

For added security, MySQL does a forward lookup on the hostname it obtained from the reverse lookup. MySQL is checking that the IP address this hostname resolves to matches the peer IP address [239]. MySQL resolves the IP address to a hostname by calling the standard C library gethostbyname() [214]. If the lookup succeeds its always cached; if the lookup fails for any reason it is never cached. The forward lookup can fail for the same reasons the reverse look can fail: Non-responsive DNS or an NXDomain response. The standard C library gethostbyname() works a little differently than gethostbyaddr().

Standard C library gethostbyname() uses /etc/resolv.conf, queries each listed nameserver twice in order, and waits 5 seconds for each one just like gethostbyaddr() except it goes one step further if the lookup fails. If gethostbyname() receives no response from any nameserver, it tries all over again but with a modified hostname: It takes the domain of the server's hostname and appends it to the hostname in question. For example, if your server's hostname is my.server.com, and the hostname in question is someone.else.com, gethostbyname() will try to resolve someone.else.com.server.com. The maximum wait time for gethostbyname() is 20 seconds times the number of nameservers.

If gethostbyname() receives an NXDomain response from a nameserver, it always tries again with the modified hostname (e.g. someone.else.com.server.com.). Interestingly enough, it may or may not retry with all nameservers. If the first nameserver returns NXDomain, gethostbyname() retries only that nameserver with the modified hostname. If the first nameserver doesn't respond, and the second one does, gethostbyname() retries the first nameserver, then retries the second nameserver.

In either case, should gethostbyname() fail the failure is never cached by MySQL [220]. Only successful forward lookups are cached. While MySQL is waiting for forward resolution the status of this connection in the process list is shown the same as above.

Additional Checks

After reverse and forward lookups complete successfully MySQL makes two additional checks. First it checks the hostname obtained from the reverse lookup does not start with a series of numbers followed by a dot [226]. "MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name never matches the Host column of the grant tables" (5.5.5.). Finally, MySQL checks that the IP address obtained from the forward lookup matches the peer IP address [239]. If the two match, the function (ip_to_hostname()) returns the hostname [244]. If the two do not match this failure is cached [250]. (And if you're curious, ip_to_hostname() returns to sql/sql_parse.cc line 525.)

A Bug In MySQL DNS?

I originally became intersted in how MySQL uses DNS because I literally watched a MySQL server for over 20 hours trying to figure out why it would stop authenticating network connections before I realized it was a problem with DNS, easily fixed by using the --skip-name-resolve startup option. My original post to several boards read:

I have a very weird problem with MySQL 4.0.22 where it stops authenticating remote connections. Eventually MySQL reaches max_connections and it has to be restarted. Sometimes it restarts nicely, sometimes it wont and it has to be killed. Here's what I've done to isolate the problem to MySQL:
1. Local connections always work so MySQL isn't frozen
2. Any remote server is effected, so it's not MySQL blocking one particular IP
3. Any remote script is effected, so it's not a particular PHP or Perl issue
4. No query is frozen. I enabled general logging and every time every connection ID quits nicely, then there is a large gap of no queries, no connections.
5. Any db or table is effected
6. The network is ok because I never lose SSH or ping connectivity
7. In the priv table it doesn't matter if it's allowed by IP or domain--either fail
8. Full process list shows *nothing* but:
Id: 1858
User: unauthenticated user
Host: 1.2.3.4:51568
db: NULL
Command: Connect
Time: NULL
State: login
Info: NULL
9. Any MySQL user is effect. I created a new user: It won't authenticate either
10. MySQL is the only thing running on this server--No firewall, no floodguard, no IDS, etc.
11. Server load, queries/second are all very low and neither shows any relation to the issue

Later I Googled the problem more and found I was not alone, and neither was this a new phenomenon: