Persistent Connections / Failover

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
manuelmaurer
Posts: 4
Joined: Thu 18 Jul 2019 08:03

Persistent Connections / Failover

Post by manuelmaurer » Thu 18 Jul 2019 08:32

Hi,

I am getting started with dotConnect and am quite happy with the functionality. I am connecting to a Galera cluster with 3 servers, if one of them is unavailable, the connection should move to the next server.
At the moment this is done by using a local load balancer (pen) which does the job nicely, but I would like to move away from it as it is an additional tool I have to use.

My code for the initial evaluation was this:

Code: Select all

public Database()
{
	_conn = new MySqlConnection()
	{
		LocalFailover = true,
		ConnectionString = GetConnectionString("ConnString"),
	};
	_conn.ConnectionLost += _conn_ConnectionLost;
}

private int errc = 0;

private void _conn_ConnectionLost(object sender, ConnectionLostEventArgs e)
{
	if (errc++ > _maxretry)
	{
		e.RetryMode = RetryMode.Raise;
		return;
	}
	if (e.Cause == ConnectionLostCause.Execute)
	{
		if (e.Context == ConnectionLostContext.None)
			e.RetryMode = RetryMode.Reexecute;
		else
			e.RetryMode = RetryMode.Raise;
	} else {
		e.RetryMode = RetryMode.Raise;
	}
}
In combination with an external load balancer this works like a charm. For production I needed a way to reset the error counter in case the connection was ok. As I have a lot of functions that query data I was not willing to reset the counter after every successful request, therefore I tried to play around with the StateChanged event:

Code: Select all

private void _conn_StateChange(object sender, StateChangeEventArgs e)
{
	if (e.CurrentState.HasFlag(ConnectionState.Open)) errc = 0;
}
After enabling this StateChange-Event on the connection object, I can see, that it opens and closes the connection for every query. As Timing is critical in this application, I want to keep the connection open as long as possible.
I tried setting the KeepAlive paramter in the connection string and also the PingInterval parameter in the connection object. Both settings did not change the behaviour.
Is there any way to disable this behaviour?

Bonus-Question: If I want to replace the external load balancer with the option to write multiple hosts in the connection string, how do I handle the switch to the next server? Do I need to do that in the ConnectionLost EventHandler?

Regards
Manuel

manuelmaurer
Posts: 4
Joined: Thu 18 Jul 2019 08:03

Re: Persistent Connections / Failover

Post by manuelmaurer » Thu 18 Jul 2019 10:04

I've played around with the different options and have now moved to an solution that works, although I do not really like it.

I read the connection string, extract the servers and split them into an array. In case the connection is lost, I close it (otherwise it would be taken from the pool again) and manually assign the next server in the list. As this is going to be a windows service which runs for weeks or months at a time, I need a way to reset the error counter. For this I am using an backgroundworker which resets it in case there was no error in the last x seconds. It is not a pretty solution, but it will have to do for the moment.

Code: Select all

private string[] _servers;
const int _maxretry = 4; //Retry per server - dotconnect tries 2 times itself
private BackgroundWorker _bgw = new BackgroundWorker() { WorkerSupportsCancellation = true };

public Database()
{
	_conn = new MySqlConnection()
	{
		LocalFailover = true,
		ConnectionString = GetConnectionString("ConnString"),
		ConnectionTimeout = 3
	};
	_servers = _conn.Host.Replace("(", "").Replace(")", "").Split(',');
	if (_servers.Length == 0) throw new Exception("Invalid connection string");
	_conn.Host = _servers[0];
	_conn.ConnectionLost += _conn_ConnectionLost;
	_bgw.DoWork += _bgw_DoWork;
	_bgw.RunWorkerAsync();
}

private void _bgw_DoWork(object sender, DoWorkEventArgs e)
{
	while (!_bgw.CancellationPending)
	{
		if (DateTime.UtcNow.Subtract(_lastError).TotalSeconds > 10 && errc > 0) errc = 0;
		System.Threading.Thread.Sleep(500);
	}
}

private int errc = 0;
private DateTime _lastError = DateTime.UtcNow;

private void _conn_ConnectionLost(object sender, ConnectionLostEventArgs e)
{
	_lastError = DateTime.UtcNow;
	if (
		errc > (_servers.Length * _maxretry) ||
		e.Context == ConnectionLostContext.HasPrepared ||
		e.Context == ConnectionLostContext.InTransaction
	)
	{
		errc = 0;
		e.RetryMode = RetryMode.Raise;
	}
	else
	{
		_conn.Close();
		_conn.Host = _servers[errc % _servers.Length]; //Use next server
		e.RetryMode = RetryMode.Reexecute;
	}
	errc++; //MUST be done last, otherwise backgroundworker might reset it
}
If there is a similar solution without the workarounds please let me know.

Regards
Manuel

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: Persistent Connections / Failover

Post by Shalex » Fri 19 Jul 2019 17:01

manuelmaurer wrote:
Thu 18 Jul 2019 08:32
After enabling this StateChange-Event on the connection object, I can see, that it opens and closes the connection for every query. As Timing is critical in this application, I want to keep the connection open as long as possible.
By default, pooling is turned on. As a result, the actual connection is not closed in order to be used later by your application. This boosts performance greatly. Refer to https://www.devart.com/dotconnect/mysql ... q.html#q56.
manuelmaurer wrote:
Thu 18 Jul 2019 08:32
Bonus-Question: If I want to replace the external load balancer with the option to write multiple hosts in the connection string, how do I handle the switch to the next server? Do I need to do that in the ConnectionLost EventHandler?
dotConnect for MySQL allows using load balancing with MySQL clusters by specifying several hosts via the Host connection string parameter, like "Host = (localhost:3307,db:3308,db)". Refer to https://www.devart.com/dotconnect/mysql ... ncing.html.

manuelmaurer
Posts: 4
Joined: Thu 18 Jul 2019 08:03

Re: Persistent Connections / Failover

Post by manuelmaurer » Tue 27 Aug 2019 11:10

Thanks for the links, especially the second one was very helpful.
Shalex wrote:
Fri 19 Jul 2019 17:01
manuelmaurer wrote:
Thu 18 Jul 2019 08:32
After enabling this StateChange-Event on the connection object, I can see, that it opens and closes the connection for every query. As Timing is critical in this application, I want to keep the connection open as long as possible.
By default, pooling is turned on. As a result, the actual connection is not closed in order to be used later by your application. This boosts performance greatly. Refer to https://www.devart.com/dotconnect/mysql ... q.html#q56.
Pooling itself is ok, however it does not work well when one of the servers is gone. Therefore I am using pooling, but not with the load balancing option.
Shalex wrote:
Fri 19 Jul 2019 17:01
manuelmaurer wrote:
Thu 18 Jul 2019 08:32
Bonus-Question: If I want to replace the external load balancer with the option to write multiple hosts in the connection string, how do I handle the switch to the next server? Do I need to do that in the ConnectionLost EventHandler?
dotConnect for MySQL allows using load balancing with MySQL clusters by specifying several hosts via the Host connection string parameter, like "Host = (localhost:3307,db:3308,db)". Refer to https://www.devart.com/dotconnect/mysql ... ncing.html.
I've tried, but this does not work for me. In case the "master" is offline, it will always try to open the connection first. This takes a lot of time i do not want to spend.

I am more or less happy with my solution now, however, there is one problem. If the connection breaks exactly during a query, it will result in an exception 2013 (Lost connection to MySQL server during query). I really do not want to wrap all my queries into a retry loop, is there any other way to catch that error before it is thrown in the query itself?

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: Persistent Connections / Failover

Post by Shalex » Thu 29 Aug 2019 08:58

manuelmaurer wrote:
Tue 27 Aug 2019 11:10
I am more or less happy with my solution now, however, there is one problem. If the connection breaks exactly during a query, it will result in an exception 2013 (Lost connection to MySQL server during query). I really do not want to wrap all my queries into a retry loop, is there any other way to catch that error before it is thrown in the query itself?
"Lost connection to MySQL server during query" is thrown when the query execution exceeds the time set in cmd.CommandTimeout (in seconds, default value is 30). You can specify cmd.CommandTimeout explicitly or via the Default Command Timeout connection string parameter in the corresponding MySqlConnection object.

Refer to https://www.devart.com/dotconnect/mysql ... tring.html.

manuelmaurer
Posts: 4
Joined: Thu 18 Jul 2019 08:03

Re: Persistent Connections / Failover

Post by manuelmaurer » Thu 29 Aug 2019 13:56

Shalex wrote:
Thu 29 Aug 2019 08:58
manuelmaurer wrote:
Tue 27 Aug 2019 11:10
I am more or less happy with my solution now, however, there is one problem. If the connection breaks exactly during a query, it will result in an exception 2013 (Lost connection to MySQL server during query). I really do not want to wrap all my queries into a retry loop, is there any other way to catch that error before it is thrown in the query itself?
"Lost connection to MySQL server during query" is thrown when the query execution exceeds the time set in cmd.CommandTimeout (in seconds, default value is 30). You can specify cmd.CommandTimeout explicitly or via the Default Command Timeout connection string parameter in the corresponding MySqlConnection object.

Refer to https://www.devart.com/dotconnect/mysql ... tring.html.
Yeah, that doesn't help at all. The timeout itself is rather fast, as the server closes the connection when it reboots. When running tests with repeated queries as fast as possible, the error codes change, I guess depending on when exactly the connection breaks away. Sometimes I get server shutdown in progress instead. For reading that doesn't matter, I've now create a wrapper function that handles transient read errors gracefully:

Code: Select all

private T _read<T>(Func<T> execute)
        {
            Exception lastex = null;
            for (int i = 0; i < 4; i++)
            {
                try
                {
                    return execute();
                }
                catch (Devart.Data.Linq.LinqCommandExecutionException ex0) when (ex0.InnerException is MySqlException)
                {
                    lastex = ex0;
                    System.Threading.Thread.Sleep(50);
                    continue;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            throw lastex;
        }
        
        public void ExampleCall()
        {
        	var data = _read(() => MyContext.Table.Where(x => x.Id = 1));
        }
        
It gets really bad if I try to write to the database. In this case, LinqConnect seems to be not usable at all as the internal state does not handle server changes well during an transaction. Sometimes it creates an entry, but reports an error (I don't recall exactly, it had something to do with disposing), most of the time it fails with different error messages (like the internal state does not match the database).

I guess I will use LinqConnect only for reading data (as it is really nice to use) and doing the write operations (of which I have only a few fortunately) manually with the MySqlCommand-Class.

Shalex
Site Admin
Posts: 8829
Joined: Thu 14 Aug 2008 12:44

Re: Persistent Connections / Failover

Post by Shalex » Fri 30 Aug 2019 16:19

manuelmaurer wrote:
Thu 29 Aug 2019 13:56
Sometimes it creates an entry, but reports an error (I don't recall exactly, it had something to do with disposing), most of the time it fails with different error messages (like the internal state does not match the database).
Please give us the additional information so that we can investigate the issue:
1) send us a small complete test project with the corresponding DDL/DML script
2) specify the exact text of the exception and its full stack trace

Post Reply