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. Therefore, 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 more information about the error codes supported by the MySQL mode of OceanBase Database, see Overview of error messages.

In addition to errors returned by OceanBase Database, applications also detect local errors, such as database connection failures, returned by the driver. Applications that use MySQL tenants of OceanBase Database can use MySQL drivers, such as the MySQL JDBC driver. OceanBase Database is fully compatible with these error codes in MySQL Database. These client error codes usually range from 2000 to 3000.

Obtain error information

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

Java driver

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 reference.

Methods for capturing an SQLException are as follows:

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

libmysql C driver

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 the mysql_errno() method to obtain the error code.
• Call the mysql_sqlstate() method to obtain the SQLSTATE value.
• Call the mysql_error() method to obtain the error message.

Error handling specifications

Most errors in the MySQL 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 less than 4000, applications follow the same handling methods as those for MySQL Database. In particular, applications need to pay attention to the following error codes.