system_time_zone is set wrong

Bug #1001340 reported by Jürgen Falch on 2012-05-18
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Vladislav Vaintroub

Bug Description

In v5.5.23 on XP SP3, the server variable system_time _zone is set to a wrong value on server startup. Its value is "ope" (without the quotes), not a valid time zone designation.
IMHO, this is possibly derived form the string "Westeuropäische Sommerzeit" which is returned by the GetTimeZoneInformation() windows API call on my machine (meaning CEST, +02:00).

Note: I have an environment variable called TZ, whose current value is "CET" (without the quotes).
If I unset this variable inside a cmd.exe window, and then start mysqld.exe --console from same window, system_time _zone will be different, namely "Westeuropische Sommerzeit" (without the quotes) - note the missing "ä".
Again, this appears not to be a valid timezone name according to the mysql.time_zone_name table.

The correct value would probably be CET or Europe/Berlin.

Elena Stepanova (elenst) on 2012-05-18
Changed in maria:
assignee: nobody → Vladislav Vaintroub (wlad-montyprogram)
Jürgen Falch (mail-jfalch) wrote :

After some searching, the problem is most probably that the time zone name returned from the windows GetTimeZoneInformation() is a) non-standard and b) localized. You could probably scan the subkeys of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones until you find one whose Std or Dlt value is equal to the GetTimeZoneInformation() result (not necessarily identical to the subkey name!). Then parse the TZI value of the same entry for the appropriate bias'es (cf,1895,1166624,00.asp for format info), and use the computed bias as +|-nn:nn.

Jürgen Falch (mail-jfalch) wrote :

...or you could just use the appropriate bias'es from the TIME_ZONE_INFORMATION result structure returned by GetTimeZoneInformation() ... d'oh.

 I do not have localized Windows atm. Would it be possible for you to compile and run this program and paste the results?

#include <stdio.h>
#include <windows.h>

int main()
  wprintf(L"%s\n", tzi.StandardName);
  wprintf(L"%s\n", tzi.DaylightName);
  return 0;

It does not return 3 letter abbreviation for me, but instead

W. Europe Standard Time
W. Europe Daylight Time

I consider this ok, since the doc explicitely states that the result can be OS-dependent. However I'd rather avoid non-ASCIIs and stay English . So, I'm basically wondering if localized Windows would return localized results.


> It does not return 3 letter abbreviation for me, but instead
> W. Europe Standard Time
> W. Europe Daylight Time
the test program returns:
Westeuropäische Normalzeit
Westeuropäische Sommerzeit

> So, I'm basically wondering if localized Windows would return localized> results.
I have googled quite a lot on this, and experimented some. The result appears to be that on a localized windows, the time zone
names returned by GetTimeZoneInformation() are always a) localized, b) "long" names and c) invented by someone at Microsoft,
ie not textually equal to standard Unix/etc timezone names (as found in mysql.time_zone_names).

If such localized Microsoft-specific timezone names are understood inside mysqld, (ie when setting system_time_zone), there is
no bug/problem.

If not, please be so kind as to read my comments to the bug report, especially the second one.
IMHO, a simple way to obtain a valid value for system_time_zone on windows, localized or not, would be:
call GetTimeZoneInformation(&tzi) where tzi is a variable of type TIME_ZONE_INFORMATION:
if the resulting value is 1 (TIME_ZONE_ID_STANDARD), bias= tzi.Bias + tzi.StandardBias;
if the resulting value is 2 (TIME_ZONE_ID_DAYLIGHT), bias= tzi.Bias + tzi.DaylightBias;
(if the resulting value should be other (0), the timezone can not be determined - should not happen.)
Convert the resulting bias (unit: minutes, signed) to +|-hh:mm format, and you should be done.

Best regards,

Jürgen Falch

Changed in maria:
milestone: none → 5.5
Changed in maria:
importance: Undecided → Medium
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers