SQL hints let you override the default routing decisions of a cluster endpoint configured with Read/Write (Automatic Read/Write Splitting). Hints carry the highest routing priority and bypass both consistency levels and transaction splitting. Evaluate the impact before using them in production.
Limits
Hints work only on cluster endpoints whose read/write mode is set to Read/Write (Automatic Read/Write Splitting). They are not supported on cluster endpoints or primary endpoints set to Read-only.
For details on endpoint read/write modes, see the "Read/write modes for cluster endpoints" section in Endpoints.
Available hints
| Hint | Routes to | Typical use |
|---|---|---|
/*FORCE_MASTER*/ |
Primary node | Force a read to run on the primary node |
/*FORCE_SLAVE*/ |
Read-only node | Force a statement to a read-only node on a read/write endpoint |
/*force_node='<Node ID>'*/ |
Specific node (single statement) | Debug or inspect a specific node |
/*force_proxy_internal*/set force_node = '<Node ID>' |
Specific node (all subsequent statements) | Pin a session to a node — use with caution |
/*force_all*/ |
All nodes | Query system tables across the entire cluster |
/*FORCE_IMCI_NODES*/ |
Column store nodes | Force analytical queries to In-Memory Column Index (IMCI) nodes |
Hints are case-insensitive and must be placed before the SQL statement.
If you run hints from the MySQL command-line client, pass the-c(or--comments) flag. Without it, the client strips the hint before sending the query to the server. See MySQL client options.
Usage
/*FORCE_MASTER*/ — route to the primary node
Add this hint before any SQL statement to send it to the primary node, regardless of the default routing logic.
/*FORCE_MASTER*/ SELECT * FROM test;Without the hint, SELECT * FROM test is routed to a read-only node. With the hint, it runs on the primary node.
This hint has no effect on endpoints set to Read-only — the statement is not redirected to the primary node.
/*FORCE_SLAVE*/ — route to a read-only node
Add this hint to explicitly send a statement to a read-only node.
/*FORCE_SLAVE*/ SELECT * FROM orders WHERE status = 'shipped';Do not combine hints with statements that change session variables. For example, /*FORCE_SLAVE*/ SET NAMES utf8; may cause errors.
/*force_node='<Node ID>'*/ — target a specific node (single statement)
Add this hint to run a single SQL statement on the node you specify.
/*force_node='pi-bpxxxxxxxx'*/ SHOW PROCESSLIST;If the specified node is unavailable, the following error is returned:
force hint server node is not found, please check./*force_proxy_internal*/set force_node — pin a session to a specific node
Run this statement to route all subsequent SQL statements in the session to a specific node.
/*force_proxy_internal*/set force_node = 'pi-bpxxxxxxxx';If the node fails, the following error is returned:
set force node 'rr-bpxxxxx' is not found, please check.Avoid using /*force_proxy_internal*/ unless you have a specific reason to pin the session. It disables read/write splitting for all subsequent statements in the connection.
/*force_all*/ — broadcast to all nodes
Add this hint to broadcast a statement to every node in the cluster.
/*force_all*/ SELECT * FROM information_schema.processlist;/*force_all*/ requires PolarProxy 2.8.36 or later. To check and update your PolarProxy version, see Minor version update.The returned results depend on the query:
-
System tables (
information_schema.processlist,information_schema.innodb_trx,performance_schema.threads,performance_schema.metadata_locks,sys.schema_table_lock_waits): PolarProxy broadcasts the statement to all nodes and returns the merged result. -
All other tables and scenarios: Only the results of the primary database are returned.
Without the hint, SELECT * FROM information_schema.processlist is routed to a random node.
/*FORCE_IMCI_NODES*/ — route to column store nodes
If you have enabled automatic request distribution between row store and column store nodes, add this hint to force a query to run on IMCI (In-Memory Column Index) column store nodes.
/*FORCE_IMCI_NODES*/ SELECT * FROM large_analytics_table;For setup details, see Automatic request distribution among row store and column store nodes.