Troubleshoot SQL execution errors

SQL execution errors are common in database O&M and can directly impact business operations. There are many reasons why SQL statements may fail to execute, such as unsuccessful database connections, insufficient user privileges, syntax errors, or data that does not meet query conditions.

To help you quickly identify root causes and efficiently resolve issues, the following sections outline a clear and practical process for troubleshooting SQL errors. This process provides specific steps to improve resolution efficiency and minimize business impact, supporting your routine O&M tasks.

The diagram below illustrates the SQL execution error troubleshooting process.

Procedure

When an SQL execution error occurs, you can troubleshoot the issue by following the procedure below.

After an SQL execution error occurs, check the SQL error message. If the error message contains an error code, troubleshoot the issue based on the error code. If the error message does not contain an error code, determine the error type. The error may be caused by application execution or by manual SQL execution:

1. If the error is an application execution error, refer to Application exception: error message does not contain an OceanBase error code and Application exception: error message contains an OceanBase error code for troubleshooting methods.

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

   1. If the error cannot be reproduced, search for relevant information in the Knowledge Base using the SQL statement for troubleshooting.

   2. If the error can be reproduced, recreate the error scenario. Connect to the OceanBase cluster through port 2881 or 2883 according to the original scenario, and execute the original SQL statement to reproduce the issue.

      After reproducing the SQL execution error, perform the following steps to collect information for troubleshooting:

      1. Execute the following statement to obtain the trace_id:

         Notice

         You must execute the following statement immediately after the SQL statement that causes the error. Otherwise, the trace_id obtained will not correspond to the SQL statement that caused the error.

         MySQL-compatible mode

         Oracle-compatible mode

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

         obclient> SELECT last_trace_id();
         

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

         obclient> SELECT last_trace_id() FROM DUAL;
         

      2. Use the obtained trace_id to find the host that executed the SQL statement.

         OceanBase clusters are typically deployed across multiple nodes. You can execute the following statement to identify the node that executed the SQL statement, and then filter logs on that node.

         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;
         

         Replace 'last_trace_id' with the actual 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;
         

         Replace 'last_trace_id' with the actual trace_id obtained in the previous step.

         In the query result from the GV$OB_SQL_AUDIT view, the host indicated by svr_ip is the one that executed the SQL statement.

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

      4. Navigate to the log directory.

         For example, if the OceanBase Database installation directory is /home/admin/oceanbase, the logs are stored in that path. The actual log path may vary depending on your environment.

         cd /home/admin/oceanbase/log
         

      5. Execute the following commands to filter logs:

         grep "${trace_id}" observer.log
         

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

         Replace ${trace_id} with the trace_id obtained in the previous steps. observer.log.xxx refers to a log file with a timestamp. Replace xxx with the actual timestamp corresponding to when the SQL error was reproduced.

      6. Analyze the issue using the information provided in the logs, along with the error code or related error messages.

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

         If the log information is unclear, contact technical support for assistance.

Case studies

The following case studies describe typical scenarios in which SQL execution errors occur and how they are resolved.

References

• Search for logs of a specific SQL request

• Use tracing feature to search for logs of the last SQL request

• Log overview