Features and Limitations

Implementation Principle

The text describes the architecture of a Read-Write separation middleware, composed of multiple high-performance proxy nodes and Surfercloud's distributed load balancing product, ULB. Clients can customize the distribution of read requests, and the proxy nodes distribute these requests based on the client's configuration. The middleware's approach to handling business requests is straightforward and is based on three basic principles.

For a more detailed explanation and full description of these principles, you would need to refer to the relevant technical documentation or source material.

1. Identifying 'Select' SQL statements from business requests, as only these are considered for Read-Write separation.
2. Directing 'Select' SQL within a transaction to the primary node, while those outside a transaction are distributed to either the primary or secondary nodes based on the read request distribution strategy.
3. Broadcasting certain statements, like 'Use database' or 'Set Session' variables, to ensure data consistency across nodes. If broadcasting fails, the client connection is interrupted.

And adjustments for certain special cases:

1. SQL statements involving locks, such as "Select For Update" or "Select Lock," are directed to the primary node.
2. "Set" statements are categorized into three types: Session, Global, and User. Statements setting Session and User variables are broadcasted, while due to data consistency concerns, "Set Global" statements are only sent to the primary node. Consequently, Select statements containing global variables are also directed to the primary node.

Features and Limitations

1.MySQL Protocol Limitations

1. Lack of support for SSL encryption.
2. Temporary absence of support for compressed protocols.
3. Current unavailability of XA transactions.

2.SQL Limitations

1. Support for 'savepoint' statements (directed to the primary node) but not 'rollback to savepoint'.

2. No support for XA transaction commands.

3. 'Lock Tables/Unlock Tables' statements are sent to the primary node, without affecting the proxy layer or secondary nodes.

4. Stored procedures and subsequent 'Select' statements are routed to the primary node. call udb\_test('000001',@pp,@qq); select @pp,@qq; select \* from t1;

5. Commands like 'show processlists', 'Show master/slave status', 'kill query', 'COM_PROCESS_INFO', and 'COM_STATISTICS' are currently only forwarded to the primary node, with more system management commands under development.

6. Protocols 'COM_TABLE_DUMP' and 'COM_CHANGE_USER' are not supported.

7. Following the execution of Multi-Statement SQL commands, all subsequent requests in the current connection are routed to the primary node, requiring reconnection to restore Read-Write separation.

Special Note on Set Statements

1. Set Session' and 'Set User' variable statements are broadcasted to both primary and secondary nodes. If broadcasting fails, the proxy disconnects from the client to avoid data inconsistency. 'Set global' variable statements are only sent to the primary node, as are subsequent 'Select' statements containing global variables.

2. A single 'Set' statement cannot simultaneously include global and session/user variables.

3. The system does not support 'set sql_mode' values that are expressions.

4. The syntax for setting multiple variables in one 'Set' statement is not supported.

5. The system currently does not support MySQL 8.0's 'caching_sha2_password' encryption algorithm. If a client uses this algorithm, the proxy coordinates to use 'mysql_native_password' instead.

Scenarios Where Read-Write Separation is Not Recommended

1. All business SQL statements are transactional (all SQL statements are included within a transaction). Since transactions can only be routed to the master node, in this scenario, UDB Read-Write separation cannot effectively separate read requests.

2. The business extensively uses stored procedures. Since stored procedures can only be routed to the master node, in this scenario, UDB Read-Write separation cannot effectively separate read requests.