Bug #40230 23 seconds difference while the time_zone is SYSTEM
Submitted: 22 Oct 2008 7:23 Modified: 6 Jan 2011 14:51
Reporter: Sabyasachi Ruj Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.51, 5.1 OS:Microsoft Windows
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: time zone
Triage: Triaged: D2 (Serious)

[22 Oct 2008 7:23] Sabyasachi Ruj
My system time zone is set to GMT.
My MySQL connection is using the time_zone as 'SYSTEM'.

In a timestamp field I have entered a value, say: '2008-10-22 10:00:00'.
If you do SELECT on that timestamp column, the value comes as:-
'2008-10-22 10:00:00'.

Now I am setting my MySQL connection's time_zone as 'GMT', by executing the following query: -

set time_zone = 'GMT';

Now if I am doing SELECT on that timestamp column the following value comes: -
'2008-10-22 09:59:37'.

Why that difference?

How to repeat:
Described above.
[22 Oct 2008 8:00] Sveta Smirnova
Thank you for the report.

But version 5.0.51 is a bit old. Please upgrade to current version 5.0.67, try with it and inform us if problem still exists.
[22 Oct 2008 15:23] Sveta Smirnova
You also can experience this behavior if your system timezone is with leap seconds. To avoid the problem in this case please run query UPDATE mysql.time_zone SET Use_leap_seconds='N' and restart the server. Please inform us if this helps.
[23 Nov 2008 0:00] Bugs System
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".
[5 May 2009 10:53] Sabyasachi Ruj
Yes. Updating the mysql.timezone did the trick.
[5 May 2009 11:18] Sveta Smirnova
Thank you for the update.

Closed as "Not a Bug"
[11 Jun 2009 5:47] Trent Lloyd
I'd like to re-open this bug and get it looked at again.

This behavior only happens on Windows and not Linux which makes me think that the behavior must be wrong by default but I don't quite understand all the implications.
[11 Jun 2009 8:30] Sveta Smirnova

thank you for the feedback.

Please try new system timezone tables from http://dev.mysql.com/downloads/timezones.html See also bug #39923
[12 Jun 2009 2:00] Trent Lloyd

I see the note on the other bug .. but customer already tried with 2009g and it did not resolve the situation (with XP, on a different system)

[15 Jun 2009 8:25] Sveta Smirnova

thank you for the feedback. But I mean not OS-related settings, but MySQL timezone tables which were regenerated lately.
[15 Jun 2009 12:07] Trent Lloyd

I don't understand your comment . they tried with 2009g system tables?

- Trent
[29 Jul 2009 8:40] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround: SET time_zone='+00:00'
[19 Aug 2009 15:48] Tatiana Azundris Nuernberg
Actually, on OS X I see

- 3s difference for older TZ.zip
- 3s difference for current TZ.zip (2009g)
- 0s difference for TZ tables built using mysql_tzinfo_to_sql5

(I show the difference on XP x86 too, FWIW).

So by that token, it's not "just Windows."

It still looks to me like it's just MySQL's and the system's TZ DB disagreeing on the whole leap second business.

If I copy over the timezone-DB I generated on OS X to x86, I no longer get the 3s difference on XP, so OS X's and XP's system TZ DBs seem to agree on the whole leap second business (and disagrees with the Linux our "official version" was built on, it would seem).

These all look like strong pointers to me that our code is correct, and maybe the current download isn't perfect for XP. Probably just means we should provide previous download instead? Would discouraging tz=SYS on systems where MySQL's TZ DB wasn't natively generated but imported be a good idea? Tune in again tomorrow for another episode of "Tatiana's had some actual sleep!"
[10 Sep 2010 9:24] Tatiana Azundris Nuernberg
timezones info in MyISAM format, with leap seconds

Attachment: tz_myisam_2010l_leaps.zip (application/zip, text), 450.99 KiB.

[10 Sep 2010 9:32] Tatiana Azundris Nuernberg
(corrected full instructions)


Get zoneinfo, so we have platform-independent, canonical data to work from.



tzcode2010l.tar.gz  tzdata2010l.tar.gz

into ~/tz


Build the zoneinfo

$ cd ~/tz
$ make TOPDIR=$HOME/tzdir install

This will build tzinfo both with (tzdir/etc/zoneinfo-leaps) and
without (tzdir/etc/zoneinfo-posix) leapsecond info.

The canonical platform for this is linux; the special machine used was
Linux bk-internal.mysql.com #1 SMP .. i686 i686 i386 GNU/Linux

The zoneinfo results should be equivalent to 2010l_zones.tar.gz


Build a mysqld (used: 5.1.48), so that we may get sql/mysql_tzinfo_to_sql


In the ~/tzdir/etc (created by zoneinfo) run

$ YOUR_MYSQL_TREE/sql/mysql_tzinfo_to_sql zoneinfo-leaps > zoneinfo-leaps.sql
$ YOUR_MYSQL_TREE/sql/mysql_tzinfo_to_sql --leap zoneinfo-leaps/UTC >> zoneinfo-leaps.sql

$ YOUR_MYSQL_TREE/sql/mysql_tzinfo_to_sql zoneinfo-posix > zoneinfo-posix.sql

Ignore skip-warnings.

If things went right, this should work:

$ grep -H Use_leap_seconds *.sql|sort -u|less
zoneinfo-leaps.sql:INSERT INTO time_zone (Use_leap_seconds) VALUES ('Y');
zoneinfo-posix.sql:INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');


Run mysqld.
Import the zoneinfo you want (with or without leap seconds):

$ mysql -uroot mysql < zoneinfo-leaps.sql


$ mysql -uroot mysql < zoneinfo-posix.sql


If desired, save the MyISAM files.

Shut down the server.
Save data/mysql/time_zone*:

$ cd data/mysql

$ zip tz_myisam_2010l_leaps.zip time_zone*


$ zip tz_myisam_2010l_posix.zip time_zone*
[10 Sep 2010 9:37] Tatiana Azundris Nuernberg
In fact --leap building is suggested even for POSIX; 
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
will disable the use of leap seconds, but there is no
pressing reason why we shouldn't have correct/complete
l/s data in the table, even if we're not using them
right now.
[10 Sep 2010 10:00] Tatiana Azundris Nuernberg

../sql/mysql_tzinfo_to_sql --leap zoneinfo-posix/UTC >> zoneinfo-posix.sql

creates an empty leap table, whereas

../sql/mysql_tzinfo_to_sql --leap zoneinfo-leaps/UTC >> zoneinfo-posix.sql

creates a filled one.  NOTE the "leaps"/"posix" mix in the second form!!
[10 Sep 2010 10:02] Tatiana Azundris Nuernberg
timezones info in MyISAM format, withOUT leap seconds (POSIXly correct; leap info is included, but disabled)

Attachment: tz_myisam_2010l_posix.zip (application/zip, text), 442.35 KiB.

[10 Sep 2010 10:39] Dmitry Lenev
The latest versions of packages look fine to me.

I think it is ok to publish them after adjusting
their names to omit unnecessary information and to
follow pattern which was used for previous packages
(proposal is sent by e-mail).

Thanks for working on this Tatjana!
[14 Sep 2010 23:55] Tatiana Azundris Nuernberg
I agree with the rename, the "myISAM" tag is an artifact from when I
originally also included the intermediate "SQL" files. Hence,

tz_myisam_2010l_posix.zip -> timezone-2010l-posix.zip
tz_myisam_2010l_leaps.zip -> timezone-2010l-leaps.zip

Possible tag for "POSIX":

"Timezone data to be used on machines where we cannot build them from the
operating system's data (as we can on most UNIX-like/linux systems).
This version adheres to the POSIX standard in that it does NOT include leap
seconds. If you cannot built your own timezone data on your system, use this
package. Most systems including Windows(TM) are POSIXly correct in this regard;
only choose the other package if you know enough so you don't need this advice."

Possible tag for "leaps":

"Timezone data to be used on machines where we cannot build them from the
operating system's data (as we can be on most UNIX/linux-like systems).
This version does not adhere to the POSIX standard in that it DOES include
leap seconds. Few people need this - if at all in doubt, choose the other
[8 Oct 2010 7:22] Tatiana Azundris Nuernberg
Updated files downloadable from
[6 Jan 2011 14:51] Paul Dubois
Noted in 5.0.92, 5.1.55, 5.5.8 changelogs.

The time zone tables available at http://dev.mysql.com/downloads/timezones.html
updated. These tables can be used on systems such as Windows or HP-UX
that do not include zoneinfo files.