MySQL Bugs: #82201: '2015-03-08 02:00:00' and '2015-03-08 02:59:59' data could not be inserted

Bug #82201	'2015-03-08 02:00:00' and '2015-03-08 02:59:59' data could not be inserted
Submitted:	12 Jul 2016 16:11	Modified:	13 Jul 2016 13:57
Reporter:	Tony Sun	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.12	OS:	CentOS (Linux mysqlcl2 2.6.32-642.1.1.el6.x86_64 #1 SMP Tue May 31 21:57:07 UTC 2016 x86_64 x86_64 x86_64 GN)
Assigned to:		CPU Architecture:	Any

Description:
When importing data into the table with a column of timestamp datatype, I met the problem that MySQL database reporting the following error:
ERROR 1292 (22007): Incorrect datetime value:...

I believed that the data format is correct because only the data between '2015-03-08 02:00:00' and '2015-03-08 02:59:59' will report the error. Other data all works fine. 

Please refer the "How to repeat" section to find the detail.

How to repeat:
Database Version:

# /usr/local/mysql/bin/mysql --help
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.7.12, for linux-glibc2.5 (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2016, 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.

......

Detail:

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table test(id smallint, testdate timestamp not null default current_timestamp on update  CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test(id, testdate) values(1, '2015-03-08 01:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id, testdate) values(2, '2015-03-08 02:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2015-03-08 02:00:00' for column 'testdate' at row 1
mysql>  insert into test(id, testdate) values(2, '2015-03-08 02:30:00');
ERROR 1292 (22007): Incorrect datetime value: '2015-03-08 02:30:00' for column 'testdate' at row 1
mysql>  insert into test(id, testdate) values(2, '2015-03-08 02:59:59');
ERROR 1292 (22007): Incorrect datetime value: '2015-03-08 02:59:59' for column 'testdate' at row 1
mysql>  insert into test(id, testdate) values(2, '2015-03-08 02:45:21');
ERROR 1292 (22007): Incorrect datetime value: '2015-03-08 02:45:21' for column 'testdate' at row 1
mysql>  insert into test(id, testdate) values(2, '2015-03-08 02:18:43');
ERROR 1292 (22007): Incorrect datetime value: '2015-03-08 02:18:43' for column 'testdate' at row 1
mysql> 
mysql> 
mysql> 
mysql>  insert into test(id, testdate) values(2, '2015-03-08 03:00:00');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into test(id, testdate) values(3, '2015-03-08 03:01:03');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | testdate            |
+------+---------------------+
|    1 | 2015-03-08 01:59:59 |
|    2 | 2015-03-08 03:00:00 |
|    3 | 2015-03-08 03:01:03 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql>

Please check your timezone about daylight save time:

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.15 Source distribution 2016-JUL-09

Copyright (c) 2000, 2016, 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 5.7 > create database test;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > use test;
Database changed
mysql 5.7 > create table test(id smallint, testdate timestamp not null default current_timestamp on update  CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.49 sec)

mysql 5.7 > insert into test(id, testdate) values(1, '2015-03-08 01:59:59');
Query OK, 1 row affected (0.19 sec)

mysql 5.7 > insert into test(id, testdate) values(2, '2015-03-08 02:00:00');
Query OK, 1 row affected (0.05 sec)

mysql 5.7 > insert into test(id, testdate) values(2, '2015-03-08 02:30:00');
Query OK, 1 row affected (0.05 sec)

mysql 5.7 >  insert into test(id, testdate) values(2, '2015-03-08 02:59:59');
Query OK, 1 row affected (0.06 sec)

mysql 5.7 >  insert into test(id, testdate) values(2, '2015-03-08 02:45:21');
Query OK, 1 row affected (0.25 sec)

mysql 5.7 > insert into test(id, testdate) values(2, '2015-03-08 02:18:43');
Query OK, 1 row affected (0.06 sec)

mysql 5.7 > insert into test(id, testdate) values(2, '2015-03-08 03:00:00');
Query OK, 1 row affected (0.14 sec)

mysql 5.7 > insert into test(id, testdate) values(3, '2015-03-08 03:01:03');
Query OK, 1 row affected (0.05 sec)

mysql 5.7 > select * from test;
+------+---------------------+
| id   | testdate            |
+------+---------------------+
|    1 | 2015-03-08 01:59:59 |
|    2 | 2015-03-08 02:00:00 |
|    2 | 2015-03-08 02:30:00 |
|    2 | 2015-03-08 02:59:59 |
|    2 | 2015-03-08 02:45:21 |
|    2 | 2015-03-08 02:18:43 |
|    2 | 2015-03-08 03:00:00 |
|    3 | 2015-03-08 03:01:03 |
+------+---------------------+
8 rows in set (0.00 sec)

mysql 5.7 >

Dear Mr. Solorzano,

Thank you very much.

Could you please give me some clue on how to diagnose and fix this problem. I was importing history data into our data warehouse environment and got stuck at this issue.

I noticed that you suggested me check my timezone about the daylight save time. Could you please let me know how to do it?

Thanks a lot.

Tony

Imho daylight saving time (United States) in 2015 began at 2:00 AM on Sunday, March 8 i.e clocks ahead one hour,  thus skipping the hour between 2am to 3am  and looking at the testdate column values i.e '2015-03-08 02:00:00', '2015-03-08 02:30:00' etc are invalid values if MySQL Server gets all current time information from the operating system OR from the named timezone(US/*) and if observes daylight saving time.

I would suggest you to take a look at the manual page on this for details  http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html
https://dev.mysql.com/doc/refman/5.7/en/time-zone-upgrades.html

OK, I see.

Thank you all very much!