Features and Limitations

Working Principle

As shown in the figure, a read-write separation middleware is composed of multiple high-performance Proxy nodes and SurferCloud distributed load balancing product ULB.

Customers can configure the distribution method of read requests in a customized manner (for detailed configuration methods, see the following), and Proxy nodes distribute read requests according to customer configurations.

The processing method of read-write separation middleware for business requests is very simple, and there are three basic principles:

1. Identify Select SQL from business requests. Only Select SQL is considered for read-write separation;

2. If the Select SQL is in a transaction, send this Select SQL to the master node. If the Select SQL is not in the transaction, according to the read request distribution strategy, send this Select SQL to the master node or the slave node.

3. For some statements that must be broadcasted, such as Use database, Set Session variables, etc., the middleware broadcasts them. If all broadcasts are not successful, the client connection is interrupted to strictly guarantee the data consistency of each node.

And corrections against some special situations:

1. Select statements involving locks, such as Select For Update, Select Lock, etc., will be sent to the master node.

2. Divide the variables in the Set statement into three types: Session, Global, User. set Session, Set User variable statements will be broadcasted; considering the data consistency problem between nodes, Set Global will only be sent to the master node. Subsequent Select statements containing global variables will also only be sent to the master node.

Functional restrictions

1.MySQL Protocol Restrictions

1.1 SSL encryption is not supported

1.2 Compression protocol is not yet supported

1.3 XA transactions are not yet supported

2.SQL restrictions

2.1 Support savepoint statements (this statement will be sent to the master node), but does not support rollback to savepoint yet

2.2 XA transaction commands are not yet supported

2.3 Lock Tables/Unlock Tables will be sent to the master node, and there will be no lock status at the Proxy layer. Therefore, the lock generated by Lock Tables will not affect the slave node.

2.4 Stored procedures, as well as Select statements after stored procedures, are all sent to the master node. such as:

call udb\_test('000001', @pp, @qq); select @pp, @qq; select \* from t1;

The above two Select statements will be sent to the master node.

2.5 show processlists, Show master/slave status, kill query, COM_PROCESS_INFO, COM_STATISTICS commands, currently will only forward to the master node, system management commands for middleware and database management scenarios, with more rich system management commands being developed.

2.6 COM_TABLE_DUMP and COM_CHANGE_USER protocols are not yet supported.

2.7 If a Multi-Statement (opens in a new tab) statement is executed, all subsequent requests of the current connection will be routed to the master node. The read-write separation can be restored by disconnecting the current connection and reconnecting.

3. Special Instructions for Set Statements

3.1 Set Session, Set User variable statements will be broadcasted to the master node and slave node. If the broadcast fails, Proxy will disconnect the connection with the client, thereby revoking the data inconsistency caused by the broadcast failure; considering the data consistency between nodes, Set global variable statements will only be sent to the master node. The subsequent Select statements containing global variables will also only be sent to the master node.

3.2 It is not allowed to have Global variables and Session, User variables appear in a Set statement at the same time.

3.3 The value of set sql_mode in expressions (e.g set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY,',''))) is not supported.

3.4 Currently does not support the syntax of setting multiple variables, such as set sql_mode='XXX', sql_safe_updates='XXX', ....

3.5 The encryption algorithm of mysql8.0 caching_sha2_password is not yet supported. If the client uses the caching_sha2_password encryption algorithm, the proxy will coordinate the client and server to use mysql_native_password.

4. Scenarios Not Recommended for Using Read-Write Separation

a. All SQLs of the business are transactional SQLs (all SQLs are included in transactions). Since transactions can only be routed to the master node, the UDB read-write separation cannot play a role in separating read requests in this scenario.

b. The business uses a lot of stored procedures. Since stored procedures can only be routed to the master node, UDB read-write separation cannot separate read requests in this scenario.