Automatically reconnect to MySql after losing a connection

Question

I have an aC # application that uses the MySql database, the problem is that after a period of inactivity (8 hours) or when the connection to the server on which the database host is located is lost, the connection to the database is closed and the database queries are not may be executed. how can I enable automatic reconnection to the database.

Sincerely.

Answer 1

I think the first thing to solve is why should the connection be open for 8 hours in a row?

I think that in most cases you will connect to the database, do your work and close it:

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

The using block will create a connection that acts only in the block. When the end is complete, 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 available connections when they are not needed.

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

You can verify this using something like this:

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

Hope this helps.

Answer 2

In your application, before any requests, check if your connection continues. If it is not, then reconnect it. Et voila!

Answer 3

Run the connection command again if the attempt to access the database failed.

Answer 4

You can use the keepalive and keepalivetimeout in the connection string. For this it is better to use MySqlConnectionStringBuilder .

Answer 5

Thanks for your answers and suggestions. I have this requirement because persistence is delegated to unmanaged code, and in my C # code I only call this API.

I solved my problem by changing the wait_timeout MySQL parameter, which has 28800 seconds (8 hours) as the default value for the idle period.

Answer 6

while (conn.State != ConnectionState.Open) { sleep(1); // sleep 1 second, depends what function u use here if using timer namespace mightt need differnt function. this type of code is good for cloud type dbs like godaddy/1&1/amazon cloud. where connection can be asummed unreliable due too heavy cloud lag. conn = new MySqlConnection(connString); }

an alternative could be:

 int counter = 0; int NUM_RETRIES = 10; while (conn.State != ConnectionState.Open && counter < NUM_RETRIES) { System.Threading.Thread.Sleep(1000);//sleep(1); // sleep 1 second ( if webservice better to sleep for 50-100 ms), depends what function u use here if using timer namespace mightt need differnt function. this type of code is good for cloud type dbs like godaddy/1&1/amazon cloud. where connection can be asummed unreliable due too heavy cloud lag. conn = new MySqlConnection(connString); counter++; } 

this too. https://dev.mysql.com/doc/refman/5.7/en/auto-reconnect.html