Troubleshoot SQL execution errors

In database operations, SQL execution errors are very common and may directly affect business operations. SQL execution errors can be caused by various reasons, such as failure to connect to the database correctly, insufficient database user permissions, syntax errors, or data not meeting query conditions.

To help you quickly locate the root cause of the problem and efficiently resolve it, a clear and practical SQL error troubleshooting procedure is summarized below. This procedure provides clear operation steps, aiming to improve problem handling efficiency, minimize the impact on business, and provide strong support for daily operations.

Procedure description

When encountering SQL execution errors, you can follow the procedure below for troubleshooting.

After a SQL execution error occurs, you need to first view the SQL error information. If the error information contains a clear error code, troubleshoot the problem based on the error code information. If there is no clear error code, you need to determine the error type of the problem, whether it is an application execution error or a manual SQL execution error:

1. If it is an application execution error, see Application exception - Error information does not contain OceanBase error codes and Application exception - Error information contains OceanBase error codes for specific troubleshooting methods.

2. If it is a manual SQL execution error, determine whether it can be manually reproduced.

   2. If it can be reproduced, first reproduce the problem scenario. Based on the original scenario, connect to the OceanBase cluster through port 2881 or 2883 and execute the original SQL statement to reproduce the problem scenario.

      After reproducing the SQL error scenario, follow the steps below to collect relevant information for troubleshooting.

      1. Execute the following statement to obtain the trace_id.

         Notice

         You must execute the following statement immediately after executing the erroneous SQL. Otherwise, the query result is not the `trace_id` of the erroneous SQL.

         MySQL-compatible mode

         Oracle-compatible mode

         The statement to obtain the trace_id in MySQL-compatible mode is as follows:

         obclient> SELECT last_trace_id();
         

         The statement to obtain the trace_id in Oracle-compatible mode is as follows:

         obclient> SELECT last_trace_id() FROM DUAL;
         

      2. Obtain the host information of the actual execution of the SQL based on the obtained trace_id.

         OceanBase clusters are generally deployed on multiple nodes. You can obtain the node where the SQL is actually executed through the following SQL, 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;
         

         Here, last_trace_id needs to be replaced 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;
         

         Here, last_trace_id needs to be replaced with the trace_id obtained in the previous step.

         According to the query result of the GV$OB_SQL_AUDIT view, the host corresponding to svr_ip is the host where the SQL is actually executed.

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

      4. Enter the directory where the logs are located.

         The following example assumes that the installation directory of OceanBase Database is /home/admin/oceanbase. The specific log storage path shall be based on the actual environment.

         cd /home/admin/oceanbase/log
         

      5. Execute the following command to filter the relevant information in the logs.

         grep "${trace_id}" observer.log
         

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

         Here, ${trace_id} needs to be replaced with the trace_id obtained in the previous steps. observer.log.xxx is a log file with a timestamp, and xxx needs to be replaced with the actual timestamp based on the time when the SQL error was reproduced.

      6. Analyze the problem based on the information provided by the logs, combined with error codes or related error prompts.

         For more information about logs and error codes, see Log overview and Error information overview.

         If the information in the logs is unclear, contact technical support personnel for assistance.

Typical cases

The following are some typical SQL execution error troubleshooting cases.

References

• Search for logs of a specific SQL request

• Search for the logs of the last SQL request based on the Trace feature

• Log overview