Obtain diagnostic information

This topic describes two methods for obtaining diagnostic information.

Obtain diagnostic information by running a command

You can run the explain route <your_sql>; command to obtain the routing status information of an SQL statement. If the value of route_diagnosis_level is not 0, this command will return detailed diagnostic information. The statement specified by <your_sql> will undergo the forwarding process in OceanBase Database Proxy (ODP) without being actually forwarded to an OBServer node.

route_diagnosis_level is a global parameter that controls how detailed the routing status information is. The value is an integer. The default value is 2, which indicates that the output information can cover level-2 diagnostic points. For more information about diagnostic points, see Overview.

The value range of the route_diagnosis_level parameter is [0-4]. A larger value indicates more detailed status information. The value 0 specifies to disable this module. When this module is disabled, it does not occupy the memory of ODP or affect the performance of ODP.

This command does not apply to the following statements:

• COM_STMT_PREPARE

• COM_STMT_PREPARE_EXECUTE

• COM_STMT_CLOSE

• COM_STMT_RESET

• Text PREPARE (Syntax: PREPARE statement_name FROM preparable_SQL_statement;)

• Text DROP PREPARE (Syntax:{DEALLOCATE | DROP} PREPARE stmt_name;)

• Internal statements of ODP

Example

Execute the SELECT * FROM test.list_sub_parts_my_01 WHERE c3='1999-09-09' AND c1=mod(1999,1000) AND c2='tiger0' statement to query a table that does not exist.

obclient> EXPLAIN ROUTE SELECT * FROM test.list_sub_parts_my_01 WHERE c3='1999-09-09' AND c1=mod(1999,1000) AND c2='tiger0'\G

The output is as follows:

*************************** 1. row ***************************
Route Plan:
Trans First Query:"SELECT * FROM test.list_sub_parts_my_01"
Trans Current Query:"EXPLAIN ROUTE SELECT * FROM test.list_sub_parts_my_01 WHERE c3='1999-09-09' AND c1=mod(1999,1000) AND c2='tiger0'"
Route Prompts
-----------------
> ROUTE_INFO
  [INFO] Will do table partition location lookup to decide which OBServer to route to
> TABLE_ENTRY_LOOKUP_DONE
  [INFO] No available entry because table entry lookup failed
> ROUTE_INFO
  [INFO] Will route to cached connected server(10.10.10.1:4001)

Route Plan
-----------------
> SQL_PARSE:{cmd:"COM_QUERY", table:"list_sub_parts_my_01"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP", in_transaction:true}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
  > TABLE_ENTRY_LOOKUP_START:{}
    > FETCH_TABLE_RELATED_DATA:{table_entry:"partition information does not exist"}
  > TABLE_ENTRY_LOOKUP_DONE:{is_lookup_succ:true, entry_from_remote:false}
> ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"10.10.10.1:4001", in_transaction:true}
> CONGESTION_CONTROL:{svr_addr:"10.10.10.1:4001"}

The result set is described as follows:

• Trans First Query: the first statement in the transaction.

• Trans Current Query: the current statement in the transaction.

• Route Prompts: the description of steps in the routing process. Two types of prompts exist: [INFO] and [WARN].

  • [INFO]: some information returned during normal routing to help you understand the routing process. In this example, [INFO] information is returned in the ROUTE_INFO section. The information indicates that ODB routes the request to an OBServer node based on the partition location.

  • [WARN]: information returned when an exception occurs in a step of the routing process.

• Route Plan: the forwarding process in ODP. SQL_PARSE, ROUTE_INFO, and LOCATION_CACHE_LOOKUP are diagnostic points. Diagnostic points can be at the same level or in hierarchical relationships and are displayed in a tree structure. For more information, see Overview.

The FETCH_TABLE_RELATED_DATA:{table_entry:"partition information does not exist"} information in the Route Plan section indicates that ODP queries the partition information about this table but no partition information exists.

In most cases, you can locate an issue based on the information returned in the Route Prompts and Route Plan sections.

Obtain diagnostic information from diagnostic logs

If an SQL statement meets any of the following conditions, ODP will record the actual routing process in the obproxy_diagnosis.log file.

• The statement is not an EXPLAIN ROUTE executable_sql; statement.

• No partition is hit (is_partition_hit = false) or the value of route_diagnosis_level is 4.

The log level is WARN or TRACE, depending on the scenario of the current statement.

• WARN logs are recorded in the following cases:

  • The current statement belongs to a distributed transaction.

  • The current statement is the first statement of a transaction.

• TRACE logs are recorded in the following cases:

  • The current statement belongs to a regular transaction.

  • The table name is left empty in the statement.

  • The length of the statement exceeds the value of `request_buffer_length.

  • Partition key calculation fails.

The following command words are supported in diagnostic logs:

• COM_QUERY

• COM_STMT_PREPARE_EXECUTE

• COM_STMT_PREPARE

• COM_STMT_SEND_PIECE_DATA

• COM_STMT_GET_PIECE_DATA

• COM_STMT_FETCH

• COM_STMT_SEND_LONG_DATA

• Non-internal statements of ODP

Example

In the obproxy_diagnosis.log file, find the target row by keyword and replace '/n' with '\n' to obtain the diagnostic process in a tree structure.

The command is grep "some_key_word" obproxy_diagnosis.log | sed "s/\/n/\n/g". Here is an example:

$ grep "2023-08-17 16:56:46.521180" obproxy_diagnosis.log | sed "s/\/n/\n/g"

The output is as follows:

[2023-08-17 16:56:46.521180] [31792][Y0-00007F38DAAF34E0] [ROUTE]((*route_diagnosis=
Trans Current Query:"select * from test.range_sub_parts_my_01 where c1=22222 and c2=111111 and c3=abcd"
...
> PARTITION_ID_CALC_DONE
  [WARN] Fail to calculate first part idx may use route policy or cached server session

Route Plan
> SQL_PARSE:{cmd:"COM_QUERY", table:"range_sub_parts_my_01"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
  > TABLE_ENTRY_LOOKUP_DONE:{table:"range_sub_parts_my_01", table_id:1099511677778, partition_num:16, table_type:"USER TABLE", entry_from_remote:false}
  > PARTITION_ID_CALC_START:{}
    > EXPR_PARSE:{col_val:[[0]["c1", "22222"], [1]["c2", "111111"], [2]["c3", ""]]}
    > RESOLVE_EXPR:{error:-4002, sub_part_range:"(111111,MIN ; 111111,MAX)"}
      > RESOLVE_TOKEN:{resolve:{"BIGINT":22222}, token_type:"TOKEN_INT_VAL", token:"22222"}
      > RESOLVE_TOKEN:{resolve:{"BIGINT":111111}, token_type:"TOKEN_INT_VAL", token:"111111"}
      > RESOLVE_TOKEN:{error:-4002, , token_type:"TOKEN_COLUMN", token:"abcd"}
  > PARTITION_ID_CALC_DONE:{error:-4002, partition_id:-1, level:2, partitions:"(p-1sp-1)"}
> ROUTE_POLICY:{replica:"10.10.10.1:50110", 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"}
> CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50110"}
> HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"}
)

The diagnostic procedure is as follows:

1. Based on the [WARN] Fail to calculate first part idx may use route policy or cached server session information in PARTITION_ID_CALC_DONE, partition ID calculation fails, and the request is routed based on the routing strategy or by reusing a session.

2. Check the data near the PARTITION_ID_CALC_DONE diagnostic point. The RESOLVE_TOKEN:{error:-4002, , token_type:"TOKEN_COLUMN", token:"abcd"} information indicates that ODP encounters an error in parsing c3=abcd.