Description:
Easter weekend, we had a switch in our daylight savings time from CET to CEST.
During this, I noticed that servers started before the switch still had "CET" in the output of "show variables" and only servers started after the switch gave out "CEST".
The documentation correctly identifies this in 5.2.3 Server System Variables:
---cut
The server system time zone. When the server begins executing, it inherits a time zone setting from the machine defaults, possibly modified by the environment of the account used for running the server or the startup script. The value is used to set system_time_zone. Typically the time zone is specified by the TZ environment variable. It also can be specified using the --timezone option of the mysqld_safe script. This variable was added in MySQL 4.1.3.
---cut
So to me, this isn't a bug - yet some customers as well as support staff believe that for accuracy, this variable should represent the current value. As servers with long
uptime do correctly calculate dates and times with CEST, but state "time_zone=CET",
some people do have the crazy idea that the server prints a "wrong" date and they
have to manually recalculate the given results.
Personally I see this as merely a cosmetic feature, as the documentation is specific
enough (to me) and the issue doesn't directly affect the accuracy MySQL calculates or
presents any dates, but a few other ones have a different opinion on that third ones do assume that time_zone (4.0) and system_time_zone (4.1) represent the current timezone the server is currently using rather than the timezone used at server startup.
How to repeat:
A 4.1.10 server with 35 days of uptime:
| system_time_zone | CET |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
A 4.0.24 server with 2 days of uptime:
| timezone | CEST |
A 4.0.24 server with 35 days of uptime:
| timezone | CET |
Somehow strange is that the timezone value cannot be changed on a running server - the server prints out a "wrong" error message:
mysql> show variables like 'timezone';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| timezone | CET |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global timezone="CEST";
ERROR 1193: Unknown system variable 'timezone'
Suggested fix:
-Schedule some kind of job which "fixes" the timezone variable according to daylight savings.
-Or just print out the "current" timezone rather than the one used at startup upon "show variables".
-Or add a note to the documentation that the timezone/system_timezone-variable doesn't
reflect the current active timezone but the base timezone used for date calculations and
daylight savings are handled correctly, although this variable might let someone assume
that they were not.
Changing the error message with a more meaningful one might also be a good idea,
e.g. "Variable '%-.64s' can only be read, not set.".
