Lost Connection to MySQL Server during query

[LarsOberg]

We very frequently get "Lost Connection to MySQL Server during query" at seemingly random places and at random times. Usually the error happens when interacting with a MySQL server over the Internet, but it also occasionally happens with local servers.

I have done a few things just now to try to handle this and I would like your input:

--- On the connection object, I did this:
ConnectionTimeout=60
LocalFailover = true
ConnectionLost += new Devart.Common.ConnectionLostEventHandler(mySqlconnection_ConnectionLost);
ConnectionString += "Connection LifeTime=600;"

--- The ConnectionLost event handler
void mySqlconnection_ConnectionLost(object sender, Devart.Common.ConnectionLostEventArgs e)
{
string message = string.Format("ConnectionLost: Cause: {0}, Context: {1}", e.Cause.ToString(), e.Context.ToString());
LogUtil.Log.Error(message);

if (_retryCount++ < 5) // _retryCount is a class-level variable in my provider factory
{
LogUtil.Log.Info("Retrying (retry count: " + _retryCount + ")");
e.RetryMode = Devart.Common.RetryMode.Reexecute;
}
else
{
LogUtil.Log.Info("Giving up (retry count: " + _retryCount + ")");
e.RetryMode = Devart.Common.RetryMode.Raise;
_retryCount = 0;
}
}

--- On the MySQL Server, I have requested this (not yet done):
connect_timeout=30 (default is 5)
net_read_timeout=60 (default is 30)
net_write_timeout=120 (default is 60)


My main question is on my LostConnection event handler. I am simply always retrying 5 times, no matter what. But the example in your help file is different and much more conservative:

if (e.Cause == ConnectionLostCause.Execute)
{
if (e.Context == ConnectionLostContext.None)
e.RetryMode = RetryMode.Reexecute;
else
e.RetryMode = RetryMode.Raise;
}
else
e.RetryMode = RetryMode.Raise;


Could you let me know why the example from your help file only re-executes when e.Cause==ConnectionLostCause.Execute and e.Context ==ConnectionLostContext.None? Also, Is there any issue with the way I wrote my event handler?

Anything else you can recommend to fix the issue?

Thanks,
Lars

Ps. I am using version 5.40.37

[Shalex]

1. Our sample from the ConnectionLost Event description is not intended to cover all possible issues. You can implement your own scenario as you did in the previous post. Actually, you can write such handler to perform a reconnect in any case 10 times (there is a private constant that is set to 10 - the max number of attempts after which the exception arises) :

Code: Select all

void conn_ConnectionLost(object sender, ConnectionLostEventArgs e) { 
    e.RetryMode = RetryMode.Reexecute; 
}

2. The "Lost connection to MySQL server during query" problem was discussed at our forum here: http://www.devart.com/forums/viewtopic.php?t=14669.

[LarsOberg]

Ok. So'll take out my own retry counter and just return e.RetryMode =RetryMode.Reexecute. I was afraid this would cause a potential eternal loop.

Also, regarding the post you referred to for "Lost Connection", I saw that post already, but it does not seem to apply to our scenario (we have wait_timeout set to 86400 already, for example). Either way, the changes I made seems to have handle this issue - I have not had a single "Lost Connection" error since. I have a feeling that setting "Connection Life = 600" helped a lot. If the issue comes back, I'll revive this thread again...

Thanks,
Lars