| Bug #6826 | Long delay when connecting to Linux MySQL server from Windows MyODBC clients | ||
|---|---|---|---|
| Submitted: | 25 Nov 2004 15:15 | Modified: | 28 Jan 2005 6:36 |
| Reporter: | d di (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.10 | OS: | Linux (SuSE 9, Windows 2000) |
| Assigned to: | Dean Ellis | CPU Architecture: | Any |
[6 Dec 2004 19:54]
MySQL Verification Team
Thank you for the bug report.
[20 Dec 2004 13:24]
d di
The source line that causes the delay is in libmysql.c in the function create_named_pipe(...).
The function makes one call to CreateFile that takes ~25 seconds in the described environment.
Source code:
===========
if ((hPipe = CreateFile(pipe_name,
GENERIC_READ | GENERIC_WRITE,
0,
NULL,
OPEN_EXISTING,
0,
NULL )) != INVALID_HANDLE_VALUE) break;
===================
[20 Dec 2004 13:33]
d di
FYI: The pipe name is "\\<server-ip>\pipe\MySQL". The return code from CreateFile is -1. Proposed solution: The error in my opinion is that MyODBC (libmysql, really) wrongly assumes that since the client is running on Windows (__WIN__), the server must also be [running on Windows] and thus it is a good idea to try to use Windows named pipes. Another big mistake, IMHO, is that create_named_pipe(...) assumes that CreateFile is instantaneous and thus does not take connection timeout into account when it connects using named pipes. The problem is a lot easier to reproduce on 3.51.09 than 3.51.10, we're still trying to figure out why.
[22 Dec 2004 13:18]
d di
Found a workaround. Install Samba on the server and allow it through the firewall or disable the firewall. The clients running on Windows now gets an immediate answer that there is no such named pipe available and drops further connection attempts, eliminating the remaining 3-6 seconds of connection delay.
[27 Jan 2005 15:22]
d di
We've found out that I've been seeing two distinct, unrelated delay problems. The connection delay that stems from the named pipe problem (CreateFile) only occurs on version 3.51.9, it is a different delay problem I'm seeing on 3.51.10. Thus all comments on this bug regarding the Named Pipes problem (all up till now) only regards MyODBC version 3.51.9. 3.51.10 had been installed on all machines that exhibited the delay, and all machines had been restarted endlessly. However the MyODBC DLL had apparently not been replaced on some of the machines. There seems to be a bug in the installer, but I'm not going to pursue this any further. (I'll just double-check the DLL version after restarting next time ;-)). The remaining delay problem I'm seeing with 3.51.10 has to do with Windows and not MyODBC. Please close this bug :-)
[28 Jan 2005 6:36]
Dean Ellis
Closing; noting that the ability to specify the named pipe to use (or to not specify one), made available by 3.51.10, resolves the delay that was occuring in earlier releases.
Description: When connecting to a MySQL server running on Linux through VBScript (/ASP) or .NET using the MyODBC driver, the connection attempt takes approx. 23 seconds. This is regardless of the connection timeout, which in our tests were set to 3 seconds (it's used for timing-sensitive applications, so in the production environment the timeout would probably be 1 or 2 seconds). When switching to a (much slower) MySQL server running Windows, the problem disappeared. I fired up a network sniffer to further establish what was going on. The result was that the MySQL client tries to establish a NetBIOS connection to the server before trying to establish the MySQL connection. The Linux server doesn't listen for NetBIOS traffic, but the client waits for a total of 23 seconds anyway for the connection to be established, before it connects to the MySQL server port. Details: The Windows client first tries to establish a TCP connection to port 445 (microsoft-ds), then immediately (almost instantaneously) following it a connection to port 139 (netbios-ssn) on the server. The Linux server silently drops the SYN packets, so the TCP connection is never established. The client nevertheless waits 3.2 seconds for the connection to be established. Then the client tries again with a tcp connection to both port 139 and 445, this time waiting a whole 6.6 seconds before moving on. The client then does one last attempt, this time it waits for a whopping 13.3 seconds for a reply. First _then_ is the MySQL connection to port 3306 is made, completing (including TCP handshake) in 0,179 second. From the packet trace while using the Windows server, it seems that the client is requesting to open a file (?) named '\MySQL' on the server. The Windows server simply replies back that there is no such file. Reverting to MyODBC version 3.51.9 and 3.51.06 did not seem to have any effect on the problem. Configuring the server to send RST packets ("no service listening on port") on port 139 and 445 brings down the delay to a couple of seconds, which is unfortunately still unacceptable for the applications in question. Both the servers and the clients are on the same network segment and IP subnet. If the test is done from a client on a different subnet it seems that the NetBIOS request is never made. (The clients used on the other subnet were all Windows XP computers, so this might have something to say too.) Likewise when testing using the "ODBC Data Sources" in the Control Panel instead of through .NET or VBScript - the NetBIOS connections are never attempted. It even seems that running one or two connection tests from the ODBC Data Sources control panel (perhaps also re-installing the MyODBC 3.51.10 driver a couple of times first) causes the problem to go away - the client suddently decides to not attempt to establish NetBIOS connections anymore? Additional information: This is a packet trace from a MySQL Windows client to a MySQL Linux server, showing the connection delay. No. Time Source Dest. Prot. Info 1 0.000000 Client Server TCP 4218 > microsoft-ds [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 2 0.000313 Client Server TCP 4219 > netbios-ssn [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 3 3.208629 Client Server TCP 4219 > netbios-ssn [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 4 3.208647 Client Server TCP 4218 > microsoft-ds [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 5 9.771469 Client Server TCP 4219 > netbios-ssn [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 6 9.771502 Client Server TCP 4218 > microsoft-ds [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 7 23.034080 Client Server TCP 4269 > 3306 [SYN] Seq=0 Ack=0 Win=64240 Len=0 MSS=1460 8 23.034214 Server Client TCP 3306 > 4269 [SYN, ACK] Seq=0 Ack=1 Win=5840 Len=0 MSS=1460 9 23.034233 Client Server TCP 4269 > 3306 [ACK] Seq=1 Ack=1 Win=64240 Len=0 10 23.207308 Server Client MySQL Server Greeting Protocol : 10 ,version: 4.1.7-standard Caps: 0xffffa22c 11 23.212793 Client Server MySQL Login Request Caps: 0x20cf ,user: root ,password: PASSWORD 12 23.212907 Server Client TCP 3306 > 4269 [ACK] Seq=65 Ack=31 Win=5840 Len=0 13 23.212952 Server Client MySQL Response OK How to repeat: Use the code below. Setup a MySQL server and a MySQL Windows client on the same subnet. Make sure that the server has TCP port 139 and 445 blocked or "stealthed", eg. by running the server on Linux with Samba disabled. The delay you will see will be 2-4 seconds if the server is configured to deny connections and about 23 seconds if the server silently drops packets to these ports. In our setup, there has previously been 3.51.1 through .3, 3.51.06 and a 3.51.9 ODBC driver on the clients. I don't know if this has any importance. Also, in our setup, the first server used was a Windows MySQL server, which was later replaced by a Linux MySQL server on the same IP address. I don't know if this has any importance. VBScript code (modify connection string to match circumstances, add "Server." in front of CreateObject if running in ASP page): <code> s = "Driver={MySQL ODBC 3.51 Driver};Server=<server IP>;UID=<user name>;PWD=<password>;DATABASE=mysql;OPTION=3 MsgBox "Press enter to connect" Set conn = CreateObject("ADODB.Connection") conn.Open s MsgBox "Done connecting" </code> C# .NET code (modify connection string to match circumstances): <code> using System; using System.Data.Odbc; class MyOdbcTest { static void Main(string[] args) { string s = "Driver={MySQL ODBC 3.51 Driver};Server=<server ip>;Uid=<user name>;Pwd=<password>;Database=mysql;OPTION=3"; OdbcConnection conn = new OdbcConnection(); conn.ConnectionString = s; conn.ConnectionTimeout = 3; Console.Out.WriteLine("Connecting"); DateTime before = DateTime.Now; conn.Open(); Console.Out.WriteLine("Finished connecting in " + (DateTime.Now - before)); } } </code> Suggested fix: A connection string (OPTION=) flag to disable NetBIOS connection attempts. Information regarding why and when the NetBIOS traffic occurs in the manual.