Error loading to MySQL TPC-H data via dbgen

Question

I am using https://github.com/electrum/tpch-dbgen to generate the 8 tables of TPC-H. However, for the PART table, when loading the data to MySQL via load infile command, I get via show warnings, tons of warnings such as:

| Note    | 1265 | Data truncated for column 'P_RETAILPRICE' at row 517 |

The sample of the data itself:

517|aquamarine rosy violet moccasin snow|Manufacturer#3|Brand#34|SMALL BURNISHED COPPER|30|WRAP CAN|1417.51|uses. |

and the table definition is taken from https://github.com/dragansah/tpch-dbgen/blob/master/tpch-create.sql

I load the data like so:

load data local infile '/full-path/part.tbl' into table PART columns terminated by '|';

How to fix this?

Answer 1

In the past I have had problems with the trailing | in the file. To remove all trailing | in your table files, use this command:

sed -i 's/|$//' *.tbl

If that works for you then you can modify the dbgen code to do this always. In the file dss.h, try adding a line like this:

#define EOL_HANDLING