Forcible routing

Forcible routing cannot be controlled by users. OceanBase Database Proxy (ODP) decides whether to perform forcible routing. Forcible routing is performed in the following cases:

• Non-distributed transaction routing: Statements in a transaction are force routed to the OBServer node that starts the transaction.

• For session-level temporary table-based routing, a query is force routed to the OBServer node where the temporary table is queried for the first time.

• Session reuse-based routing: If routing calculation fails and the enable_cached_server parameter is set to True, ODP force routes a query to the OBServer node where the session resides the last time.

• Cursor/Piece-based routing: When the client uses cursors/pieces to obtain/upload data, all requests are force routed to the same OBServer node.

The OBServer node selected for forcible routing is the one where the last server session or an existing server session resides.

Example 1: Session-level forcible routing for a query to a temporary table

This example takes the Oracle mode as an example.

1. Create a temporary table in a session.

   obclient [test]> CREATE GLOBAL TEMPORARY TABLE T0(C1 INT);
   

2. Query the temporary table in the session.

   obclient [test]> SELECT * FROM T0 WHERE C1=123456;
   

3. Query the temporary table in the session again. Run the EXPLAIN ROUTE command to view the routing process.

   obclient [test]> EXPLAIN ROUTE INSERT INTO T0 VALUES(1)\G
   

   The following return result shows that ODP routes the query statement to the OBServer node that receives the first temporary table query.

   *************************** 1. row ***************************
   Route Plan:
   Trans Current Query:"EXPLAIN ROUTE INSERT INTO T0 values(1)"
   
   Route Prompts
   -----------------
   > ROUTE_INFO
     [INFO] Will route to last connected server
     [INFO] Query for session temporary table
   
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"}
   > ROUTE_INFO:{route_info_type:"USE_LAST_SESSION", svr_addr:"10.10.10.3:50111", trans_specified:true}
   

Example 2: Forcible routing for a statement in a non-distributed transaction

1. Disable distributed transaction routing.

   ALTER PROXYCONFIG SET enable_transaction_internal_routing = false;
   

   Note

   Only the root@proxysys and root@sys accounts have the privilege to execute the ALTER PROXYCONFIG statement.

2. Create a table and start a regular transaction.

   obclient [test]> CREATE TABLE T0(C1 INT)  PARTITION BY HASH(C1) PARTITIONS 8;
   obclient [test]> BEGIN;
   obclient [test]> SELECT * FROM T0 WHERE C1=0;
   

3. Run the EXPLAIN ROUTE command to view the routing process.

   obclient [test]> EXPLAIN ROUTE INSERT INTO T0 VALUES(4)\G
   

   The following return result shows that ODP routes the subsequent statement in the transaction to the OBServer node that starts the transaction.

   *************************** 1. row ***************************
   Route Plan:
   Trans First Query:"SELECT * FROM T0 WHERE C1=0"
   Trans Current Query:"EXPLAIN ROUTE INSERT INTO T0 VALUES(4)"
   
   Route Prompts
   -----------------
   > ROUTE_INFO
     [INFO] Will route to last connected server since current query in transaction
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"}
   > ROUTE_INFO:{route_info_type:"USE_LAST_SESSION", svr_addr:"10.10.10.1:50109", in_transaction:true}
   

Example 3: Routing calculation fails and the enable_cached_server statement is set to True

1. Enable the cache server and create a table.

   ALTER PROXYCONFIG SET enable_cached_server = true;
   

   Note

   Only the root@proxysys and root@sys accounts have the privilege to execute the ALTER PROXYCONFIG statement.

2. Create a table

   obclient [test]> CREATE TABLE T0(C1 INT)  PARTITION BY HASH(C1) PARTITIONS 8;
   

3. Execute a statement for which routing calculation cannot be performed.

   obclient [test]> SELECT * FROM T0 WHERE C1=SUBSTR(C1, 2);
   

4. Run the EXPLAIN ROUTE command to view the routing process.

   obclient [test]> EXPLAIN ROUTE SELECT * FROM T0 WHERE C1=substr(C1, 2)\G
   

   The following return result shows that ODP routes the statement to the session of a cache that has established a connection.

   -----------------
   > ROUTE_INFO
     [INFO] Will route to partition server or routed by route policy
   > RESOLVE_TOKEN
     [WARN] Not support expr func(OB_PROXY_EXPR_TYPE_NONE)
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"}
   > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
   > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
     > TABLE_ENTRY_LOOKUP_DONE:{table:"T0", table_id:500006, partition_num:8, table_type:"USER TABLE", entry_from_remote:false}
     > PARTITION_ID_CALC_START:{}
       > EXPR_PARSE:{col_val:"C1=substr"}
       > RESOLVE_EXPR:{error:-4002, part_range:"(MIN ; MAX)always true"}
         > RESOLVE_TOKEN:{error:-4002, token_type:"TOKEN_FUNC", token:"substr"}
     > PARTITION_ID_CALC_DONE:{error:-4002, partition_id:-1, level:1, partitions:"(p-1)"}
   > ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"10.10.10.2:50110"}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.2:50110"}
   

   Based on the information ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"10.10.10.2:50110"} in the return result, after routing fails, ODP obtains a cache server for routing.