OBLOADER & OBDUMPER V4.3.1

OBLOADER & OBDUMPER V4.3.1 released in July 2024 provides the following new features: allows you to import SQL text files that store data in the format of batch insert statements, adds the ShangMi 3 (SM3) preprocessing function to the control file, provides real-time SQL monitoring logs to improve self-service troubleshooting efficiency, allows you to connect to a database over Secure Sockets Layer (SSL), supports geographic information system (GIS) data types of OceanBase Database, and supports parallel import of DDL files in OceanBase Database V4.2.1 and later.

Version information

• Current version: V4.3.1

• Previous version: V4.3.0

• Release date: July 4, 2024

Supported OceanBase Database versions

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

Considerations

When you upgrade OBLOADER & OBDUMPER from a version earlier than V4.3.0 to V4.3.1, take note of the following considerations:

• The following command-line option adjustments are made to reduce learning costs:

  • The --storage-uri option is deprecated. Its functionality is integrated into the -f/--file-path option.

    Note that this deprecated option can still be used, but we recommend that you replace it in the following ways:

    • To use the local file system, specify -f '/home/admin/foo/bar'.

    • To use Alibaba Cloud Object Storage Service (OSS), specify -f 'oss://mybucket/foo/bar?endpoint=myendpoint&access-key=myak&secret-key=mysk'.

    When you export database object definitions and table data to another file system instead of the local file system, by default, OBDUMPER creates a temporary directory named tmp in its root directory to store temporary files. You can use the --tmp-path option to manually specify a temporary directory.

  • The default value of the --compact-schema option is true. When you export data from OceanBase Database V4.0.0 or later, you can specify this option for OBDUMPER to obtain DDL statements by using the SHOW CREATE TABLE ... statement, instead of constructing DDL statements based on metadata queried from views, which is the default schema export strategy.

    This adjustment is made for compatibility with changes of some standard views in OceanBase Database to improve the overall performance and support columnar storage. You can still set the --compact-schema option to false to use the default schema export strategy.

• When the amount of data in a table does not reach the block size specified by the --block-size option, which is 1 GB by default, the exported file is named in the format of <table name>.<file name extension> in V4.3.1, and in the format of <table name>.<block sequence number>.<file name extension> in earlier versions. Assume that you want to export table data in the CSV format:

  • If the amount of data in the t_test table is 500 MB, which does not exceed the default value 1 GB of the --block-size option, only one file named t_test.csv is generated after the export.

  • If the amount of data in the t_test table is 1.2 GB, which exceeds the default value 1 GB of the --block-size option, two files respectively named t_test.0.csv and t_test.1.csv are generated after the export.

When you upgrade OBLOADER & OBDUMPER from a version earlier than V4.2.8 to V4.3.1, take note of the following considerations:

• OBLOADER & OBDUMPER allows you to specify the value of -u/--user in the three-segment format of <user>@<tenant>#<cluster>. For backward compatibility, you can still use the -t/--tenant and -c/--cluster options.

• The behavior for parsing POS files is changed. In V4.3.1, the parser processes whole lines. In other words, in addition to the byte length defined in the control file, the parser further reads a line break for each line. You may need to modify the control file to adapt to this change. You can run the following command to calculate the number of bytes in the first line of the example.dat file:

  expr $(head -n 1 example.dat | wc -c) - 1
  

When you upgrade OBLOADER & OBDUMPER from a version earlier than V4.2.7 to V4.3.1, take note of the following considerations:

• The --file-name option is deprecated.

  • You do not need to merge sub-files exported from a single table. To export the data of a table to a single file, you can set the --block-size option to 0 to specify not to split the exported table data into file blocks.

    Note that although the tool does not limit the file size, some file systems or object storage services still limit the size of a single file. For example, in the ext2, ext3, and ext4 file systems, the size of a single file is limited to 2 TB.

  • By default, the tool generates a complex nested directory structure. You can specify the --no-nested-dir option so that the tool does not generate nested subdirectories.

• The --upload-behavior option is deprecated. For information about the write strategies of different data sources, see the feature description in the following sections.

• After the file write behavior is changed in OBLOADER & OBDUMPER V4.2.7, the tool has to downgrade the export performance to ensure that the number of exported files is predictable. If you want to improve the export performance, you can enable parallel write so that the tool writes the data of each table to multiple sub-files, which cannot be merged into one file. For more information, see the corresponding description in versions earlier than V4.2.7. The procedure is as follows:

  1. Use a text editor to open the obdumper file in the bin directory in the root directory of the tool.

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

New features

OBLOADER

• OBLOADER allows you to import SQL text files that store data in the format of batch insert statements. You can use the --sql option to specify the file format.

• OBLOADER generates SQL logs in real time and stores them in the sql-monitor.log file in the default log path. You can specify the log path in the log4j2.xml file stored in the conf directory under the root directory of OBLOADER.

• The SM3 preprocessing function is added to the control file to convert data formats during the import.

• OBLOADER allows you to connect to a database over SSL.

• OBLOADER allows you to import GIS data in both the MySQL compatible and the Oracle compatible modes of OceanBase Database.

• OBLOADER supports parallel import of DDL files in OceanBase Database V4.2.1 and later.

OBDUMPER

• OBDUMPER generates SQL logs in real time and stores them in the sql-monitor.log file in the default log path. You can specify the log path in the log4j2.xml file stored in the conf directory under the root directory of OBDUMPER.

• The SM3 preprocessing function is added to the control file to convert data formats during the export.

• OBDUMPER allows you to connect to a database over SSL.

• OBDUMPER allows you to export GIS data in both the MySQL compatible and the Oracle compatible modes of OceanBase Database.

Bug fixes

• Fixed the issue where data cannot be exported in Oracle tenants of OceanBase Database earlier than V4.0.0.0.

• Fixed the issue where the definitions of temporary tables cannot be exported when no data format is specified.

• Fixed the issue where garbled characters appear during the export with the --pos option specified because Chinese characters are truncated by the position() function.

• Fixed the issue where data that does not match the data format specified by the --par option is not recorded in logs during the import.

• Fixed the issue where INT96 data cannot be parsed during the import with the --par option specified.

• Fixed the issue where log errors generated during the import of schema files obtained by mysqldump are inaccurate.

Known issues

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

• If you do not specify the password of the sys tenant on the CLI, 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 CLI, OBDUMPER cannot export index definitions from OceanBase Database of a version earlier than V2.2.50 in the Oracle compatible mode.

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

• If you do not specify the password of the sys tenant on the CLI, OBDUMPER cannot export definitions of unique indexes on partitioned tables from OceanBase Database V2.2.70 or versions later than V2.2.70 and earlier than V4.0.0 in the Oracle compatible mode.

• OBDUMPER cannot export PL object definitions from OceanBase Database of a version earlier than V2.2.30 in the MySQL 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 the MySQL compatible mode of OceanBase Database, after you enable case sensitivity for object names such as table names and view names, objects are imported or exported in a case-insensitive manner.

• The high-availability 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. Therefore, we recommend that you do not use this option.

• If you use direct load in a multi-table restore scenario, the performance may be undesirable.

Considerations

• In a CUT file, each data record is stored in a separate line. When you specify the --cut option on the OBDUMPER CLI, 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 OBLOADER CLI, 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 into the database.

• If you specify the --logical-database option on the CLI, the definition of a random physical database shard is exported and the shard cannot be directly imported into 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 CLI 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 CLI 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 a MySQL tenant of OceanBase Database, OceanBase Connector/J converts the zero values of date and time type fields 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 datetime fields, but cannot identify whether the original data is NULL or zero values during the 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 non-zero approximate 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-value data is exported. Take note of this consideration on zero values when you export data of the DATE, DATETIME, TIME, YEAR, or TIMESTAMP type from a MySQL tenant of OceanBase Database.

• 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 tenants of OceanBase Database V2.2.30 or earlier. Definitions of unique indexes on partitioned tables cannot be exported for Oracle tenants of OceanBase Database of a version ranging from V2.2.70 (inclusive) to V4.0.0.
  Oracle compatible	Tables, views, triggers, synonyms, sequences, stored procedures, functions, packages, table groups, and types are supported.