MySQL 8.0 cannot insert NULL values to date field I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine.

I'm using this query:

LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat'
INTO TABLE Item FIELDS TERMINATED BY '|';


I'm getting an error message:

ERROR 1292 (22007) at line 1: Incorrect date value: 'NULL' for column 'i_rec_end_date' at row 1


The table was created by this query:

CREATE TABLE IF NOT EXISTS Item(
    i_item_sk integer ,
    i_item_id char(16) NOT NULL,
    i_rec_start_date date,
    i_rec_end_date date,
    i_item_desc varchar(200),
    i_current_price decimal(7,2),
    i_wholesale_cost decimal (7,2),
    i_brand_id integer,
    i_brand char(50),
    i_class_id integer default ,
    i_class char(50),
    i_category_id integer,
    i_category char(50),
    i_manufact_id integer,
    i_manufact char(50),
    i_size char(20),
    i_formulation char(20),
    i_color char(20),
    i_units char(10),
    i_container char(10),
    i_manager_id integer,
    i_product_name char(50),
    PRIMARY KEY (i_item_sk)
);


The data at line 1 is:

1|AAAAAAAABAAAAAAA|1997-10-27|NULL|Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el|27.02|23.23|5003002|exportischolar #2|3|pop|5|Music|52|ableanti|N/A|3663peru009490160959|spring|Tsp|Unknown|6|ought|
It's because MySQL is actually trying to insert the literal string 'NULL' into a DATE column, not the actual value NULL.

Your choices are:


Global search for |NULL| in your file and replace with ||
Change your LOAD DATA statement to deal with it:



  LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE
  Item FIELDS TERMINATED BY '|'
      (i_item_sk integer ,
                                      i_item_id,
                                      i_rec_start_date,
                                      @i_rec_end_date,
                                      i_item_desc,
                                      i_current_price,
                                      i_wholesale_cost,
                                      i_brand_id,
                                      i_brand,
                                      i_class_id,
                                      i_class,
                                      i_category_id,
                                      i_category,
                                      i_manufact_id,
                                      i_manufact,
                                      i_size,
                                      i_formulation,
                                      i_color,
                                      i_units,
                                      i_container,
                                      i_manager_id,
                                      i_product_name )  SET
      i_rec_end_date = nullif(@i_rec_end_date,'NULL');