ConnectionIdleTimeout needed for RDS IAM authentication

[Simonl9l]

When using pomelo to connect to a MySQL DB in an AWS RDS environment using IAM IAM Authentication, the connection password is generated of an IAM STS token at runtime, but only has a 20 minute idle lifetime.

In our implementation we do this authentication in an OnConfiguring override.

As understood when using A DBContextPool the pool keeps the connections open, which ordinarily is great.

However should the connection sit idle the underlying token willl expire and reuse of the context will fail.

How does one configure pomelo such that after a given time if idle the context will be fully disposed - and connection to DB closed?

Other drivers have configuration properties such as ConnectionIdleTimeout, how can similar be achieved with Pomelo?

Further technical details

MySQL version: RDS Auora Operating system: Pomelo.EntityFrameworkCore.MySql version: 3.1 Microsoft.AspNetCore.App version: 3.1.100

Other details about my project setup:

[mguinness]

Stack Overflow question Do I need to reconnect every 15 minutes with RDS IAM authentication has further info.

Both MySQL and PostgreSQL have a ConnectionLifetime option in the connection string which might be useful.

@bgrainger @roji Any suggestions on connection management at the ADO.NET level for this scenario? Is there any callback mechanism available when a new connection is needed?

[bgrainger]

Related issue: https://github.com/mysql-net/MySqlConnector/issues/800

[bgrainger]

Other drivers have configuration properties such as ConnectionIdleTimeout, how can similar be achieved with Pomelo?

AFAIK, this is the opposite of what you want. My understanding is that while a RDS connection remains open in the pool, it remains usable forever, even though the authentication token it was opened with has expired.

But if you do want to experiment with it, MySqlConnector supports ConnectionIdleTimeout: https://mysqlconnector.net/connection-options/#ConnectionIdleTimeout

[lauxjpn]

As understood when using A DBContextPool the pool keeps the connections open

@Simonl9l That is not the case. A DbContext pool just pools the DbContext instances, not the ADO.NET connections. The connections are being released when the DbContext is being returned to the (EF Core level) pool. However, if you are also using a connection pool, then the released connections are returned to this (ADO.NET level) connection pool.

See https://github.com/mysql-net/MySqlConnector/issues/917#issuecomment-750030544, where I explain the difference of the DbContext pool and the connection pool in more details.

So what you are looking for are the connection pool options, which can be configured as part of the connection string (see MySQL .NET Connection String Options).

Also, there is a retry strategy for failing connections, that you can either just enable or derive your own implementation from, in case you don't want to set the connection lifetime to a specific value, but just want to open a new connection, once an existing one has failed).

Feel free to post some code (e.g. your OnConfiguring() method), so we get a bit more context of how this is being handled at the moment by your code.

[Simonl9l]

@bgrainger @lauxjpn @mguinness thanks all for the quick chime in.

All the links are helpful, and see that I did not even know the full extent of my issues with the connection pool keying on the full connection string including password (that ordinarily changes with each authenticated connection).

Additional and likely orthogonal to this, now that I'm (trying) using the DbContextPool - to limit the connection count, my DBContext implementation can only take a DbContextOptions in its constructor where as before it would DI in IConfiguration such that I can retrieve the configuration, and make the connection in the OnConfiguration.

I note that in the constructor I can call this.GetInfrastructure().GetRequiredService<IConfiguration>() or this.GetRequiresService<IConfiguration> but these fail if I don't specify .UseInternalServiceProvider(serviceProvider) as part of the services.AddDbContextPool but can use .UseInternalServiceProvider(serviceProvider) as I can actually configure the provider until I have a connection string to configure on said provider (in OnConfiguring).

I also saw this related issue https://github.com/dotnet/efcore/issues/7185 but not sure where all these things are...

[lauxjpn]

@Simonl9l Your observations about UseInternalServiceProvider() are correct.

But since DbContextPool does not limit your underlying connections at all, it is the wrong tool for the job. It is intended for high throughput applications, where the very minor overhead of creating a DbContext instance instead of resetting an existing one, might actually matter. For most applications, this does not matter at all. But since this has nothing to do with the underlying connection (the MySqlConnection.Close() method will be called in both cases, when the DbContext is being disposed and when it is being reset), it does not matter to your issue here at all.

Therefore, since you don't need a DbContext pool, you don't have to limit your constructor.

You need to either manage your connection pool (which you can do with the connection string options) or you need to handle your failing connections, which you can with a retry strategy (see Connection Resiliency).

[roji]

Other drivers have configuration properties such as ConnectionIdleTimeout, how can similar be achieved with Pomelo?

AFAIK, this is the opposite of what you want. My understanding is that while a RDS connection remains open in the pool, it remains usable forever, even though the authentication token it was opened with has expired.

@bgrainger that's a good point. However... Even if connections aren't forcibly closed by the server, since the token needs to rotate, I'm assuming the user will be changing the connection string every 20 minutes. At least on Npgsql, this means that a new pool will be created every 20 minutes, but the old pools will continue to retain their idle physical connections, in what is effectively a connection leak. ConnectionIdleTimeout would help clear those out - or the user could manage this manually by calling a ClearPool API explicitly. Unless MySqlConnector manages its connection strings/pool/access tokens differently, the same is likely true there.

(note that Npgsql removes physical connections, but the pool structure itself is currently not released - so there's still a memory leak when doing this scenario in Npgsql, https://github.com/npgsql/npgsql/issues/3396).

Finally, to rotate the connection string with EF Core, a connection interceptor can be used - see this example in the EF docs. Note, however, that it's strongly discouraged to do anything heavy in this interceptor, since (pooled!) connections are opened a lot. A caching mechanism should be implemented in there.

[roji]

Also, I agree with @lauxjpn that DbContext pooling is completely orthogonal to all of this. I'd recommend concentrating on the ADO.NET layer and getting this right.

[lauxjpn]

Even if connections aren't forcibly closed by the server, since the token needs to rotate, I'm assuming the user will be changing the connection string every 20 minutes. At least on Npgsql, this means that a new pool will be created every 20 minutes, but the old pools will continue to retain their idle physical connections, in what is effectively a conneciton leak.

@Simonl9l This leaves you with at least two options at the moment:

• You could just disable connection pooling altogether. If the overhead of creating a connection is low (e.g. if the database server is on the same machine or same local network), then this will be good enough. You would do this using the Pooling=false connection string option.
• You can call MySqlConnection.ClearPool() or MySqlConnection.ClearAllPools() when the token rotates.

Of course you could also just use the ConnectionIdleTimeout option and let MySqlConnector take care of closing the idle connections, once the token has rotated. At the worst, this will leave you with an outdated connection pool full of idle connections for the ConnectionIdleTimeout time, in addition to your currently active connection pool. If your MySQL max_connections setting can handle this, then this might also be good enough for you.

[mguinness]

My understanding is that while a RDS connection remains open in the pool, it remains usable forever, even though the authentication token it was opened with has expired.

Does interactive_timeout setting have a bearing on this? If left to the default value server will close the idle connection after 8 hours?

[lauxjpn]

Does interactive_timeout setting have a bearing on this? If left to the default value server will close the idle connection after 8 hours?

Well if you want to handle this at the actual database sever level, then it should be wait_timeout, unless the connection has been marked as interactive.

The default would not be good enough for most scenarios though, because if the token resets every 20 minutes and lets say 15 connections are being held in the connection pool at the time on average and you are using the default value for max_connections of 151, then you can't establish new connections after less than 4 hours anymore.

I have been using carefully setup max_connections and wait_timeout settings in the past, but I would use wait_timeout only as the last bastion to ensure, that if a connection does in fact leak, it will be closed at some point. The reason this is not the preferred way is, that wait_timeout will close your connection (pooled or not), whether you are currently using it or not. So this should only be used in conjunction with a retry strategy, because your connection might just be forcefully closed by the server in the middle of your apps operations.

If your database is the only one on the server though, and you can therefore setup wait_timeout to 21 minutes, then this could also be good enough to cleanup you connections (depending on your apps workload and your max_connections setting). Or you can manually set the variable for the session after establishing the connection, in which case this would also work with a database that is being shared.

However, I would recommend to handle this issue on another level than the database server level.

[Simonl9l]

All - thanks again for the replies - trying to parse all your points all very helpful...here is more context:

We have a reactive UI (Blazor) that get data for each component on the fly with specific requests to an underlying micro service REST calls. All these calls are async.

The underlying service is currently using a regular DdContext, with the .UseMySql with the configuration and RDS SDK IAM authentication in the OnConfiguring override.

The REST calls to the micro service each makes one or more EF DBContext calls depending on the service request, all also as async (as an aside passing though the CancelationToken context chain from razor page, and vis the HTTP Pipeline, into the controller - and we handle the cascaded cancelations cleanly), on the context DI'd into the controller. So they are very transient.

If the UI is refreshed we need to go and get all the underlying data components, each being small but many. This causes cascading layers of async calls, each in turn results in a REST service call and each in turn then injects a DBContext, resulting in a new DB connection.

Current we're not in production, so we are using one of the smaller RDS MySql instances that has a max of 90 concurrent connections - as we launch we will probably operate on a larger tier but need to save the $$$. This is less than the default 151.

We're finding for a relatively small user data footprint these cascaded async's are blowing the connection limit. It's good to know this as we scale. My sense is that as this refresh happens, it results in a ton of connections that are idle and not closed as each REST request rest in a new one being created, as the 151 has not been reached.

The thought was to use the DBContextPool as a mechanism to limit the number of connection per micro service instance. Even larger tiers only allow 1000 concurrent connection so we need to manage that either way and that we do foresee high transaction rates.

I've already enabled EnableRetryOnFailure()

It seem to smell to have the DbContext somehow cache the token and reuse for the require 15 minutes. Besides, given the "state" of a DBContext, and that in is not recommended for DBContextPools it still comes back to using this.GetInfrastructure().GetRequiredService<TokenCache>() or similar, and that does not seem to work? as its not "yet" available in the OnConfiguring.

Unless there are EF core plan (or features already in 5.x) to manages the pool better (than using the full connection string including password), and separate concerns of the pool management, and the authorized use of a connection in that pool, and ability to use the services in the OnConfiguring it may be best to drop the IAM auth for the time being.

I'd figure this is an areas that need more attention from an AWS/RDS/IAM auth perspective before it can be used at scale that includes how best to manage the other timeout settings in an encompassing manner.

Unless there are other specific recommendations ?

Thanks!

[roji]

The thought was to use the DBContextPool as a mechanism to limit the number of connection per micro service instance. Even larger tiers only allow 1000 concurrent connection so we need to manage that either way and that we do foresee high transaction rates.

As written above, DbContext will not do anything to reduce your connection usage. When you instantiate an (unpooled) EF DbContext, it does not hold a connection, but rather opens and closes connections as needed (i.e. every time you execute a query). In other words, EF relies on the underlying database driver to implement pooling, i.e. that "opening" and "closing" connection is an extremely fast operation. Pooled contexts function in the exact same way; the only thing that context pooling does, is reduce some processing for setting up the DbContext itself (resolving services and such things). This usually makes sense only in very high-perf scenarios, and doesn't affect EF's handling of database connections in any way.

It seem to smell to have the DbContext somehow cache the token and reuse for the require 15 minutes.

The DbContext wouldn't cache anything - but your connection interceptor would (this is what I mentioned above). There is nothing wrong with that - the interceptors job would be to inject an up-to-date connection string with a valid token, and it needs to do that as fast as possible (since again, EF opens connections very frequently).

Unless there are EF core plan (or features already in 5.x) to manages the pool better (than using the full connection string including password)

There isn't really anything for EF to do here, as far as I know. Your underlying MySQL driver requires that the token be given as the password in the connection string - that's all. The EF tool to using changing connection strings - as tokens rotate - is the DB connection interceptor. I'm not aware of anything blocking at the moment for successful work with AWS/RDS/IAM.

From your description, I'd carefully sit down and separate the different concerns/problems. Are you reaching your database's connection limits within the 20 minute window of a token? If so, then your problem has nothing to do with any of the above. If old physical connections are staying open after the token has already rotated, then setting ConnectionIdleTimeout to 21 minutes should fix that, as suggested above.

[Simonl9l]

The plan was to use the DbContext connection pool to throttle the connections (as a concentration point) but that won’t work per other linked issues as the pool used the full connections string to manage pooling criteria - including password, that changes for every connection.

That is an EF issue...for any data source that uses similar token based authentication.

The other issue is the inability to access IConfiguration via the service provider in OnConnecting override. This as yet is also unresolved.

By dropping token based authentication I can pass the connection string into the DbConnection options and have a single connection pool as the string is the same.

[bgrainger]

My understanding is that while a RDS connection remains open in the pool, it remains usable forever, even though the authentication token it was opened with has expired.

Does interactive_timeout setting have a bearing on this? If left to the default value server will close the idle connection after 8 hours?

Well if you want to handle this at the actual database sever level, then it should be wait_timeout, unless the connection has been marked as interactive.

Yes, "forever" overstated it. Generally most MySQL Servers are configured to close idle connections after some time (usually 8 hours). You can increase ConnectionIdleTimeout from its default of three minutes up to your server's wait_timeout value if you want idle connections to live longer.

[bgrainger]

including password, that changes for every connection

@Simonl9l If you're changing the password for each new connection you make, then add Pooling=false to your connection string, so that the connection to RDS is closed when the MySqlConnection is disposed. This will prevent any idle connections from hanging around, at the cost of a slightly longer time to open the connection (because you will get a new connection to the server every time a MySqlConnection is opened).

The EF tool to using changing connection strings - as tokens rotate - is the DB connection interceptor.

Otherwise I'd follow @roji's advice (that I just quoted) and update your connection string every 10-15 minutes with a new token (and leave pooling enabled). This will provide efficient connection reuse, while creating new pools of connections as tokens expire. You can reduce ConnectionIdleTimeout to, say, 30 seconds, to clean up the old pool of connections quickly once the new one becomes active.

[Simonl9l]

@bgrainger et al...

Unless there is a way to get IConfiguration accessible in OnConnecting via GetServices I can’t get to the connection string parts given the constructor limitations of pooled DbContexts.

I need to use the pool to act as a concentration point to throttle connection access.

However I can pass in the constructed .UseMySql call in startup if I don’t use token based authentication.

[lauxjpn]

[...] pooled DbContexts [...] I need to use the pool to act as a concentration point to throttle connection access.

@Simonl9l Let's make this very clear, because this has been corrected now multiple times by us:

No, you don't need to use the DbContextPool as a concentration point to throttle your connections. The DbContextPool has nothing to do with the database connections. It has a completely different purpose. (Again, see https://github.com/mysql-net/MySqlConnector/issues/917#issuecomment-750030544 for the difference between the DbContext pool and the connection pool.)

Do not use DbContextPool when trying to solve your issue. It cannot work.

You are trying to clean your room with a hammer. It is the wrong tool for the job.

---

Now, with the DbContextPool out of the way, there are no restrictions what your DbContext constructor can take as parameters. Inject whatever you like.

---

There are basically 3 suggested ways by us to handle your issue. Every single one of the 3 ways will work to solve your issue:

• Disable connection pooling and keep injecting your token in the connection string in OnConfiguring. Done. Nothing more to do. (If you are concerned about overhead when establishing a connection, just measure the difference between your API with and without using a connection pool. Since your servers are likely hosted in the same AWS region, the overhead is likely to be negligible.)
• Keep connection pooling enabled and use a connection interceptor to inject your token into the connection string. To cleanup the old connection pool after you got a new token, use MySqlConnection.ClearAllPools() (or MySqlConnection.ClearPool()) or let MySqlConnector close the connections automatically after some time by setting ConnectionIdleTimeout to an appropriate value.
• Keep connection pooling enabled, keep injecting your token into the connection string in OnConfiguring and set the wait_timeout MySQL system variable either globally, or for every session/connection that has been opened (e.g. by using a connection interceptor), to an appropriate value (e.g. 21 minutes).

The pros and cons of these approaches have been described above. I listed the approaches in the order I would recommend them.

Unless you need some code to get you started (which I am happy to provide as always), I don't think there is anything left to discuss here.

(Fair warning: Should any further comments of yours mention the DbContext pool again, I will close this issue and assume that you are trolling us on purpose, as you seemed to have ignored all our previous answers about the DbContext pool.)

[Simonl9l]

@lauxjpn & @bgrainger , @roji @mguinness

Your patience is appreciated! it's taken me a while too get back to you to digest and experiment.

I figure I do want to keep using the SqlConnection polling as I need to really leverage the MaximumPoolSize if I'm correctly interpreting what you are collectively saying.

It seems that if I just set Pooling=false nothing will limit the number of REST/connection requests, due to my current inability to otherwise control that flow - be agree and understand this will at some time be needed.

Even with experiments I still get Too Many Connections issues...

In such case I presume to also need to use the Interceptor as I assume (perhaps incorrectly) given other comments that the password/token in a connection string is part of the connection pool key, so with it changing I may have many pools of one connection?

I've looked for some examples of the connection interceptor and can't seem to find one that works with MySql, any samples to get me going (with need package references) would be appreciated.

[mguinness]

I've looked for some examples of the connection interceptor and can't seem to find one that works with MySql, any samples to get me going (with need package references) would be appreciated.

See Connection interception for SQL Azure authentication using ADD and substitute SqlConnection with MySqlConnection.

[Simonl9l]

@mguinness yep - I don't see SqlConnection.AccessToken, it the intent to recreate the entire connection string (with the tokenized password)?

[mguinness]

Yes, you can use MySqlConnectionStringBuilder to make changing connection string easier along w/ IMemoryCache for token.

[lauxjpn]

It seems that if I just set Pooling=false nothing will limit the number of REST/connection requests, due to my current inability to >otherwise control that flow - be agree and understand this will at some time be needed.

That is correct. Though from a database perspective, this does not matter, since the connections get closed the moment the request is done. You will not exhaust your max_connections setting with this (if you would, you would also do it with any other possible method, since this method gives you all available connections if you should really have 151 concurrent requests, and makes them available to other request as soon as possible by closing them).

Or are there other (e.g. business related) reasons why you want to control the number of concurrent database connections?

---

Even with experiments I still get Too Many Connections issues...

If you use Pooling=false, than this should be impossible to happen. I would however restart the database server after disabling pooling, just in case all the tinkering before left you with some stale connections.

This is definitely the simplest method and simple is usually good.

You can check your active connections in MySql Workbench (preferable) or with the following statements:

select @@max_connections;
show status where `variable_name` = 'Threads_connected';
show processlist;

If you were talking about other experiments, what exactly are they?

---

In such case I presume to also need to use the Interceptor as I assume (perhaps incorrectly) given other comments that the password/token in a connection string is part of the connection pool key, so with it changing I may have many pools of one connection?

You will not need a connection interceptor or any other mechanism, if you set Pooling=false. It just works. There is no connection pool at all. I would only recommend to you to use the other approaches, if the performance of establishing a connection to the database server is too slow on average for your requests.

If that is the case, how many microseconds (or milliseconds) does it take on average to open a connection to your database without a connection pool?

If you set Pooling=true, than it is correct that MySqlConnector will create one pool per distinct connection string. So if the performance of disabling pooling is not good enough, than using a connection interceptor and calling MySqlConnection.ClearAllPools(), is an option with pooling enabled. (Instead of calling MySqlConnection.ClearAllPools(), you could also set an appropriate ConnectionLifeTime or ConnectionIdleTimeout. But this is more imprecise than calling MySqlConnection.ClearAllPools(), so I would definitely call MySqlConnection.ClearAllPools() and only use ConnectionLifeTime or ConnectionIdleTimeout as a backup (if you are paranoid).)

~~BTW, pooling will open more connections (until MySQL's max_connections is reached) than the MaximumPoolSize setting. But if more connections than MaximumPoolSize have been opened, than those excess connections will not be put in the connection pool but will be closed, once your request is done. I say this explicitly here to make sure you understand, that you cannot control the number of concurrent connections to your database with the connection pool. Analog to how the DbContext pool is a mechanism to speedup DbContext creations, the connection pool is a mechanism to speedup connection establishment to your database server. It is not the right tool to control the number of concurrent connections.~~

---

[...] I may have many pools of one connection?

If you set Pooling=true and you don't cleanup the old pool when your password changes, then all connections of the old pool will stay active (and that is usually more than one connection, unless there has only been one concurrent database connection for the lifetime of the old pool). I did a sample calculation above for a moderately used app, explaining how and when this could lead to a connection exhaustion:

The default [wait_timeout of 8 hours] would not be good enough for most scenarios though, because if the token resets every 20 minutes and lets say 15 connections are being held in the connection pool at the time on average, and you are using the default value for max_connections of 151, then you can't establish new connections after less than 4 hours anymore.

[Simonl9l]

@mguinness - Thanks I have the interceptor working...and with that, I don't (yet) see the Too Many Connection but will continue to test.

The implementation is decidedly simple (Such that it may help others):

 public class RdsAuthenticationInterceptor: DbConnectionInterceptor
    {
        public override Task<InterceptionResult> ConnectionOpeningAsync(
            DbConnection connection, 
            ConnectionEventData eventData, 
            InterceptionResult result, 
            CancellationToken cancellationToken = default)
        {
            return Task.FromResult(ConnectionOpening(connection, eventData, result));
        }

        public override InterceptionResult ConnectionOpening(
            DbConnection connection,
            ConnectionEventData eventData,
            InterceptionResult result)
        {
            if (!(connection is MySqlConnection conn))
            {
                return result;
            }

            var builder = new MySqlConnectionStringBuilder(conn.ConnectionString);

            builder.Password = RDSAuthTokenGenerator.GenerateAuthToken(builder.Server, (int)builder.Port, builder.UserID);

            conn.ConnectionString = builder.ConnectionString;
            return result;
        }        
    }

@lauxjpn - thanks for the continued insights.

At my current scale with RDS - So the availability is lower than the MySqlConnector default 115.

I need to share these 90 connections across 3+ micro services - at least until we have paying customers.. Whist I appreciate your direction to set pooling=false, my worst case use case just now initiates over 200 rest requests, that the .Net server can easily handle....not ideal but 20+ requests for a single user is not unrealistic, if I get multiple of those simultaneously....

For now I'm setting the following:

ConnectionLifeTime = 60,
ConnectionIdleTimeout = 10,
MaximumPoolSize = 10,
ConnectionReset = true

With these settings the connection will expire long before the token does.

I'll say thank all and close this issue!

[roji]

BTW, pooling will open more connections (until MySQL's max_connections is reached) than the MaximumPoolSize setting. But if more connections than MaximumPoolSize have been opened, than those excess connections will not be put in the connection pool but will be closed, once your request is done. I say this explicitly here to make sure you understand, that you cannot control the number of concurrent connections to your database with the connection pool. Analog to how the DbContext pool is a mechanism to speedup DbContext creations, the connection pool is a mechanism to speedup connection establishment to your database server. It is not the right tool to control the number of concurrent connections.

@lauxjpn @bgrainger just to note that in Npgsql we make a lot of effort to never go above the prescribed max pool size - attempts to open beyond that will wait until a connection is released back to the pool, rather than create a new physical connection. This is because physical connections tie up (potentially significant) resources on the database side, and usage predictability has been shown to be important, especially when there are multiple applications (or application instances) accessing the same database.

See this recent conversation comparing DbContext pooling and database connection pooling.

[bgrainger]

BTW, pooling will open more connections (until MySQL's max_connections is reached) than the MaximumPoolSize setting. But if more connections than MaximumPoolSize have been opened, than those excess connections will not be put in the connection pool but will be closed, once your request is done. I say this explicitly here to make sure you understand, that you cannot control the number of concurrent connections to your database with the connection pool.

@lauxjpn This is not how the MySqlConnectorPool works. MaximumPoolSize does actually control the maximum number of concurrent connections. CC @roji

(I have a vague memory of there being a discussion early on about whether it should just control the maximum number of pooled connections, as the name implies, or also control the maximum number of concurrent connections, but I can't find that now.)

If MaxPoolSize=90 (and Pooling=True), and all 90 connections are in active use, then MySqlConnector.Open(Async) will block for up to ConnectTimeout seconds waiting for a connection to be closed (and returned to the pool). If this doesn't happen, a MySqlException (with MySqlErrorCode.UnableToConnectToHost) will be thrown.

See https://github.com/mysql-net/MySqlConnector/issues/747.

You will not exhaust your max_connections setting with this If you use Pooling=false, than [getting Too Many Connections]should be impossible to happen.

This is also untrue. Setting Pooling=false has the side-effect of disabling MySqlConnector's blocking when MaxPoolSize has been reached, so an unlimited number of concurrent connections can be created (based on the number of active requests to your web tier). If pooling is disabled, you'd have to implement your own semaphore to limit the total number of open MySqlConnection objects.

[bgrainger]

The implementation is decidedly simple (Such that it may help others):

@Simonl9l Thanks for sharing!

I'm curious (since I don't know all that much about EF Core or AWS 😀) does this line (a) run for every single new connection, and (b) generate a new password every time, or reuse passwords for 10-15 minutes until they expire?

builder.Password = RDSAuthTokenGenerator.GenerateAuthToken(builder.Server, (int)builder.Port, builder.UserID);

If it creates a new password for each new connection, then you'll end up with one pool per connection (because the password is included in the connection string, and the full connection string is used as the key to identify/distinguish pools). This will defeat any benefits of pooling, and you might as well go back to the simpler solution that avoids it.

However, if GenerateAuthToken reuses and returns a cached password, then pooling will work to control the maximum number of active connections and everything should work great. (I'm hoping this is the case.)

[lauxjpn]

This is not how the MySqlConnectorPool works. MaximumPoolSize does actually control the maximum number of concurrent connections.

@bgrainger My bad, must have remembered it wrong (for the second time now, because I was already wrong about it in the issue you referenced)!

You will not exhaust your max_connections setting with this If you use Pooling=false, than [getting Too Many Connections]should be impossible to happen.

This is also untrue. Setting Pooling=false has the side-effect of disabling MySqlConnector's blocking when MaxPoolSize has been reached, so an unlimited number of concurrent connections can be created (based on the number of active requests to your web tier). If pooling is disabled, you'd have to implement your own semaphore to limit the total number of open MySqlConnection objects.

I absolutely agree. It was meant in the way that you cannot exhaust your max_connections with "dead" connections from pools that are not actively used by your app anymore, because there are no pools anymore.

[Simonl9l]

@bgrainger you are most welcome (I’m sure other IAM RDS authentication use cases will need to do this) also in return thanks for the clarifications with @lauxjpn.

Per your question above - the RDS IAM token does have a lifetime as indicated elsewhere by others above. However, although a system account, I don’t currently think it wise to have connections sit around and be reused on the basis the connection is still allowed for that account at the time given a prior authentication/authorization. Albeit there are limits on the number of times a given period this auth can be called, so may have to cache and expire it with a timer.

My understanding also is that by only supplying it via the interceptor, means it’s NOT included in the pooling key per @lauxjpn confirm a few posts above. I have to say I believe I’m observing that to be correct - is this your understanding?

With all that in mind please could you point me at anything related to how these pooled connections are re-used in by the DbContext. Do I need to assume OnConfiguring is still called or is just the interceptor called?

If the latter, perhaps I need to guard the existence of the auth token and only generate new ones if needed?

The setting Indicated above were a guess (beyond the MaxPoolSize) and will look to tune once I have data related to my use case scenario so such that I can tune them, or fix them related to any caching strategy.

I’d assume to make the idle lifetime very short in the absence of token caching such that pool just acts as a max connections limiter but closes them as soon as they are first idle.

[roji]

@Simonl9l as already written above, a DbContext will open a DbConnection every time it needs to perform a database operation (e.g. a query); each time an open occurs, the interceptor runs. This means it's very important for connection opening to be extremely fast; if your RDS token API performs any sort of network call every time you call it, then you're definitely going to want to avoid calling it every time from the interceptor, by implementing some sort of caching there. If, on the other hand, the RDS API already caches internally, and therefore returns very clearly in the majority of cases, there's no issue and no need for an additional caching layer in your code.

Beyond that, we can't help you with the specific of the Amazon APIs - you're going to have to figure out yourself how these work, and what their perf characteristics are like.

[Simonl9l]

@roji understood and agreed (RDS)!

Seems that I'm still breaking connection limits if I run a user a request that will generate 200+ rest requests async, if I run show processlist; I get:

• 30 odd users/conections as username unauthenticated user with command Connect and State Reading from net, and a couple as command Login,
• 20 users/connections as the micro server account, with a status with a command of Sleep and State cleaned up

Not sure why the unauthenticated users are coming from, is that the DBContexts being fired up and waiting for the RDS token to authenticate them?

Somehow the pool is not working as expected as I should not see the 20 connection with the micro-service account?

I also get a bunch of these..

MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired.
 ---> System.ObjectDisposedException: Cannot access a disposed object.
Object name: 'System.Net.Sockets.Socket'.
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.TcpClient.EndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.TcpClient.<>c.<ConnectAsync>b__27_1(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 872
   at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 900
   at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 338
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ConnectionPool.cs:line 112
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ConnectionPool.cs:line 141
   at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 645
   at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 312
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(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.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)

[mguinness]

I'm curious (since I don't know all that much about EF Core or AWS 😀) does this line (a) run for every single new connection, and (b) generate a new password every time, or reuse passwords for 10-15 minutes until they expire?

builder.Password = RDSAuthTokenGenerator.GenerateAuthToken(builder.Server, (int)builder.Port, builder.UserID);

However, if GenerateAuthToken reuses and returns a cached password, then pooling will work to control the maximum number of active connections and everything should work great. (I'm hoping this is the case.)

Using IMemoryCache it's easy to reuse token from AWS within the 15 minute expiration (if not already done in GenerateAuthToken).

var key = builder.UserID; //whatever needed for uniqueness

var token = _cache.Get<string>(key);
if (token == null)
{
    token = _cache.Set<string>(key, RDSAuthTokenGenerator.GenerateAuthToken(...),
        new MemoryCacheEntryOptions().SetAbsoluteExpiration(TimeSpan.FromMinutes(15)));
}

builder.Password = token;

[roji]

Seems that I'm still breaking connection limits if I run a user a request that will generate 200+ rest requests async

If you're generating 200 concurrent REST requests (which presumably need to access the database), and your maximum connection is 90, that makes sense, doesn't it? Connection pooling doesn't magically allow you to bypass your limit of concurrent connections.

Regarding the rest, I'm no MySQL expert but at this point it seems you need to post a clear code sample - the discussion has gone long enough, and I think that the details around pooling/auth tokens has been clarified. You seem to be having issues with your application code, and I personally wouldn't know how to help with some very concrete code.

@mguinness that'll work, but you can just as well just cache the complete connection string (including the up-to-date token) directly on the interceptor, along with a timestamp that says when it expires. Then, whenever the interceptor is called, just check the timestamp and regenerate the connection string based on the new token. In other words, I'm not sure there's a need for multiple entries/keys, which is what IMemoryCache is usually about.

[Simonl9l]

@roji et al - I appreciate all the help from what appear to be the big guns - but Im still having issues.

As it happens even with following the instruction laid out collectively above, I still have more connections being attempted on the DB than the pool as configured is supposedly to allow via MaximumPoolSize, so something is missing.

Given a connection from a new DBContext, to confirm again, this will pick an idle connection for the pool if there is one or wait till there is, and per an .EnableRetryOnFailure(10, TimeSpan.FromSeconds(10), null) will retry that 10 times. every 10 seconds, so in time all the connection should be serviced (albeit not an ideal user experiance) - hooray for reactive UI's.

So why do I see 20 or more connection threads when running show processlist, where most of them have a RDS status of 'clean up' the equivalent of plan in regular MySql environments ?

If the fact that I am (currently) generating a new token every connection on the interceptor still messing with the connection string as a whole and thus the pooling key - so I still have pools of one connection?

I have also set ConnectionIdleTimeout to 1 as the lowest value, such that connection are dropped from the pool. however per the docs, its seem these connection are only cleanup with a background task every minute.

I also seem to get the processlist threads "swamped" with connections for unauthenticated user, with command Connect and status reading from net or login what are these? As these connection within to be picked up by the connection pool, but in turn are flooding the maxconnections, and then blocking the pool connections?!

Seem also that AWS RDS also manages wait-timeout differently

Whist I appreciate this thread is getting long, much had been added but all parties getting aligned on the expected behavior, I fell were now getting to understand what the issue is here and how RDS might behave differently from regular MySql, the perhaps Pomelo and the underling SQL libraries are not accounting for?

[roji]

@Simonl9l I'll defer to others for all the MySQL-specific stuff, but you haven't posted a single code sample that may help understand what your application is doing, or help investigate the issue. As always, the best way forward is for you to isolate the problematic behavior in a minimal code sample, and then post that.

Note also that at least for PG, AWS RDS isn't generally a customized version of the database - it's just the standard PG/MySQL, so I'd be surprised if this is an RDS-specific issue. If you put together a minimal repro, you can then execute it against regular MySQL to make sure whether this is indeed RDS-related or not.

[lauxjpn]

My understanding also is that by only supplying it via the interceptor, means it’s NOT included in the pooling key per @lauxjpn confirm a few posts above. I have to say I believe I’m observing that to be correct - is this your understanding?

@Simonl9l If you have connection pooling enabled (which as I understand is the case), than your statement is incorrect. MySqlConnector creates a dedicated connection pool for every distinct connection string.

I stated the same in my answer(s) above:

If you set Pooling=true, than it is correct that MySqlConnector will create one pool per distinct connection string.

(A connection interceptor does no magic. It is just some code that is being called by EF Core, before EF Core calls the DbConnection.Open() method, to give you a simple way to change some connection settings.)

Assuming you call your RDSAuthTokenGenerator.GenerateAuthToken(...) every time before a connection opens (e.g. in your connection interceptor) and assuming that RDSAuthTokenGenerator.GenerateAuthToken(...) generates a new and distinct token on every call, and assuming you use that newly generated token as the password of the connection string for the connection that is about to be opened, than you end up with a dedicated connection pool for every single connection, because every single connection uses a distinct connection string (because of the distinct token).

In your 200 concurrent connections test case, this would result in 200 connection pools, each containing a single connection, effectively circumventing all your connection pool options that you did setup. And since your max_connections setting is probably lower than 200, you could get timeout exceptions again.

In his inquiry above, @bgrainger already warned you about exactly that:

If [RDSAuthTokenGenerator.GenerateAuthToken(...)] creates a new password for each new connection, then you'll end up with one pool per connection (because the password is included in the connection string, and the full connection string is used as the key to identify/distinguish pools). This will defeat any benefits of pooling, and you might as well go back to the simpler solution that avoids it.

Implementing what @mguinness suggested (IMemoryCache), or just caching the token in your connection interceptor (and only retrieving a new one once the old one has expired or is about to expire) as @roji suggested, are valid solutions to this issue.

@Simonl9l Are you sure you don't want to use the approach I recommended multiple times to you, to not use connection pooling at all? It is super simple. You seem way over your head here.

---

Seem also that AWS RDS also manages wait-timeout differently

While this is an interesting difference, it is not relevant to your issues, as you are currently managing your connections through MySqlConnector (and its pool connection string options) and not through your MySQL system variables (like wait_timeout).

Also, this is only a minor difference in most cases.

Whist I appreciate this thread is getting long, much had been added but all parties getting aligned on the expected behavior, I fell were now getting to understand what the issue is here and how RDS might behave differently from regular MySql, the perhaps Pomelo and the underling SQL libraries are not accounting for?

That is not the case. Pomelo and MySqlConnector don't care about this difference in behavior at all.

---

Regarding the rest, I'm no MySQL expert but at this point it seems you need to post a clear code sample - the discussion has gone long enough, and I think that the details around pooling/auth tokens has been clarified. You seem to be having issues with your application code, and I personally wouldn't know how to help with some very concrete code.

[...] but you haven't posted a single code sample that may help understand what your application is doing, or help investigate the issue. As always, the best way forward is for you to isolate the problematic behavior in a minimal code sample, and then post that.

@roji I fully agree.

@Simonl9l If you need further help, post a code sample (preferably a minimal code sample), that reproduces the same issue in your environment. In addition to that, post the related system variables of the RDS MySQL/Aurora server (e.g. max_connections) and the output of the 3 statement SQL script I posted above, when the test fails. The sample needs to contain everything relevant, including your test case (and your non-sensitive connection string settings) you have issues with. Then we can continue to help you get your code in order.

Otherwise we will close this issue, as this is related to your own code and/or environment and your limited understanding of the underlying technologies and concepts involved (and not Pomelo). (It also appears to be more difficult for us to effectively communicate with you than with other members of the community, which makes helping you harder than usual for us).

[rstml]

MySqlConnector v2.0 introduced password callback to solve the problem with auth tokens. Can we add support for this and a way to supply a callback function through Pamelo?

Here's an excerpt from the docs:

For systems that use frequently-updated authentication tokens (such as Amazon Aurora RDS with IAM Authentication), leave this value empty, and set MySqlConnection.ProvidePasswordCallback to a delegate that will provide the password (or authentication token) on demand before calling MySqlConnection.Open. This retains the benefits of connection pooling.

[mguinness]

@rstml Please refer to issue https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1643.