MySQL Bugs: #9477: Long connection delay from Admin client, MyODBC, but not from command line

Bug #9477	Long connection delay from Admin client, MyODBC, but not from command line
Submitted:	30 Mar 2005 7:57	Modified:	30 Mar 2005 19:17
Reporter:	Christian Schlepphorst	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
Contact to the server from a remote machine has a long overhead before reaction comes. Seems not to be a DNS resolution problem. 

Symptoms:
- Delay of 5-10 seconds when a linked table is opened the first time via MyODBC. 
- Consecutive operations on the same table are immediate, no delay (either the connection is established and reused, or data is cached somewhere?). 
- Other tables show again the 5-10 sec delay on first open.
- Any operation with MySQL Administrator client from a client machine has the delay, even simple Refresh Catalog View.
- Administrator client on localhost (the server machine itself) has no delay.
- The command line client mysql.exe works fast, with no delay, even on remote clients.

- NO delay with MyODBC or Admin client from client machine if server runs under Windows 2000 Server !

Maybe the problem is connected to the closed bug # 482?

How to repeat:
- Install MySQL 4.1.10 under Windows 2003 Server
- Install Admin client on remote machine under any Windows operating system
- Open Admin client, show Catalog view, press Refresh. Each time refresh is pressed, the view vanishes for several seconds.

Suggested fix:
Downgrade to Windows 2000 Server if possible.

I wasn't able to repeat this behavior with Windows 2003 Server and connecting
from a XP client. If you are using IP numbers for user's authentication can you
test starting the server with --skip-name-resolve ?

I followed your recommendation, set the parameter to skip name resolution (on Admin client) and stopped and restarted the server.

The delay vanishes.

So the delay IS caused by DNS problems, nevertheless. This is a feasible workaround for us. But I still think there must be something buggy in the combination of Windows 2003/MySQL 4.1.10/Admin Client because

- mysql.exe from command line is fast even with server name instead of IP adress

- without the parameter switch, admin client is slow even when server is given by IP address

- the problem seems not to arise under Windows 2000 Server.

I am not an Windows administration specialist, so there might be something I am missing in the DNS setup that is not a MySQL problem, though.

Thanks for the help anyhow.

Best regards

C. Schlepphorst

I checked my DNS settings and found a problem on my Win2003 installation (that my Win2000 installation didn`t have). After fixing, the connection works fast with the full server name as well. So it *was* the DNS (I thought I checked that...) and no MySQL bug :-)

Could you please comment here the DNS's setting which caused this
issue (for to share this information for others users). I will change
the status to not a bug after this.

Thanks in advance.

I will try to describe what I did.

The whole problem was created mainly because I don't know enough about the details of DNS, WINS and Active Directory and all that, so I hope it makes sense what I am writing here.

The MySQL server was set up only as a test system. I installed Win2003 Server from scratch and used the wizards to declare it as a primary domain controller (myserver.mydomain.local, IP 192.168.0.1). No connection to other servers or the internet. An XP Home client was connected via Ethernet cable, IP assigned via DHCP (got 192.168.0.33).

Things like "ping myserver" from the client worked fine, so I assumed the DNS to be working (that was actually not the case).

Under that constellation, using MySQL via ODBC or Admin Client from the XP client works, but with the delay described in the bug report. (Connect to myserver, root, rootpwd)

Actually, it seems the Win2003 wizards are not configuring DNS completely. I tested "NSLOOKUP myserver" and "NSLOOKUP myserver.mydomain.local" from the client command line, and got an error like "Server 192.168.0.1 not found" (192.168.0.1 was specified in the TCP/IP settings as DNS host).

Looking into the DNS administration, I found that a Forward Lookup Zone was defined, but no Reverse Lookup Zone, d.h. DNS could not get from the IP 192.168.0.1 back to the name of the server.
I defined a Reverse Lookup Zone "192.168.0.x Subnet" with 192.168.0.1 pointing to myserver.mydomain.local, rebooted both machines, and DNS worked.

However, now I had to specify the server name fully qualified when connecting to the MySQL server, i.e. myserver.mydomain.local, root, rootpwd. But the delay is gone.

A question remains for me, why MySQL has to do the name lookup on the server machine at all, especially if the server address is already given as IP address. And why does the delay happen with Admin Client and ODBC, but not with the command line client?

Thank you for the feedback. I can't say you what is/are the reason(s) for the
mysql client behaves different than the GUI client and Connector/ODBC
but now there is a chance for to test this issue by our development team
and try to fix it is necessary.

It seems that the server does a reverse DNS lookup.  If the netblock does not have it set up you get the long delays.

I was a little vague in my last comment.
I am running mysql on a freebsd host.  I installed myodbc 3.51.12 on a
windows 2003 server.  We were having horrible slow connection problems.
After reading this thread I saw that the windows 2003 servers IP addresses (it has an entire netblock assigned to it)  did not have reverse dns set up on them yet.  Once I added in the reverse entries all connection problems went away.

I hope this helps someone else from frustration!

Aaron