Suggestions on Using Database Proxy

Function Constraints

• If read/write splitting is enabled and you delete a primary RDS for MySQL instance, its read replicas are also deleted and read/write splitting is disabled.
• Read/write splitting does not support the caching_sha2_password identity authentication plugin for RDS for MySQL 8.0.
• After read/write splitting is enabled, the database ports and floating IP addresses of both the primary instance and read replicas cannot be changed.
• Read/write splitting does not support compression protocols.
• Read/write splitting does not support the isolation level READ UNCOMMITTED.
• If multi-statements are executed, all subsequent requests will be routed to the primary instance by default. To restore read/write splitting, disconnect the connection between your application and the read/write splitting address and establish a connection again. Multiple multi-statement processing modes are supported. For details, see Configuring Multi-Statement Processing Modes.
• If operations related to temporary tables are performed, all subsequent requests of the current connection will be routed to the primary instance by default. To restore read/write splitting, disconnect the connection and reestablish a connection.
• If the HANDLER statement is executed, all subsequent requests will be routed to the primary instance by default. To restore read/write splitting, disconnect the connection and reestablish a connection.
• When the read/write splitting address is used, all transaction requests are routed to the primary instance (you can use the transaction splitting feature to route read requests prior to write operations in a transaction to read replicas). The non-transaction read consistency is not ensured. To ensure read consistency, encapsulate the read requests into a transaction.
• When the read/write splitting address is used, the LAST_INSERT_ID() function can be used only in transactions.
• When user-defined variables are used, statements containing user-defined variables are routed to the primary instance.
• When a database proxy is used, the size of a concatenate SQL statement cannot exceed 100 MB to prevent statement parsing from consuming too many resources.
• When a .NET client is used to connect to database proxies, the MySQL.Data driver version of the client must be 8.0.19 or later because earlier driver versions may be incompatible with database proxies.
• To use transaction splitting, you need to upgrade the database proxy to the latest version.
• Database proxies do not support the SQL mode parameter PAD_CHAR_TO_FULL_LENGTH.

Syntax Constraints

Read/write splitting routes frontend requests to backend instance nodes by the configured weights.

Therefore, some SQL statements may have different results when being executed multiple times.

• If you connect to a DB instance through a proxy and run show processlist, the result returned displays only the running threads on the proxy node where show processlist is executed, so it is different from that returned when you directly connect to the DB instance.
• If a proxy node is abnormal, and you connect to the proxy through the read/write splitting address and run show processlist or kill, the command execution may be prolonged or freezes, but your services are not affected.
• If you run show processlist on a proxy and this proxy has a node deleted, the running threads on this node may be returned.
• If you run kill on a proxy, errors such as timeout may be reported. In this case, you can run show processlist again to check whether the thread is killed successfully.
• Requests that are routed by a database proxy can be killed only through the proxy.
• When the read/write splitting address is used, the show errors and show warnings commands are not supported.
• When the read/write splitting address is used, if stored procedures and functions depend on user variables (@variable), the execution result may be incorrect.