PolarDB:Hints

Limits

You can use hints only after you set the read/write mode to Read/Write (Automatic Read/Write Splitting) for a cluster endpoint. Hints are not supported when the read/write mode of a cluster endpoint or a primary endpoint is set to Read-only. For more information about the read/write mode of a cluster endpoint, see the "Read/write modes for cluster endpoints" section of the Cluster endpoints and primary endpoints topic.

Precautions

Hints have the highest routing priority and are not limited by consistency levels or transaction splitting. Before you use hints, perform an evaluation.

Usage

• You can add /*FORCE_MASTER*/ or /*FORCE_SLAVE*/ to an SQL statement to forcibly specify the routing direction of the SQL statement.

  For example, assume that the SELECT * FROM test statement is routed to a read-only node. If the SQL statement is changed to /*FORCE_MASTER*/ SELECT * FROM test, the SQL statement is routed to the primary node. Note that /*FORCE_MASTER*/ takes effect only on endpoints whose read/write mode is set to read/write splitting. If /*FORCE_MASTER*/ is added to an SQL statement for an endpoint whose read/write mode is set to read-only, the SQL statement is not routed to the primary node.

• You can add /*force_node='<Node ID>'*/ to an SQL statement to forcibly specify a node to execute the SQL statement.

  For example, /*force_node='pi-bpxxxxxxxx'*/ show processlist specifies that the show processlist statement is executed on a node named pi-bpxxxxxxxx. If the node is unavailable, the error message force hint server node is not found, please check. is returned.

• You can add /*force_proxy_internal*/set force_node = '<Node ID>' to an SQL statement to forcibly specify a node to execute all SQL statements.

  For example, if you execute the /*force_proxy_internal*/set force_node = 'pi-bpxxxxxxxx' statement, all subsequent SQL statements are routed to a node named pi-bpxxxxxxxx. If the node fails, the set force node 'rr-bpxxxxx' is not found, please check. error message is returned.

• If you enable automatic request distribution among row store and column store nodes, you can add a HINT /*FORCE_IMIC_NODES*/ to forcibly route requests to column store nodes. For more information, see Automatic request distribution among row store and column store nodes.

  Note

  • Hints are case-insensitive and must be placed at the beginning of an SQL statement.

  • If you want to execute the preceding SQL statement that contains the hint on the official command line of MySQL, add the -c parameter to the SQL statement. Otherwise, the hint becomes invalid because the official command line of MySQL filters out the hint. For more information, see mysql Client Options.

  • We recommend that you do not use /*force_proxy_internal*/ in SQL statements. Otherwise, all subsequent SQL statements are routed to the specified node and the read/write splitting feature becomes invalid.

  • Hints cannot contain SQL statements that change environment variables. For example, if you use /*FORCE_SLAVE*/ set names utf8;, errors may occur.