Auto reconnect to MySql after connection lost

Question

Hi,

I have a c# application that uses a MySql database, the problem that after a period of inactivity (8 hours) or when connection to the server where database is host is lost, the connection to the database is closed and database queries cannot be executed. how can i enable the auto reconnect to the database.

Best regards.

Answer 1

Run your connection command again if the attempt to access the database fails.

Answer 2

In your application, before any queries, test if your connection is still active. If it isn't, then reconnect it. Et voila !

Answer 3

I think the first thing that should be addressed is, why would a connection be open for 8 hours straight?

I'd think that in most cases you'll connect to the database, do your thing, and close it:

using (var conn = new MySqlConnection(connString))
{
  // do what you need here
}

The using block will create a connection which is only valid within the block. When the block ends, the Dispose method will be called on the connection and close and properly dispose of the connection object. This will prevent your application from using up available connections when they aren't needed.

If you are in a situation where you'll need to determine if the connection is open, your connection variable will have a State property which will be a ConnectionState enumeration.

You can check it using something like this:

if (conn.State != ConnectionState.Open)
{
  conn = new MySqlConnection(connString);
}

Hope that helps.

Answer 4

You can use the option "keepalive" and keepalivetimeout in the connection string. It is best to use the MySqlConnectionStringBuilder for that.

Answer 5

Thank you for your answers and suggestions. I have this requirement because persistence is delegated to unmanaged code and in my c# code i only make calls to this API. I resolved my problem by changing the "wait_timeout" MySql parameter that has 28800 seconds (8 hours) as default value for the period of inactivity.