0

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|
1

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');

1

Plan A: See Phil's Answer

Plan B:

SHOW VARIABLES LIKE 'sql_mode';

Then think about changing the setting(s) relating to dates before doing the LOAD. Perhaps change it back after cleaning up the data.

Plan C:

LOAD into a separate table with a VARCHAR for that column. Then populate the 'real' table from it, fixing the "NULL" (or whatever) to whatever. Ditto for any other columns that need cleansing.

Plan D: (Shorter than C, but not as powerful for cleansing):

Use @variables in the LOAD, plus SET with an expression to set the column with whatever you want instead of the null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.