MySQL in .NET: Native or ODBC

There are a few different options for accessing a MySQL database with ADO.net. Connector/NET, written in C#, has some upsides over the ODBC driver. Unfortunately, we ran into a number of problems with it and switched to the ODBC driver. With a few small changes we were able to convert from one to the other.

Connector/Net Woes

We chose the “native” .NET driver (Connector/Net) initially, due to ease of bundling and because it seemed like the most obvious choice for our ASP.NET application; but we also have an ETL process which loads aggregated data from views and materialized views in an Oracle data warehouse and stores them in our MySQL database. This process uses some fairly prolific and long-winded transactions, which led to a number of problems with Connector/Net.

Timeouts

Everything went smoothly initially. Over time, as we started importing more and more data via our ETL process, we started running into timeouts. These are rectified easily enough when the timeout happens while connecting (by setting MySqlConnection.ConnectionTimeout) or during a command (by setting MysqlCommand.CommandTimeout), but is not as easily fixed if the timeout occurs when committing or rolling back transactions. Connector/Net uses the default command timeout for committing and rolling back and doesn’t provide a way to override this.

The fix: Specifying a command timeout at the connection level by adding command timeout=XXX in the connection string. This value is used when Connector/Net commits or rolls back a transaction.

TransactionAbortedException

After working through the timeout issues, what ultimately drove us from Connector/Net were nondeterministic TransactionAborted exceptions. At random points during our ETL process, we’d get a TransactionAborted exception when trying to execute a command, with no insight into why the transaction was aborted (the exception was raised when MySqlCommand performed some sanity checks before starting, one of which was to see if the current transaction was in the state TransactionStatus.Aborted). Furthermore, this led to cases where DataReader objects were not correctly cleaned up, leading to an unusable connection (the same behavior documented in this bug).

Switching to ODBC

After googling and reading through the Connector/Net driver code failed to lead insights into the TransactionAbortedExceptions, we decided the most cost-effective solution for our client was to switch to ODBC. We ran into a number of incompatibilities and found the following solutions:

Named Parameter Support

We used named parameter interpolation as supported by the .NET adapter, where a parameter with name foo would be escaped and interpolated wherever ?foo appeared in the query. This syntax doesn’t work with OdbcCommands. Rather than interpolate the values ourselves, or convert to another interpolation scheme, we implemented a simple transformation from unnamed to named parameters:

<code class="language-csharp">
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Linq;
using System.Text.RegularExpressions;

namespace Foo
{
internal static class OdbcConnectionExtensions {
static readonly Regex NamedParameterPattern = new Regex(@"?(w+)");

public static OdbcCommand CreateCommandWithNamedParameters(this OdbcConnection connection, string sql, IDictionary<string, object> parameters)
{
var cmd = connection.CreateCommand();
var parameterIndex = 0;
cmd.CommandText = NamedParameterPattern.Replace(sql, (m) => {
var key = m.Groups[1].Value;
var value = parameters[key];
var parameterName = string.Format("{0}_{1}", key, parameterIndex++);

if ((value as string) != null || (value as IEnumerable) == null) {
cmd.Parameters.AddWithValue(parameterName, value ?? DBNull.Value);
return "?";
} else {
var enumerable = (IEnumerable<object>)value;
var i = 0;
foreach (var el in enumerable) {
var elementName = string.Format("{0}_{1}", parameterName, i++);
cmd.Parameters.AddWithValue(elementName, el ?? DBNull.Value);
}
return string.Join(",", enumerable.Select(_ => "?"));
}
});
return cmd;
}
}
}

This code doesn’t work if you have strings which contain this syntax inside of a SQL string, but gets the job done for us. As a bonus, it interpolates IEnumerable objects into SQL IN-compatible strings (which we were already doing manually with the MySQL driver, but was convenient and led us down this path).

TransactionScope

The ODBC driver complained of an unsupported optional feature during the conversion. This was due to our connection being instantiated within a TransactionScope. Switching to using connection.BeginTransaction() worked well enough.

<code class="language-csharp">
class MySqlClass
{
public virtual void Transaction(Action action)
{
using (var transaction = Connection.BeginTransaction())
{
try
{
action();

transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
}

Allocation Errors

Next up were allocation errors that happened in a number of places. These were due to null being used for the value of some of our OdbcParameters. We used value ?? DBNull.Value instead of whatever value we wanted to interpolate. In our codebase, all of our commands were ultimately created with the CreateCommandWithNamedParameters method above, so that was the only place we needed to make this change.

Connection String Changes

One other minor difference was in the connection string. We added DRIVER={MySQL ODBC 5.1 Driver};" and changed User id=... to uid=....