Transient Errors using MySQL with .NET 5/EF

Question

My IT guy just updated our MySQL database to a new cluster, and now I am getting transient errors like these:

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.

So, after research, I added this code:

options.UseMySQL(Configuration.GetConnectionString(connectionString),
    options => options.EnableRetryOnFailure
        (
            maxRetryCount: 10,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null
        )
    );
});

Which then, unfortunately resulted in a new error:

Error CS1061 'MySQLDbContextOptionsBuilder' does not contain a definition for 'EnableRetryOnFailure' and no accessible extension method 'EnableRetryOnFailure'

I have NO idea if I am missing a reference or not, but intellisense doesn't help

So, I do more research and find information here https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html and here https://entityframeworkcore.com/knowledge-base/57595183/using-entity-framework-with-multiple-databases-and-providers-in-the-same-project--sql-server-and-mysql- about using this line from MySQL:

SetExecutionStrategy("MySql.Data.MySqlClient", () => new MySqlExecutionStrategy());

Which also throws errors.

Does anyone know how I can implement Connection Resiliency/Retry Logic using MySQL?

.Net 5, MySQL 5.7, MySqlConnector 8.0.20

Answer 1

Foolowing this https://learn.microsoft.com/en-us/dotnet/architecture/microservices/implement-resilient-applications/implement-resilient-entity-framework-core-sql-connections

use

services.AddDbContext<AppDbContext>(options =>
{
    string connectionString = AppConfig.Configuration.GetConnectionString("DefaultConnection");
    options.UseMySql(connectionString,
        ServerVersion.AutoDetect(connectionString),
        mySqlOptions =>
            mySqlOptions.EnableRetryOnFailure(
                maxRetryCount: 10,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
        );
});

Answer 2

I've created a library that enables you to have resiliency on SaveChanges/SaveChangesAsync with a configured execution strategy and extension methods.

First install ResilientSaveChanges.EFCore, then configure it when your app starts, eg:

ResilientSaveChangesConfig.Logger = _logger;
ResilientSaveChangesConfig.LoggerWarnLongRunning = 3_000;
ResilientSaveChangesConfig.ConcurrentSaveChangesLimit = 5;

Then set up your execution strategy for MySQL, eg:

public static class Constants
{
    public const int MAX_RETRY_COUNT = 10;
    public const int MAX_RETRY_DELAY_SECONDS = 6;
    public const int COMMAND_TIMEOUT = 120;
}

public class MyExecutionStrategy : ExecutionStrategy
{
    public MyExecutionStrategy(MyDbContext context) : base(
        context,
        Constants.MAX_RETRY_COUNT,
        TimeSpan.FromSeconds(Constants.MAX_RETRY_DELAY_SECONDS))
    { }

    public MyExecutionStrategy(ExecutionStrategyDependencies dependencies) : base(
        dependencies,
        Constants.MAX_RETRY_COUNT,
        TimeSpan.FromSeconds(Constants.MAX_RETRY_DELAY_SECONDS))
    { }

    public MyExecutionStrategy(MyDbContext context, int maxRetryCount, TimeSpan maxRetryDelay) : base(
        context,
        maxRetryCount,
        maxRetryDelay)
    { }

    protected override bool ShouldRetryOn([NotNull] Exception exception)
    {
        if (exception is MySqlException mySqlException)
        {
            if (mySqlException.IsTransient)
            {
                Debug.WriteLine($"MySqlException transient error detected. Retrying in {Constants.MAX_RETRY_DELAY_SECONDS} seconds");
                return true;
            }
            Debug.WriteLine($"Non-transient MySqlException detected.");
            return false;
        }

        if (exception is DbUpdateException)
        {
            Debug.WriteLine($"DbUpdateException detected. Retrying in {Constants.MAX_RETRY_DELAY_SECONDS} seconds");
            return true;
        }

        Debug.WriteLine($"Error that won't be retried. Type is {exception.GetType()}");
        return false;
    }
}

Then make use of your execution strategy, for example something like this:

services.AddPooledDbContextFactory<MyDbContext>(options =>
{
    options.UseMySql(
        Configuration.GetConnectionString("DefaultConnection"),
        "8.0.29",
        options =>
        {
            options.EnableRetryOnFailure(
                Constants.MAX_RETRY_COUNT, 
                TimeSpan.FromSeconds(Constants.MAX_RETRY_DELAY_SECONDS),
                null);
            options.CommandTimeout(Constants.COMMAND_TIMEOUT);
            options.ExecutionStrategy(s => new MyExecutionStrategy(s));
        }
    ).EnableDetailedErrors();
});

And finally simply replace your context.SaveChanges(); and await context.SaveChangesAsync(); with context.ResilientSaveChanges(); and context.ResilientSaveChangesAsync(); respectively.