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

[xPaw]

I just updated from 0.29.1 to 0.32.0 and I'm not sure if this issue is a bug in my code or not. I can't see any obvious issue on my end. Dapper was also updated from 1.50.4-alpha1-00070 to 1.50.4

Code block that is starting the connection:
https://github.com/SteamDatabase/SteamDatabaseBackend/blob/e6953ba6a20bb89fe4abe25f2fee1f663c3057d3/Processors/DepotProcessor.cs#L626-L632

And then it throws on the very first call to QueryAsync on the same database connection in ProcessDepotAfterDownload.

Full stack trace:

Exception: System.InvalidOperationException: The transaction associated with this command is not the connection's active transaction.
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<QueryAsync>d__31`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 389
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at SteamDatabaseBackend.DepotProcessor.<ProcessDepotAfterDownload>d__20.MoveNext() in D:\GitHub\SteamDatabaseBackend\Processors\DepotProcessor.cs:line 637
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at SteamDatabaseBackend.DepotProcessor.<ProcessDepotAfterDownload>d__19.MoveNext() in D:\GitHub\SteamDatabaseBackend\Processors\DepotProcessor.cs:line 629
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at SteamDatabaseBackend.DepotProcessor.<DownloadDepots>d__16.MoveNext() in D:\GitHub\SteamDatabaseBackend\Processors\DepotProcessor.cs:line 515
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at SteamDatabaseBackend.DepotProcessor.<>c__DisplayClass12_0.<<Process>b__5>d.MoveNext() in D:\GitHub\SteamDatabaseBackend\Processors\DepotProcessor.cs:line 259

[bgrainger]

This is a result of fixing #389, which exposed the underlying strictness of MySqlConnector with regards to transactions (see #333 and https://bugs.mysql.com/bug.php?id=88611 where Connector/NET's default behaviour is reported as a bug).

• MySqlConnector requires MySqlCommand.Transaction to be explicitly set to the active transaction, to guard against programming errors
• Dapper calls MySqlConnection.CreateCommand to create a command
• Prior to CreateCommand sets Transaction property #389, CreateCommand used to automatically set the Transaction to the active transaction; it no longer does.

The fix is fairly simple: if you want a Dapper query to participate in a connection, explicitly denote that intent:

private async Task<EResult> ProcessDepotAfterDownload(ManifestJob request, DepotManifest depotManifest)
{
    using (var db = await Database.GetConnectionAsync())
    using (var transaction = await db.BeginTransactionAsync())
    {
        // add `transaction` to method call below
        var result = await ProcessDepotAfterDownload(db, transaction, request, depotManifest);
        await transaction.CommitAsync();
        return result;
    }
}

private async Task<EResult> ProcessDepotAfterDownload(IDbConnection db, IDbTransaction transaction, ManifestJob request, DepotManifest depotManifest)
{
    // pass `transaction` to Dapper's QueryAsync below
    var filesOld = (await db.QueryAsync<DepotFile>("SELECT `ID`, `File`, `Hash`, `Size`, `Flags` FROM `DepotsFiles` WHERE `DepotID` = @DepotID", new { request.DepotID }, transaction: transaction)).ToDictionary(x => x.File, x => x);
    ....

[xPaw]

Damn, that's unfortunate. I always expected queries executed to be within the started transaction for that connection.

Would it be possible to somehow automate having a single transaction per connection?

[bgrainger]

It's an unfortunate mismatch between database servers' actual implementations (which typically have an active transaction as a "global" connection-level property) and ADO.NET, which allows DbCommand.Transaction to be set (or not) for each command executed on the connection.

The behaviour of ADO.NET is not very well specified in this situation, and the various providers choose to implement it differently. In this case, (for now) I've deliberately chosen to make a breaking change from Connector/NET in order to proactively catch potential bugs (see links in my first reply).

[bgrainger]

For future reference, you can also opt in to the Connector/NET behaviour by adding IgnoreCommandTransaction=true to your connection string (in 0.39.0 and later).