OBLOADER & OBDUMPER V4.2.7

OBLOADER & OBDUMPER V4.2.7 was released in November 2023. It allows you to write all data of a single table into one file or evenly distribute the data in multiple files based on the number of rows or the physical data size, export database object definitions and table data to Hadoop Distributed File System (HDFS), and upload database object definitions and table data to an Alibaba Cloud Object Storage Service (OSS) bucket specified by the --storage-uri option without first exporting the data to the path specified by the -f option. It also allows you to import MySQL schema definitions in a compatible mode. You can import data from a server in direct load mode and declare anonymous columns when you import data files in the POS format. Several known issues are fixed.

Version information

• Current version: V4.2.7

• Previous version: V4.2.6

• Release date: November 28, 2023

Supported OceanBase Database versions

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

Upgrade considerations

• The --file-name option is deprecated. You can set the --block-size option to 0, which means that the maximum file size is not limited. This way, you no longer need to merge several sub-files exported from a single table into one file.

  Notice

  Although OBLOADER & OBDUMPER V4.2.7 does not limit the size of individual files, some file systems or object storage services may do. For example, Alibaba Cloud OSS allows you to upload a single file of not greater than 5 GB in append mode, and an Ext2, Ext3, or Ext4 file system allows you to upload a single file of not greater than 2 TB. By default, OBDUMPER generates nested directories. You can use the --no-nested-dir option to disable this feature.

• The --upload-behavior option is deprecated. OBDUMPER exports data to different data storage services based on the following strategies:

  • OBDUMPER directly uploads data to an HDFS storage space specified by the --storage-uri option without first exporting the data to the path specified by the -f option.

  • OBDUMPER directly uploads data to an OSS bucket in append mode without first exporting the data to the path specified by the -f option.

  • OBDUMPER exports data to the path specified by the -f option and then uploads the data from the specified path to an Amazon Simple Storage Service (S3) bucket specified by the --storage-uri option.

• When you export the data of a single schema or table, if the value of the -f or --file-path option is a file path, all data is exported to one file.

• If you want to improve the export performance, you can enable parallel write so that OBDUMPER writes the data of each table to multiple subfiles, which cannot be merged into one file. Perform the following steps:

  • Use a text editor to open the obdumper file in the {ob-loader-dumper}/bin/ directory.

  • Find the Java startup parameter -Denable.parallel.write=false, and change its value from false to true.

• When you import a CSV file exported by OBDUMPER, you may need to manually add the --skip-header option, because the MANIFEST.bin file has been removed.

  Note

  When you use OBDUMPER to export data to a CSV file, a MANIFEST.bin file is generated to record the format information. When you use OBLOADER to import data from the CSV file later, OBLOADER will parse the MANIFEST.bin file and extract the format information to overwrite format related command-line options during the import. If no MANIFEST.bin file is generated, a CSV header is generated by default during export. However, when OBLOADER imports data from the CSV file, it will read data starting from the first record by default. As a result, the header is processed as a data record, causing an error. To address this issue, you need to manually add the --skip-header option during import.

Feature updates

• OBLOADER:

  • Provides the _FILLER syntax keyword, which is case-insensitive, in control files for you to declare anonymous columns when you import data files in the POS format. You can use the _FILLER keyword in a control file to replace a column name and exclude a segment of bytes that you do not want to import to the target database. For example, _FILLER POSITION(5:10) specifies to skip the fifth to tenth bytes.

    Note

    You can use this keyword to skip line breaks.

  • Provides the --compat-mode option for you to import MySQL table schema definitions in compatible mode. If you have specified this option when you import MySQL table schema definitions, OBLOADER converts the MySQL native table creation statement to a synonymous statement that is supported in MySQL compatible mode of OceanBase Database. Only table definitions of MySQL databases are converted. For more information, see Command-line options.

  • Allows you to set the --escape-character option to special (invisible) characters. The special characters must be represented in their hexadecimal format.

• OBDUMPER :

  • Supports exporting database object definitions and table data to HDFS.

  • Provides the --no-nested-dir option for you to export data to a non-nested directory. For more information, see Command-line options.

  • Allows you to set the --escape-character option to special (invisible) characters. The special characters must be represented in their hexadecimal format.

Bug fixes

• Fixed the issue where OBLOADER reports errors if you specify the --file-regular-expression option to match multiple data files and import files in the ORA or PAR format in direct load mode.

• Fixed the issue where the specified --exclude-table option does not take effect when you use OBLOADER to import DDL definition files from an Amazon S3 bucket.

• Fixed the issue where OBLOADER reports an error indicating that the object name already exists if you repeatedly execute the command with the --replace-object option specified.

• Fixed the issue where OBLOADER reports the [WARN] Refresh observer load status failed error when you import data in direct load mode.

• Fixed the issue where the Apache ORC memory leaks in the third-party database cause a NullPointerException (NPE) when you use OBDUMPER to export data files in the ORC format.

• Fixed the issue where OBDUMPER reports the Unable to calculate MD5 hash error when you export data to an Amazon S3 bucket.

• Fixed the issue where an NPE is thrown if the path specified by the -f option on the command line of OBLOADER & OBDUMPER does not exist.

Known issues

• The exported database object definitions may contain the name of the database to which a specified object belongs.

• OBDUMPER cannot export PL object definitions from OceanBase Database of a version earlier than V2.2.30 in MySQL compatible mode.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export table group definitions from OceanBase Database of a version earlier than V2.2.70.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export index definitions from OceanBase Database of a version earlier than V2.2.50 in Oracle compatible mode.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export partition information of unique indexes from OceanBase Database of a version earlier than V2.2.70 in Oracle compatible mode.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export definitions of unique indexes on partitioned tables from OceanBase Database V2.2.70 or later in Oracle compatible mode.

• When you use OBLOADER to import data, an error occurs if the specified file format, such as --sql or --csv, does not match the actual file format.

• In MySQL compatible mode of OceanBase Database, after you enable case sensitivity for object names such as table names and view names, objects are still imported or exported in a case-insensitive manner.

• The high availability (HA) mode "sequential" of OceanBase Connector/J is unavailable for now. You can use the "loadbalance" mode instead.

• If you specify the --remove-newline option to remove line breaks, unexpected escape characters are generated. We recommend that you do not use this option.

Considerations

• In a CUT file, each data record is stored in a separate line. When you specify the --cut option on the command line of OBDUMPER, if the exported data contains a single-character field delimiter, OBDUMPER escapes special characters in the data, such as delimiters, carriage returns, and line breaks. For example, if the data is abc|def and the delimiter is |, the exported data is abc\|def.

• When you specify the --cut option on the command line of OBLOADER, do not use the --trail-delimiter option if no field delimiter or delimiter string exists at the end of each data line in the file. Otherwise, the data cannot be correctly imported to the database.

• If you specify the --logical-database option on the command line, the definition of a random physical database shard is exported and the shard cannot be directly imported to the database. You must manually convert the exported physical shard to a logical one before you import it into the database for business use.

• When you specify the --partition option on the command line to export data in a partition, you need to specify the name of the subpartition for a composite partitioned table. OBDUMPER cannot export data in a partition of a composite partitioned table. If the specified partition name does not exist, OBDUMPER returns an error.

• When you specify the --add-extra-message option on the command line to export table definitions, OBDUMPER exports the name of the table group to which each table belongs. This option depends on the privileges of the sys tenant. If OBDUMPER does not have the privileges of the sys tenant, do not specify this option.

• When you use OBLOADER & OBDUMPER to import data to or export data from OceanBase Database V3.2.4 or later, set the open_cursors system parameter to a large value. Otherwise, an error may occur during the import or export. After the data is imported or exported, reset the system parameter to the initial value, such as ALTER SYSTEM SET open_cursors = 65535;.

• In OceanBase Database V4.0.0 or later, if the schema of a table has been changed, you cannot use OBDUMPER to export the baseline data, namely the consistent snapshot data, obtained after the last major compaction. You can manually initiate a major compaction and then export the most recent baseline data.

• When you use OBDUMPER of a version earlier than V4.2.0 to export data from OceanBase Database in MySQL compatible mode, OceanBase Connector/J converts the zero values of date and time columns in the database to NULL. If a column has a NOT NULL constraint, an error is reported during the export. OBDUMPER V4.2.0 can export zero values of date and time columns, but cannot identify whether the original data is NULL or zero values during export and forcibly converts the data to zero values by default. In addition, exported zero values of the DATETIME and TIMESTAMP data types may be distorted into approximate non-zero values. If the sql_mode variable in the database is specified with the NO_ZERO_DATE or NO_ZERO_IN_DATE constraint, an error is reported when zero values are imported.

  Take note of this consideration on zero values when you export data of the DATE, DATETIME, TIME, YEAR, or TIMESTAMP type from OceanBase Database in MySQL compatible mode.

• Feature differences in schema export in the MySQL compatible and Oracle compatible modes:

  Compatibility Mode	Password of the sys tenant provided	Password of the sys tenant not provided
  MySQL compatible	Tables, views, table groups, stored procedures, and functions are supported.	The export behavior is basically the same as that when the password of the sys tenant is provided, except for the following known issues: Table group definitions cannot be exported for OceanBase Database of a version earlier than V2.2.70. Partition information of unique indexes cannot be exported for OceanBase Database of a version earlier than V2.2.70. Index definitions cannot be exported for Oracle-compatible tenants of OceanBase Database V2.2.30 or earlier. Definitions of unique indexes on partitioned tables cannot be exported for Oracle-compatible tenants of OceanBase Database of a version ranging from V2.2.70 (included) to V4.0.0.
  Oracle compatible	Tables, views, triggers, synonyms, sequences, stored procedures, functions, packages, table groups, and types are supported.