MySQL Bugs: #49877: remote connection delay due to long netbios lookup timeout

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	connect, dns, gethostbyaddr, NetBios, timeout, windows

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.

Thank you for the bug report.

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.

more descriptive bug title, now includes the actual issue

This problem is fixed in 5.5 and following releases with the IPv6 extensions.