This topic describes the composition and meaning of error codes in OceanBase Database.
Whether you use a Java or C driver to connect to OceanBase Database, when any SQL statement fails to execute, you will receive an error indicating the failure. An error usually consists of the following three parts:
A 32-bit integer error code
A 5-character SQLSTATE value
A human-readable error message
For example, if an SQL statement failed to be executed after you connect to OceanBase Database through OceanBase Client (OBClient), the system displays the following error message on the command-line interface (CLI) of OBClient:
ERROR 1054 (42S22): Unknown column 'haha' in 'field list'
Here, 1054 is the error code, 42S22 in the parentheses is the SQLSTATE value, and the colon is followed by the error message.
For compatibility, different error codes may be returned for the same error in MySQL mode (the sys tenant is in MySQL mode) and Oracle mode. For example, the internal error code -5217 of OceanBase Database indicates that a column name referenced in an SQL statement does not exist. The following table describes the error codes returned for this error in two tenant modes.
| Tenant mode | Error code | SQLSTATE | Error message |
|---|---|---|---|
| MySQL | 1054 | 42S22 | Unknown column |
| Oracle | 904, namely, ORA-00904 | 42S22 | invalid identifier |
You can use the error code parsing tool ob_error 10 to query the meaning of error codes and the mappings between internal and external error codes. For more information about the ob_error tool, see ob_error.
SQLSTATE
SQLSTATE is an error code specified by the SQL standard. It is a string that contains five characters that can be digits or uppercase Latin letters. The first two characters indicate the error category, and the last three characters indicate the specific error. For example, 00 indicates success, 01 indicates a warning, 02 indicates that data is not found, 22 indicates a data error, and 42 indicates a syntax error. Therefore, for the same error, OceanBase Database returns the same SQLSTATE value in MySQL mode and Oracle mode.
Theoretically, all databases that conform to the SQL standard return the same SQLSTATE value for the same error, so that the error handling logic of applications can be compatible with different databases.
Error codes
Although the SQL standard specifies SQLSTATE, the development of the standard always lags behind the practice of database manufacturers. Manufacturers usually need to define their own error codes (vendor codes) to identify more detailed error causes. In addition, error codes defined by a manufacturer are not compatible with databases provided by other manufacturers.
In OceanBase Database, the SQLSTATE value is generally HY000 for custom error codes that are incompatible with other databases.
Error messages
Error messages are user-friendly prompt information. However, the messages may change as the database version evolves, and different messages may be displayed for the same error due to the language settings. Therefore, errors in applications are usually handled based on error codes or SQLSTATE instead of error messages.