AWS Lambda using NET8 image hangs on database access

[geedsen]

Steps to reproduce

None.

The issue

We moved to NET8, so therefor we started using a NET8 image for a AWS Lambda. The Lambda accesses two MySql databases in RDS. On the Lambda we see in Configuration - RDS Databases that both databases should be accessible with the provided VPC's and security groups. But the problem is that we can only use one of them. The moment we reference the other database (context.Database) the Lambda hangs until the timeout is triggered after 10 minutes (10 minutes for the Lambda, the sql connection has a timeout of 5 minutes) . There is no error/exception whatsoever. We are using Entity Framework Core 8.0 with Pomelo 8-beta2 Both databases are 5.7, For both connectionstrings => ServerVersion.AutoDetect(connectionString) returns "5.7.12-mysql" In the Pomelo code I can see that it actually does a connection.Open() for the AutoDetect.

in C#

dbContext1.Database.CanConnect() works dbContext2.Database.CanConnect() hangs

dbContext1.Database.EntitySet1.Where( x => x.Id ==1) works dbContext2.Database.EntitySet1.Where( x => x.Id ==1) hangs

And these last ones only build the query, they don't even actually execute it yet.

The Lambda was working fine when using NET6 (without an image). And we are using the same VPC and securitygroups as with the NET6 version.

We tried adding Pipelining=False to the connectionstring, no difference.

Further technical details

MySQL version: 5.7.12 Operating system: Linux (Amazon) Pomelo.EntityFrameworkCore.MySql version: 8.0.0-beta.2

Because the AutoDetect is working fine which does open a connection. We added a straight MySqlConnection to the Lambda using the same connectionstring we used for the EF dbcontext. Using that MySqlConnection we have no problem accessing /querying the database. So our conclusion is that there is something going on in the pomelo implementation. It looks like accessing the dbContext2.Database property just is causing it to hang.

I added the source for EFCore.MySql from GitHub to the code hoping I could place some log messages at locations in that code that would allow me find the actual location it is hanging. But I am unable to find any code that is being called if I just do a contact.Database.CanConnect() for example.

I understand this will be very complicated to reproduce if I cannot provide any steps/code. The thing is that it works for one database and not for the other.... So I would really appreciate some hints of how I can include even more of Pomelo's implementation to find out where the problem could be.

[lauxjpn]

The Pomelo implementation does not do anything unusual with the connection, so it is unlikely, that this is a Pomelo issues.

That being said, lets start with the following:

Is your original application using two RDS servers, or the same RDS server (MySQL instance) but two different databases?

Get the actual connection string that Pomelo is using when making a connection for your DbContext to the (failing) database (it could be different than the one the AutoDetect method uses, because Pomelo might add options). Without any use of a DbContext (so using ADO.NET), make multiple connections (only) to that failing database using the previously retrieved (from a different run) connection string and execute some simple queries. Are all connections and queries successful, or only the first one?

Next thing to check is, how many active connections are held to the database server and how large the max_connections setting is (see https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1749#issuecomment-1404243694).

How do your connection strings look like (remove/replace sensitive information)? Are they static, or do they change?

Is the app in question the only app that is accessing the database server (or the database), or are there other applications as well?

Do all connection strings from all applications use Pipelining=False?

[bgrainger]

We tried adding Pipelining=False to the connectionstring, no difference.

Do all connection strings from all applications use Pipelining=False?

Given that you seem to be using Aurora v2.x ("Both databases are 5.7") my mind immediately jumped to https://mysqlconnector.net/troubleshooting/aurora-freeze/ being the problem. Pipelining=false is the recommended solution.

If that's consistently used in all connection strings, some trace-level logging from before the hang might be helpful in pointing towards a potential cause.

[geedsen]

Is your original application using two RDS servers, or the same RDS server (MySQL instance) but two different databases?

Different RDS servers

Get the actual connection string that Pomelo is using when making a connection for your DbContext to the (failing) database (it could be different than the one the AutoDetect method uses, because Pomelo might add options).

How do I get what Pomelo is using?

Next thing to check is, how many active connections are held to the database server and how large the max_connections setting is (see [#1749 (comment)]

Unlikely this is the problem, other applications we have have no problems accessing the database at the same time. And this is a DEV environment, there are only a few connections.

How do your connection strings look like (remove/replace sensitive information)? Are they static, or do they change?

"Server=some-aws-address.rds.amazonaws.com;database=xxxxxxdev;port=3306;User ID=xxxx;password=xxxxx;Database=xxxxx;Allow User Variables=True;Connection Timeout=600;Default Command Timeout=600;Use Affected Rows=False";

Static I guess

Is the app in question the only app that is accessing the database server (or the database), or are there other applications as well?

There are others

Do all connection strings from all applications use Pipelining=False?

No, the database that is working does not need that. And adding it to the connection string for the database that is not working did not help.

[geedsen]

We tried adding Pipelining=False to the connectionstring, no difference.

Do all connection strings from all applications use Pipelining=False?

Given that you seem to be using Aurora v2.x ("Both databases are 5.7") my mind immediately jumped to https://mysqlconnector.net/troubleshooting/aurora-freeze/ being the problem. Pipelining=false is the recommended solution.

If that's consistently used in all connection strings, some trace-level logging from before the hang might be helpful in pointing towards a potential cause.

As I said, it is NOT in the connection string for the database that works, and it does not help on the database that does not work.

[lauxjpn]

Get the actual connection string that Pomelo is using when making a connection for your DbContext to the (failing) database (it could be different than the one the AutoDetect method uses, because Pomelo might add options).

How do I get what Pomelo is using?

You could use a connection interceptor:

public class MyConnectionInterceptor : DbConnectionInterceptor
{
    public override InterceptionResult ConnectionOpening(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result)
    {
        var connectionString = connection.ConnectionString;
        
        //
        //  Log/output your connection string here.
        //

        Console.WriteLine("My actual connection string: " + connectionString);

        return base.ConnectionOpening(connection, eventData, result);
    }

    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        var connectionString = connection.ConnectionString;
        
        //
        // Log/output your connection string here.
        //
        
        Console.WriteLine("My actual connection string: " + connectionString);
        
        return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
    }
}

// [...]

.UseMySql(
    connectionString,
    serverVersion)
.AddInterceptors(new MyConnectionInterceptor())

// [...]

Please post the (redacted) connection string here and follow-up with:

Without any use of a DbContext (so using ADO.NET), make multiple connections (only) to that failing database using the previously retrieved (from a different run) connection string and execute some simple queries. Are all connections and queries successful, or only the first one?

---

Next thing to check is, how many active connections are held to the database server and how large the max_connections setting is (see #1749 (comment))

Unlikely this is the problem, other applications we have have no problems accessing the database at the same time. And this is a DEV environment, there are only a few connections.

Are those other apps using Pomelo as well? Or are they non-C# applications?

Next thing to check is, how many active connections are held to the database server and how large the max_connections setting is (see #1749 (comment))

Please post the results.

---

If everything seems to be as it should be, then call the following method from your (failing) Lambda app with the address of your (failing) RDS server to ensure, that the server can indeed be reached:

private static async Task<bool> IsMySqlTcpPortOpenAsync(string dnsOrIpAddress, int port = 3306)
{
    try
    {
        var cts = new CancellationTokenSource(TimeSpan.FromSeconds(15));
    
        using var socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
        await socket.ConnectAsync(dnsOrIpAddress, port, cts.Token);
        
        Console.WriteLine("Connection succeeded.");
        return true;
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        return false;
    }
}

[geedsen]

While trying to add the above requested parts to the code I saw that Pomelo.EntityFrameworkCore.MySql version: 8 was available (I was using beta2). So I just only replaced it with that version. And ....no more hangs.... So it must have been something in Pomelo I guess. Thanks for all the help anyway. Glad that it works now.

[lauxjpn]

@geedsen There should be no relevant changes in Pomelo in regards to making a default database connection between Pomelo 8.0.0-beta.2 and 8.0.0.

To find out the actual reason for the issue you had, please downgrade again to Pomelo 8.0.0-beta.2 (which uses MySqlConnector 2.3.1). Then check whether the issue appears again.

If the issue does not appear again, then the issue had nothing to do with Pomelo (or MySqlConnector) in the first place (which is what I think is the most likely) and something else changed (e.g. in your environment, like an RDS server restart) that made it suddenly work.

If the issue does appear again, please add an explicit package reference to MySqlConnector 2.3.5 (which is the version that Pomelo 8.0.0 implicitly references) but keep your reference to Pomelo 8.0.0-beta.2. If that works, then the issue was resolved by MySqlConnector between versions 2.3.1 and 2.3.5. If it does not work, but works again when you change the Pomelo version to 8.0.0, then it would be indeed a Pomelo issue, which would be kind of weird.

Thanks for reporting back to us.

[alexeygritsenko]

Hi, I also have problems with the connection after upgrading from 6.0 to 8.0. From time to time such errors appear in the logs, although this is a local server and it is 100% accessible and productive:

03:26:43.4532 [ThreadID: 00006] ERROR Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`2.Log An error occurred using the connection to database 'db1.prices' on server 'localhost'.
03:26:43.4532 [ThreadID: 00027] ERROR Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`3.Log An exception occurred while iterating over the results of a query for context type 'db1.prices.Models.PricesDB.PricesDbContext'.
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call.
 ---> MySqlConnector.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
   at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1063
   at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int64 startingTimestamp, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 425
   at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, Action`4 logMessage, Int64 startingTimestamp, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 428
   at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, Action`4 logMessage, Int64 startingTimestamp, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 433
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int64 startingTimestamp, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 111
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int64 startingTimestamp, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 144
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 919
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 419
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlRelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

[lauxjpn]

Thanks for reporting back to us.

@geedsen How is it going? Have you already gone through the outlined steps, so we can find out, what the actual issue was?

---

@alexeygritsenko I opened a new issue for you under #1855, since it seems unrelated to this issue here.

[geedsen]

I put back the beta version , but still could not reproduce the error. So I am sorry but I dont know what caused it. It must indeed have been something else than Pomelo. Thanks for the help anyway.

[lauxjpn]

@geedsen Thanks for reporting back to us! It is possible then, that this issue could happen again in the future. If this occurs, please follow the steps outlined in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1838#issuecomment-1943737867, post the results here and we reopen the issue.

---

Assuming the port was indeed open an the server generally reachable from your app, the most likely cause is probably a connection exhaustion on the database server side (meaning that a connection number equal to the max_connection setting is simultaneously held open). This can happen quickly, if different apps use the same database server, or the same app dynamically changes its connection string during its runtime.

MySqlConnector uses a connection pool of up to 100 connections per distinct connection string by default. The default value for max_connections is 151. That means that if you use just 2 apps (and each app only uses a single connection string that does not change), both with the default connection pool size of 100 connections, you can hit the max_connections value if your app gets under load, resulting in new connections being blocked.

A quick fix in these cases is to just restart the server (or service/daemon), which closes all connections held open. The issue would then happen again though in the future.

A better fix would be to ensure, that the combined connection pool sizes of all the connection strings of all your apps (as stated above, there is one connection pool per distinct connection string) are lower than the configured max_connection setting on the database server.

---

AWS lambda can usually spin-up multiple instances of your app, each using its own connection pool. So lookout for that. If that is the case, it might be simpler to just completely disable connection pooling for your AWS lambda apps. The other way to handle this would be to carefully configure the connection pool to your needs via MySqlConnector's Connection Pooling Options in all connection strings.

---

The connection exhaustion issue has been discussed multiple times in our issues section, so if you search for max_connection or ConnectionIdleTimeout, you will find more information (I believe there is also a related issue with AWS and the connection pool that we discussed in the past).