Query cancellation broken with AWS RDS Proxy

[petedishman]

Software versions
MySqlConnector version: 2.3.7
Server type and version: AWS RDS Aurora 8.0.mysql_aurora.3.08.2 + RDS Proxy
.NET version: v8

Describe the bug
When using a query timeout via a CancellationToken queries are correctly timed out when connecting directly to a local MySQL instance, or directly to an RDS Aurora instance, but when connecting via RDS Proxy the query fails to timeout.

Logging shows that RDS Proxy is returning some sort of internal ID for ServerThread so when this gets used in the KILL QUERY x command to cancel the timed out query an error is just returned with 'Unknown thread id: Y' and the query continues.

I've just seen ShouldGetRealServerDetails() and GetRealServerDetailsAsync() in ServerSession.cs which appear to cover this problem, and with a quick bodge to force ShouldGetRealServerDetails() to return true cancellation starts working. The 'correct' thread id is available through SELECT CONNECTION_ID();

Would updating ShouldGetRealServerDetails() to detect RDS Proxy hostnames be an acceptable fix?
They're of the format <custom-proxy-name>.proxy-<random character string>.<aws-region>.rds.amazonaws.com

Alternatively a connection string parameter requesting this behaviour on connection could work?

Expected behavior
MySQL Connector should be able to cancel a running query whilst connected to RDS Proxy

Additional context
Could be considered an RDS Proxy issue rather than a MySqlConnector problem

[petedishman]

#1582 adds simple detection of RDS Proxy along the same lines as Azure Database

[bgrainger]

Given what I'm reading about RDS Proxy at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html, "updating ShouldGetRealServerDetails() to detect RDS Proxy hostnames" sounds like a good fix (if the hostname is always so predictable).

(A connection string option, perhaps with values Auto/On/Off, could be added to give users more explicit control over this, but it doesn't seem necessary yet.)

Finally, RDS Proxy seems like it primarily exists to create a connection pool for a single DB server. MySqlConnector already implements connection pooling, so RDS Proxy might have limited utility here. Would your application work well without it?

[petedishman]

We have multiple instances of multiple services connecting to the same RDS instance so had issues with maxing out connection limits when just using the built in connection pooling.
RDS proxy helps us manage that.

It also speeds up failover between the RDS instances as I believe it's not just reliant on DNS for this to happen.

I was considering moving one of our services away from it and connecting directly as part of this problem until I found this particular piece of code.