Rerouting

After rerouting is enabled, when OceanBase Database Proxy (ODP) forwards a request to an OBServer node for the first time, if the OBServer node determines that a remote plan needs to be generated for the request, it will return a packet to notify ODP of the location of the replica where the statement can be executed. Then, ODP resends the request based on the information provided in the received packet. If remote plans are generated for many read/write requests, you can enable rerouting to effectively reduce the network overhead caused by remote plans.

You can execute the following statement to set the ODP parameter ENABLE_REROUTE to True to enable rerouting:

ALTER PROXYCONFIG SET ENABLE_REROUTE = True;

Rerouting is supported only in the following cases:

• The first statement of a transaction

• Distributed transaction routing

• Rerouting has never been performed.

• Requests whose length is not greater than the value of the ODP parameter request_buffer_length

• The primary zone is not specified.

• The routing mode is not IP address-based routing.

• The cached server is disabled.

Example

This example reroutes a request that is inaccurately routed.

1. Enable rerouting.

   ALTER PROXYCONFIG SET proxy_primary_zone_name='';
   ALTER PROXYCONFIG SET enable_reroute = True;
   ALTER PROXYCONFIG SET enable_cached_server = False;
   

2. Execute the following statements to create a table and execute a statement for which routing calculation cannot be correctly performed:

   • Create a table

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

   • Execute an SQL statement

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

3. View the routing process in the diagnostic logs.

   [2023-08-21 17:05:38.148301] [70901][Y0-00007F9A7B45A4E0] [ROUTE]((*route_diagnosis=
   Trans Current Query:"SELECT * FROM t_reroute WHERE C1=substr('abc',1,2)"
   
   Route Prompts
   > ROUTE_INFO
     [INFO] Will route to partition server or routed by route policy
   > PARTITION_ID_CALC_DONE
     [WARN] Fail to calculate first part idx
   
   Route Plan
   > SQL_PARSE:{cmd:"COM_QUERY", table:"t_reroute"}
   > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
   > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
     > TABLE_ENTRY_LOOKUP_DONE:{table:"t_reroute", table_id:500030, partition_num:8, table_type:"USER TABLE", entry_from_remote:false}
     > PARTITION_ID_CALC_START:{}
       > EXPR_PARSE:{col_val:"C1=substr"}
       > RESOLVE_EXPR:{part_range:"[ab ; ab]"}
         > RESOLVE_TOKEN:{token_type:"TOKEN_FUNC", resolve:{"VARCHAR":"ab", collation:"utf8mb4_general_ci"}, token:"substr", expr_type:"OB_PROXY_EXPR_TYPE_FUNC_SUBSTR"}
       > CALC_PARTITION_ID:{error:-4226, part_description:"partition by hash(INT(binary)) partitions 8"}
     > PARTITION_ID_CALC_DONE:{error:-4226, partition_id:-1, level:1, partitions:"(p-1)"}
   > ROUTE_POLICY:{replica:"10.10.10.3:50111", idc_type:"SAME_IDC", zone_type:"ReadWrite", role:"FOLLOWER", type:"FULL", chosen_route_type:"ROUTE_TYPE_NONPARTITION_UNMERGE_LOCAL", route_policy:"MERGE_IDC_ORDER_OPTIMIZED", trans_consistency:"STRONG", session_consistency:"STRONG", proxy_idc_name:"zue"}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.3:50111"}
   > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"}
   > RETRY:{attempts:2, retry_status:"FOUND_EXISTING_ADDR", retry_type:"REROUTE", retry_addr:{10.10.10.2:50110}}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.2:50110"}
   > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"}
   )
   

   Based on the > RETRY:{attempts:2, retry_status:"FOUND_EXISTING_ADDR", retry_type:"REROUTE", retry_addr:{10.10.10.2:50110}} information in the return result, after routing calculation fails and the OBServer node generates a remote plan for the request, ODP performs tries to reroute the request.