I'm getting a transient failure with MySQL and MariaDB

Question

I am working on a simple Web API that uses MySQL as database. It's pretty basic:

var builder = WebApplication.CreateBuilder(args);
builder
    .Services
    .AddDbContext<RequestContext>(options =>
    {
        options
            .UseMySql(builder.Configuration["ConnectionStrings:Requests"], new MySqlServerVersion(new Version()))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    })
var app = builder.Build();
app
    .UseHsts()
    .UseHttpsRedirection()
    .UseDefaultFiles();
app
    .Use(async (HttpContext context, Func<Task> next) =>
    {
        context.Request.EnableBuffering();
        context.Request.Body.Position = 0;
        await next();
        var db = context.RequestServices.GetRequiredService<RequestContext>();
        string headers = string.Join("\r\n", context.Request.Headers.Select(r => $"{r.Key} = {r.Value}"));
        string body = await new StreamReader(context.Request.Body).ReadToEndAsync();
        var rec = new Request()
        {
            Host = context.Request.Host.Host,
            Port = context.Connection.LocalPort,
            IPAddress = context.Connection.RemoteIpAddress ?? IPAddress.None,
            Url = context.Request.GetEncodedUrl(),
            Method = context.Request.Method,
            Headers = headers,
            Body = body,
            Valid = context.Response.StatusCode < 400
        };
        db.Requests.Add(rec);
        await db.SaveChangesAsync();
    });
... (Several endpoints after this.) ...

The idea is simple. This is a middleware method that writes all requests to the database with either "Valid" or "Invalid", depending on the status code in the response. This API might receive about 10 requests per hour so it should work. Except it doesn't...

MySqlException: Connect Timeout expired.
MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, int startTickCount, Activity activity, Nullable<IOBehavior> ioBehavior, CancellationToken cancellationToken) in MySqlConnection.cs
System.Threading.Tasks.ValueTask<TResult>.get_Result()
MySqlConnector.MySqlConnection.OpenAsync(Nullable<IOBehavior> ioBehavior, CancellationToken cancellationToken) in MySqlConnection.cs
MySqlConnector.MySqlConnection.Open() in MySqlConnection.cs
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(bool errorsExpected)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(bool errorsExpected)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(bool errorsExpected)
Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlRelationalConnection.Open(bool errorsExpected)
Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlDatabaseCreator+<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions+<>c__DisplayClass12_0<TState, TResult>.<Execute>b__0(DbContext _, TState s)
Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute<TState, TResult>(TState state, Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult<TResult>> verifySucceeded)

That is one of the exceptions I get, if I get any. Sometimes, the action performs but nothing gets written to the database. And sometimes I get this timeout which also includes this line:

InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call.

The problem is that I use the same database in a console application, where it runs perfectly well. And fast. But in this web API, using the same connection string, I just end up with this exception or with no records written. And now I'm stumped.
I'm doing something wrong here but this request logging is a requirement for this project. Even more annoying, this project was first developed in .NET 6.0 with EF6 and Pomelo 6.0.2. But it needs to move to .NET 7 and thus I use EF7 and Pomelo 7.0.0-silver.1 and it keeps failing over and over again. It's becoming so bad that I'm seriously considering moving back to .NET 6.0 with the older EF6 framework.
The errors seem to be located in the Pomelo package and in the MySqlConnector package which Pomelo needs.
Btw, adding options => options.EnableRetryOnFailure() to the UseMySql() just makes it crash slower. And I don't know why the performance in my Web API is so bad while I have no problems in my console application...

Answer 1

If you are only getting a few requests per hour, you may as well do

connect
perform the inserts and any related actions
disconnect

A guess: the connection is timing out, but the package obscures that error with a more cryptic "EnableRetryOnFailure". Auto-reconnect can lead to screwed-up transactions.