Error handling specifications for MySQL-compatible applications

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

OceanBase Database V1.0 and later are fully compatible with MySQL Database. OceanBase Database also strives to maintain consistency with MySQL Database in terms of error codes, SQLSTATE, and error messages. Therefore, according to our product design principles, application error handling can refer to MySQL Database's handling specifications. However, as a distributed database, OceanBase Database has fundamental differences from a standalone MySQL database, and some errors unique to distributed environments cannot be expressed using MySQL Database's error codes. Generally, if the value of an error code is greater than 4000, the error code is unique to OceanBase Database; if the error code's value is within 4000, it is a compatible error code of MySQL Database. For the complete list of error codes supported by the MySQL-compatible mode of OceanBase Database on the server side, see Overview of error messages.

In addition to errors returned by the database server, applications may also detect errors that are returned locally by the driver library, such as database connection failures. Applications that use MySQL-compatible tenants of OceanBase Database can use MySQL driver libraries, such as the MySQL JDBC Driver. OceanBase Database is fully compatible with MySQL Database for these error codes. The value range of these client error codes is typically 2000 to 3000.

Obtain error information

Drivers in different languages provide different APIs to obtain error information. For more information about each driver, see the OceanBase Database documentation and MySQL Database documentation.

Java driver library

When a Java application that uses JDBC fails to execute an SQL statement, an SQLException can be captured. The exception contains the error code, SQLSTATE value, and error message. For more information about SQLException, see JDBC API documentation.

Methods for capturing an SQLException are as follows:

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

libmysql C driver library

When a C application that uses libmysql fails to execute an SQL statement, you can obtain the error information by using the following methods:

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

Error handling specifications

Most errors in the MySQL-compatible mode of OceanBase Database do not need to be handled by applications and can be directly returned. For fault tolerance and application robustness, the following four different strategies can be used for error handling based on the scenario and error type:

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

   • If a timeout error occurs during execution of a SELECT statement, the statement can be retried because the retry operation has no side effect.

   • If a timeout error occurs during remote or distributed execution of a modification statement such as UPDATE, the transaction must be rolled back because whether the statement is successfully executed is uncertain. If the statement is idempotent, it can be retried.

   • If a timeout error occurs during execution of a COMMIT or ROLLBACK statement, the error is directly returned because the transaction status is unknown.

2. Strategy 2: If an error occurs during execution of any statement, roll back the current transaction and then retry it.

3. Strategy 3: If an error occurs during execution of any statement, retry the current statement.

4. Strategy 4: If an error occurs during execution of any statement, close the current connection and then reestablish the connection.

Error codes unique to OceanBase Database

The following table describes common runtime error codes unique to OceanBase Database. Applications can handle such errors by using strategies suggested for corresponding error codes.

MySQL-compatible error codes

For MySQL-compatible error codes with a value less than 4000, follow the same handling methods as those for MySQL Database. In particular, applications need to pay attention to the following error codes.