Why does "LOAD DATA INFILE ..." fail with formatted date strings?

Question

I am unable to get an export/import round-trip working for a single DATETIME formatted field. I must be able to load files with dates formatted as:

2/9/2022 0:00:00

Note the absence of leading '0' in the month, day, and hour field.

I'm attempting this simple round-trip in order to show myself that I understand the relevant date coercion behavior.

Here is a simple table definition:

CREATE TABLE `load_test` (
  `some_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Here is an INSERT statement that adds a single row to this table:

INSERT INTO `load_test` (
    `some_field`)
VALUE (
    str_to_date('2/9/2022 0:00:00', '%m/%d/%Y %H:%i:%s')
);

Here is a SELECT INTO... statement that dumps this into a CSV file:

(SELECT 'some_field')
UNION ALL
SELECT DATE_FORMAT(`some_field`, '%m/%d/%Y %H:%i:%s') INTO OUTFILE '/var/lib/mysql-files/store_test.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM `load_test`;

Here is what that file looks like:

"some_field"
"02/09/2022 00:00:00"

Here is my failing LOAD DATA INFILE ... statement:

LOAD DATA INFILE '/var/lib/mysql-files/store_test.csv' 
    INTO TABLE `load_test`
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS
    SET
        `some_field` = STR_TO_DATE(@`some_field`, '%m/%d/%Y %H:%i:%s');

When I execute this (in MySQL Workbench), I get the following complaint:

Error Code: 1292. Incorrect datetime value: 'some_field' for column 'some_field' at row 1

The documentation says that LOAD DATE INFILE ... is the counterpart to SELECT INTO.... I'm using the same date format string on both output and input, and I'm attempting to load the very same file that SELECT INTO stored earlier.

What am I missing?

Answer 1

After much wailing, gnashing of teeth, trial, and many errors I found the answer -- there may be other ways, but this works.

The issue is that in the SET clauses, I was referencing a name ("@some_field") that was defined.

Here is a form that works:

LOAD DATA INFILE '/var/lib/mysql-files/store_test.csv' 
    INTO TABLE `load_test`
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS
    (@`some_field`) SET
        `some_field` = STR_TO_DATE(@`some_field`, '%m/%d/%Y %H:%i:%s');

This has several implications. It appears to me that the SET clause must include a phrase for each column to be set. That means the parenthesized list that precedes the SET must have an entry of the form "@field_name" for each field in the file to be imported. There must be corresponding phrase in the SET clause that references each temporary name.

When I only specified the columns that needed special processing the others were each null.

Here the actual working LOAD DATA INFILE ... command that I needed:

LOAD DATA INFILE '/path/to/import/files/foobar.csv'
    INTO TABLE `3p_day_rate_t`
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS
    (
        @`area`,
        @`fips`,
        @`us-counties-cases`,
        @`us-counties_1-cases`,
        @`us-counties_1_date`,
        @`us-counties-date`,
        @`predicted_date`,
        @`population2019`,
        @`case_growth_per_capita`,
        @`interim_cases_term`
    )
    SET
        `area` = @`area`,
        `fips` = @`fips`,
        `us-counties-cases` = @`us-counties-cases`,
        `us-counties_1-cases` = @`us-counties_1-cases`,
        `us-counties-date` = STR_TO_DATE(@`us-counties-date`, '%m/%d/%Y %H:%i:%s'),
        `us-counties_1_date` = STR_TO_DATE(@`us-counties_1_date`, '%m/%d/%Y %H:%i:%s'),
        `predicted_date` = STR_TO_DATE(@`predicted_date`, '%m/%d/%Y %H:%i:%s'),
        `population2019` = @`population2019`,
        `case_growth_per_capita` = @`case_growth_per_capita`,
        `interim_cases_term` = @`interim_cases_term`

While tedious, this at least works. It was hard enough for me to find this working answer that the exercise is perhaps valuable to those who come later with the same question.