| 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 TABLENAMEThis 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_RELAXEDThis 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.
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
Please note that employing the -- style will
override any /* */ comments.
To enable SQL routing, use the following operations with tpm:
shell> tpm configure alpha
--property=selective.rwsplitting=trueRead/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