Bug #49877 remote connection delay due to long netbios lookup timeout
Submitted: 22 Dec 2009 13:24 Modified: 13 Sep 2012 11:40
Reporter: Viktor Štujber Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Windows Severity:S3 (Non-critical)
Version:5.1.41 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: connect, dns, gethostbyaddr, NetBios, timeout, windows

[22 Dec 2009 13:24] Viktor Štujber
Description:
The gethostbyaddr() call is, according to the docs, used by mysqld to do reverse DNS lookups on incoming connections, for hostname-based privilege checking purposes.

According to the msdn documentation ( http://msdn.microsoft.com/en-us/library/ms738521(VS.85).aspx ), gethostbyaddr() is deprecated in favor of getaddrinfo(). One extra feature it has is, that it will do NetBIOS lookups when reverse DNS resolution fails and when it detects that the name could be a valid NetBIOS name.

On Windows, if NetBIOS is turned on (default, still enabled on Server 2008 R2), the following sequence of events will occur if someone with a missing rDNS record and NATed connection tries to connect to this server:
Legend: C = client, S = mysql server, D = dns server

0.000 C->S: SYN
0.000 S->C: SYN,ACK
0.189 C->S: ACK
0.202 S->D: reverse dns query
0.385 D->S: no such name
0.385 S->C: Name query NBSTAT *<00>...
0.571 C->S: ICMP destination port unreachable
1.880 S->C: Name query NBSTAT *<00>...
2.067 C->S: ICMP destination port unreachable
3.380 S->C: Name query NBSTAT *<00>...
3.567 C->S: ICMP destination port unreachable
4.880 S->C: MySQL server greeting
...

As you can see, this faulty netbios interaction delays the connection procedure for 5 seconds.

How to repeat:
Run Wireshark (or some other packet capture tool) on server.
Use mysql console to connect to server.

Suggested fix:
Various approaches.

1. If the only purpose of the rDNS resolution is user account hostname matching, and the mysql specs require that the hostnames be dns resolvable, then replace use of gethostbyaddr (and other deprecated functions) with getaddrinfo, which does not invoke netbios functionality.

2. Since you're already caching successful rDNS lookups, cache the failed ones as well. Currently, phpMyAdmin without mysql_pconnect incurs a 5 second delay on each and every page load.

3. Do rDNS lookups only when really necessary. If the only privilege rule with my username on it has '%', or numeric IPs only, then the dns lookup is not needed here.

4. Educate the users. The MySQL docs and user comments on 'How MySQL Uses DNS' ( http://dev.mysql.com/doc/refman/5.0/en/dns.html ) attempt to cover several problem scenarios, but the word 'netbios' does not appear on that page at all.
[31 Dec 2009 1:21] MySQL Verification Team
Thank you for the bug report.
[31 Dec 2009 9:22] Viktor Štujber
Thanks for verifying. Slight errata: by 'getaddrinfo' I meant 'getnameinfo' (the former does name2ip; the latter does ip2name which we want). Turning off NetBIOS, which was my quick solution, might not be applicable in production since it makes the machine stop advertising its presence (unless using Active Directory). Also, this only affects certain types of remote access; if you're on LAN, the server will most likely be able to resolve your ip immediately.
[15 Dec 2010 14:52] Viktor Štujber
more descriptive bug title, now includes the actual issue
[13 Sep 2012 11:40] Ståle Deraas
This problem is fixed in 5.5 and following releases with the IPv6 extensions.