| Bug #97448 | MySQL.Data v8.0.18 fails if more than one IP is found in DNS for a named host | ||
|---|---|---|---|
| Submitted: | 31 Oct 2019 19:02 | Modified: | 3 Mar 2020 21:48 |
| Reporter: | Jeremy Gamble | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 8.0.18 | OS: | Windows (Windows 10 Pro v10.0.17134) |
| Assigned to: | CPU Architecture: | Other (x64 - Intel Core i7-4770 @ 3.4GHz, 4 cores, 8 logical processors) | |
[31 Oct 2019 19:02]
Jeremy Gamble
[1 Nov 2019 14:13]
Jeremy Gamble
Based on just a quick reading of the description of the "Connection Pooling with Multiple Servers" section for the MySqlConnector, it seems my current, temporary solution of using System.Net.Dns.GetHostEntry(<SERVERNAME>).AddressList(0).MapToIPv4.ToString() to resolve a hostname to an available IP(v4) address follows along the lines of the "Failover" option: "All connections will initially be made to the first server in the list." Obviously, in my particular scenario, there's not really a need for a connection "pool" - all of the IP's point to the same server - so it would likely be best to simply pick one and go with it. I'm not sure if there would be additional tests possible to determine the "best" IP, but it would likely be good to at least do a basic connectivity test and use the first one that works or throw an exception only when there's actually a problem connecting to *all* of the IP's in the list. This could still allow for the user to provide a specific IP address in the event that one or more of those interfaces has some sort of port blocking or other sort of connection filtering enabled that would prevent a connection to the MySQL server. Otherwise, if a hostname is provided, the connector would establish the connection to the first IP in the list with which it can successfully communicate. My concern, however, is that the connector, instead of automatically returning a single IP or even a list of IP's, simply throws an exception when more than one IP is found. That exception is somewhat unhelpful in that the message, "Unable to connect to any of the specified MySQL hosts", can be a little confusing. There is only one actual host to which the connection should be established. The question is simply, if that hostname resolves to more than one IP address, which interface should be used to connect. Often in these situations - specifically, multiple interfaces on the same host - nobody cares which interface is used for the connection unless additional security measures have been implemented to block/prevent communication on a particular interface.
[1 Nov 2019 16:20]
Bradley Grainger
> My concern, however, is that the connector, instead of automatically returning a single IP or even a list of IP's, simply throws an exception when more than one IP is found. Right. As I see it, you have three options: 1) Downgrade to 8.0.17. 2) Use your workaround of performing DNS resolution yourself, then using an IP address in the connection string. 3) Switch MySQL libraries (to MySqlConnector).
[6 Nov 2019 3:38]
Patrick Loftus
Confirmed same issue connecting to an AWS RDS instance. AWS only published a DNS host name for the instance, no IP address. Although I could get the currently assigned IP address, it can change over time, so that is not an option. Currently downgrading to 8.0.17 until issue is fixed.
[6 Nov 2019 15:44]
Jeremy Gamble
As I stated in my OP, I've implemented a "workaround" for this issue that involves resolving the IP address of the hostname from DNS for passing to the connection string. Following is the (VB.NET) code I have in a class constructor for connections to a MySQL database server instance:
------------------------------------------------------------------------
Dim MyCSB As New MySqlConnectionStringBuilder
If Not IsHostIPAddress(Hostname) Then
MyCSB.Server = System.Net.Dns.GetHostEntry(Hostname).AddressList(0).MapToIPv4.ToString()
Else
MyCSB.Server = Hostname
End If
' Other property setters
------------------------------------------------------------------------
Where the IsHostIPAddress() method is defined as follows:
------------------------------------------------------------------------
Public Function IsHostIPAddress(ByVal HostAddress As String) As Boolean
Dim Valid As Boolean = False
If Not String.IsNullOrEmpty(HostAddress) AndAlso HostAddress.Contains(".") Then
If IsNumeric(HostAddress.Replace(".", "")) Then
Dim AddressParts() As String = HostAddress.Split({"."c}, StringSplitOptions.RemoveEmptyEntries)
Dim ValidIP As System.Net.IPAddress = Nothing
If AddressParts.Length = 4 AndAlso System.Net.IPAddress.TryParse(HostAddress, ValidIP) Then
Valid = True
End If
End If
End If
Return Valid
End Function
------------------------------------------------------------------------
This seems to provide the desired result and allows me to pass either a DNS hostname or an IP address into my constructor. Not sure if this would be helpful in your situation, but I wanted to share for anyone else that comes across this bug report and is looking for a workable solution other than downgrading or switching connectors entirely until a fix is released.
[6 Nov 2019 16:46]
Patrick Loftus
Jeremy: Thanks for reporting the issue and providing the workaround code. I am going to stick with 8.0.17 for now until this is resolved. AWS RDS instances are referenced by DNS hostname only and although I could resolve this to the IP address for each call, we would prefer to just use the hostname, as recommended by AWS. There is nothing compelling in 8.0.18 that we are losing by downgrading, just the inconvenience of not being able to apply all package updates with one command (Update-Package) for updating our apps prior to deployment.
[12 Nov 2019 20:18]
Patrick Loftus
Jeremy - We switched to MySQLConnector 0.61.0 (https://www.nuget.org/packages/MySqlConnector/) and the issue is resolved. All that was required was to uninstall the MySql.Data package, remove the residual dll/xml files from /bin to prevent conflicts and then install MySqlConnector package. The API is presented identical to MySql.Data for all of the various calls we were using, so it was just a recompile, deploy to test and verify operation. Can't speak to if it is faster, although I saw that mentioned in the docs (https://mysqlconnector.net/). I prefer this to downgrading as we can march along doing updates to applications without stalling the version for the known issue.
[12 Nov 2019 20:26]
Patrick Loftus
Jeremy - Just did a series of side-to-side tests on our app using MySql.Data 8.0.17 versus MySqlConnector 0.61.0. MySqlConnector is noticeably faster (from a user experience factor) and in a few complicated search tests was about 35% faster for a 500 row SELECT. We will continue to test for another 2 days and then roll-out this to production, as it looks like a winner.
[13 Nov 2019 14:44]
Jeremy Gamble
Patrick - Thank you for sharing your experience. I've been considering the possibility of switching over to MySQLConnector, although I've been trying to give the "official" library some time to get this particular issue corrected. Still, if you're noticing a (relatively) significant performance increase and no apparent interoperability issues, it certainly may be time to give this whole thing another look.
[13 Nov 2019 14:53]
Patrick Loftus
Yeah, we actually wrapped our testing up on one app yesterday and pushed to production. The performance boost is significant. We took a long running report that would normally be async and return maybe 30 seconds later in the queue, changed it to sync for testing and the page brought the report back in 18 seconds. Complicated joins and a lot of rows. Not that we would leave it in sync mode, but we wanted a big data set to really see the difference. We now plan to roll out the change to all of our apps.
[3 Mar 2020 21:20]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL Connector/NET 8.0.20 release, and here's the proposed changelog entry from the documentation team: A connection made to a named server with multiple DNS entries pointing to different IP addresses for the same server generated an exception. Now, only the first element is returned when multiple elements are found. Thank you for the bug report.
[3 Mar 2020 21:48]
Jeremy Gamble
I'm curious with regards to the wording in the changelog notes. Will the 8.0.20 revision *only* return the first IP found during DNS translation, or will it do any additional testing to ensure that the IP it "chooses" is actually capable of communication with the MySQL database? I ask because there are some cases I've encountered over the years where a device with multiple interfaces has only a single path for that traffic - either the other interfaces have additional port blocking/filtering or other security measures in place, or the MySQL server is configured to only listen on a specific interface, or both. If additional testing is done in the connection process, can some indication of this be added to the changelog notes?
[21 Apr 2020 12:28]
MySQL Verification Team
Bug #98791 marked as duplicate of this one.
[5 Jun 2020 1:02]
Viacheslav Latypov
Hello guys, During the "fresh" installation of MySQL 8.0.20, I faced error 1042; Sequence contains more than one matching element. In which way is it possible to fix the issue on 8.0.20?
[5 Jun 2020 14:51]
Daniel Valdez
Posted by developer: Hi Viacheslav Latypov, Can you provide us a scenario when this is happening to you? Maybe a "how-to-repeat" piece of code. Thanks, Daniel
[5 Jun 2020 15:51]
Viacheslav Latypov
Hi Daniel, Sure. The scenario is pretty simple. I just downloaded MySQL v8.0.20 and started the installation process on Win10 X64. The problem appears during the configuration step (starting the server). The message about attempts can be seen in the log. After 10 retries, the configuration wizard displays the error. A particular feature that I want to highlight that I did not have any DB on the system before I tried to install v8.0.20 I put the part of the log here: https://forums.mysql.com/read.php?169,687103,688090#msg-688090 Also, you can find a few amounts of steps for resolving the issue that I tried to do on this link: https://forums.mysql.com/read.php?169,687103,688087#msg-688087 MySQL v8.0.17 does not have this problem. As I can see, the issue exists on the 8.0.18-8.0.20. Best Regards, Slava
[5 Jun 2020 17:08]
Daniel Valdez
Posted by developer: Ok, so by reviewing your scenario it may seems this issue is related to MySQL Installer, not properly for Connector/NET. I'm going to contact the MySQL Installer team to look into this.
