Bug #6387 Queried timestamp values do not match the inserted value

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.

Hi, Richard!

Thank you for your persistence, I was able to repeat this behavior!

Bug happens if one runs MySQL server in time zone which description contains
leap seconds (Rather rare case, some of Linux distributions even don't ship 
zoneinfo files with leap seconds information...)

Simpler test case will look like:

mysql> create table testts (ts timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into testts values (19730101235900);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testts;
+---------------------+
| ts                  |
+---------------------+
| 1973-01-01 23:59:58 |
+---------------------+
1 row in set (0.00 sec) 

I suggest you to use time zone without leap seconds for your server as temporary work-around (but please be aware that data in TIMESTAMP columns will change 
once you will decide to move back to time zone with leap seconds).

Thank you once again!

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.2055.2.2 2004/11/03 17:59:03 dlenev@mysql.com
  Fix for bug #6387 "Queried timestamp values do not match the inserted
  value if server runs in time zone with leap seconds".