Error handling for MySQL-compatible applications

This topic describes the error handling for MySQL-compatible applications.

OceanBase Database is fully compatible with MySQL Database starting from V1.0. We try to ensure that the error codes, SQLSTATE, and error messages are consistent with those of MySQL Database. Therefore, we recommend that you handle application errors in the same way as MySQL Database. However, OceanBase Database is a distributed database, which is fundamentally different from a standalone MySQL Database. Some errors that occur in a distributed environment cannot be expressed by the error codes of MySQL Database. Generally, an error code greater than 4000 indicates an error code unique to OceanBase Database. An error code less than or equal to 4000 indicates an error code compatible with MySQL Database. For more information about the error codes of OceanBase Database in MySQL tenant mode, see Overview of error messages.

In addition to the errors returned by the database server, an application may also receive errors returned by the driver library. For example, an error may be returned when a database connection fails. OceanBase Database is fully compatible with MySQL Database in terms of error codes. Therefore, the error codes of OceanBase Database are the same as those of MySQL Database. The values of these error codes range from 2000 to 3000.

Obtain error information

Different drivers provide different APIs to obtain error information. For more information about the drivers, see OceanBase Database documentation and MySQL Database documentation.

Java driver

If a Java application using JDBC fails to execute an SQL statement, it can capture the SQLException exception, which contains the error code, SQLSTATE, and error message. For more information about the SQLException exception, see JDBC API documentation.

You can capture the SQLException exception by using the following methods:

• Call the getErrorCode() method to obtain the error code.
• Call the getSQLState() method to obtain the SQLSTATE.
• Call the getMessage() method to obtain the error message.

libmysql C driver

If a C application using the libmysql driver fails to execute an SQL statement, it can obtain the error information by using the following methods:

• Call the mysql_errno() method to obtain the error code.
• Call the mysql_sqlstate() method to obtain the SQLSTATE.
• Call the mysql_error() method to obtain the error message.

Error handling

For most error codes of OceanBase Database in MySQL tenant mode, you do not need to handle the errors. You can directly return the errors to the client. For some error codes, you can use the following four strategies based on the scenarios and error types:

1. Strategy 1: Handle errors based on the statement type.

   • If a timeout error occurs when you execute a SELECT statement, you can retry the statement because the SELECT statement has no side effects.

   • If a timeout error occurs when you execute an UPDATE statement in a remote or distributed environment, you cannot determine whether the statement is executed successfully. Therefore, you need to roll back the transaction. If the UPDATE statement is idempotent, you can retry the statement.

   • If a timeout error occurs when you execute a COMMIT or ROLLBACK statement, you need to directly return the error to the client because the transaction state is unknown.

2. Strategy 2: Roll back the current transaction and retry the transaction.

3. Strategy 3: Retry the current statement.

4. Strategy 4: Close the current connection and then rebuild the connection.

Error codes unique to OceanBase Database

The following table lists the error codes unique to OceanBase Database. You can handle these error codes based on the strategies in the table.

Error codes compatible with MySQL Database

For error codes less than 4000, handle them in the same way as MySQL Database. Pay special attention to the following error codes.