mysql time-zone ambiguity?
Bruce Ferrell
bferrell at baywinds.org
Fri Mar 26 16:05:42 UTC 2021
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 at 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-questions at freebsd.org mailing list
> https://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to "freebsd-questions-unsubscribe at freebsd.org"
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/'|
||
||
||
More information about the freebsd-questions
mailing list