Pomelo.EntityFrameworkCore.MySql: "Cannot Open when State is Connecting." with AddDbContextPool.

@nelsonprsousa Should work pretty much the same as for projects without using Microsoft.Build.CentralPackageVersions:

Update the Nuget.config file with the nightly build feed. Then update the referenced package version (in your case located in your Packages.props file) to the respective version from the newly added feed (e.g. 6.0.1-servicing.1.ci.20220114125709+sha.1974429).

Everything’s rocking 🚀

Fixed in MySqlConnector 2.1.1.

@nelsonprsousa Should work pretty much the same as for projects without using Microsoft.Build.CentralPackageVersions:

Update the Nuget.config file with the nightly build feed. Then update the referenced package version (in your case located in your Packages.props file) to the respective version from the newly added feed (e.g. 6.0.1-servicing.1.ci.20220114125709+sha.1974429).

@nelsonprsousa Took a week longer than planned, but the latest nightly build is feature complete in regards to 6.0.1 and references MySqlConnector 2.1.2, in case you want to give it a spin.

@nelsonprsousa Since this issue was in the underlying MySqlConnector library, it doesn’t necessitate a change to the Pomelo library. Simply add <PackageReference Include="MySqlConnector" Version="2.1.1" /> to your csproj file.

Working as expected 🚀

Thank you

— EDIT

Will the dependency be updated to MySqlConnector (>= 2.1.1) in the next release?

Yes, the dependency is MySqlConnector (>= 2.0.0) so just add it to your csproj.

@vaital3000 Thanks for the repro; this is very helpful. The problem is that MySqlConnection isn’t setting the state to Closed here: https://github.com/mysql-net/MySqlConnector/blob/73b3ddefd8682bfb0dad620ebb487158250f989e/src/MySqlConnector/MySqlConnection.cs#L411-L421

Seems like @vaital3000 found what’s causing the issue. When passing a CancellationToken to DbContext async operations and cancelling it (pretty common in ASP.NET Core) causes the existing DbContext instances in the pool to get messed up for future uses.

@mguinness @bgrainger Here is a repository with a simple console app that reproduces the issue consistently. It kind of replicates a high throughput ASP.NET 6 web app. As soon as an async task (ToListASync(), CountAsync(), AnyAsync(), etc.) is cancelled via a CancellationToken, Cannot Open when State is Connecting. starts being thrown in subsequent DbContext uses.

Hope this helps.

using System;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContextPool<MyDbContext>(opt =>
	opt.UseMySql(
		"Server=localhost;Port=3306;Database=test;Uid=root;SSL Mode=None;Allow User Variables=True;  Pooling=true;",
		new MySqlServerVersion(new Version(5, 7, 28))), 2
);
builder.Services.AddHostedService<MyHostedService>();
var app = builder.Build();

app.Run();


public class MyHostedService : IHostedService
{
	private readonly IServiceScopeFactory _scopeFactory;
	private readonly ILogger<MyHostedService> _logger;


	public MyHostedService(IServiceScopeFactory scopeFactory, ILogger<MyHostedService> logger)
	{
		_scopeFactory = scopeFactory;
		_logger = logger;
	}

	public async Task StartAsync(CancellationToken _)
	{
		CancellationTokenSource cts = new();
		CancellationToken cancellationToken = cts.Token;

		using var startScope = _scopeFactory.CreateScope();
		var startContext = startScope.ServiceProvider.GetService<MyDbContext>();
		await startContext.Database.EnsureDeletedAsync(cancellationToken);
		await startContext.Database.EnsureCreatedAsync(cancellationToken);
		var task1 =  Task.Run(AddTask("Group1",cancellationToken), cancellationToken);
		var task2 =  Task.Run(AddTask("Group2",CancellationToken.None), CancellationToken.None);

		await Task.Delay(2000, cancellationToken);
		_logger.LogWarning("Cancel task!!!");
		cts.Cancel();
		await task1;
		await task2;
	}

	private Func<Task?> AddTask(string group, CancellationToken cancellationToken)
	{
		return async () =>
		{
			while (true)
			{
				try
				{
					await Task.Delay(1000);
					using var scope = _scopeFactory.CreateScope();
					var context = scope.ServiceProvider.GetService<MyDbContext>();
					await context.Entities.AddAsync(new MyDbContext.MyEntity()
					{
						SomeText = Guid.NewGuid().ToString()
					}, cancellationToken);
					await context.SaveChangesAsync(cancellationToken);
				}
				catch (Exception e)
				{
					_logger.LogError(group);
				}
			}
		};
	}

	public Task StopAsync(CancellationToken cancellationToken)
	{
		return Task.CompletedTask;
	}
}

public class MyDbContext : DbContext
{
	public MyDbContext(DbContextOptions<MyDbContext> options)
		: base(options)
	{
	}

	public DbSet<MyEntity> Entities { get; set; }

	public sealed class MyEntity
	{
		public int Id { get; set; }
		public string SomeText { get; set; }
	}
}

I reproduced this error in this case. Errors happened after calling cts.Cancel(). But I’m not sure what this is the same case that I see in production.

CancellationToken wasn’t cancelled in production.

PS: I updated example. If I set small value for poolSize in AddDbContextPool, I saw error for both task. With ef core 5 I saw problem only with “Group1” task.

@bgrainger Experiencing exactly the same issue after migrating to Pomelo.EntityFrameworkCore.MySql 6 / EFCore 6 and .NET 6. When using AddDbContextPool() in an ASP.NET 6 web application, after a few hundred requests, most DbContext operations throw System.InvalidOperationException: Cannot Open when State is Connecting.

System.InvalidOperationException: Cannot Open when State is Connecting.
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 445
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlRelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.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)
   at ITSD.Services.CacheService.GetServiceStatus(String serviceId, CancellationToken cancellationToken) in /app/ITSD/Caching/CacheService.cs:line 1081
   at ITSD.Web.Controllers.ProblemsController.Map(String shortname, CancellationToken cancellationToken) in /app/ITSD.Web/Controllers/ProblemsController.cs:line 1099
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore\MiniProfilerMiddleware.cs:line 121
   at ITSD.Web.Middleware.CustomHeadersMiddleware.Invoke(HttpContext httpContext) in /app/ITSD.Web/Middleware/CustomHeadersMiddleware.cs:line 40
   at ITSD.Web.Middleware.ErrorHandlerMiddleware.Invoke(HttpContext context) in /app/ITSD.Web/Middleware/ErrorHandlerMiddleware.cs:line 65", "State":{…}}

Replacing AddDbContextPool() with AddDbContext() solves the issue. This problem was not present in Pomelo.EntityFrameworkCore.MySql 5 / EFCore 5 with .NET 5.