Mysql – ODBC can’t connect to internal MySQL without external network

dnsMySQLodbc

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?

Best Answer

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.

Related Question