Application exceptions -- error messages with OceanBase error codes

During database operations, various exceptions may arise, such as application errors, connection issues, permission problems, resource limitations, and network disruptions. Among these, a common scenario is application exceptions where the error message contains an OceanBase error code.

To help you quickly identify the root cause and efficiently resolve such issues, this topic outlines a clear and practical troubleshooting process for application exceptions where error messages include OceanBase error codes. This process provides explicit steps to improve troubleshooting efficiency and minimize business impact, supporting daily operations.

Process description

When an application exception occurs and the error message contains OceanBase error codes, follow this process to troubleshoot the issue.

First, determine whether you can use OBClient to connect to the database and manually reproduce the issue:

• If yes, you can then connect to the database after the issue is reproduced and proceed with further troubleshooting.

• If no, you need to check the data source information in the program configuration.

Troubleshoot the issue by reproducing the issue

1. Use OBClient to connect to the OceanBase cluster by using port 2881 (the default port for SQL). If a custom port is used, replace the port number with the actual port number.

2. Manually execute the SQL statement that reported an error to reproduce the issue.

3. Execute the following statement to obtain the trace_id.

   Notice

   You must execute the following statement immediately after the SQL statement that reported an error is executed. Otherwise, the trace_id of the error-reporting SQL statement cannot be obtained.

   MySQL-compatible mode

   Oracle-compatible mode

   The following statement is used to obtain the trace_id in MySQL-compatible mode:

   obclient> SELECT last_trace_id();
   

   The following statement is used to obtain the trace_id in Oracle-compatible mode:

   obclient> SELECT last_trace_id() FROM DUAL;
   

4. Obtain the host information of the actual SQL execution.

   OceanBase Database is usually deployed on multiple nodes. You can execute the following SQL statement to obtain the node where the SQL statement is actually executed, and then filter the logs.

   MySQL-compatible mode

   Oracle-compatible mode

   Execute the following statement in MySQL-compatible mode:

   obclient> SELECT * FROM oceanbase.GV$OB_SQL_AUDIT WHERE trace_id=last_trace_id;
   

   In this statement, replace last_trace_id with the trace_id obtained in the previous step.

   Execute the following statement in Oracle-compatible mode:

   obclient> SELECT * FROM SYS.GV$OB_SQL_AUDIT WHERE trace_id=last_trace_id;
   

   In this statement, replace last_trace_id with the trace_id obtained in the previous step.

   The host corresponding to svr_ip in the query result of the GV$OB_SQL_AUDIT view is the host that actually executed the SQL statement.

5. Use the ssh command to log in to the host based on the obtained host information.

6. Go to the directory where the logs are stored.

   Take /home/admin/oceanbase as an example, which is the installation directory of OceanBase Database. In an actual environment, the log storage path may vary.

   cd /home/admin/oceanbase/log
   

7. Execute the following command to filter relevant information from the logs.

   grep "${trace_id}" observer.log
   

   grep "${trace_id}" observer.log.xxx
   

   In these statements, replace ${trace_id} with the trace_id obtained in a previous step and observer.log.xxx with the log file that contains a timestamp. In this example, xxx needs to be replaced with the actual timestamp based on the time when the SQL statement that reported an error is executed.

8. Analyze the information provided by the logs, and take note of the error messages.

   For more information about logs, see Overview of logs. If the information in the logs is unclear, contact technical support for assistance.

Troubleshoot data source configuration information

1. Specify the access path based on the data source.

2. Specify the number and addresses of the following components in the access path:

   • OceanBase Database Proxy (ODP) nodes

   • OBServer nodes

3. Determine whether the access path passes through ODP.

   • If yes, perform the following steps:

     1. Use the ssh command to log in to the ODP node.

     2. Use the cd command to go to the log directory of ODP. The logs of ODP are stored in the /log directory of its installation directory.

     3. Run the following command to filter out the error logs:

        grep "Error text" obproxy_error.log obproxy_error.log.xxx | grep "Error time"
        

     4. Based on the filtered log entries, find the OBServer node that routes the erroneous SQL statement.

     5. Use the ssh command to log in to the corresponding OBServer node for troubleshooting.

   • If no, perform the following steps:

     1. Use the ssh command to log in to the OBServer node.

     2. Go to the directory where the logs are stored.

        Here is an example: If the installation directory of OceanBase Database is /home/admin/oceanbase, the logs are stored in the /home/admin/oceanbase/log directory. Please note that the actual path may vary based on your environment.

        cd /home/admin/oceanbase/log
        

     3. Run the following command to filter out the error logs:

        grep "sending error" observer.log observer.log.xxx | grep "Error code"
        

     4. Based on the filtered log entries, obtain the trace_id, and then use the trace_id to filter the logs.

        grep "trace id" observer.log observer.log.xxx
        

     5. Analyze the problem based on the information provided in the logs and the relevant error prompts.

        For more information about logs, see Overview of logs. If the information in the logs is unclear, contact technical support for assistance.

References

• Find the log of a specific SQL request

• Find the log of the last SQL request by using the trace feature

• Overview of logs