使用HammerDB直接导入数据时，导入MySQL数据失败的解决方法

问题现象描述

使用HammerDB直接导入数据时，导入MySQL数据失败。

关键过程、根本原因分析

可能是HammerDB默认配置不匹配系统环境所致，可以采用tbl文件的方式导入数据。

结论、解决方案及效果

采用tbl文件的方式导入，解决此问题。

修改HammerDB安装路径下的“HammerDB-3.2/modules/tpchcommon-1.0.tm”，将143行中英文月份修改为2位数字。

生成tbl文件。

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

命令	含义
dgset directory /data/tbl	tbl文件存储路径，路径需存在并存在剩余空间
dgset scale_fact 1	数据量
dgset vu 1	任务并发数，与生成的tbl文件个数相关
datagenrun	创建tbl

可以进入tbl目录下查看生成的.tbl文件。

cd /data/tbl

将生成的tbl文件拷贝至数据库服务端。
由于本次测试的客户端和服务端在同一台服务器上，所以不需要拷贝数据。

在数据库服务器上创建库并按测试模型建表，可在HammerDB安装路径下的“src/mysql/mysqlolap.tcl”中查看OLAP测试脚本。

建表（默认行存）：

--建tpch数据库及表，添加主键和外键

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)
);

在数据库中按表逐个导入tbl文件。

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;

导入数据完成。

父主题： 故障排除