Bug #69832 Replicated Servers - ConnectionString Breaking Change
Submitted: 24 Jul 2013 19:34 Modified: 28 May 2014 5:35
Reporter: Jonathan Oliver Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.7.4 OS:Any
Assigned to: Roberto Ezequiel Garcia Ballesteros CPU Architecture:Any

[24 Jul 2013 19:34] Jonathan Oliver
Description:
From version 6.6.5 to 6.7.4 of the .NET connector, there is a critical breaking change in how the Server/Host/Data Source/etc attribute of the connection string is parsed.  It used to be that you could specify multiple endpoints within a single "Server" attribute using a simple comma separator, e.g. "Server=host1.example.com,host2.example.com".

Using 6.7.4, the following exception is thrown when using the comma-delimited notation:

Unable to connect to any of the specified MySQL hosts. ---> System.Net.Sockets.SocketException: The requested name is valid, but no data of the requested type was found
   at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
   at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
   at MySql.Data.Common.MyNetworkStream.CreateStream(MySqlConnectionStringBuilder settings, Boolean unix)
   at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   --- End of inner exception stack trace ---
   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()

It appears that 6.7.4 has some kind of new "ReplicationManager" behavior.  I don't see anything specific in the documentation about how to utilize it.  By looking through the source code, it appears that we now need to create a ReplicationElement in the web.config/app.config to configure multiple replicated endpoints, and then we need to have the primary connection string "Server=" point to the various group names.

How to repeat:
Simply specify multiple endpoints within a given "Server=" attribute of a connection string and leave the optional "Replication=" attribute out.

Suggested fix:
Ideally, we'd be able to get the simple comma-delimited behavior back such that the connector chooses a given endpoint to connect to.  I have no problem with the new ReplicationManager and ReplicationConfiguration stuff giving users a bit more control, but the breaking change was a little unexpected.

For the time being, I will revert to 6.6.x
[15 Sep 2013 7:28] Moshe Lampert
I have to agree. the new options as no docs, and the old method do not work.

Waiting for examples,

Moshe
[17 Sep 2013 12:52] MySQL Verification Team
Hello and thank you for the report.

Per http://dev.mysql.com/doc/refman/5.6/en/connector-net-connection-options.html 

-------------- snippet -------------

"Host, Server, Data Source, DataSource, Address, Addr, Network Address	localhost	 : 

The name or network address of the instance of MySQL to which to connect. Multiple hosts can be specified separated by commas. This can be useful where multiple MySQL servers are configured for replication and you are not concerned about the precise server you are connecting to. No attempt is made by the provider to synchronize writes to the database, so take care when using this option. In Unix environment with Mono, this can be a fully qualified path to a MySQL socket file. With this configuration, the Unix socket is used instead of the TCP/IP socket. Currently, only a single socket name can be given, so accessing MySQL in a replicated environment using Unix sockets is not currently supported."

-------------- snippet -------------

Also have you referenced "MySql.Data.dll" ?
[18 Oct 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[18 Oct 2013 8:59] Moshe Lampert
before posting on bug I was tried the method described in docs, and its not work!  6.7.4 tried to lookup for "192.168.1.1,192.168.7.2" in the DNS server, and does not try to use 2 servers.
[20 Nov 2013 0:21] Roberto Ezequiel Garcia Ballesteros
The following link is an example about Replication and Load Balancing configuration:

https://blogs.oracle.com/MySqlOnWindows/entry/how_to_using_replication_load

Regards.
[20 Dec 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[20 Dec 2013 9:00] Moshe Lampert
sorry for the delay...

tried to do with app.config, the problem still here.

I uploaded a testcase.
[20 Dec 2013 9:04] Moshe Lampert
דוגמא

Attachment: RepTest2.zip (application/x-zip-compressed, text), 17.76 KiB.

[20 Jan 2014 12:08] Moshe Lampert
I updated the testcase in my machine to 6.8.3.
the original problem does not here, but I have a new one:
after a while I see this error message:  {"No available server found."}.

why?
[21 Jan 2014 23:51] Roberto Ezequiel Garcia Ballesteros
Hi Moshe,

I run your test project and I got the same error, the cause was I had no a database named 'db' and the connection internally was done but the database was not found. I tested it on 6.7.4 so if you create db schema you should not have any problem.

Please let me know your results.

Regards,
Roberto
[22 Jan 2014 0:07] Moshe Lampert
In 6.7.4 I was found errors and timeouts as I described in the bug.
I do have an database with the name "db".

In 6.8.3 - connection pooling was effectively disabled
http://bugs.mysql.com/bug.php?id=70467
[22 Jan 2014 0:09] Moshe Lampert
also, I was tried to test with IIS, simple ASP.net page and WCAT, after a while, IIS  hangs!
(same code, 6.6.5, works without problems for years)
[22 Jan 2014 1:05] Roberto Ezequiel Garcia Ballesteros
Probably the problem is timeout. Let's do another test:

1. Change to higher values in connection string for Connect Timeout and Default Command timeout (something like 999999 just for testing purpose)

2. Do a MySqlCommand with the statement 'set net_read_timeout=999999; set net_write_timeout=999999' and execute twice (or the number of servers you have) at the beginning of all the process

      Cmd.CommandText = "set net_read_timeout=999999; set net_write_timeout=999999"
      Cmd.ExecuteNonQuery()
      Cmd.ExecuteNonQuery()

Let me know the results.
Thanks.
[22 Jan 2014 1:16] Roberto Ezequiel Garcia Ballesteros
I was able to reproduce the bug. We will be working on the fix and also we will verify the connection pool when using replication.

Thank you for your help.
[22 Jan 2014 7:15] Moshe Lampert
I tried to test with your SQL query. in the first  task its works but not on the second one.
Conn.ServerThread still not reused.

{"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"}

   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at MySql.Data.MySqlClient.Replication.ReplicationRoundRobinServerGroup.GetServer(Boolean isMaster)
   at MySql.Data.MySqlClient.Replication.ReplicationManager.GetNewConnection(String groupName, Boolean master, MySqlConnection connection)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at convertFiles.Module1.DoTask() in D:\משה\המסמכים שלי\Visual Studio 2010\Projects\RepTest\Module1.vb:line 33
   at System.Threading.Tasks.Task.Execute()
[28 May 2014 5:35] Philip Olson
Fixed as of the upcoming Connector/Net 6.7.5 release, and here's the changelog entry:

Configuring replication and load balancing by passing in multiple servers
as a comma-separated list to the connection string would fail to function.
Threading synchronization problems when using replication (specifically
when getting a new server/connection) would also sometimes occur.

Thank you for the bug report.