.NET 7 Web API连接MySQL/MariaDB遇瞬态故障求助

我正在开发一个基于MySQL的简单Web API，核心逻辑是通过中间件记录所有请求到数据库，根据响应状态码标记请求是否“有效”（API每小时约接收10次请求）。迁移后故障频发，具体问题如下：

异常现象 #

• 偶尔抛出连接超时异常：

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)

• 有时执行操作但无数据写入，同时收到提示：

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.

环境背景 #

原项目基于.NET 6 + EF Core 6 + Pomelo 6.0.2运行正常，迁移至.NET 7 + EF Core 7 + Pomelo 7.0.0-silver.1后出现问题。使用相同连接字符串的控制台程序可正常快速运行，但Web API性能极差，添加options => options.EnableRetryOnFailure()仅能延缓崩溃，无法彻底解决。

核心代码 #

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();
    });
... (后续还有若干端点) ...

针对上述问题，从连接配置、DbContext生命周期、Pomelo版本、中间件优化几个维度入手：

1. 修正MySQL版本配置 #

当前代码中new MySqlServerVersion(new Version())未指定具体版本号，EF Core无法正确识别MySQL特性，可能引发连接和执行策略异常。替换为数据库实际版本，例如MySQL 8.0.33：

.UseMySql(builder.Configuration["ConnectionStrings:Requests"], new MySqlServerVersion(new Version(8, 0, 33)))

2. 优化DbContext生命周期管理 #

中间件中直接获取的RequestContext默认是Scoped生命周期，异步场景下易导致连接池耗尽。改用using块手动创建独立实例：

app.Use(async (HttpContext context, Func<Task> next) =>
{
    context.Request.EnableBuffering();
    context.Request.Body.Position = 0;
    await next();
    
    // 创建独立作用域获取DbContext，避免生命周期冲突
    using var scope = context.RequestServices.CreateScope();
    var db = scope.ServiceProvider.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();
});

3. 升级Pomelo到稳定版本 #

7.0.0-silver.1是预览版，存在连接池和稳定性缺陷。升级到.NET 7对应的稳定版：

<!-- 项目文件中更新包引用 -->
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="7.0.0" />

4. 配置连接池与重试策略 #

在连接字符串和EF配置中补充连接池参数，同时启用针对性的重试策略：

options.UseMySql(
    builder.Configuration["ConnectionStrings:Requests"],
    new MySqlServerVersion(new Version(8, 0, 33)),
    mySqlOptions =>
    {
        // 配置 transient 错误重试策略
        mySqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null);
        // 配置连接池参数
        mySqlOptions.ConnectionPoolSettings(pool =>
        {
            pool.MaximumPoolSize = 10; // 匹配你的请求量，10足够
            pool.MinimumPoolSize = 2;
            pool.ConnectionTimeout = 10; // 缩短超时，避免阻塞
        });
    })

连接字符串补充对应参数：

Server=your-server;Database=requests;Uid=user;Pwd=password;SslMode=Required;Connection Timeout=10;Max Pool Size=10;Min Pool Size=2;

5. 优化请求体读取逻辑 #

确保读取请求体后重置流位置，避免后续端点无法读取：

context.Request.Body.Position = 0;
// 保留流打开，方便后续使用
string body = await new StreamReader(context.Request.Body, leaveOpen: true).ReadToEndAsync();
context.Request.Body.Position = 0;

内容的提问来源于stack exchange，提问作者Wim ten Brink