Laoujin Laoujin - 2 years ago 130
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.
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 Source

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

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:

public class FancyPantsService {}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download