1

Running ODBC 5.2 (10.0.0.10) and MySQL 4.x (10.0.0.50). Yes, I know that ODBC 5.x dosen't officaly support MySQL 4.x but it was working fine for ~8 months till now when we have little problems with ISP and external network is not that stable.

Thing is that ODBC can make connection with MySQL fine when external network is working but in a moment when we get problem with external network the connection between 10.0.0.10 (ODBC) and 10.0.0.50 (MySQL) stops.

OLE DB provider "MSDASQL" for linked server "xyz" returned message\
"[MySQL][ODBC 5.2(w) Driver]Lost connection to MySQL server at\
'waiting for initial communication packet', system error: 10060".

Cannot initialize the data source object of OLE DB "MSDASQL" for linked server "xyz".

... and it gets even more interesting I can successfully ping between ODBC and MySQL machines and even make telnet for 3306 from 10.0.0.10 to 10.0.0.50 while external network dosen't work and ODBC connection fails.

This leads to my question... why ODBC can't make connection to MySQL without external network? Have I missed something or should I downgrade to ODBC 3.51?

  • do check the trace route and see are you using external network to reach at destination MySQL machine when you do connect to it, seem some network related issue. are entries present in /etc/hosts?. Do add the address for both machines and see. – Nawaz Sohail Jan 27 '17 at 14:42
  • Traceroute dosen't end up using external network. Oh and I forgot to tell that both machines are on Windows. – Gen Jan 27 '17 at 14:47
  • 1
    then add entries in C:\Windows\System32\drivers\etc\hosts there may be a chance that they would be using DNS of external netwrok, give it a try. – Nawaz Sohail Jan 27 '17 at 15:08
  • 2
    Random guess, the MySQL server is trying to do a reverse DNS lookup on the client's IP and instead if immediately getting a response or NXDOMAIN, it's timing out and failing to continue the connection setup. If 4.x (seriously, now...you are way behind the curve) has the skip-name-resolve option to disable these lookups by the server, that might resolve the issue if you add it to config and restart MySQL. As random as this sounds, it's the only external dependency thst comes to mind. – Michael - sqlbot Jan 29 '17 at 1:25
  • @Michael-sqlbot thank you! skip-name-resolve did resolve the issue. Now it's time to get this thing upgraded. – Gen Apr 2 '17 at 21:03
2

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same.

https://dev.mysql.com/doc/refman/5.7/en/host-cache.html

(Docs here are 5.x, but this behavior is present pre-5.x).

Assuming loss of the external network also means the MySQL server can no longer reach the DNS server, then the lack of timely DNS responses will stall the handshaking logic until they get back some kind of timeout error... by which point, ODBC has already given up and thrown this error.

The solution is to disable the reverse DNS lookups with each client connection by starting the server using --skip-name-resolve. Unless you have users in the mysql.user table with actual hostnames in the Host column, the default behavior is of no real value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.