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 errors occur, 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 provide a clear and practical SQL error troubleshooting process. This process offers specific steps to enhance issue resolution efficiency and minimize business impact, providing strong support for daily O&M work.

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 type can 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 contains OceanBase error code.

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

   If the error can be reproduced, reproduce the scenario where the error occurred. Connect to the OceanBase cluster through port 2881 or 2883 based on the original scenario and execute the original SQL statement to reproduce the 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 caused the error is executed; otherwise, the trace_id obtained by executing the following statement will not be that of the SQL statement that caused 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 of the host that actually executed the SQL statement based on the obtained trace_id.

      OceanBase clusters are generally 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;
      

      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.

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

      cd /home/admin/oceanbase/log
      

   5. Execute the following command to filter out the required information from the 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. xxx indicates the actual timestamp, which depends on the time when the SQL execution error was reproduced.

   6. Based on the information provided in the logs, perform issue analysis 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 typical scenarios where SQL execution errors occur.

References

• Search for logs of a specific SQL request

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

• Overview of logs