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.
h2. 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.
h3. 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”:http://bugs.mysql.com/bug.php?id=55558).
h2. Switching to ODBC
After googling and reading through the Connector/Net driver code failed to lead insights into the @TransactionAbortedException@s, 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:
h3. 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 @OdbcCommand@s. Rather than interpolate the values ourselves, or convert to another interpolation scheme, we implemented a simple transformation from unnamed to named parameters:
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 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 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).
h3. 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.
class MySqlClass
{
public virtual void Transaction(Action action)
{
using (var transaction = Connection.BeginTransaction())
{
try
{
action();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
}
h3. Allocation Errors
Next up were allocation errors that happened in a number of places. These were “due to”:http://stackoverflow.com/questions/2981270/memory-allocation-error-from-mysql-odbc-5-1-driver-in-c-application-on-insert-st @null@ being used for the value of some of our @OdbcParameter@s. 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.
h3. 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=…@.