MySQL Bugs: #80758: Incorrect datetime value: '2016-03-13 02:59:36' for timestamp column

Bug #80758	Incorrect datetime value: '2016-03-13 02:59:36' for timestamp column
Submitted:	16 Mar 2016 7:15	Modified:	18 Apr 2016 1:36
Reporter:	Yifei Jia	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.6.23-log	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	incorrect, timestamp

Description:
my company use date_add() get a values like '2016-03-13 02:59:36',
then use it insert into a timestamp column.
But,
Incorrect datetime value: '2016-03-13 02:59:36' for timestamp column

How to repeat:
CREATE PROCEDURE test_timestamp()
BEGIN
DECLARE var_time timestamp DEFAULT null;
set var_time='2016-03-13 02:59:36';
SELECT var_time;
END;
call test_timestamp();

[SQL]call test_timestamp();
[Err] 1292 - Incorrect datetime value: '2016-03-13 02:59:36' for column 'var_time' at row 1

Hello Leonidas Jia,

Thank you for the report.
I'm not seeing the reported issue in latest GA builds(5.6.29/5.7.11). 
Could you please try with latest build and let us know if you are still having the issue along with conf file used?

--

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29 MySQL Community Server (GPL)

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> use test
Database changed
mysql> delimiter //
mysql> CREATE PROCEDURE test_timestamp()
    -> BEGIN
    -> DECLARE var_time timestamp DEFAULT null;
    -> set var_time='2016-03-13 02:59:36';
    -> SELECT var_time;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call test_timestamp();
+---------------------+
| var_time            |
+---------------------+
| 2016-03-13 02:59:36 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> call test_timestamp();
+---------------------+
| var_time            |
+---------------------+
| 2016-03-13 02:59:36 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> call test_timestamp();
+---------------------+
| var_time            |
+---------------------+
| 2016-03-13 02:59:36 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Thanks,
Umesh

[root@localhost ~]# date
Thu Mar 17 02:49:31 EDT 2016
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11-log Source distribution

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> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-03-17 02:49:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.01 sec)

mysql> show create procedure test_timestamp;
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure      | sql_mode                                   | Create Procedure                                                                                                                                                    | character_set_client | collation_connection | Database Collation |
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test_timestamp | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`admin`@`%` PROCEDURE `test_timestamp`()
BEGIN
DECLARE var_time timestamp DEFAULT null;
set var_time='2016-03-13 02:59:36';
SELECT var_time;
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> CALL test_timestamp();
ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:59:36' for column 'var_time' at row 1
mysql>

may this,
daylight saving time(2016)
begin with 2016-3-13 2:00:00，
error time in  2016-3-13 2:00:00 and  2016-3-13 2:59:59

mark