Troubleshoot SQL execution errors

SQL execution errors are common in database O&M and can directly impact business operations. Many factors can cause SQL execution errors, such as failed database connection, insufficient user privileges, syntax errors, or data not meeting query conditions.

To help you quickly identify the root cause and efficiently resolve SQL execution errors, the following sections provide a clear and practical troubleshooting process. This process offers specific steps to enhance issue resolution efficiency and minimize business impact, supporting your routine O&M work.

The following figure shows the SQL execution error troubleshooting process.

Procedure

You can troubleshoot the issue when an SQL execution error occurs, based on the following procedure.

After an SQL execution error occurs, view 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 type can be an application execution error or an SQL execution error.

1. If the error is caused by application execution, search for information in the Knowledge Base to troubleshoot the issue.

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

   1. If the error cannot be reproduced, search for information in the Knowledge Base to troubleshoot the issue based on the SQL statement.

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

      After you reproduce the SQL execution error, perform the following steps to collect information for troubleshooting.

      1. Execute the following statement to obtain trace_id.

         Notice

         You must execute the following statement immediately after the SQL statement that causes the error is executed; otherwise, the trace_id obtained by executing the following statement does not belong to the SQL statement that causes the error.

         MySQL mode

         Oracle mode

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

         obclient> SELECT last_trace_id();
         

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

         obclient> SELECT last_trace_id() FROM DUAL;
         

      2. Obtain the host information on which the SQL statement is actually executed based on the obtained trace_id.

         Generally, an OceanBase cluster is deployed across multiple nodes. You can execute the following statement to obtain the node on which the SQL statement is actually executed and then filter logs.

         MySQL mode

         Oracle mode

         Execute the following statement in MySQL mode:

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

         Replace last_trace_id with the trace_id obtained in the previous step.

         Execute the following statement in Oracle mode:

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

         Replace last_trace_id with the trace_id obtained in the previous step.

         Based on the query result in the GV$OB_SQL_AUDIT view, the host corresponding to svr_ip is the host on which the SQL statement is actually executed.

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

      4. Go to the log directory.

         Assume that the installation directory of OceanBase Database is /home/admin/oceanbase. The specific log path depends on the actual environment.

         cd /home/admin/oceanbase/log
         

      5. Execute the following command to filter related logs.

         grep "${trace_id}" observer.log
         

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

         Replace ${trace_id} with the trace_id obtained in the previous steps; replace observer.log.xxx with a log file that contains timestamps based on the actual time when you reproduced the SQL execution error.

      6. Perform issue analysis based on the information provided in the logs, based on the error code or error message.

         For more information about logs and error codes, see Overview of logs and Overview of error information.

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

Case studies

The following case studies describe some typical scenarios where SQL execution errors occur.

References

• Search for logs of a specific SQL request

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

• Log overview