I’ve been working with dbt2 benchmarking tool recently and had a few issues I thought I’d detail here for anyone else having the same issues. I was attempting to load the dbt2 database with test data;

sh mysql_load_db.sh --database dbt2 --path /tmp/dbt2-w3 --mysql-path /usr/bin/mysql --user root --password secret

The script threw the following error;

Loading of DBT2 dataset located in /tmp/dbt2-w3 to database dbt2.

DB_ENGINE: INNODB
DB_HOST: localhost
DB_PORT: 3306
DB_USER: root
DB_SOCKET:
PARTITION:
PARTITION_NO:
NDB_DISK_DATA:
USING_HASH:
LOGFILE_GROUP: lg1
TABLESPACE: ts1
TABLESPACE SIZE: 12G
LOGFILE SIZE: 256M
DROP/CREATE Database
/usr/bin/mysql -p rootpa55 -h localhost -u root --protocol=tcp --port 3306 -e "drop database if exists dbt2"
Enter password:
ERROR 1049 (42000): Unknown database 'secret'
ERROR: rc=1
SCRIPT INTERRUPTED

Whatever combination or order of switches I provided the script it always complained. To get the script to run I simply hard-coded the appropriate switch in the call to the mysql client;

vi mysql_load_db.sh

Search for a function called command_exec and change this line…

eval "$1";

To the following;

eval "$1 -psecret";

Replacing “secret” for the appropriate mysql user password. While this solved the initial problem it did uncover another problem with the load;

sh mysql_load_db.sh --database dbt2 --path /tmp/dbt2-w3 --mysql-path /usr/bin/mysql --user root

DB_ENGINE: INNODB
DB_HOST: localhost
DB_PORT: 3306
DB_USER: root
DB_SOCKET:
PARTITION:
PARTITION_NO:
NDB_DISK_DATA:
USING_HASH:
LOGFILE_GROUP: lg1
TABLESPACE: ts1
TABLESPACE SIZE: 12G
LOGFILE SIZE: 256M
DROP/CREATE Database

Creating table STOCK in INNODB
Creating table ITEM in INNODB
Creating table ORDER_LINE in INNODB
Creating table ORDERS in INNODB
Creating table NEW_ORDER in INNODB
Creating table HISTORY in INNODB
Creating table CUSTOMER in INNODB
Creating table DISTRICT in INNODB
Creating table WAREHOUSE in INNODB

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
ERROR 1292 (22007) at line 1: Incorrect datetime value: ‘’ for column ‘ol_delivery_d’ at row 20083
ERROR: rc=1
SCRIPT INTERRUPTED
To fix this edit the mysql_load_db.sh and search for the command_exec function. Change this line…

"$MYSQL $DB_NAME -e \"LOAD DATA $LOCAL INFILE \\\"$DB_PATH/$FN.data\\\" \

To…

"$MYSQL $DB_NAME -e \"LOAD DATA $LOCAL INFILE \\\"$DB_PATH/$FN.data\\\" IGNORE\

The IGNORE will skip any errors so the rest of the process does not fail. Now I was able to load my data!

sh mysql_load_db.sh --database dbt2 --path /tmp/dbt2-w3 --mysql-path /usr/bin/mysql --user root

Loading of DBT2 dataset located in /tmp/dbt2-w3 to database dbt2.

DB_ENGINE: INNODB
DB_HOST: localhost
DB_PORT: 3306
DB_USER: root
DB_SOCKET:
PARTITION:
PARTITION_NO:
NDB_DISK_DATA:
USING_HASH:
LOGFILE_GROUP: lg1
TABLESPACE: ts1
TABLESPACE SIZE: 12G
LOGFILE SIZE: 256M
DROP/CREATE Database

Creating table STOCK in INNODB
Creating table ITEM in INNODB
Creating table ORDER_LINE in INNODB
Creating table ORDERS in INNODB
Creating table NEW_ORDER in INNODB
Creating table HISTORY in INNODB
Creating table CUSTOMER in INNODB
Creating table DISTRICT in INNODB
Creating table WAREHOUSE in INNODB

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
Loading table orders
Loading table stock
Loading table warehouse