MySQL Bugs: #40230: 23 seconds difference while the time

Bug #40230	23 seconds difference while the time_zone is SYSTEM
Submitted:	22 Oct 2008 9:23	Modified:	19 Aug 17:48
Reporter:	Sabyasachi Ruj
Status:	In progress
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.51, 5.1	OS:	Microsoft Windows
Assigned to:	Tatjana A. Nuernberg	Target Version:	5.1+
Tags:	time zone
Triage:	Triaged: D2 (Serious)

Description:
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.

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.

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.

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".

Yes. Updating the mysql.timezone did the trick.

Thank you for the update.

Closed as "Not a Bug"

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.

Trent,

thank you for the feedback.

Please try new system timezone tables from http://dev.mysql.com/downloads/timezones.html
See also bug #39923

Sveta,

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)

Trent

Trent,

thank you for the feedback. But I mean not OS-related settings, but MySQL timezone tables
which were regenerated lately.

Sveta,

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

- Trent

Thank you for the report.

Verified as described.

Workaround: SET time_zone='+00:00'

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!"