Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call

user_7125221 0
Viewed 21846

I have an application based on F#, and I use EF-Core and MySQL (Pomelo.EntityFrameworkCore.MySql). I have an async method which updates data in DB(MySql)

let updatePlayerAchievementsAsync (logger:ILogger) (ctx:ReportCacheDbContext) (id: int) = async {
  let! account = ctx.AccountCaches.FirstOrDefaultAsync(fun e -> e.AccountId = id) |> Async.AwaitTask
  if account <> null then
    account.State <- "Closed"
    do! ctx.SaveChangesAsync true |> Async.AwaitTask |> Async.Ignore
    logger.LogInformation("Account{0} updated", id)        
}

when this method comes to the 99th element, the following errors occurred:

|ERROR|System.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. 
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired. All pooled connections are in use.

I tried to follow 1st error's recomendation and tried to add EnableRetryOnFailure()

member this.ConfigureServices(services: IServiceCollection) =
    services.AddOptions() |> ignore
    services.AddCors() |> ignore
    
    services
        .AddDbContext<ApplicationDbContext>(
            fun (service:IServiceProvider) (dbContext:DbContextOptionsBuilder) ->
                dbContext.UseMySql(profile.DbConnectionToAdmin /*HERE*/)|> ignore)
    ...

And I can't find any documentation about this adding options for F# & MySQL, cause all found info written on C#. Maybe problem in used pools (default max=100) and I wrote next:

...
do! ctx.SaveChangesAsync true |> Async.AwaitTask |> Async.Ignore
ctx.Database.CloseConnection()
logger.LogInformation("Account{0} updated", id)

But anyway problem wasn't solved. This is my new experience in F# and async and I cant understand what I did incorrectly. Could anyone help me in such problem? Thanks a lot!

async-await asynchronous f# entity-framework mysql
3 Answers
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFMiscellanous.ConnectionResiliency;Trusted_Connection=True;ConnectRetryCount=0",
            options => options.EnableRetryOnFailure());
}
user_83605870

This was answered above with UseSqlServer() and this might be a bit confusing. It will work with UseMySql(), you just need to update your Startup.cs like below.

I tested and ran this against Pomelo.EntityFrameworkCore.MySql version 5.0.0-alpha.2

Adjust your maxRetryCount, maxRetryDelay etc to suit your needs.

        services.AddDbContextPool<DBContext>(options =>
        {
            options.UseMySql(
                mySqlConnectionStr, 
                ServerVersion.AutoDetect(mySqlConnectionStr),
                options => options.EnableRetryOnFailure(
                    maxRetryCount: 5,
                    maxRetryDelay: System.TimeSpan.FromSeconds(30),
                    errorNumbersToAdd: null)
                );
        });
user_23449140

i had this error before in EF Core 3 along with a very weird slow connection to Mysql server.

Solution 1

add SslMode=None to your connection string.

Solution 2

1-fresh windows install
2-disable windows firewall
3-install Mysql

PS: you don't have to apply the second solution on your machine directly if your not comfortable with it, instead you can just try it in a virtual environment like virtual box first then if it works try it on your actual machine.

user_48495560
Related