MySQL Bugs: #69832: Replicated Servers - ConnectionString Breaking Change

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

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

I have to agree. the new options as no docs, and the old method do not work.

Waiting for examples,

Moshe

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" ?

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".

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.

The following link is an example about Replication and Load Balancing configuration:

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

Regards.

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".

sorry for the delay...

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

I uploaded a testcase.

דוגמא

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

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?

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

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

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)

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.

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.

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()

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.