FAQ

What are the differences among the files in the CSV, SQL, DDL, CUT, POS, and MIX formats?

What is the relationship between the format of a data file and the extension of the file?

All data files are named in the table.group.sequence.suffix format.

OBLOADER supports six data file formats: CSV, SQL, DDL, CUT, POS, and MIX. The default extension of a file in the CSV format is .csv. The default extension of a file in the SQL format is .sql. The default extension of a file in the DDL format is -schema.sql. You can use any extension for all data formats, except the DDL format.

For example, you can use test.dat as the name of a file in the CSV format, as long as the content of the file is in the standard CSV format. When you import a file, you must specify its extension by adding the --file-suffix '.dat' option to the command. Otherwise, an error is reported to indicate that the data file is not found.

What do I need to pay attention to when I use OBLOADER to import data that is exported by using a third-party tool such as Mydumper and SQL Developer?

When you import a file in the CSV format, set the valid symbols, such as the row delimiters, column delimiters, delimiters, and escape characters.
When you import a file in the SQL format, make sure that the file contains only INSERT statements and each statement occupies one line without line breaks.
When you import files in the DDL format, make sure that one file contains only the definition of one object without other unnecessary content such as comments and switch statements.
Set the date and time in the valid format before you import data. This prevents import failures caused by an invalid date and time format.

What do I need to pay attention to when I specify the --table option?

You can use the --table option to specify the table name.

When you specify multiple table names, separate them with commas (,). The letter cases of the table name and the file name must be the same.

Example:

In MySQL mode, table names are in lowercase by default. In this case, the table name specified by the --table option must be in lowercase, and the file name specified by the -f option must also be in lowercase.
In Oracle mode, table names are in uppercase by default. In this case, the table name specified by the --table option must be in uppercase, and the file name specified by the -f option must also be in uppercase.

What are the differences between using OBLOADER in Alibaba Cloud and Private Cloud?

Difference in proxyro permissions:
- In Alibaba Cloud (limited mode), you have limited proxyro permissions and can export only table data and the schemas of tables and views.
- In Private Cloud (unlimited mode), you have unlimited proxyro permissions and can export the schemas and data of all objects in a database.
Difference in connection methods:
- In Alibaba Cloud, OBLOADER is connected by using the --public-cloud option.
- In Private Cloud, OBLOADER is connected by using the --sys-password option or a key file.
  
  The limited mode does not support all features of OBLOADER. In this mode, the performance and stability of OBLOADER are also affected. In addition, only OceanBase Database V2.2.30 and later support throttling on the server. Therefore, when you use the limited mode, run the following command to modify the throttling settings on the server:
```
alter system set freeze_trigger_percentage=50;
alter system set minor_merge_concurrence=64;
alter system set writing_throttling_trigger_percentage=80 t
```

How do I run OBLOADER or OBDUMPER in debugging mode?

Run the debugging script, such as obloader-debug and obdumper-debug, in the bin directory.

After data in a large table is exported by partition into several files with indefinite extensions, how do I import them by using OBLOADER?

You can specify a regular expression for file extensions in the command.

For example, if the names of the data files that you want to import are test.tbl.0, test.tbl.1, and test.tbl, you can specify the following options in the command: --table test --file-suffix '^.{1}\\w+.{1}\\d+'. You must write a valid regular expression. Otherwise, the system prompts that no data files are found.

How do I use OBLOADER to import a data file whose name is different from the table?

Generally, the -f option specifies the absolute path of a visible, readable, and writable directory. Example: --table 't1,2' -f '/root/output'. However, you can also use the -f option to specify a data file. Example: --table 'test' -f '/output/hello.csv'.

Why does the control file configured for a table fail to take effect when I import data to or export data from the table?

The name of the control file must be identical to the table name. By default, table names are in lowercase in MySQL mode and in uppercase in Oracle mode.

Why are command-line options not parsed as expected when I run the OBLOADER or OBDUMPER script?

This issue may occur if command-line options contain special characters. If you run OBLOADER or OBDUMPER on a Linux operating system, a greater-than sign (>) in the password may cause the loss of all operational logs. The greater-than sign (>) is a redirection operator. Therefore, you must use appropriate quotation marks to resolve this issue on different operating systems. For more information, see the next question.

When do I need to enclose options in single quotation marks (' ') or double quotation marks (" ") when I run the OBLOADER or OBDUMPER script?

We recommend that you enclose complex options such as passwords and cluster names in quotation marks.

On the Windows operating system, use double quotation marks (" "). Example: --table "*".
On a Linux-like operating system, use single quotation marks (' '). Example: --table '*'.

What do I do if external files fail to be imported due to invalid file formats?

Import an SQL file.

The SQL file can contain only INSERT statements but not comments or SET statements. Each INSERT statement cannot occupy multiple lines. If the file contains DDL or DML statements, we recommend that you run MySQL commands to import it.
Import a CSV file.

The content of the CSV file must be in the standard CSV format and contain column and row delimiters. We recommend that you enclose string-type column values in quotation marks. By default, you need to use single quotation marks (' '). In addition, you must escape the column values enclosed by single quotation marks (' ').

What do I need to pay attention to when I import table schemas or data?

When you import table schemas, set the concurrency of the --threads option to no more than 2. The concurrent DDL operations may not speed up the data import. In OceanBase Database, DDL statements are executed in series, and each DDL statement takes 1 second to execute on average.
OBLOADER may use a large amount of memory when a large amount of data is imported.
- First, you can open the OBLOADER script in the bin directory and modify the following two Java virtual machine (JVM) parameters: -Xms4G and -Xmx4G. The -Xms4G parameter specifies the initial heap memory, and the -Xmx4G parameter specifies the maximum heap memory for JVM. We recommend that you set both parameters to 60% of the available physical memory.
- Second, you can specify the --threads option in the command to control concurrency. The default value is twice the number of logical CPU cores.
- Finally, as the import performance is affected by the number of indexes on the table, we recommend that you set only the primary key and unique key when you create a table, and create indexes after data is imported.
The incremental memory write speed of the tenant may also affect data import.
- Insufficient incremental memory may trigger major or minor compaction. Prevent major compaction because it greatly affects import performance. You can enable minor compaction of memory and set the number of minor compaction times to more than 100. This way, minor compaction is available for at least 24 hours.
- If the incremental memory usage reaches the throttling threshold specified for the tenant, the import performance falls.
- If the incremental memory usage reaches the upper limit, data import may fail. We recommend that you set the tenant throttling threshold to more than 90. As the parameters of minor compaction are related to the size and write speed of the tenant memory, you need to optimize the parameters as needed.

What do I do if garbled characters occur after I truncate long strings of Chinese characters?

Use GBK for character encoding in a proper manner. If the application can distinguish carriage returns from line feeds, 90% of the issues can be resolved. Note

Dirty data may not be simulated for customers.

What do I do if OBLOADER imports data to an incorrect table?

OBLOADER and OBDUMPER V2.1.14 and later are not allowed to export data to a non-empty directory.
If the default data export directory is empty, you can specify the --skip-check-dir option to skip the empty-directory check.
- Bug example:
  
  Note
  
  The -f option is used to specify a directory. If the -f option is used to specify a specific file, erroneous writes are prevented.
  1. The name of a table is the prefix of another table, such as test and testtt, and the two tables have the same column types, regardless of whether they have different column names and numbers of columns.
  2. OBDUMBER exports the schemas and data of different tables from multiple databases to the same directory.
```
-- Schemas of the test and testtt tables:

create table test (
 c1 bigint,
  c2 varchar(20),
  ....
);

create table testtt (
 c1 bigint,
  c2 varchar(20)
); 
```
- Background of bug discovery:
  
  The POC test result indicates that the LIST_LOAN_BALANCE_S and LIST_LOAN_BALANCE_SEARCH tables have similar names, where the name of the former table is the prefix of the latter. They also have similar schemas. As a result, data is written to the incorrect table.

What do I do if the following error is reported when I start OBLOADER or OBDUMPER: Access denied for user 'root'@'xxx.xxx.xxx.xxx'?

By default, OBLOADER and OBDUMPER require the permissions of the root user. If you have set a password for the root user under the sys tenant in the cluster, specify the configured password in the --sys-password option in the command.

What do I do if the following error is reported when OBLOADER is running: Over tenant memory limits?

Increase the percentage of tenant memory, or specify a lower value for the --thread option to reduce the concurrency.

What do I do if the following error is reported when OBLOADER is running: No memory or reach tenant memory limit?

Increase the percentage of tenant memory, or specify a lower value for the --thread option to reduce the concurrency.

What do I do if the following error is reported when OBLOADER is running: No tables are exists in the schema: "xxx"?

For example, if you specify the --table 't1,t2' option, t1 and t2 must be the table names that you have already defined in the database. Make sure that the letter case of the specified table names must be consistent with that of the table names defined in the database.

By default, table names are in lowercase in MySQL mode and in uppercase in Oracle mode. To specify a lowercase table name in Oracle mode, you must include the table name in brackets ([ ]). Example: --table '[t1]'.

What do I do if the following error is reported when OBLOADER is running: The xxx files are not found in the path: "xxx"?

The name of the data file in the directory specified by the -f option must be identical to the table name.

By default, table names are in lowercase in MySQL mode and in uppercase in Oracle mode. For example, if you specify --table 't1', the data file in the directory must be named t1 in a supported format, such as t1.csv and t1.sql. Other file names such as T1.csv are invalid.

What do I do if the following error is reported when OBLOADER is running: The manifest file: "xxx" is missing?

MANIFEST.bin is a metadata file that is generated when you export data by using OBDUMPER. If you use other tools to export data, no metadata file is generated. You can specify the --external-data option to skip the check of metadata files.

What do I do if the following error is reported when I use OBLOADER to import a delimited text file: Index: 0, Size: 0?

This error occurs if the data file contains carriage returns or line feeds. You must run a script to delete the carriage returns or line feeds from the data file before you import it.