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 theROUTE_INFOsection. 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, andLOCATION_CACHE_LOOKUPare 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 ofroute_diagnosis_levelis4.
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:
Based on the
[WARN] Fail to calculate first part idx may use route policy or cached server sessioninformation inPARTITION_ID_CALC_DONE, partition ID calculation fails, and the request is routed based on the routing strategy or by reusing a session.Check the data near the
PARTITION_ID_CALC_DONEdiagnostic point. TheRESOLVE_TOKEN:{error:-4002, , token_type:"TOKEN_COLUMN", token:"abcd"}information indicates that ODP encounters an error in parsing c3=abcd.