Troubleshoot SQL execution errors

SQL execution errors are common in database O&M and can directly impact business operations. There are many reasons for 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 the issue, the following sections provide a clear and practical SQL error troubleshooting process. This process offers explicit steps to enhance issue resolution efficiency and minimize business impact, thereby supporting your daily O&M work.

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 may be an application execution error or an SQL execution error:

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

2. If the error is an SQL execution error, check whether the error can be manually reproduced.

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

   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 reproducing 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 error occurs; otherwise, trace_id of the error SQL cannot be obtained.

         MySQL mode

         Oracle mode

         The following statement is executed to obtain trace_id in MySQL mode:

         obclient> SELECT last_trace_id();
         

         The following statement is executed to obtain trace_id in Oracle mode:

         obclient> SELECT last_trace_id() FROM DUAL;
         

      2. Obtain the host information 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 that actually executed the SQL statement, and then filter logs based on the node.

         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;
         

         In this statement, replace last_trace_id with 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;
         

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

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

      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 your actual setup.

         cd /home/admin/oceanbase/log
         

      5. Execute the following command to filter out related information from logs.

         grep "${trace_id}" observer.log
         

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

         In these commands, replace ${trace_id} with trace_id obtained in earlier steps, and replace observer.log.xxx with a log file that contains a timestamp based on the actual situation.

      6. Based on the information provided in the logs, analyze the issue based on the error code or error message.

         For more information about logs and error codes, see Overview of logging 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 and how to troubleshoot them.

References

• Search for logs of a specific SQL request

• Use trace information to search for logs of the last SQL request

• Log overview