Incorrect datetime value while importing in to MySQL table

Question

I'm facing an issue while importing a .csv file into a MySQL table.

mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv' 
REPLACE INTO TABLE incidents columns terminated by ',' optionally 
enclosed by '"' ignore 1 lines;

ERROR 1292 (22007): Incorrect datetime value: '2021-08-29T04:18:35Z' 
for column 'incident_start' at row 1

But, I can import without an issue with IGNORE command.

mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv' 
IGNORE INTO TABLE incidents columns terminated by ',' optionally 
enclosed by '"' ignore 1 lines;

Query OK, 0 rows affected, 809 warnings (0.01 sec) 
Records: 266 Deleted: 0 Skipped: 266 Warnings: 809

My requirement is to replace/update the columns in case any update on previous values in the table.

Table Structure

+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| incident | varchar(12) | NO | PRI | NULL | |
| description | varchar(300) | YES | | NULL | |
| status | varchar(12) | YES | | NULL | |
| urgency | varchar(7) | YES | | NULL | |
| service | varchar(27) | YES | | NULL | |
| trigger | varchar(25) | YES | | NULL | |
| team | varchar(20) | YES | | NULL | |
| incident_start | datetime(6) | YES | | NULL | |
| incident_end | datetime(6) | YES | | NULL | |
| resolved_by | varchar(20) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+

How we can fix the issue of Incorrect datetime while importing .csv with replace option?

• matigo: The fastest method I've found for this sort of situation is to open the CSV in Excel, perform some string manipulation to convert the values to what I want (in this case, removing the T and Z characters from dates), then using the updated output as the source ...
• Randolph West: @matigo Or importing the CSV as-is, and doing that conversion in a staging table.

Answer 1

I got the solution for this. We just need to remove the STRICT_TRANS_TABLES mode while uploading the csv file. We can use the below commands for that

set session sql_mode = '';
set session sql_mode = 'NO_ENGINE_SUBSTITUTION';

One's it's done, we can upload the data with REPLACE

MariaDB [pagerduty]> LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' REPLACE INTO TABLE temp_incidents columns terminated by ',' optionally enclosed by '"' ignore 1 lines;
Query OK, 246 rows affected, 504 warnings (0.004 sec)
Records: 246 Deleted: 0 Skipped: 0 Warnings: 504

Note: The above SQL mode needs to set before uploading the file into the table everytime.