Failed to Import MySQL Data Using HammerDB

Symptom

MySQL data fails to be imported through HammerDB.

Key Process and Cause Analysis

A possible cause is that the default HammerDB configuration does not match the system environment. You can import data using TBL files.

Conclusion and Solution

To solve this problem, import the data using TBL files.

In the HammerDB-3.2/modules/tpchcommon-1.0.tm file in the HammerDB installation path, change the months in line 143 to two-digit numbers. For example, change JAN to 01, or FEB to 02.

Generate TBL files.

dbset db mysql
dbset bm tpc-h
dgset directory /data/tbl
dgset scale_fact 1
dgset vu 1
datagenrun

Command	Description
dgset directory /data/tbl	Specifies the path for storing the TBL files. The path must exist and have available space.
dgset scale_fact 1	Specifies the data volume.
dgset vu 1	Specifies the number of concurrent tasks, which is related to the number of generated TBL files.
datagenrun	Creates TBL files.

View the generated TBL files in the tbl directory.

cd /data/tbl

Copy the generated TBL files to the database server.
In this test, the client and server are installed on the same host. Therefore, you do not need to copy data.

Create a database on the database server and create tables based on the test model. You can view the OLAP's test script in src/mysql/mysqlolap.tcl in the HammerDB installation path.

Create tables (using row-store by default).

-- Create a TPC-H database and tables, and add the primary key and foreign key.

CREATE DATABASE mysql_tpch;
USE mysql_tpch;

CREATE TABLE REGION (
R_REGIONKEY INT NOT NULL,
R_NAME CHAR(25) BINARY NULL,
R_COMMENT VARCHAR(152) BINARY NULL,
PRIMARY KEY (R_REGIONKEY)
);

CREATE TABLE NATION (
N_NATIONKEY INT NOT NULL,
N_NAME CHAR(25) BINARY NULL,
N_REGIONKEY INT NULL,
N_COMMENT VARCHAR(152) BINARY NULL,
PRIMARY KEY (N_NATIONKEY),
FOREIGN KEY NATION_FK1(N_REGIONKEY) REFERENCES REGION(R_REGIONKEY)
);

CREATE TABLE SUPPLIER (
S_SUPPKEY INT NOT NULL,
S_NATIONKEY INT NULL,
S_COMMENT VARCHAR(102) BINARY NULL,
S_NAME CHAR(25) BINARY NULL,
S_ADDRESS VARCHAR(40) BINARY NULL,
S_PHONE CHAR(15) BINARY NULL,
S_ACCTBAL DECIMAL(10,2) NULL,
PRIMARY KEY (S_SUPPKEY),
FOREIGN KEY SUPPLIER_FK1(S_NATIONKEY) REFERENCES NATION(N_NATIONKEY)
);

CREATE TABLE PART (
P_PARTKEY INT NOT NULL,
P_TYPE VARCHAR(25) BINARY NULL,
P_SIZE INT NULL,
P_BRAND CHAR(10) BINARY NULL,
P_NAME VARCHAR(55) BINARY NULL,
P_CONTAINER CHAR(10) BINARY NULL,
P_MFGR CHAR(25) BINARY NULL,
P_RETAILPRICE DECIMAL(10,2) NULL,
P_COMMENT VARCHAR(23) BINARY NULL,
PRIMARY KEY (P_PARTKEY)
);

CREATE TABLE CUSTOMER (
C_CUSTKEY INT NOT NULL,
C_MKTSEGMENT CHAR(10) BINARY NULL,
C_NATIONKEY INT NULL,
C_NAME VARCHAR(25) BINARY NULL,
C_ADDRESS VARCHAR(40) BINARY NULL,
C_PHONE CHAR(15) BINARY NULL,
C_ACCTBAL DECIMAL(10,2) NULL,
C_COMMENT VARCHAR(118) BINARY NULL,
PRIMARY KEY (C_CUSTKEY),
FOREIGN KEY CUSTOMER_FK1(C_NATIONKEY) REFERENCES NATION(N_NATIONKEY)
);

CREATE TABLE PARTSUPP (
PS_PARTKEY INT NOT NULL,
PS_SUPPKEY INT NOT NULL,
PS_SUPPLYCOST INT NOT NULL,
PS_AVAILQTY INT NULL,
PS_COMMENT VARCHAR(199) BINARY NULL,
PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
FOREIGN KEY PARTSUPP_FK1(PS_PARTKEY) REFERENCES PART(P_PARTKEY),
FOREIGN KEY PARTSUPP_FK2(PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY)
);

CREATE TABLE ORDERS (
O_ORDERDATE DATE NULL,
O_ORDERKEY INT NOT NULL,
O_CUSTKEY INT NOT NULL,
O_ORDERPRIORITY CHAR(15) BINARY NULL,
O_SHIPPRIORITY INT NULL,
O_CLERK CHAR(15) BINARY NULL,
O_ORDERSTATUS CHAR(1) BINARY NULL,
O_TOTALPRICE DECIMAL(10,2) NULL,
O_COMMENT VARCHAR(79) BINARY NULL,
PRIMARY KEY (O_ORDERKEY),
FOREIGN KEY ORDERS_FK1(O_CUSTKEY) REFERENCES CUSTOMER(C_CUSTKEY),
INDEX ORDERS_DT_IDX (O_ORDERDATE)
);

CREATE TABLE LINEITEM (
L_SHIPDATE DATE NULL,
L_ORDERKEY INT NOT NULL,
L_DISCOUNT DECIMAL(10,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(10,2) NOT NULL,
L_SUPPKEY INT NOT NULL,
L_QUANTITY INT NOT NULL,
L_RETURNFLAG CHAR(1) BINARY NULL,
L_PARTKEY INT NOT NULL,
L_LINESTATUS CHAR(1) BINARY NULL,
L_TAX DECIMAL(10,2) NOT NULL,
L_COMMITDATE DATE NULL,
L_RECEIPTDATE DATE NULL,
L_SHIPMODE CHAR(10) BINARY NULL,
L_LINENUMBER INT NOT NULL,
L_SHIPINSTRUCT CHAR(25) BINARY NULL,
L_COMMENT VARCHAR(44) BINARY NULL,
PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),
FOREIGN KEY LINEITEM_FK1(L_ORDERKEY) REFERENCES ORDERS(O_ORDERKEY),
FOREIGN KEY LINEITEM_FK2(L_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY),
FOREIGN KEY LINEITEM_FK3(L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY, PS_SUPPKEY),
FOREIGN KEY LINEITEM_FK4(L_PARTKEY) REFERENCES PART(P_PARTKEY),
INDEX LI_SHP_DT_IDX (L_SHIPDATE),
INDEX LI_COM_DT_IDX (L_COMMITDATE),
INDEX LI_RCPT_DT_IDX (L_RECEIPTDATE)
);

Import the TBL files as the corresponding tables to the database.

USE mysql_tpch;
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/data/tbl/customer_1.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/lineitem_1.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/nation_1.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/orders_1.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/partsupp_1.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/part_1.tbl' INTO TABLE PART FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/region_1.tbl' INTO TABLE REGION  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/data/tbl/supplier_1.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
SET FOREIGN_KEY_CHECKS=1;

The data import is complete.

Parent topic: Troubleshooting