LinkMenuExpand(external link)DocumentSearchCopyCopied

The transaction associated with this command is not the connection’s active transaction

Problem

When using MySqlTransaction from a C# program, you may receive the following error:

By default, MySqlConnector requires MySqlCommand.Transaction to be set to the connection’s active transaction in order for the command to be executed successfully. This strictness is intended to catch programming bugs related to using the wrong transaction, a disposed transaction, or forgetting to set the transaction (and using the default value null).

However, this strictness can make migrating from Connector/NET more difficult, as it may require significant code changes to pass the current transaction through to all command objects. It can also be challenging when using a library like Dapper that creates the MySqlCommand objects itself.

Workaround: Use IgnoreCommandTransaction=true

To easily migrate code from Connector/NET, use the IgnoreCommandTransaction=true connection string setting to emulate Connector/NET’s behaviour and not validate the value of MySqlCommand.Transaction. By doing this, you will not need the code fixes prescribed below.

Code Fix: Set MySqlCommand.Transaction

ADO.NET example

using var connection = new MySqlConnection(...);
connection.Open();

using var transaction = connection.BeginTransaction();
using var command = connection.CreateCommand();
command.CommandText = "SELECT ...";

// *** ADD THIS LINE ***
command.Transaction = transaction;

// otherwise, this will throw System.InvalidOperationException: The transaction
// associated with this command is not the connection's active transaction.
command.ExecuteScalar();

// ... remaining code
transaction.Commit();

Dapper Example

using var connection = new MySqlConnection(...);
connection.Open();
using var transaction = connection.BeginTransaction();

// this will throw System.InvalidOperationException: The transaction
// associated with this command is not the connection's active transaction.
connection.Query("SELECT ...");

// use this instead:
connection.Query("SELECT ...", transaction: transaction);

// ... remaining code
transaction.Commit();

Further Reading