Laoujin Laoujin - 6 months ago 20
SQL Question

Have EntityFramework respect mysql max_user_connections

Is there a way to tell Entity Framework to wait if max_user_connections connections are already open?

I guess I could catch the Exception and retry or keep a counter but that feels hacky at best.

My Azure logs are filling up with the following message:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open.
MySql.Data.MySqlClient.MySqlException:
Authentication to host 'xxx' for user 'yyy' using method 'mysql_native_password' failed with message:
User 'yyy' has exceeded the 'max_user_connections' resource (current value: 4)


I tried adding
Max Pool Size=4
to the connection string but that didn't help.

<add name="DbContext" connectionString="Database=db;Data Source=xxx;User Id=yyy;Password=zzz;CharSet=utf8;Persist Security Info=True;Convert Zero Datetime=True;Max Pool Size=4" providerName="MySql.Data.MySqlClient" />

Answer

As per Vito's suggestion: catch the exception and have the frontend decide what to do:

On the backend, I used PostSharp, an implementation of Aspect-Oriented programming for .NET. It allows me to decorate all my Repositories with an attribute to handle this specific exception.

On the frontend I will probably do something like waiting for 400ms and trying again 1-3 times before giving up.

In the Package Manager Console:
Install-Package PostSharp

You also need to install a PostSharp plugin for Visual Studio

[Serializable]
public class MaxMysqlConnectionExceptionHandlerAspect : OnExceptionAspect
{
    public override void OnException(MethodExecutionArgs args)
    {
        if (args.Exception.InnerException != null && args.Exception.InnerException.Message.Contains("Too many connections"))
        {
            args.FlowBehavior = FlowBehavior.ThrowException;
            args.Exception = new Exception("MAX_CONNECTIONS");
        }
    }

    public override Type GetExceptionType(MethodBase method)
    {
        return typeof(EntityException);
    }
}

Then decorate the classes or methods:

[MaxMysqlConnectionExceptionHandlerAspect]
public class FancyPantsService {}