1

I would like to detect connection state to MySql database. My database is deployed in different server than my app and there is good chances to lose connection to it via network. So I have to take this scenario into consideration.

Here is what I tried so far (a simplified test example):

static string connectionString = "***";
public static MySqlConnection Connection;
static System.Timers.Timer _timer; 

static void _timer_Elapsed(object sender, ElapsedEventArgs e)
{
    try
    {
        if (Connection.State != System.Data.ConnectionState.Open)
            Connection.Open();
        // Call method to invoke MySqlCommand.ExecuteNonQuery
        mysqlCommand.ExecuteNonQuery();
    }
    catch (MySqlException ex)
    {
        Console.WriteLine("SQL EXCEPTION: " + ex);
        // Handle all type of database exceptions
        switch(ex.Number)
        {...}
    }
    catch (Exception ex)
    {
        Console.WriteLine("OTHER EXCEPTION: " + ex);
    }
}

static void Main(string[] args)
{
    Connection = new MySqlConnection(connectionString);

    _timer = new System.Timers.Timer(3000); 
    _timer.Elapsed += new ElapsedEventHandler(_timer_Elapsed);
    _timer.Enabled = true; 

    Console.ReadKey();
}

If the connection to MySql is lost, I got a general exception:

IOException : Unable to write data to the transport connection: An established connection was aborted by the software in your host machine.

I was expecting MySqlException to be fired but that was not the case. Also, if the connection to MySql is restored, I still get the IOException instead of executing the query. Seems like, MySqlConnection object has not been updated and it doesn't care about new connection state.

  1. What's the best way to handle connection lost exception?
  2. How can I refresh MySqlConnection when connection is restored?

Note: that I can't instantiate a new MySqlConnection object for each new query, because the program I'm trying to change has a Singleton of type MySqlConnection which is initialized only once. I know that's a bad design but I don't want to change this design now. I just want to catch connection lost exception and try to refresh MySqlConnection to continue to work correctly.

Mhd
  • 2,778
  • 5
  • 22
  • 59

2 Answers2

0

If your MySqlConnection instance loses its connection to your MySQL server, you cannot expect that instances's connection to be restored automatically or otherwise.

You need to try to reconnect with a new instance of MySqlConnection. The one that has lost the connection is now in a terminal state and cannot be reused.

To do this, I suppose you could do something like this

  ...
  catch (MySqlException ex)
  {
     if (/*ex is a connection drop */) {
        Connection?.Dispose();
        Connection = new MySqlConnection(...);
        Connection.ConnectionString = /* your connection string */;
        Connection.Open();
     }
     else {
        throw;
     }
  }

You are correct that your design has a flaw. Whether or not your flaw is fatal is hard to tell without testing.

These Connection instances are not thread safe or in any way reentrant. If you use one in a timer handler or thread, you may only use it in that context. Otherwise, if it's already in use when your timer or thread is invoked, things will get dicey. If you're lucky you'll get an exception. If you're less lucky your MySQL server will receive gibberish from your client and detect it. If you're even less lucky your data will get scrambled up.

ADO.NET and the MySqlConnection object implement connection pooling. This matters because it makes opening connections, using them, and then closing them, a lot cheaper than you might have expected.

Sometimes MySQL drops connections that your programs have held open for long periods of time. This post may help if that is your problem. How can I change the default Mysql connection timeout when connecting through python?

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • So my only option here is to change my design and create new `MySqlConnection` instance for every database interaction? – Mhd Apr 17 '17 at 15:40
0

When you lost your connection by networks problems, the connection object does not change Status property so evaluate it before executting commands doesn't work. However, the database property (connection.database) goes to empty string so you can evaluate it so can close the connection an restores it:

oConn is an instance of MySQLConnection (it works on odbcconnection)

[VB.NET]
 If Not IsNothing(oConn) Then
    If (oConn.Database.Equals(String.Empty)) Then oConn.Close()
 End If

[C#]
 If (Not IsNothing(oConn)){
    If (oConn.Database.Equals(String.Empty)) oConn.Close();
 }
Raist
  • 1
  • 1