Data from export table failed to load via LOAD DATA

[bheni]

Here I clone the data, export the tables to two files and then grab the create table statements needed when I create the tables on the new server

~/datasets>dolt clone post-no-preference/options
596,063 of 596,063 chunks complete. 0 chunks being downloaded currently.
~/datasets/options>cd options
~/datasets/options>dolt table export option_chain option_chain.psv
Successfully exported data.
~/datasets/options>dolt table export volatility_history volatility_history.psv
Successfully exported data.
~/datasets/options>dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
options> show create table option_chain;
+--------------+---------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------+---------------------------------------------------------------+
| option_chain | CREATE TABLE `option_chain` (
  `date` date NOT NULL,
  `act_symbol` text collate utf8mb4_0900_ai_ci NOT NULL,
  `expiration` date NOT NULL,
  `strike` decimal(7,2) NOT NULL,
  `call_put` text collate utf8mb4_0900_ai_ci NOT NULL,
  `bid` decimal(7,2),
  `ask` decimal(7,2),
  `vol` decimal(5,4),
  `delta` decimal(5,4),
  `gamma` decimal(5,4),
  `theta` decimal(5,4),
  `vega` decimal(5,4),
  `rho` decimal(5,4),
  PRIMARY KEY (`date`,`act_symbol`,`expiration`,`strike`,`call_put`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+--------------+---------------------------------------------------------------+
options> show create table volatility_history;
+--------------------+---------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------+---------------------------------------------------------+
| volatility_history | CREATE TABLE `volatility_history` (
  `date` date NOT NULL,
  `act_symbol` text collate utf8mb4_0900_ai_ci NOT NULL,
  `hv_current` decimal(5,4),
  `hv_week_ago` decimal(5,4),
  `hv_month_ago` decimal(5,4),
  `hv_year_high` decimal(5,4),
  `hv_year_high_date` date,
  `hv_year_low` decimal(5,4),
  `hv_year_low_date` date,
  `iv_current` decimal(5,4),
  `iv_week_ago` decimal(5,4),
  `iv_month_ago` decimal(5,4),
  `iv_year_high` decimal(5,4),
  `iv_year_high_date` date,
  `iv_year_low` decimal(5,4),
  `iv_year_low_date` date,
  PRIMARY KEY (`date`,`act_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+--------------------+---------------------------------------------------------+

Start a new dolt sql on 127.0.0.1:3306

Here I connect to the server, create the db, create the tables, and try to load the data.

~/datasets/options>mysql --host=127.0.0.1 --port=3306 --user=dolt  --local-infile 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.9-Vitess

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>CREATE DATABASE options;
Query OK, 1 row affected (0.050 sec)

MySQL [(none)]> use options
Database changed
MySQL [options]> CREATE TABLE `option_chain` (
    ->   `date` date NOT NULL,
    ->   `act_symbol` text collate utf8mb4_0900_ai_ci NOT NULL,
    ->   `expiration` date NOT NULL,
    ->   `strike` decimal(7,2) NOT NULL,
    ->   `call_put` text collate utf8mb4_0900_ai_ci NOT NULL,
    ->   `bid` decimal(7,2),
    ->   `ask` decimal(7,2),
    ->   `vol` decimal(5,4),
    ->   `delta` decimal(5,4),
    ->   `gamma` decimal(5,4),
    ->   `theta` decimal(5,4),
    ->   `vega` decimal(5,4),
    ->   `rho` decimal(5,4),
    ->   PRIMARY KEY (`date`,`act_symbol`,`expiration`,`strike`,`call_put`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
Query OK, 0 rows affected (0.061 sec)

MySQL [options]> CREATE TABLE `volatility_history` (
    ->   `date` date NOT NULL,
    ->   `act_symbol` text collate utf8mb4_0900_ai_ci NOT NULL,
    ->   `hv_current` decimal(5,4),
    ->   `hv_week_ago` decimal(5,4),
    ->   `hv_month_ago` decimal(5,4),
    ->   `hv_year_high` decimal(5,4),
    ->   `hv_year_high_date` date,
    ->   `hv_year_low` decimal(5,4),
    ->   `hv_year_low_date` date,
    ->   `iv_current` decimal(5,4),
    ->   `iv_week_ago` decimal(5,4),
    ->   `iv_month_ago` decimal(5,4),
    ->   `iv_year_high` decimal(5,4),
    ->   `iv_year_high_date` date,
    ->   `iv_year_low` decimal(5,4),
    ->   `iv_year_low_date` date,
    ->   PRIMARY KEY (`date`,`act_symbol`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
Query OK, 0 rows affected (0.054 sec)

MySQL [options]> SET GLOBAL local_infile=1;
Query OK, 1 row affected (0.049 sec)

MySQL [options]> LOAD DATA LOCAL INFILE 'volatility_history.psv' INTO TABLE volatility_history;
ERROR 1048 (HY000): column name 'act_symbol' is non-nullable but attempted to set a value of null
MySQL [options]> LOAD DATA LOCAL INFILE 'option_chain.psv' INTO TABLE option_chain;
ERROR 1048 (HY000): column name 'act_symbol' is non-nullable but attempted to set a value of null

[bheni]

I'm guessing there are empty strings being exported to the psv as || which is being treated as null by LOAD DATA.

[VinaiRachakonda]

The following command worked for me for a smaller subset of the psv.

LOAD DATA LOCAL INFILE 'option_chain.psv' INTO TABLE option_chain FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES;

[VinaiRachakonda]

Can we close @bheni

[fulghum]

@VinaiRachakonda – were you able to repro that error? Should we at least add a test (or confirm we have one) that exercises exports/imports with empty strings? Seems like a valid problem Brian found and just wanted to make sure we learned from it.

[bheni]

I think the real problem here is it took multiple minutes to get that error message, and that error message isn't really decipherable. It looks like a foreign key constraint violation... but that's not the real issue and there is very little info here to debug it.

[timsehn]

Gonna close.