Search code examples
mysqlmariadbload-data-infile

MariaDB LOAD DATA INFILE for null DATE field

I'm trying to load a tsv file into a mariadb table. One of the fields is a DATE that's nullable. From what I gather, load data infile uses "\N" to identify null content but in this case it's a no go:

ERROR 1292 (22007) at line 1: Incorrect date value: '\N' for column 'delete_date' at row 1

Any ideas how I can import that data without writing specific CASE WHENs for the import? It's a part of a script that loads multiple tables so I don't want to treat this as a special case.

Leaving the field blank is of course an error as well:

ERROR 1292 (22007): Incorrect date value: '' for column 'delete_date' at row 1

I assume it's possible to import null values for dates so I must be missing something.

Solution

  • taken from the mariadb knowledge base

    That error is thrown when the sql_mode server system variable is set to strict mode. If it's not set, you can enter a blank date - a warning is generated and '0000-00-00' will be inserted.

    If you can set strict mode off for the duration of the import, that should work, otherwise you'll need to replace the blank date columns in the CSV with NULL, or '0000-00-00'.