Description:
When used in INSERT and other DML statements IGNORE clause allows to ignore both duplicate key/foreign key errors and data conversion errors.
In cases when duplicate/foreign key errors are ignored by INSERT IGNORE the
offending row is not inserted into table and thus no inconsistency occurs.
In cases when data conversion errors are ignored the value should be adjusted
to some valid value. But this doesn't happen for datetime values with zero dates. Also problems/inconsistencies are still possible then problematic value is actually based on values in other colums, i.e. when functional indexes or generated columns are used (see bug #95997 for example).
I think it would be nice to have version of IGNORE that would allow to ignore only duplicate key/foreign key errors and not data conversions errors/override strict mode.
How to repeat:
create table t1 (dt datetime);
insert into t1 values ('2001-01-00 00:00:00');
#ERROR 22007: Incorrect datetime value: '2001-01-00 00:00:00' for column 'dt' at row 1
insert ignore into t1 values ('2001-01-00 00:00:00');
#Warnings:
#Warning 1264 Out of range value for column 'dt' at row 1
# Observe inconsitent data
select * from t1;
# dt
# 0000-00-00 00:00:00