AWS Aurora: NO_ZERO_IN_DATE and/or NO_ZERO_DATE sql_mode does not error on query with 0 date

Question

We are seeing an issue that 0 dates are being inserted into the AWS Aurora database MySQL 8.0.28 (and 5.7 too).

The current SQL_MODE is not set to strict mode but is set explicitly to: `NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO`

Does anyone know if this is a known issue with AWS Aurora and how to get around it?

```
create table test 
(id int auto_increment, dateField datetime not null, primary key(id));

insert into test (id, dateField) 
values (null, '0000-00-00 00:00:00');

show warnings;
Level	Code	Message
Warning	1264	Out of range value for column 'dateField' at row 1

```

Leo K · Accepted Answer

Hmm, isn't that exactly what MySQL should do when NO_ZERO_DATE is specified? When not in strict mode, that setting should cause a warning for an all-zeroes date, which is what the output is showing. In strict mode, it would cause an error instead of a warning.

If you want the all-zeroes date to be accepted without warnings, then I believe you should disable the NO_ZERO_DATE option.

Ref: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_date

AWS Aurora: NO_ZERO_IN_DATE and/or NO_ZERO_DATE sql_mode does not error on query with 0 date

Add your answer

Relevant content