MySQL Bugs: #5202: --skip-name-resolve not fully functional

Bug #5202	--skip-name-resolve not fully functional
Submitted:	25 Aug 2004 10:41	Modified:	1 Sep 2004 22:08
Reporter:	Marten Lehmann	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.20	OS:	Linux (RHELinux Kernel 2.4.21-15.0.2.EL)
Assigned to:		CPU Architecture:	Any

Description:
When the mysqld is started with --skip-name-resolve, no resolving of dns-names should be done. As many of my clients connect from various ip-addresses, I put % in the Host-field of the db-table. But as % isn't in the ip-address format, mysqld simply ignores --skip-name-resolve and tries to resolve the reverse-dns-name of the connecting client's ip-address. This causes some clients to fail to connect or it takes at least too much time.

How to repeat:
Connect to a mysql-server that's started with --skip-name-resolve from a client with an ip-address, that can't be looked up through reverse-dns because of a non-existing or temporarily failung nameserver.

Suggested fix:
--skip-name-resolve should really prevent mysqld from looking up dns-names in any case. Alternatively, another option doing this should be created or the timeout for resolves should be configurable.

Thanks you for the report!

I wasn't able to repeat it on latest BK 4.0 tree.

What do you exactly mean "too much time"?

> I wasn't able to repeat it on latest BK 4.0 tree.

What's the latest? I'm running 4.0.20 and the problem exists.

> What do you exactly mean "too much time"?

Clients fail to connect because of a timeout. This happens when the timeout on the client-side is less than the time it takes on the server-side to do the dns-lookup.

Sometimes, the timeout on the client-side isn't reached, because the server responded faster. But anyway: If it takes three seconds for the server to lookup a dns-record, the client has to wait this three seconds and a dynamical website will open very slowly.

As long as your dns runs fine, you won't notice this problem. But as soon as you dns-servers are offline or are that overloaded, that it takes several seconds to respond to the mysql-servers queries, you will ask the same like me: Why does mysql lookup reverse-dns entries while I told him not to do any resolvs.

We experienced this problem as well and after 5 long days of working on it we've discovered a solution.  Use a mixed IP address and wildcard in the host column value for the user in the user table of the mysql database.  

For example: 192.168.% 

and add skip-name-resolve to the /etc/my.cnf file in the [mysqld] section.

This way the client PC browsing via HTTP and connecting to MySQL via DBI will not attempt to do a reverse name lookup if the client is inside the network behind the device containing the ARP table and pointing to the DNS server.

Of course, you'll still need a user with the '%' host value for browsers outside of the network, for the times when the internet connection is working and the DNS server is available.