OBLOADER & OBDUMPER V4.2.0

OBLOADER & OBDUMPER V4.2.0, released in April 2023, adds support for importing from and exporting to Amazon S3, uploading data to cloud storage immediately or in batches, and encrypting sensitive command-line information. It also fixes an export performance regression in Oracle compatible mode.

Version information

• Current version: V4.2.0

• Previous version: V4.1.0

• Release date: April 21, 2023

Supported OceanBase Database versions

The following table lists the OceanBase Database versions supported by OBLOADER & OBDUMPER V4.2.0.

Feature updates

• OBLOADER:

  • Added support for importing object definitions and data from Amazon S3.

  • Added support for parsing SQL files exported by mysqldump.

  • Added support for encrypting sensitive command-line information. The following sensitive fields can be encrypted: the password of a user in an OceanBase Database business tenant (oceanbase.jdbc.password), the password of a user in the sys tenant (oceanbase.jdbc.sys.password), the AccessKey ID used to access cloud storage (cloud.storage.access.key), and the AccessKey secret used to access cloud storage (cloud.storage.secret.key). For more information, see Security features.

• OBDUMPER:

  • Added support for encrypting sensitive command-line information. The following sensitive fields can be encrypted: the password of a user in an OceanBase Database business tenant (oceanbase.jdbc.password), the password of a user in the sys tenant (oceanbase.jdbc.sys.password), the AccessKey ID used to access cloud storage (cloud.storage.access.key), and the AccessKey secret used to access cloud storage (cloud.storage.secret.key).

  • Added the --storage-uri option to export object definitions and data to Amazon S3. For more information, see OBDUMPER command-line options.

  • Added the --upload-behavoir option to specify how exported data is uploaded to cloud storage. The supported modes are FAST, which uploads each data file immediately after it is exported to the local disk, and COMPLETE, which uploads all data files in a batch after the export is complete.

  • Added the --with-trim option to remove leading and trailing spaces from data when exporting CSV and CUT files.

  • Added the --fetch-size option to specify the number of rows read from a database cursor at a time in Oracle compatible mode.

  • Preserved the original partition names when exporting table object definitions.

  • Added support for splitting exported data by row.

  • Allowed the column allowlist (--include-column-names) to omit primary key columns and the blocklists (--exclude-column-names and --exclude-data-types) to exclude primary key columns during export.

Bug fixes

• Fixed the issue where the performance of OBDUMPER in the Oracle compatible mode of OceanBase Database regressed.

• Fixed the issue where the column filtering feature of OBDUMPER did not take effect.

• Fixed the issue where the fuzzy match feature of OBDUMPER took effect even if it was not specified.

• Fixed the issue where the object definitions of temporary tables exported by OBDUMPER were incorrect.

• Fixed the issue where OBDUMPER returned an error when exporting data from OceanBase Database in MySQL compatible mode with case sensitivity enabled.

• Fixed the issue where OBDUMPER consumed a large amount of memory when the --query-sql option was specified.

• Fixed the issue where OBDUMPER omitted the schema name of a referenced table in another database when exporting foreign key constraints.

• Fixed the issue where OBDUMPER could not export user-defined indexes in the MySQL compatible mode of OceanBase Database.

• Fixed the issue where OBDUMPER could not export zero values stored in the DATETIME type in MySQL.

• Fixed the issue where OBDUMPER could not generate empty files when the --query-sql and --retain-empty-files options were specified.

• Fixed the issue where OBDUMPER returned an unexpected error when no export file was generated and the --file-name option was specified.

• Fixed the issue where OBLOADER returned an error when a file contained a COMMIT statement in the --mix mode.

• Fixed the issue where OBLOADER displayed inaccurate summary information when importing definitions for objects that had the same name but different types.

• Fixed the issue where OBLOADER logged incorrect information when importing invalid date and time values.

• Fixed the issue where OBDUMPER and OBLOADER failed to find the session.properties and decrypt.properties configuration files in the running directory.

Known issues

• The exported database object definitions may contain the names of the databases to which the objects belong.

• OceanBase Database versions earlier than V2.2.30 in the MySQL compatible mode do not support exporting PL object definitions.

• When you specify the --query-sql option to export DATETIME type data, the exported data may have format issues.

• OceanBase Database versions earlier than V2.2.70 do not support exporting table group definitions if the sys tenant password is not specified on the command line.

• OceanBase Database versions earlier than V2.2.50 in the Oracle compatible mode do not support exporting index definitions if the sys tenant password is not specified in the command line.

• OceanBase Database versions earlier than V2.2.70 in the Oracle compatible mode do not support exporting partition information of unique indexes if the sys tenant password is not specified in the command line.

• OceanBase Database versions from V2.2.70 (inclusive) to V4.0.0.0 (exclusive) in Oracle compatible mode do not support exporting unique index definitions of partitioned tables if the sys tenant password is not specified on the command line.

• OBLOADER may return an error when the file format specified by options such as --sql and --csv does not match the actual file format.

• After case sensitivity is enabled in MySQL compatible mode, strict case-sensitive matching is not supported for database object names such as table and view names.

• Authentication configured through secure.crt does not work during export. As a workaround, run vi bin/obdumper in the OBLOADER & OBDUMPER root directory, locate -Dproxy.secureFile=file://$PARENT_PATH/conf/secure.crt, and replace it with -Dproxy.secureFile=$PARENT_PATH/conf/secure.crt.

Considerations

• The CUT format requires that data be stored in full lines. When you specify the --cut option in the OBDUMPER command line to export data that contains field delimiters, if the delimiter is a single character, OBDUMPER will escape special characters such as delimiters, carriage returns, or line breaks in the data. For example, if the data is abc|def and the delimiter is |, the exported data will be abc\|def.

• When you specify the --cut option in the OBDUMPER command line to import data, do not use the --trail-delimiter option if the data lines in the file do not end with a field delimiter (or delimiter string). Otherwise, the data cannot be correctly imported into the database.

• When you specify the --logical-database option in the OBDUMPER command line, the exported data contains the definition of a random physical database shard. You cannot directly import the data into the database. You need to manually change the physical database shard definition to a logical database shard definition before you can import the data into the database for business use.

• When you specify the --partition option to export data from a composite partitioned table, you must specify a subpartition name. OBDUMPER cannot export data directly from a first-level partition. If the specified partition does not exist, OBDUMPER returns an error.

• When you specify the --add-extra-message option in the OBDUMPER command line to export table definitions, the program will export the name of the table group to which each table belongs. This option requires the sys tenant privilege. If OBDUMPER does not have the sys tenant privilege when it is running, do not specify this option.

• Before you use OBLOADER or OBDUMPER with OceanBase Database V3.2.4 or later, set the system parameter open_cursors to a large value. Otherwise, an error may occur during data import or export. After the operation is complete, reset open_cursors to its initial value. Example: ALTER SYSTEM SET open_cursors = 65535;.

• In OceanBase Database V4.0.0 and later, if a table schema has been modified, you cannot use OBDUMPER to export the baseline data (i.e., consistent snapshot data) of the last successful major compaction. You can manually initiate a major compaction and then re-export the baseline data of the last successful major compaction.

• When you import DDL statements, if you specify -f as a non-standard directory structure (i.e., a directory structure that is not generated by OBDUMPER), use the --mix option instead of the --ddl option. If you specify the --sql option, the file data format must be such that each statement inserts only one record. If this is not the case, use the --mix option instead of the --sql option for import.

• In OceanBase Database in MySQL compatible mode, when you use OBDUMPER of a version earlier than V4.2.0 to export data, the JDBC driver converts zero values of date and time types to NULL. If the column is defined with the NOT NULL constraint, an error will be returned during data export. OBDUMPER V4.2.0 can export zero values of date and time types, but it cannot distinguish between NULL and zero values in the original data. By default, OBDUMPER V4.2.0 forcibly converts the data to zero values. In addition, when you export zero values of the DATETIME and TIMESTAMP data types, data distortion may occur, which means that a non-zero approximate value is returned. If the sql_mode variable of the database contains the NO_ZERO_DATE or NO_ZERO_IN_DATE constraint, an error will be returned when you import zero values.

  In OceanBase Database in MySQL compatible mode, the DATE, DATETIME, TIME, YEAR, and TIMESTAMP data types are involved in zero value issues.

• Differences in schema export

  Tenant	Provided sys tenant password	Not provided sys tenant password
  MySQL compatible	Tables, views, table groups, stored procedures, and functions	The export behavior is basically the same as that when the sys tenant password is provided, but the following issues remain. OBDUMPER of OceanBase Database earlier than V2.2.70 cannot export table group definitions. OBDUMPER of OceanBase Database earlier than V2.2.70 cannot export partition information of unique indexes. OBDUMPER cannot export index definitions from OceanBase Database versions earlier than V2.2.50 in Oracle compatible mode. OBDUMPER cannot export unique index definitions of partitioned tables from OceanBase Database versions from V2.2.70 (inclusive) to V4.0.0.0 (exclusive) in Oracle compatible mode.
  Oracle compatible	Tables, views, triggers, synonyms, sequences, stored procedures, functions, packages, table groups, and types