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