Bug #95512 Really odd bug. Incorrect error of datetime
Submitted: 24 May 2019 1:16 Modified: 24 May 2019 21:01
Reporter: Jorge Castro Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.16 OS:Windows
Assigned to: CPU Architecture:x86

[24 May 2019 1:16] Jorge Castro
Description:
I tried to insert this value

'2014-09-07 00:00:00'

In a TIMESTAMP column and it returns the next message

Error Code: 1292. Incorrect datetime value: '2014-09-07 00:00:00' for column 'last_update' at row 1

However, if I try to insert the next value

'2014-09-06 00:00:00' // day 06 instead of 07.

it works.

What fails:

'2014-09-07 00:00:00'
'2014-09-07 00:01:01'
'2014-09-07 00:22:22'
'2014-09-07 00:00:22'

What doesn't fail

'2014-09-06 00:00:00'
'2014-09-07 01:00:00' // same day but different hour.
'2014-09-08 00:00:00'

I tried on another machine (Windows Server) with the same result.

How to repeat:
CREATE TABLE `dummy`(`timecol` TIMESTAMP NULL);

insert into dummy(timecol) values('2014-09-07 00:00:00');
[24 May 2019 8:15] MySQL Verification Team
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test
Database changed
mysql> CREATE TABLE `dummy`(`timecol` TIMESTAMP NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into dummy(timecol) values('2014-09-07 00:00:00');
Query OK, 1 row affected (0.02 sec)

mysql>

Check your local timezone Daylight saving time?
[24 May 2019 12:23] Jorge Castro
More information:

insert into dummy(timecol) values('2014-09-07 00:00:00');

It fails:

Windows:  Timezone = (UTC -4:00 Santiago) with daily saving.
Mysql: @@global.time_zone SYSTEM (default-time-zone is not set)

Error Code: 1292. Incorrect datetime value: '2014-09-07 00:00:00' for column 'timecol' at row 1

But it works if we set another timezone, including

(UTC -4:00 Atlantic Canada).

1 row(s) affected

It also works if we set the timezone in my.ini

default-time-zone = '-04:00'

1 row(s) affected
[24 May 2019 21:01] Jorge Castro
It was my mistake.

The current daily saving GMT -4:00 Santiago is 13 May and 12 August.  However and during 2014, the daily saving was the 7 September, ergo 7 September 2014 0 hours was skipped.