Binlog commands
The following table lists MySQL binlog commands supported by the Binlog service of OceanBase Database.
| Command | Description | Compatibility |
|---|---|---|
| SHOW MASTER STATUS | Queries the latest binlog file. | Compatible |
| SHOW BINARY LOGS | Queries all binlog files. | Compatible |
| SHOW BINLOG EVENTS | Views all binlog events. | Compatible |
| SHOW PROCESSLIST | Queries connection sessions. | Compatible |
| PURGE BINARY LOGS | Purges binlog files. | Supported only on binlog servers; not yet supported via client operations. |
In addition, the binlog service supports a variety of management commands on binlog server nodes. For more information, see Management commands.
Binlog protocols
| Protocol | Compatibility |
|---|---|
| COM_BINLOG_DUMP | Compatible |
| COM_BINLOG_DUMP_GTID | Compatible |
| COM_REGISTER_SLAVE | Compatible |
System variables
| Variable | Compatibility |
|---|---|
| server_id | Compatible |
| server_uuid | Compatible |
| binlog_format | Compatible |
| binlog_row_image | Compatible |
| binlog_cache_size | Compatible |
| binlog_checksum | Compatible |
| max_binlog_cache_size | Compatible |
| max_binlog_size | Compatible |
| binlog_row_query_log_events | Compatible |
| log_bin | Compatible |
| log_bin_basename | Compatible |
| log_bin_index | Compatible |
| gtid_mode | Compatible |
| gtid_purged | Compatible |
| gtid_executed | Compatible |
| GLOBAL.GTID_EXECUTED | Compatible |
| GLOBAL.GTID_PURGED | Compatible |
| GTID_NEXT | Compatible |
Functions
| Function | Compatibility |
|---|---|
| GTID_SUBSET() | Compatible |
| GTID_SUBTRACT() | Compatible |
| WAIT_FOR_EXECUTED_GTID_SET() | Compatible |
DDL
The DDL syntax in OceanBase Database's MySQL-compatible mode differs from that of MySQL Database. Specifically, OceanBase Database introduces some unique syntax extensions, which may not be recognized by MySQL tools.
To address this issue, starting from OceanBase Database V4.1.0_CE_BP2, a system variable named _show_ddl_in_compat_mode was introduced. By default, this variable is set to 0, which means compatibility mode is disabled. When this compatibility mode is enabled, the result returned by the SHOW CREATE TABLE command will be adjusted to fully conform to MySQL syntax.
Data types
There are differences in data type precision and implementation between OceanBase Database's MySQL-compatible mode and MySQL Database. As a result, not all MySQL data types are supported in OceanBase. The unsupported types and behaviors are described as follows:
The binlog service does not support the extended semantics of the ENUM and SET data types in OceanBase Database's MySQL-compatible mode. For example, SET allows more than 64 elements and duplication, and ENUM allows insertion of undefined values such as ''.
The VARCHAR data type supports up to 65,535 bytes. If the length exceeds this value, parsing errors may occur for the corresponding table data.
Notice
The character set configuration may affect the maximum length of the VARCHAR data type. For example, in utf8mb4, each character occupies four bytes, so the maximum length for VARCHAR is 16,383.
The maximum supported size for LONGBLOB and LONGTEXT is 48 MiB.
The MEDIUMBLOB, LONGBLOB, BLOB, MEDIUMTEXT, LONGTEXT, TEXT, JSON, and GIS data types are all stored as LOB types at the underlying layer. By default, data larger than 4 KiB is stored in OUTROW mode, which is incompatible with MySQL.
When OUTROW data is updated to INROW data, the before image outputs NULL and the after image outputs the updated data.
When non-LOB columns are updated in a table with OUTROW LOB columns, both the before and after images output NULL.
When OUTROW LOB columns are deleted, the before image outputs NULL.
Note
LOB columns with data less than or equal to 4 KiB are treated as regular columns, with data stored in the primary table. For columns larger than 4 KiB, data is stored in a LOB auxiliary table, and the primary table records the corresponding auxiliary table information. The default threshold is 4 KiB.
OceanBase Database V4.x and later do not support output for virtual generated columns.
The CREATE TABLE AS SELECT (CTAS) statement is not supported.
In OceanBase Database V4.x, table names created using CTAS cannot be accurately output. For example, when executing
create table t2 as select * from t1, OceanBase will only output a temporary table name such as__ctas_xxxx, instead of the actual table namet2.