Date: Fri, 26 Mar 2021 09:05:32 -0700 From: Bruce Ferrell <bferrell@baywinds.org> To: freebsd-questions@freebsd.org Subject: Re: mysql time-zone ambiguity? Message-ID: <bfc8b528-8af0-2d13-7294-0095549ffd25@baywinds.org> In-Reply-To: <dba947d1-c5bd-b732-55da-4651d9297dff@dreamchaser.org> References: <dba947d1-c5bd-b732-55da-4651d9297dff@dreamchaser.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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. > > Gary > _______________________________________________ > freebsd-questions@freebsd.org mailing list > https://lists.freebsd.org/mailman/listinfo/freebsd-questions > To unsubscribe, send any mail to "freebsd-questions-unsubscribe@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/'| || || ||
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?bfc8b528-8af0-2d13-7294-0095549ffd25>