CODEWITHSUNDEEP
mysqlentity-frameworkasynchronousasync-awaitf#
N.Ayaz
N.Ayaz

Reputation: 355

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

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 with it?

Upvotes: 17

Views: 74160

Answers (3)

Doris Franse
Doris Franse

Reputation: 49

I want to add that this error is not always a retry error. In my case, I was changing the connection string for the database dynamically and I had given an incorrect database name. This database did not exist and I received that error. I'm working on a Web API application using C#, EF Core, and .Net 7.

Upvotes: 4

Sheldon Cohen
Sheldon Cohen

Reputation: 541

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)
                );
        });

Upvotes: 16

Nicat Hidayet-zade
Nicat Hidayet-zade

Reputation: 153

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFMiscellanous.ConnectionResiliency;Trusted_Connection=True;ConnectRetryCount=0",
            options => options.EnableRetryOnFailure());
}

Upvotes: 9

Related Questions