MySql .NET Connector Connection Problems

Summary

It’s been a while since I’ve posted anything and I’ve got a few subject ideas lined up, if and when I get going again.  In the meantime I thought I’d share some problems I’ve had with the MySql .NET Connector (MySql.Data.dll).

When using the C API you can specify a reconnect option for a connection.  Specifying this means that if the connection to the server fails then the client library will automatically reconnect and retry the connection.  Unfortunately, this option doesn’t seem to be available in the .NET Connector and we were seeing issues with connections timing out or otherwise closing.

To get round this issue I wrote a couple of small helper routines that will retry failed queries and automatically re-open a connection if it fails.

The Implementation

So the plan I came up with was to create a generic wrapper function that will run a provided delegate, catch database related errors and retry the query a couple of times.  I also provided a function that checks the state of a database connection, and re-opens it if necessary.

Retry Wrapper

public static TRetVal RetryWrapper<TRetVal>(RetryRoutine<TRetVal> queryRoutine)
{
  Exception lastException = null;
  for ( int queryRetries = 0; queryRetries < 2; ++queryRetries )
  {
    try
    {
      return queryRoutine();
    }
    catch ( DbException err )
    {
      lastException = err;
    }
    catch ( System.IO.IOException err )
    {
      lastException = err;
    }
  }
  
  throw new DatabaseRetryFailedException(
      String.Format("Too many retries.  Last error: {0}", lastException.Message),
      lastException);
}

I’ve implemented it by catching DbException so it could be used for other providers (I did this primarily for use with DbLinq).  I’ve also thrown a custom exception at the end, but you could change this to re-throw the final exception.

Connection Checking

You would think this bit would be straight forward, check if the connection is open and if it’s not then re-open it.  Unfortunately, when I tried this with the MySql Connector the first open after killing a connection would always fail.  So I put this in a retry loop as shown below.

public static DbConnection CheckConnection(DbConnection connection)
{
  if ( connection.State != System.Data.ConnectionState.Open )
  {
    // For some crappy reason, the first re-open seems to fail in MySql
    bool isRetry = false;
    while ( true )
    {
      try
      {
        connection.Open();
        return connection;
      }
      catch ( Exception )
      {
        connection.Close();
        if ( isRetry )
          throw;
        isRetry = true;
      }
    }
  }
  return connection;
}

Using The Retry Functions

For ease of use, I created a MySql specific function that automatically checks the connection and tries a query:

public static MySqlDataReader ExecuteReader(MySqlCommand cmd, System.Data.CommandBehavior behaviour)
{
  DatabaseRetryHelper.RetryRoutine<MySqlDataReader> queryRoutine = delegate()
    {
      DatabaseRetryHelper.CheckConnection(cmd.Connection);
      return cmd.ExecuteReader(behaviour);
    };
  return DatabaseRetryHelper.RetryWrapper<MySqlDataReader>(queryRoutine);
}

and then in your code:

MySqlCommand selectCmd = new MySqlCommand("SELECT * FROM atable", myDbConnection);
using ( MySqlDataReader dataReader = MySqlRetryHelper.ExecuteReader(selectCmd) )
{
  if ( dataReader.Read() )
    return dataReader.GetString(0);     // "First field"
}

Using With Linq

Using the retry helpers with Linq isn’t quite as neat, primarily as you have to run all the Linq expressions again.  (The retry routine was actually already written by the time we started using DbLinq with MySql).

private static string LookupSomeOtherFieldValue(string someWhereValue)
{
  DatabaseRetryHelper.RetryRoutine<string> routine = () =>  
  {
    IDbConnection db conn = CheckConnection(myDbConnection);
    MyLinqDb db = new MyLinqDb(conn);
    string result = (from record in db.MyTable
      where record.SomeField == someWhereValue
      select record.SomeOtherField).FirstOrDefault();
    return userRecresultrd;
  };
  return DatabaseRetryHelper.RetryWrapper<string>(routine);
}

Other Notes

This is a work around for what I see as missing functionality in the .NET Connector (did you used to be able to set the MySql Options with a raw value in an old version of connector?).  This isn’t necessarily the best solution, especially using Linq, which could do with a Proxy class of some kind (so only the actual query gets retried), but it’s relatively simple and flexible.

Another method that may help solve this is to use pooled connections.  The documentation seems to suggest this is the preferred way to use the connector, but the software that uses this will end up doing lots of queries per second so I’d want to check performance etc, but I may well look in to this in the future.

Ultimately, I hope the Connector will implement automatic reconnection- I might even look in the source code a bit deeper myself…