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 issues, the following sections describe a clear and practical SQL error troubleshooting process. This process provides specific steps to improve issue resolution efficiency and minimize business impact, providing strong support for daily O&M work.

The SQL execution error troubleshooting process is shown in the following figure.

Procedure

When an SQL execution error occurs, you can troubleshoot the issue 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, troubleshoot the issue based on Application exceptions--error message does not contain OceanBase error code or Application exceptions--error message contains OceanBase error code.

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

   1. If the error cannot be reproduced, view the Knowledge Base for troubleshooting.

   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 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 that executed the SQL statement based on the obtained trace_id.

         Generally, an OceanBase cluster is deployed with multiple nodes. You can obtain the node that executed the SQL statement based on trace_id 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 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 trace_id obtained in the previous step.

         View the svr_ip column in the GV$OB_SQL_AUDIT view. The value in the svr_ip column is the host that 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.

         For example, if the installation directory of OceanBase Database is /home/admin/oceanbase, the specific log path depends on your actual environment.

         cd /home/admin/oceanbase/log
         

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

         grep "${trace_id}" observer.log
         

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

         Replace ${trace_id} with trace_id obtained in the previous steps. Replace observer.log.xxx with a log file that contains timestamps, such as observer.log.20210520. xxx must be replaced with the actual timestamp.

      6. Based on the information provided in the logs, analyze the error 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 support 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

• Overview of logs