Description:
This is the same issue as reported yesterday (#6361) - I suggested it might be a server problem our end, but having looked into it further, it does look like a bug that has been previously reported:
The inserted timestamp value is not being returned correctly - the actual timestamp value returned appears to be 38-58 seconds ahead depending on the year.
I've searched for similar problem reports and found the following on your mailing lists:
http://lists.mysql.com/mysql/103438
This exactly describes the problem we're seeing - we get the same 38 second difference for the date mentioned.
The following table shows the differences on a per-year basis - the code generating this table is included in the "How to repeat" section below:
The server timezone is set to GMT.
mysql> select * from timestamp_test;
+----------------+------+----------------+--------------+------------------------+
| aa | bb | cc | aa_equals_cc | aa_plus_zero_equals_cc |
+----------------+------+----------------+--------------+------------------------+
| 19700101235900 | 1970 | 19700101235900 | 1 | 1 |
| 19710101235900 | 1971 | 19710101235900 | 1 | 1 |
| 19720101235900 | 1972 | 19720101235900 | 1 | 1 |
| 19730101235958 | 1973 | 19730101235900 | 1 | 0 |
| 19740101235957 | 1974 | 19740101235900 | 1 | 0 |
| 19750101235956 | 1975 | 19750101235900 | 1 | 0 |
| 19760101235955 | 1976 | 19760101235900 | 1 | 0 |
| 19770101235954 | 1977 | 19770101235900 | 1 | 0 |
| 19780101235953 | 1978 | 19780101235900 | 1 | 0 |
| 19790101235952 | 1979 | 19790101235900 | 1 | 0 |
| 19800101235951 | 1980 | 19800101235900 | 1 | 0 |
| 19810101235951 | 1981 | 19810101235900 | 1 | 0 |
| 19820101235950 | 1982 | 19820101235900 | 1 | 0 |
| 19830101235949 | 1983 | 19830101235900 | 1 | 0 |
| 19840101235948 | 1984 | 19840101235900 | 1 | 0 |
| 19850101235948 | 1985 | 19850101235900 | 1 | 0 |
| 19860101235947 | 1986 | 19860101235900 | 1 | 0 |
| 19870101235947 | 1987 | 19870101235900 | 1 | 0 |
| 19880101235946 | 1988 | 19880101235900 | 1 | 0 |
| 19890101235946 | 1989 | 19890101235900 | 1 | 0 |
| 19900101235945 | 1990 | 19900101235900 | 1 | 0 |
| 19910101235944 | 1991 | 19910101235900 | 1 | 0 |
| 19920101235944 | 1992 | 19920101235900 | 1 | 0 |
| 19930101235943 | 1993 | 19930101235900 | 1 | 0 |
| 19940101235942 | 1994 | 19940101235900 | 1 | 0 |
| 19950101235941 | 1995 | 19950101235900 | 1 | 0 |
| 19960101235940 | 1996 | 19960101235900 | 1 | 0 |
| 19970101235940 | 1997 | 19970101235900 | 1 | 0 |
| 19980101235939 | 1998 | 19980101235900 | 1 | 0 |
| 19990101235938 | 1999 | 19990101235900 | 1 | 0 |
| 20000101235938 | 2000 | 20000101235900 | 1 | 0 |
| 20010101235938 | 2001 | 20010101235900 | 1 | 0 |
| 20020101235938 | 2002 | 20020101235900 | 1 | 0 |
| 20030101235938 | 2003 | 20030101235900 | 1 | 0 |
| 20040101235938 | 2004 | 20040101235900 | 1 | 0 |
| 20050101235938 | 2005 | 20050101235900 | 1 | 0 |
| 20060101235938 | 2006 | 20060101235900 | 1 | 0 |
| 20070101235938 | 2007 | 20070101235900 | 1 | 0 |
| 20080101235938 | 2008 | 20080101235900 | 1 | 0 |
| 20090101235938 | 2009 | 20090101235900 | 1 | 0 |
| 20100101235938 | 2010 | 20100101235900 | 1 | 0 |
| 20110101235938 | 2011 | 20110101235900 | 1 | 0 |
| 20120101235938 | 2012 | 20120101235900 | 1 | 0 |
| 20130101235938 | 2013 | 20130101235900 | 1 | 0 |
| 20140101235938 | 2014 | 20140101235900 | 1 | 0 |
| 20150101235938 | 2015 | 20150101235900 | 1 | 0 |
| 20160101235938 | 2016 | 20160101235900 | 1 | 0 |
| 20170101235938 | 2017 | 20170101235900 | 1 | 0 |
| 20180101235938 | 2018 | 20180101235900 | 1 | 0 |
| 20190101235938 | 2019 | 20190101235900 | 1 | 0 |
| 20200101235938 | 2020 | 20200101235900 | 1 | 0 |
+----------------+------+----------------+--------------+------------------------+
51 rows in set (0.00 sec)
How to repeat:
#!/bin/sh
mysql -e "use test;\
DROP TABLE IF EXISTS timestamp_test;\
CREATE TABLE timestamp_test (\
aa timestamp(14) NOT NULL,\
bb varchar(10) NOT NULL default '',\
cc varchar(14) default NULL,\
aa_equals_cc tinyint default NULL,\
aa_plus_zero_equals_cc tinyint default NULL,\
PRIMARY KEY (bb),\
UNIQUE KEY bb (bb)\
) TYPE=MyISAM;"
mysql -e "use test; delete from timestamp_test"
for i in `seq 1970 2020`; do
mysql -e "use test; insert into timestamp_test values ('${i}0101235900',
'$i', '${i}0101235900', aa='${i}0101235900', aa+0='${i}0101235900')"
done
mysql -e "use test; select * from timestamp_test"
Suggested fix:
No idea - from the mailing list comments it looks as if it's a problem related to glibc, MySQL and timezones but that was for MySQL 3 servers.
