I recently updated the NuGet package for MySql.Data from 8.0.17 to 8.0.18 in one of my VB.NET (VS2017) projects. Prior to this update, I had no problem connecting to a named server which has multiple DNS entries pointing to different interfaces (and thus, IP addresses) on the same server. As soon as I updated the MySql.Data package, however, the connection started failing with the error "Unable to connect to any of the specified MySQL hosts".
My application has an error handler that logs the details of the exception to a file which it then sends to me via e-mail. Looking through the stack trace of the exception and its inner exception, I found that it seems to be doing something odd when it comes to a named host (<SERVERNAME> below). The inner exception indicates that it's returning multiple addresses to the MySqlClient.NativeDriver.Open() method, which is apparently causing it to fail.
I changed the connection properties to use a specific IP address from DNS assigned to that host, and the connection worked just fine. I also tried with other specific IP's for that host, and they all came back successful.
I'm running VS2017 on Windows 10 Pro (x64) in a domain environment. I've been able to modify my code to make a call to System.Net.Dns.GetHostEntry so that I can get a valid IP as resolved by DNS for a named host before attempting to open the connection, but that seems a bit excessive. I need to have the ability/option to pass in a simple host name and not have MySql.Data break.
Here are the (obfuscated) details gathered by my application's error handler:
------------------------------------------------------------------------
EXCEPTION DETAILS
------------------------------------------------------------------------
Error Type: MySql.Data.MySqlClient.MySqlException
Error Source: MySql.Data
Error Message: Unable to connect to any of the specified MySQL hosts.
------------------------------------------------------------------------
MYSQL DATABASE ERROR DETAILS
------------------------------------------------------------------------
Database Error: Unable to connect to any of the specified MySQL hosts.
Error Code: -2147467259
------------------------------------------------------------------------
STACK TRACE
------------------------------------------------------------------------
at MySQLDB.Open() in clsMySQL.vb:line 1175
at MySQLDB.TestConnection() in clsMySQL.vb:line 852
at ValidateUser() in DataTier.vb:line 2538
------------------------------------------------------------------------
EXCEPTION DATA
------------------------------------------------------------------------
Server Error Code: 1042
ConnectionString: server=<SERVERNAME>;port=3306;database=<DBNAME>;user id=<USERNAME>;password=<PASSWORD>
Stack Trace from MySql.Data.MySqlClient:
at MySql.Data.MySqlClient.NativeDriver.Open()
at MySql.Data.MySqlClient.Driver.Open()
at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
at MySql.Data.MySqlClient.MySqlPool.GetConnection()
at MySql.Data.MySqlClient.MySqlConnection.Open()
at MySQLDB.Open() in clsMySQL.vb:line 1169
------------------------------------------------------------------------
INNER EXCEPTION
------------------------------------------------------------------------
Error Type: System.InvalidOperationException
Error Source: System.Core
Error Message: Sequence contains more than one matching element
Exception Level: 1
Stack Trace from Inner Exception (1):
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)
at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings)
at MySql.Data.MySqlClient.NativeDriver.Open()
Please let me know if you require any additional information. Thank you.