1

I have a small database on a server in my LAN that I try to access remotely via ODBC.

Currently, I am making a program in Visual Basic to access, retrieve and display data from the server to an Excel file.

Sometimes, the program will stop working all of a sudden and display an error message:

[MySQL][ODBC 8.0(w) Driver]Can't connect to MySQL server on '{the IP address of the server with the correct port}'(10060)

My troubleshooting efforts so far:

  • Tried to connect with the ODBC connector remotely: Failed
  • Tried to connect with MySQL Workbench and access the Database remotely: Failed
  • Tried to connect to the Database locally with HeidiSQL: Can connect without a problem
  • Tried to connect to the Database locally with MySQL workbench: Can connect without a problem

With HeidiSQL, went to Host -> Status and checked the "Connections" value : Increase at every connection attempt. Checked the Connection_errors_xxx fields : all are 0 and never change.

I don't think the problem is the connection string as the program worked well before and nothing changed since. My guess is that either a firewall or the database itself is blocking incoming connections from my computer for some reason.

I tried to search if there was a way of having details on incoming connection requests (like in a Log file) but couldn't find anything.

The only way I found to "resolve" the problem is to wait some time (maybe a couple of hours) and then try to connect again.

Would anyone have an idea of where I could find the reason or some explanation on why this problem is happening and how to fix it?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
SuperRamen
  • 13
  • 1
  • 5

2 Answers2

0

As you previously said,

  1. Check the firewall: Ensure that the firewall is not blocking incoming connections to the database. You can check the firewall settings on both the client and the server.
  2. Verify that the server is configured to allow remote connections and that the correct port is open. You can also try increasing the max_connections value in the MySQL configuration file if it is set too low.

These are what i am thinking of, right now.

Vasileios G
  • 43
  • 1
  • 7
0

I was just working on a similiar issue, where an application suddenly stopped working. In that case, a short value for connection timeout was built into the application (or at least it was not documented how to adjust it). Running the same application locally on the server worked fine and the MySQL Server had no performance issue at all, neither the network in general.

Turned out that a DNS Error caused the issues. MySQL by default tries to resolve a client IP to hostname before responding to login (for authenticating user@hostname - if hostname can be resolved, instead of the fallback user@IP). And the name lookup took more than 2 seconds because of a DNS Server failure (probably the primary forwarding DNS server of the servers DNS server does not respond or is unreachable - and the server only has one DNS server configured so could not even send DNS queries to multiple DNS servers instead). So fix the DNS configuration, make the IP address resolve to hostname without doing DNS or disable the MySQL servers reverse lookup for login (if not needed) helps.

Elias Ohm
  • 1