Is the TransactionScope object fully supported using MySqlConnector for .NET?

Question

I'm writing unit tests (technically integration tests since I am connecting to a database) and I want to create records in a transaction within the test, and rollback all database modifications once the test completes. The idea being that I'll create records through one API call that another API call expects to find in the database.

I have the following code that works:

string connectionstring = "Server=MyDbServer;Database=MySchema;Uid=MyUser;Pwd=XXX;";
string sql = "Insert Into MyTable (date, Description) VALUES('2009-12-11', 'foo test description');";

 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
 using (MySqlConnection conn = new MySqlConnection(connectionstring))
 {
       conn.Open();

       using (MySqlCommand cmd = new MySqlCommand(sql, conn))
       {
            cmd.ExecuteNonQuery();
       }
 }

However, if I move the TransactionScope instantiation to occur just before the MySqlCommand, the transaction is not rolled back, and my test data is persisted to the database:

string connectionstring = "Server=MyDbServer;Database=MySchema;Uid=MyUser;Pwd=XXX;";
string sql = "Insert Into MyTable (date, Description) VALUES('2009-12-11', 'foo test description');";

using (MySqlConnection conn = new MySqlConnection(connectionstring))
{
    conn.Open();

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
    {
        cmd.ExecuteNonQuery();
    }
}

What am I missing?

Note: The MySQL server (v. 5.0.67-0ubuntu6) is hosted on a linux machine, MySqlConnecter version is 6.1.3

Answer 1

From the docs (for SQL Server compact, but still relevant):

Implicitly enlisting connections is not supported. To enlist in a transaction scope, you can do the following:

Open a connection in a transaction scope.

Or, if the connection is already opened, call EnlistTransaction method on the connection object.

In the first example, you are opening a connection in a transaction scope. In the 2nd you are not enlisting.

Try adding:

cmd.Connection.EnlistTransaction(Transaction.Current);

To see if that helps.