MySQL Bugs: #28621: Inserting a valid datetime into a timestamp column causes warning and data chang

Bug #28621	Inserting a valid datetime into a timestamp column causes warning and data chang
Submitted:	23 May 2007 14:15	Modified:	26 Aug 2010 20:17
Reporter:	Matt Bridges	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.37, 5.0.67	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	alter, datetime, modify, timestamp

Description:
We have a table which contains a datetime value of '2006-05-03 16:57:36'

When we attempt to insert this value into a new table where the target column is timestamp mysql issues a warning and the data is changed in the target column.

The warning issued is: 
| Warning | 1299 | Invalid TIMESTAMP value in column 'created' at row 1 | 

(it might be relevant that this table was originally restored with ibbackup then altered into ndbcluster, then altered into myisam)

How to repeat:
mysql> create table test like profile;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test set created =  '2006-05-03 16:57:36';
Query OK, 1 row affected (0.04 sec)

mysql> alter table test modify column created timestamp not null default '1979-01-01 01:01:01';
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test select * from profile where id = 16297;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1299 | Invalid TIMESTAMP value in column 'created' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.03 sec)      

mysql> select created from test;
+---------------------+
| created             |
+---------------------+
| 2006-05-03 16:57:36 |
| 2006-03-26 02:00:00 |
+---------------------+
2 rows in set (0.04 sec)

mysql> select created from profile where id = 16297;
+---------------------+
| created             |
+---------------------+
| 2006-03-26 01:32:07 |
+---------------------+
1 row in set (0.04 sec)

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.41, and, in case of the same bug, send a dump of your profile table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Please, check if the problem is repeatable with a newer version, 5.0.67.

Thank you for the feedback.

You provided data:

mysql> select created from test;
+---------------------+
| created             |
+---------------------+
| 2006-05-03 16:57:36 |
| 2006-03-26 02:00:00 |
+---------------------+
2 rows in set (0.04 sec)

mysql> select created from profile where id = 16297;
+---------------------+
| created             |
+---------------------+
| 2006-03-26 01:32:07 |
+---------------------+
1 row in set (0.04 sec)

But '2006-03-26 01:32:07' looks like time when time is changes (==time which not exists). Because TIMESTAMP field affected by timezone settings it is expected what such date changed to '2006-03-26 02:00:00'. So I tend to think this is not a bug. Please check value of time_zone and system_time_zone system variables to be sure.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can reproduce this bug consistently in version 5.0.67. 

Here is simple example; 

 CREATE TABLE t (ts1 TIMESTAMP NULL DEFAULT NULL );
 insert into t (ts1) values ("2009-03-08 02:29:45");
 Query OK, 1 row affected, 1 warning (0.00 sec)
 
 insert into t (ts1) values ("2009-03-08 03:29:45"); // WORKS FINE
 select * from T; // gives`  2009-03-08 03:00:00 

This is so awful.

I can reproduce this on 5.0.90 :

mysql> create temporary table test (ts timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (ts) 
    -> VALUES ('2008-03-29 18:25:31'), 
    -> ('2008-03-30 02:25:31'),     -> ('2008-03-29 18:25:31' + INTERVAL 8 HOUR);
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from test;
+---------------------+
| ts                  |
+---------------------+
| 2008-03-29 18:25:31 |
| 2008-03-30 03:00:00 |
| 2008-03-30 03:00:00 |
+---------------------+
3 rows in set (0.00 sec)

I think this is not correct.
The warning is OK for ('2008-03-30 02:25:31'), but ('2008-03-29 18:25:31' + INTERVAL 8 HOUR) should be '2008-03-30 03:25:31'.

That's the way I see it at least, since times goes from '2008-03-30 01:59:59' to '2008-03-30 03:00:00'.

Thank you for the feedback.

You all affected by day saving time changes. This is not a bug.

I know it is daylight saving change, but how can you say this is not a bug ?

If I am in a day light saving timezone, '2008-03-29 18:25:31' + INTERVAL 8
HOUR should be '2008-03-30 03:25:31'. 
'2008-03-30 03:00:00' is a terrible result !

Thank you for the feedback, but you calculate wrongly:

mysql> select ('2008-03-29 18:25:31' + INTERVAL 8 HOUR);
+-------------------------------------------+
| ('2008-03-29 18:25:31' + INTERVAL 8 HOUR) |
+-------------------------------------------+
| 2008-03-30 02:25:31                       |
+-------------------------------------------+
1 row in set (0.11 sec)

You don't get it. In my server's time zone, this time DOES NOT EXIST !
It's like 2010-02-30 : it's impossible.
I do not calculate wrongly. Change your server's time zone to Europe/Brussels and you'll see what I mean.

Happened to me the same. But finally I get it why the DST is the clue: all the examples are 02:xx:xx time, which doesn't exist since that is the moment to change the hours from 2AM to 3AM.
Maybe the bug is why setting 03:00:00 instead of 03:xx:xx (adding 1 hour and not rounding it), what do you think?
I am using MySQ 5.7.21-0ubuntu0.16.04.01.