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 for a cluster endpoint or a primary endpoint whose read/write mode 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 Endpoints topic.

Usage notes

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 the /*FORCE_MASTER*/ or /*FORCE_SLAVE*/ hint before 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. The /*FORCE_MASTER*/ hint takes effect only on endpoints whose read/write mode is set to Read/Write (Automatic Read/Write Splitting). If you add the /*FORCE_MASTER*/ hint before 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 the /*force_node='<Node ID>'*/ hint before 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 force hint server node is not found, please check. error message is returned.

• You can add the /*force_proxy_internal*/set force_node = '<Node ID>' hint before 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.

• You can add the /*force_all*/ hint before an SQL statement to forcibly broadcast the statement to all nodes. The returned results vary based on the query scenarios.

  Note

  PolarProxy must be 2.8.36 or later. For information about how to view and update the PolarProxy version, see Minor version update.

  • If the statement is used to query specific system tables, such as information_schema.processlist, information_schema.innodb_trx, performance_schema.threads, performance_schema.metadata_locks, and sys.schema_table_lock_waits, the merged result of all nodes is returned.

    For example, if you execute the /*force_all*/SELECT * FROM information_schema.processlist statement, PolarProxy broadcasts the statement to all nodes and returns the merged result of all nodes. If you execute the SELECT * FROM information_schema.processlist statement without adding the /*force_all*/ hint, the statement is routed to a random node.

  • If the statement is used to query other databases and tables or used in other scenarios, only the results of the primary database are returned.

• If you enable automatic request distribution among row store and column store nodes, you can add the /*FORCE_IMIC_NODES*/ hint 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 before 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 be combined with SQL statements that change environment variables. For example, if you execute the /*FORCE_SLAVE*/ SET NAMES utf8; statement, errors may occur.