Query the log of an SQL request

OceanBase Database allows you to query the complete log of an SQL request.

To facilitate log query, you can set a log generation level in advance to reduce the number of logs generated in different modules except for the SQL module. For more information about how to set a log generation level, see Set a log generation level.

Procedure:

1. Log on to the host of the relevant OBServer node.

2. Run the following command to enter the directory where log files are located:

   cd ~/oceanbase/log
   

3. Filter logs based on the specified log generation level. For example, you can query logs of the WARN level from the observer.log file.

   grep 'WARN' observer.log
   observer.log: [2021-07-15 14:05:11.218141] WARN  [SQL] execute_get_plan (ob_sql.cpp:3159) [119331][0][YB42AC1E87ED-0005C6866C3BAFB1-0-0] [lt=5] [dc=0] fail to get plan retry(ret=-5138)
   observer.log: [2021-07-15 14:05:11.300671] WARN  [SQL] execute_get_plan (ob_sql.cpp:3159) [119342][0][YB42AC1E87ED-0005C686685C0492-0-0] [lt=18] [dc=0] fail to get plan retry(ret=-5138)
   observer.log: [2021-07-15 14:05:11.549102] WARN  [STORAGE] set_io_prohibited (ob_storage.cpp:110) [119002][0][Y0-0000000000000000-0-0] [lt=14] [dc=0] set_io_prohibited(io_prohibited=false, prohibited=false)
   observer.log: [2021-07-15 14:05:11.549109] WARN  [STORAGE] enable_backup_white_list (ob_partition_service.cpp:14335) [119002][0][Y0-0000000000000000-0-0] [lt=6] [dc=0] backup set_io_prohibited(prohibited=false)
   

4. Obtain the log of the required SQL request based on the trace_id field.

   grep $trace_id observer.log
   

   Here is an example about how to obtain the log with a trace_id value of YB42AC1E87ED-0005C6866C3BAFB1-0-0:

   grep YB42AC1E87ED-0005C6866C3BAFB1-0-0 observer.log
   observer.log: [2021-07-15 14:05:11.2181N  [SQ41] WARL] execute_get_plan (ob_sql.cpp:3159) [119331][0][YB42AC1E87ED-0005C6866C3BAFB1-0-0] [lt=5] [dc=0] fail to get plan retry(ret=-5138)