After upgrading to .NET 6 my calls to MySQL RDS are...timing out?

Question

I have a solution that was running on .NET 5, and recently I updated it to .NET 6. I also updated all my Nuget packages to the latest versions. Microsoft.EntityFrameworkCore and related packages are version 6.0.9.

When I run the updated project locally against a local MySQL database (version 5.7.12), it works just fine. Hooray!

But in non-dev environments we connect to a MySQL RDS database (same version) at AWS. And when I try to connect to RDS, things get weird. In any of the projects, before doing any real work, we log a some things. And what I'm seeing is that the first message will get logged as expected. But the second call just hangs when SaveChanges() is called. The call never completes.

Well, almost never. One time it did get through and I saw this error on the next database call:

 Microsoft.EntityFrameworkCore.Infrastructure[10404]
  A transient exception occurred during execution. The operation will be retried after 0ms.
  MySqlConnector.MySqlException (0x80004005): Connect Timeout expired.

The reconnect attempt did not work, by the way. I think it just hung like the others.

I am creating the database context like this:

services.AddDbContext<LoggingDbContextAdapter>(optionsBuilder =>
        {
            optionsBuilder.UseLazyLoadingProxies(false)
                .UseMySql(connStr, ServerVersion.AutoDetect(connStr), mysqlOptions =>
                {
                });
        });

We use dependency injection and all the repositories are Transient. I don't think the issue is with RDS settings here as we have been connecting to this instance for years. The issue seems to be related to the assembly upgrades, because if I change .NET 5 to .NET 6 but leave all the assemblies as they are, I don't have this problem (but I do have other problems that require me to upgrade the database-related assemblies). I know there is not a lot to go on, but does anyone have any notion of why this is happening? As far as I can tell this is not a widespread issue for people using .NET 6 and connecting to RDS.

Answer 1

I also recently upgraded a project to .NET 6 which targets an Amazon Aurora 2.10.2 RDS cluster running MySql 5.7.12, with EntityFramework Core 6.

In my case, I am calling DbContext.Database.Migrate() in my start-up code to apply any migrations which have not yet been applied and, similar to your experience, I encountered issues whereby it would hang/freeze indefinitely.

Solution

I found this post on mysqlconnector.net which details the cause of the issue as following:

When using Amazon Aurora RDS, you may experience a hang when calling MySqlConnection.Open()... The cause of this problem is Amazon Aurora not correctly supporting pipelining in the MySQL protocol. This is known to be a problem with 2.x versions of Aurora (that implement MySQL 5.7), but not with 3.x versions (that implement MySQL 8.0).

It then says:

To work around it, add Pipelining = False; to your connection string to disable the pipelining feature.

So, all you need to do is modify your connection string to look like the following:

"Server=<server>;Database=<database>;User Id=<user_id>;Password=<password>;Pipelining=False;"

Notes

EF Core 6 uses the MySqlConnector library internally when communicating with a MySql database, so in case you're thinking (like me at the time) that you're not using it... well you are, just transitively.

There's actually some code in this library which checks the server version of your database and the host name to try to identify if it's a MySql 5.7 running in Amazon RDS and, if so, it will disable pipelining automatically for you:

if (!cs.Pipelining.HasValue && ServerVersion.Version.Major == 5 && ServerVersion.Version.Minor == 7 && HostName.EndsWith(".rds.amazonaws.com", StringComparison.OrdinalIgnoreCase))
{
    m_logArguments[1] = HostName;
    Log.Debug("Session{0} auto-detected Aurora 5.7 at '{1}'; disabling pipelining", m_logArguments);
    m_supportsPipelining = false;
}

This wasn't working for me as my connection string points to an alias which proxies the request to Amazon RDS (i.e. my hostname doesn't end in ".rds.amazonaws.com", so it fails the check above and doesn't disable pipelining). I presume this is the same for you, hence why it hangs/freezes.