| Bug #48326 | current_timestamp and now() don't produce the same value for a given statement | ||
|---|---|---|---|
| Submitted: | 26 Oct 2009 18:03 | Modified: | 24 Nov 2009 9:02 |
| Reporter: | Rishi Malik | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.75, 5.1 bzr | OS: | Linux (5.0.75-0ubuntu10.2-log) |
| Assigned to: | CPU Architecture: | Any | |
[26 Oct 2009 18:03]
Rishi Malik
[26 Oct 2009 20:05]
Peter Laursen
Looks like related to 'daylight saving time'. I do not know how the British handle this (they only get up around 10 in the morning anyway!), but here in Denmark DST stopped the night between last Saturday and last Sunday. I get (CET - with no DST):
CREATE TABLE `test_table` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`nonset` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`queryset` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;
SHOW GLOBAL VARIABLES LIKE 'time_zone%';
/*
Variable_name Value
------------- ------
time_zone SYSTEM
*/
INSERT INTO test_table(queryset) VALUES(NOW());
SELECT nonset,UNIX_TIMESTAMP(nonset),queryset,UNIX_TIMESTAMP(queryset) FROM test_table;
/*
nonset unix_timestamp(nonset) queryset unix_timestamp(queryset)
------------------- ---------------------- ------------------- ------------------------
2009-10-26 20:58:33 1256587113 2009-10-26 20:58:33 1256587113
*/
One possibility is that now() and current_timestamp is *stored* identically but *returned* differently to the client. Maybe it was interesting to see something like:
SELECT nonset,HEX(UNIX_TIMESTAMP(nonset)),queryset,HEX(UNIX_TIMESTAMP(queryset)) FROM test_table;
and
SELECT nonset,MD5(UNIX_TIMESTAMP(nonset)),queryset,MD5(UNIX_TIMESTAMP(queryset)) FROM test_table;
Peter
(not a MySQL person)
[26 Oct 2009 21:28]
Peter Laursen
OK .. my last comment was probably mistaken if read literally. The INSERT and SELECT statements are independent. SELECT cannot see if "now()" or "current_timestamp" were used when INSERTing. However a HEX() would still be interesting to see what is actually stored. If they are stored identically then a TIMESTAMP DEFAULT CURRENT_TIMESTAMP and a TIMESTAMP with no or a 'fixed string' default are returned differently when returned as unix_timestamp's.
[27 Oct 2009 10:21]
Rishi Malik
Daylight savings may have an impact on this. The date picked (2009-10-25 01:48:24 BST ) is about 11 minutes before the UK switched daylight savings time. This problem does not appear to occur after the switch at 2:00am. In fact, further testing shows that the problem only occurs between 01:00 and 02:00. Setting the time to values before that, or after that on the same day produce identical values for current_timestamp and now(). Output of the HEX and MD5 querys are below. I believe it is showing that mysql has stored a different value internally. mysql> SELECT nonset,HEX(UNIX_TIMESTAMP(nonset)),queryset,HEX(UNIX_TIMESTAMP(queryset)) FROM test_table; +---------------------+-----------------------------+---------------------+-------------------------------+ | nonset | HEX(UNIX_TIMESTAMP(nonset)) | queryset | HEX(UNIX_TIMESTAMP(queryset)) | +---------------------+-----------------------------+---------------------+-------------------------------+ | 2009-10-25 01:51:46 | 4AE3A122 | 2009-10-25 01:51:46 | 4AE3AF32 | +---------------------+-----------------------------+---------------------+-------------------------------+ mysql> SELECT nonset,MD5(UNIX_TIMESTAMP(nonset)),queryset,MD5(UNIX_TIMESTAMP(queryset)) FROM test_table; +---------------------+----------------------------------+---------------------+----------------------------------+ | nonset | MD5(UNIX_TIMESTAMP(nonset)) | queryset | MD5(UNIX_TIMESTAMP(queryset)) | +---------------------+----------------------------------+---------------------+----------------------------------+ | 2009-10-25 01:51:46 | 61e61fefb95996b45a7f5da0a503dcd5 | 2009-10-25 01:51:46 | ebfa7a40df4a77837fdd3b2d663dff82 | +---------------------+----------------------------------+---------------------+----------------------------------+
[18 Nov 2009 11:14]
Sveta Smirnova
Thank you for the report. Verified as described. In my case problem repeatable at Oct 25 02:49:38 MSD 2009, but not repeatable at Oct 25 01:49:33 MSD 2009: CREATE TABLE `test_table` ( `id` int(11) NOT NULL auto_increment, `nonset` timestamp NOT NULL default CURRENT_TIMESTAMP, `queryset` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ; show global variables like 'time_zone%'; Variable_name Value time_zone SYSTEM вс окт 25 02:49:38 MSD 2009 insert into test_table(queryset) values(now()); select nonset,queryset,unix_timestamp(nonset) as un,unix_timestamp(queryset) as us from test_table; nonset 2009-10-25 02:49:38 queryset 2009-10-25 02:49:38 un 1256424578 us 1256428178
[24 Nov 2009 9:02]
Sveta Smirnova
Bug is repeatable with DATETIME column: CREATE TABLE `test_table` ( `id` int(11) NOT NULL auto_increment, `nonset` timestamp NOT NULL default CURRENT_TIMESTAMP, `queryset` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ; show global variables like 'time_zone%'; Variable_name Value time_zone SYSTEM вс окт 25 02:51:07 MSD 2009 insert into test_table(queryset) values(now()); select nonset,queryset,unix_timestamp(nonset) as un,unix_timestamp(queryset) as us from test_table; nonset 2009-10-25 02:51:07 queryset 2009-10-25 02:51:07 un 1256424667 us 1256428267 But there is 1 thing needed to repeat the bug. Please look at the time: вс окт 25 02:51:07 MSD 2009 According to DST changes timezone should be named MSK. Bug only repeatable if timezone listed as MSD while it should be MSK. This is possible to set using regular OS tools.
[9 Feb 2014 20:42]
Josh Snyder
I can reproduce this issue in MySQL Server 5.6.16. I originally encountered it in Percona Server 5.5.29. As far as I can tell, the issue occurs whenever a daylight savings time transition occurs that causes a given hour to validly occur in two different timezones. In the America/Los_Angeles zone, for instance, Sun Nov 3 2013 01:00:00-01:59:59 occurs in both the PDT and PST timezones. This is because the clock jumps back by one hour, so that the second succeeding 01:59:59 PDT is 01:00:00 PST. This minimal test case uses unix timestamp 1383465600 (Sun Nov 3 01:00:00 PDT 2013) as an example. I executed it on a system in the America/Los_Angeles timezone. > show variables like 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+ 1 row in set (0.00 sec) > create table time_test (id int primary key, timestamp timestamp default current_timestamp) engine=innodb; > set timestamp = 1383465600; > insert into time_test (id) values (1); > insert into time_test (id, timestamp) values (2, now()); > insert into time_test (id, timestamp) values (3, current_timestamp()); > insert into time_test (id, timestamp) values (4, current_timestamp); > select id, timestamp, unix_timestamp(timestamp) from time_test; +----+---------------------+---------------------------+ | id | timestamp | unix_timestamp(timestamp) | +----+---------------------+---------------------------+ | 1 | 2013-11-03 01:00:00 | 1383465600 | | 2 | 2013-11-03 01:00:00 | 1383469200 | | 3 | 2013-11-03 01:00:00 | 1383469200 | | 4 | 2013-11-03 01:00:00 | 1383469200 | +----+---------------------+---------------------------+ This demonstrates that for all cases where a time function is used explictly, MySQL has inserted a different value into the table than the one specified by `SET timestamp`. I believe this is because the values of the `now` and `current_timestamp` functions are being converted into local time for display, and then converted back into unix time for storage. When doing the conversion from display to unix time, MySQL has to make a choice for which timezone corresponds to '2013-11-03 01:00:00'. I have not been able to find any documentation of this pitfall. In fact, the documentation states that CURRENT_TIMESTAMP should have the same value as an auto-update of a timestamp column. "To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP)." [1]. [1] http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
