From owner-freebsd-questions@freebsd.org Fri Mar 26 16:05:42 2021 Return-Path: Delivered-To: freebsd-questions@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id A14AA57B556 for ; Fri, 26 Mar 2021 16:05:42 +0000 (UTC) (envelope-from bferrell@baywinds.org) Received: from baywinds.org (50-196-187-248-static.hfc.comcastbusiness.net [50.196.187.248]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "baywinds.org", Issuer "rr-v" (not verified)) by mx1.freebsd.org (Postfix) with ESMTPS id 4F6Rdx5qBrz4rmg for ; Fri, 26 Mar 2021 16:05:41 +0000 (UTC) (envelope-from bferrell@baywinds.org) Received: from [192.0.2.130] (rr-iii [192.0.2.130]) by baywinds.org (8.14.4/8.14.4) with ESMTP id 12QG5WMV025622 for ; Fri, 26 Mar 2021 09:05:32 -0700 Subject: Re: mysql time-zone ambiguity? To: freebsd-questions@freebsd.org References: From: Bruce Ferrell Message-ID: Date: Fri, 26 Mar 2021 09:05:32 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.11.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-Greylist: inspected by milter-greylist-4.5.12 (baywinds.org [192.0.2.134]); Fri, 26 Mar 2021 09:05:32 -0700 (PDT) for IP:'192.0.2.130' DOMAIN:'rr-iii' HELO:'[192.0.2.130]' FROM:'bferrell@baywinds.org' RCPT:'' X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-4.5.12 (baywinds.org [192.0.2.134]); Fri, 26 Mar 2021 09:05:32 -0700 (PDT) X-Rspamd-Queue-Id: 4F6Rdx5qBrz4rmg X-Spamd-Bar: / Authentication-Results: mx1.freebsd.org; dkim=none; dmarc=pass (policy=none) header.from=baywinds.org; spf=pass (mx1.freebsd.org: domain of bferrell@baywinds.org designates 50.196.187.248 as permitted sender) smtp.mailfrom=bferrell@baywinds.org X-Spamd-Result: default: False [-0.80 / 15.00]; SUBJECT_ENDS_QUESTION(1.00)[]; ARC_NA(0.00)[]; MID_RHS_MATCH_FROM(0.00)[]; FROM_HAS_DN(0.00)[]; RBL_DBL_DONT_QUERY_IPS(0.00)[50.196.187.248:from]; TO_MATCH_ENVRCPT_ALL(0.00)[]; R_SPF_ALLOW(-0.20)[+a]; MIME_GOOD(-0.10)[text/plain]; TO_DN_NONE(0.00)[]; PREVIOUSLY_DELIVERED(0.00)[freebsd-questions@freebsd.org]; RCPT_COUNT_ONE(0.00)[1]; SPAMHAUS_ZRD(0.00)[50.196.187.248:from:127.0.2.255]; RCVD_TLS_LAST(0.00)[]; NEURAL_SPAM_SHORT(1.00)[1.000]; NEURAL_HAM_LONG(-1.00)[-1.000]; DMARC_POLICY_ALLOW(-0.50)[baywinds.org,none]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; FROM_EQ_ENVFROM(0.00)[]; R_DKIM_NA(0.00)[]; MIME_TRACE(0.00)[0:+]; RCVD_COUNT_TWO(0.00)[2]; ASN(0.00)[asn:7922, ipnet:50.128.0.0/9, country:US]; MAILMAN_DEST(0.00)[freebsd-questions] X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 26 Mar 2021 16:05:42 -0000 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* 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/'| || || ||