Background: So, we got a setup with our backend running in AWS Lambda which scales horizontally for each parallell request, potentially up to 1000 instances at the same time. To not overwhelm our database on peak load we use a AWS RDS Proxy. Due to this we also minimise the number of connections for each lambda instance.
But we’re having an issue still with many connections when we get peaks and the connections are not returned to the pool. Activating ´enhanced logging´ on the RDS proxy gave us:
[WARN] [proxyEndpoint=default] [clientConnection=870930275] The client session was pinned to the database connection [dbConnection=3915647555] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: A protocol-level prepared statement was detected.
Pinning is suboptimal when using an RDS proxy (see https://medium.com/@andre.decamargo/rds-proxy-and-connection-pinning-d26efcadb53c)
We do not really use prepared statements for reusing statements but rather passing parameters as ? for preventing sql-injections.
Question: Is there a possibility to enable binary mode in mysql like there is in postgressql? See https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/
Or is there any other solution to prevent pinned connections when using AWS RDS Proxy with MySql and prepared statements? (Using Aurora MySQL with a read replica in a different region than the master if that should be relevant.)
Edit: Using golang as language. We're not explicitly using prepared statments but golang uses prepared statements in the background for a query like:
func createUser(user User, db *sqlx.DB) (*int64, error) {
res, err := db.ExecContext(
ctx,
`INSERT INTO users (
email,
name)
VALUES (?, ?)`,
user.Email,
user.Name,
)
if err != nil {
return nil, err
}
id, err := res.LastInsertId()
if err != nil {
return nil, err
}
return &id, nil
}