ASP.NET C# MySQL query execution retry upon failure

Question

ASP.NET C# MySQL query execution retry upon failure

asp.net c# dapper mysql

Question

Is there any ASP.NET package/DLL that allows for MySQL query execution to retry upon failure?

I have read about Transient Fault Handling and even came across a Dapper issue which shows an example but from my research this only works with SqlServer and/or Azure.

My tech stack is as follows:

.NET 4.5.2
Dapper 1.50.2.0
MySQL 5.6 (using Amazon Aurora)

Ultimately I am trying to solve a sporadic failure issue and I believe that implementing some retry logic would help to alleviate the issue.

I tried implementing some code from this Dapper issue but because I am using MySql.Data to connect to my MySql database it does not work with the various methods that are specific to SqlServer connections.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

namespace TransientDapper
{
    public static class TransientDapperExtensions
    {
        private static readonly RetryManager SqlRetryManager = GetDefaultRetryManager();
        private static readonly RetryPolicy SqlCommandRetryPolicy = SqlRetryManager.GetDefaultSqlCommandRetryPolicy();
        private static readonly RetryPolicy SqlConnectionRetryPolicy =
            SqlRetryManager.GetDefaultSqlConnectionRetryPolicy();

        private static RetryManager GetDefaultRetryManager()
        {
            const int retryCount = 4;
            const int minBackoffDelayMilliseconds = 2000;
            const int maxBackoffDelayMilliseconds = 8000;
            const int deltaBackoffMilliseconds = 2000;

            var exponentialBackoffStrategy =
                new ExponentialBackoff(
                    "exponentialBackoffStrategy",
                    retryCount,
                    TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(deltaBackoffMilliseconds)
                    );

            var manager = new RetryManager(
                new List<RetryStrategy>
                {
                    exponentialBackoffStrategy
                },
                exponentialBackoffStrategy.Name
                );

            return manager;
        }

        public static void OpenWithRetry(this SqlConnection cnn)
        {
            cnn.OpenWithRetry(SqlConnectionRetryPolicy);
        }

        public static IEnumerable<T> QueryWithRetry<T>(
            this SqlConnection cnn, string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null
            )
        {
            return SqlCommandRetryPolicy.ExecuteAction(
                () => cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType)
                );
        }
    }
}

james

View more on Stack Overflow

james · Accepted Answer · 10/2/2017 5:07:10 PM

Shortly after posting this, I found a package called Polly that seems to solve this "retry" issue. I tracked it down via this StackOverflow question.

Here is my implementation for querying from a MySQL database and retrying upon failure:

var policy = Policy
    .Handle<AuthenticationException>(ex => ex.InnerException is Win32Exception)
    .Or<AuthenticationException>()
    .Retry((exception, attempt) =>
    {                        
        Log.Error(exception, "Class: {Class} | Method: {Method} | Failure executing query, on attempt number: {Attempt}", GetType().Name,
            MethodBase.GetCurrentMethod().Name, attempt);
    });

try
{
    var token = new Token();

    policy.Execute(() =>
    {
        using (var connection = _mySqlDatabase.GetConnection())
        {
            token = connection.Query<Token>("SELECT * FROM Token...").FirstOrDefault();
        }
    });

    return token;
}
catch (Exception ex)
{
    Log.Error(ex, "Class: {Class} | Method: {Method} | Ultimately failed to retrieve data from the database", GetType().Name,
        MethodBase.GetCurrentMethod().Name);
    throw new HttpError(HttpStatusCode.InternalServerError);
}

ASP.NET C# MySQL query execution retry upon failure

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator