We have recently deployed horizontally scaled wordpress instances on AWS EC2. Currently we have just 1 MySQL instance. To handle surge of connections we introduced RDS Proxy. While this has been largely successful, users are intermittently seeing "Error Establishing a Database Connection" error. this error goes off when page is refreshed. In the RDS proxy logs we noticed lots of these warnings:
2023-03-16T04:42:45.493Z [WARN] [proxyEndpoint=default] [clientConnection=1550110989]
The client session was pinned to the database connection [dbConnection=1847974180] for the remainder of the session.
The proxy can't reuse this connection until the session ends.
Reason: SQL changed session settings that the proxy doesn't track.
Consider moving session configuration to the proxy's initialization query. Digest:
"SET @@`session` . `time_zone` = ? , @@`session` . `sql_mode` = REPLACE ( REPLACE ( REPLACE ( @@`sql_mode` , ?, ... ) , ?, ... ) , ?, ... ) ,
@@`session` . `wait_timeout` = `GREATEST` ( CONVERT ( COALESCE ( @@`wait_timeout` , ? ) , SIGNED ) , ? ) ,
NAMES `utf8mb4` COLLATE `utf8mb4_unicode_520_ci` "
We are guessing "connection pinning" may be leading to intermittent errors. Wanted to know how we can avoid this when we connect WordPress to RDS proxy? Are there any plugins or specific code in database files which can help us avoid this?
