6.4.9. SQL Routing

Auto Read/Write Splitting No
Primary Selection Manually, by SQL comments
Replica Selection Manually, by SQL comments
QoS Compatibility Supported
SmartScale Compatibility Yes
Direct Compatibility Yes

With SQL-based routing, the redirection of queries and operations through the Connector is controlled by hints on the QoS provided in the comments of individual statements. Note that this is explicit routing using SQL comments, not the automated read/write splitting supported by Direct or SmartScale routing.

Unless otherwise specified, statements will go to the current Primary to be executed, or whatever Replica is selected by the read-write splitting configuration, if enabled. To specify that a statement can be executed on the Replica, place a comment before the statement:

/* TUNGSTEN USE qos=RO_RELAXED */ SELECT * FROM TABLENAME

This style of comment indicates to the connector that the specific query that follows should go to a Replica. If unavailable, the query may still be executed on the Primary.

-- TUNGSTEN USE qos=RO_RELAXED

This style of comment indicates to the connector that all queries that follow should go to a Replica. If unavailable, any query may still be executed on the Primary.

Warning

If you force the Connector to send traffic to a Replica using qos=RO_RELAXED, then any write operations that follow will also go to the Replica until you tell the Connector to go back to the Primary by indicating qos=RW_STRICT. The application is fully responsible for where the traffic is routed to. if care is not taken, the application could send writes to a Replica this way which is unacceptable from a clustering perspective. All writes must go to the Primary or they will be lost to a non-authoritative node, and may corrupt the data badly.

The below forces all following queries to go to the Primary directly, effectively "turning off" reads from the Replica.

-- TUNGSTEN USE qos=RW_STRICT

Important

Please note that employing the -- style will override any /* */ comments.

6.4.9.1. Enabling SQL Routing

To enable SQL routing, use the following operations with tpm:

shell> tpm configure alpha
    --property=selective.rwsplitting=true

6.4.9.2. Limitations of SQL Routing

  • Read/write splitting must be handled entirely by the client application using the comments to specify which statements are Replica safe. Unless applications explicit make the decision to write and read to the hosts using the comment system, operations may go to the wrong hosts.

  • Prepared statements must be executed against the Primary.

  • When testing the operation of the read/write splitting through the mysql client, ensure that command-line client is called using the -c option to ensure that comments are preserved:

    shell> mysql -c -h host -u tungsten -ppassword -P3306 test