mysql time-zone ambiguity?
Gary Aitken
The server time zone value 'MDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specific time zone value if you want to utilize time zone support.
A diff says that /usr/share/zoneinfo/America/Denver is the same as /etc/localtime.
And the mysql time by default is set to 'SYSTEM':
garya@localhost [(none)]> select @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
on 11.4-RELEASE
Feels like the system is reporting the timezone as MDT and mysql wants something
like MDT/Denver? I thought MDT was unique; Arizona is different.
Seems like this should default properly; ideas for what I have screwed up?
Also running ntpd if that matters.
Gary
_______________________________________________
freebsd-...@freebsd.org mailing list
https://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to "freebsd-questi...@freebsd.org"
Bruce Ferrell
Gary,
Your error is from JDBC/java.
if you enter "date" at the cli, you'll see the timezone your system is set for.
%: date
Fri Mar 26 08:49:13 PDT 2021
"Technically" all timezones are just an offset from UTC, and US/Mountain is a label that points to that offset in the zone info files.
From the mysql documents:
/|timezone|/ values can be given in several formats, none of which are case-sensitive:
*
As the value |'SYSTEM'|, indicating that the server time zone is the same as the system time zone.
*
As a string indicating an offset from UTC of the form |[/|H|/]/|H|/:/|MM|/|, prefixed with a |+| or |-|, such as |'+10:00'|, |'-6:00'|, or |'+05:30'|. A leading zero can
optionally be used for hours values less than 10; MySQL prepends a leading zero when storing and retriving the value in such cases. MySQL converts |'-00:00'| or |'-0:00'| to
|'+00:00'|.
mysql timezones are expressed as that offset and the labels come from a timezone table that is not populated by default.
Named time zones can be used only if the time zone information tables in the |mysql| database have been created and populated. Otherwise, use of a named time zone results
in an error:
There is a cli command that is usually part of the mysql distribution, *mysql_tzinfo_to_sql* <https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html> that converts the
system zone information files to sql for loading into the time zone tables
ex:
|mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql |
the mysql server timezeone setting is usually set in the /etc/my.cnf file, expressed as that offset:
|default-time-zone='/timezone/'|
Gary Aitken
> On 3/26/21 8:29 AM, Gary Aitken wrote:
>> mysql57-server installed; When testing a jdbc connection I get:
>>
>> The server time zone value 'MDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specific time zone value if you want to utilize time zone support.
>>
>> A diff says that /usr/share/zoneinfo/America/Denver is the same as /etc/localtime.
>>
>> And the mysql time by default is set to 'SYSTEM':
>>
>> garya@localhost [(none)]> select @@GLOBAL.time_zone, @@SESSION.time_zone;
>> +--------------------+---------------------+
>> | @@GLOBAL.time_zone | @@SESSION.time_zone |
>> +--------------------+---------------------+
>> | SYSTEM | SYSTEM |
>> +--------------------+---------------------+
>> 1 row in set (0.00 sec)
>>
>> on 11.4-RELEASE
>>
>> Feels like the system is reporting the timezone as MDT and mysql wants something
>> like MDT/Denver? I thought MDT was unique; Arizona is different.
>> Seems like this should default properly; ideas for what I have screwed up?
>> Also running ntpd if that matters.
> Your error is from JDBC/java.
I figured that out :-)
<snip>
> Named time zones can be used only if the time zone information tables in the |mysql| database have been created and populated. Otherwise, use of a named time zone results in an error:
>
> There is a cli command that is usually part of the mysql distribution, *mysql_tzinfo_to_sql* <https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html> that converts the system zone information files to sql for loading into the time zone tables
>
> ex:
>
> |mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql |
>
> the mysql server timezeone setting is usually set in the /etc/my.cnf file, expressed as that offset:
>
> |default-time-zone='/timezone/'|
Thanks.
I read that but thought (and still think) it should happen by default.
Why doesn't the default installation process load the time zone tables?
The default installation results in mysql setting the time to "SYSTEM",
with or without the time zone tables loaded.
Since the system appears to be returning the time "MDT", which mysql
doesn't understand, it seems to me there is something wrong with the
mysql_tzinfo_to_sql translation or the zoneinfo or both.
After loading the time zone tables, if .my.cnf default-time-zone is explicitly
set to "MDT" (what the default 'SYSTEM' value for mysql results in), I still
get an error.
I have to not only load the zone tables into the server,
I have to also explicitly set default-time-zone='America/Denver'
For some reason, 'MDT' and 'America/Denver' are not considered equivalent.
Bruce Ferrell
There is what "should be" and the way it works. Opinions always vary on what should be default.
I suspect the reason for not making a symbolic setting is the data/time information is stored numerically and all matching is done numerically. Conversion from a symbol to a
number (and any related manipulations) would slow the sql. Do enough of them and it really messes with performance.
The thing that ALWAYS works for setting the time zone is to manually set it as the offset and never use symbolics in the options file:
default-time-zone='+0700'
Mine looks like this and I didn't set it in the my.cnf file :
mysql> select @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-03-26 10:56:51 |
+---------------------+
1 row in set (0.00 sec)
java doesn't like it that way, but mysql is fine with it... I have my opinions about what java thinks, but like I said, opinions vary :-)
Kurt Hackenberg
> The thing that ALWAYS works for setting the time zone is to manually set
> it as the offset and never use symbolics in the options file:
>
> default-time-zone='+0700'
Bruce Ferrell
Did I say it dealt with DST? This is why, when I set up an enterprise instance, I try to set for UTC and make the developers adjust for the users locale in the application...
Sometimes I get away with it.
:-)
There is a discussion of DST rules and named timezone support in the mysql documentation. I'll put the link for v5.7 below as it's kind of long to paste below. It's more or less
the same with minor changes for various versions:
https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html
It also mentions that once one starts doing this one needs to be aware of the fact that DST rules DO change periodically and the tables for that support need to be updated
accordingly.... One more thing to keep up to date!
Gary Aitken
> On 3/26/21 10:39 AM, Gary Aitken wrote:
>> I read that but thought (and still think) it should happen by default.
>> Why doesn't the default installation process load the time zone tables?
>> The default installation results in mysql setting the time to "SYSTEM",
>> with or without the time zone tables loaded.
>> Since the system appears to be returning the time "MDT", which mysql
>> doesn't understand, it seems to me there is something wrong with the
>> mysql_tzinfo_to_sql translation or the zoneinfo or both.
>> After loading the time zone tables, if .my.cnf default-time-zone is explicitly
>> set to "MDT" (what the default 'SYSTEM' value for mysql results in), I still
>> get an error.
>> I have to not only load the zone tables into the server,
>> I have to also explicitly set default-time-zone='America/Denver'
>>
>> For some reason, 'MDT' and 'America/Denver' are not considered equivalent.
> There is what "should be" and the way it works. Opinions always vary on what should be default.
>
> I suspect the reason for not making a symbolic setting is the data/time information is stored numerically and all matching is done numerically. Conversion from a symbol to a number (and any related manipulations) would slow the sql. Do enough of them and it really messes with performance.
hmmm
At first I thought this was just about server startup,
but I can see it may be about jdbc connection establishment.
If that's so, then possibly bad, depending on how often the connection is
made/broken, although it is only a hash lookup in a small table.
But there shouldn't be any translation on record access or sql computations
other that adding the GMT offset, which would be done whether the offset is
specified on the connection via symbol or number. Output formatting is a
different matter.
localhost [(none)]> select @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| America/Denver | America/Denver |
+--------------------+---------------------+
1 row in set (0.00 sec)
localhost [(none)]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-03-26 22:25:17 |
+---------------------+
1 row in set (0.02 sec)
Gary