System.InvalidOperationException: Can't replace active reader.

[adamhathcock]

Software versions
MySqlConnector version: 2.1.8
Server type (MySQL, MariaDB, Aurora, etc.) and version: Aurora (MySQL 5.7)
.NET version: .NET 6
(Optional) ORM NuGet packages and versions: Pomelo 6.0.1

Describe the bug
After some usage my application, I start getting pseudo-random exceptions. It doesn't happen on each call but it happens around 50% of web calls after some time. Restarting the application seems to reset things but they again start to go wrong after a while.

This only happened after the latest update. I changed from version 2.1.6 to 2.1.8. I updated from .NET and EFCore 6.0.2 to 6.0.3. Pomelo remained the same.

Admittingly there are other code changes but nothing big nor that should do this as I don't use Readers directly. Only with EFCore. I don't see connection exhaustion or anything looking at metrics in AWS.

I'm not sure if I'm doing something wrong or where to look for to how to solve this problem. Can't find anything similar. I'm going to downgrade to 2.1.6 to see if the issue reoccurs. I'm hoping to get some pointers about how to figure out what's going on.

Exception

System.InvalidOperationException: Can't replace active reader.
   at MySqlConnector.MySqlConnection.SetActiveReader(MySqlDataReader dataReader) in /_/src/MySqlConnector/MySqlConnection.cs:line 874
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 460
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 344
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 337
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)

[bgrainger]

I don't think I've seen this error reported before.

It might be useful to test EF Core 6.0.3 with MySqlConnector 2.1.6 and EF Core 6.0.2 with MySqlConnector 2.1.8 to see if changing just one of those libraries has introduced the problem.

[adamhathcock]

Seemingly, reverting to 2.1.6 stops the problem.

It only happens in one of the services after some period of use. It's like I'm leaking something is one specific piece of code but I don't know where to begin as I don't believe I'm doing anything different in one area versus another.

I've had several hours with no errors now when it only took 1-2 at most (this service is relatively low usage).

[adamhathcock]

Actually, got the problem overnight it seems. I'll try downgrading EFCore soon.

Definitely a weird problem. Just wish I knew what i was doing 😬

[adamhathcock]

Actually, I now have a theory.

I discovered some bad code that created a runaway query. The command timed out which caused on exception at some point. However, the reader used on the query is somehow still active. Later the pool tries to use it causing this exception.

Not sure if that's reality but I definitely found my problem query happening. Then a series of sequent errors above.

I've fixed the code and kept my downgrades. We'll see if errors reoccur. If not I'll upgrade again and wait more.

[adamhathcock]

I've now re-upgraded to everything and it's fine. Definitely my own bug but the error was so unfamiliar that I wanted to report it.

Happy to close this unless you want to do something

[bgrainger]

Hmm, MySqlConnector should generate a better exception if a connection/reader is being reused (rather than returning an in-use object to the pool).

But unless you have a consistent repro for this problem I don't know if I can solve it based solely on the description, so you can close this case.

[adamhathcock]

It's going to be hard to reproduce as my guess it's like:

1. execute long query on connection
2. command times out (magic here is that the connection is still in use)
3. pool finds the same reader that thinks its rightly or wrongly in use
4. boom exception

happy to close....maybe someone else will google in the future and find this helpful

[jcfore21]

@bgrainger Can we reopen this issue? We recently upgraded to .net 6 and MySqlConnector 2.1.11 and I'm seeing the exact same error. Its sporadic just like the original issue mentions. I'm unable to determine what is causing this and its causing us data loss in our application.

Connection timeouts shouldn't cause this to happen - the connection pool shouldn't reuse a connection that is already active. There seems to be something fundamentally wrong here.

[bgrainger]

@jcfore21 What was the previous version of MySqlConnector you upgraded from? Were there any application changes prior to this exception happening, or just a PackageReference update? Is there any more information you can provide that might help reproduce this?

[jcfore21]

@bgrainger

We upgraded from .net core 3.1 to .net 6 and upgraded our packages to support that:

MySqlConnector 2.1.9 -> 2.1.11
Microsoft.EntityFrameworkCore 5.0.10 -> 6.0.7
Microsoft.EntityFrameworkCore.Design 5.0.10 -> 6.0.7
Microsoft.EntityFrameworkCore.Tools 5.0.6 -> 6.0.7
Pomelo.EntityFrameworkCore.MySql 5.0.4 -> 6.0.2
Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft 5.0.4 -> 6.0.2

These were the relevant packages that we updated.

Also, we are using Aurora - Mysql 5.7.

[jcfore21]

On our connection string we also have a ConnectionLifeTime=1800 and MaximumPoolSize = 250

This has been this way for quite some time before our upgrade - but mentioning it just in case that has something to do with it.

Also we are using AddDbContextPool with the following UseMysql (in our Startup.cs)

 options.UseMySql(conn, ServerVersion.Parse("5.7.32", ServerType.MySql), options =>
  {
      options.UseNewtonsoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedFast)
          .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
          .EnableIndexOptimizedBooleanColumns(true);
  })
  .AddInterceptors(new List<IInterceptor>() {new RemoveLastOrderByInterceptor()});

[jcfore21]

@bgrainger We have restarted our service and the issue has subsided but as @adamhathcock had experienced, im sure it will come back in a few hours.

I'm wondering if the issue is really a MySqlConnector issue or a change in EF Core. While I understand the DBContext Pooling is different from the MySqlConnector connection pooling, could there be something related there?

I noticed that the EF Core 6 documentation says that the DBContext pool size has been defaulted to 1024 - while it use to be 128:
https://docs.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cwith-constant#dbcontext-pooling

Not sure how that would cause connections to still be active though and back in the pool..

[jcfore21]

@bgrainger Yep issue persists. It came back in exactly one hour on the dot.