mysql time-zone ambiguity?

[Bruce Ferrell]

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/'|

||
||

||