From owner-freebsd-questions@freebsd.org Sat Mar 27 04:32:39 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 1BDCF5B8958 for ; Sat, 27 Mar 2021 04:32:39 +0000 (UTC) (envelope-from freebsd@dreamchaser.org) Received: from nightmare.dreamchaser.org (ns.dreamchaser.org [66.109.141.57]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "dreamchaser.org", Issuer "R3" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id 4F6mCp0VcPz4hws for ; Sat, 27 Mar 2021 04:32:37 +0000 (UTC) (envelope-from freebsd@dreamchaser.org) Received: from breakaway.dreamchaser.org (breakaway [192.168.151.122]) by nightmare.dreamchaser.org (8.15.2/8.15.2) with ESMTP id 12R4WPLk070630; Fri, 26 Mar 2021 22:32:26 -0600 (MDT) (envelope-from freebsd@dreamchaser.org) Reply-To: freebsd@dreamchaser.org Subject: Re: mysql time-zone ambiguity? To: Bruce Ferrell , freebsd-questions@freebsd.org References: <80c1be90-8780-6cb9-36aa-1dc4e22ccfaf@dreamchaser.org> <6cc15ffb-8941-27bd-e9ef-b4960c4ed303@baywinds.org> From: Gary Aitken Message-ID: <66bad0ec-a886-3740-98b3-e30b41b16ccc@dreamchaser.org> Date: Fri, 26 Mar 2021 22:32:25 -0600 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:78.0) Gecko/20100101 Thunderbird/78.6.1 MIME-Version: 1.0 In-Reply-To: <6cc15ffb-8941-27bd-e9ef-b4960c4ed303@baywinds.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-4.6.2 (nightmare.dreamchaser.org [192.168.151.101]); Fri, 26 Mar 2021 22:32:27 -0600 (MDT) X-Rspamd-Queue-Id: 4F6mCp0VcPz4hws X-Spamd-Bar: -- Authentication-Results: mx1.freebsd.org; dkim=none; dmarc=none; spf=pass (mx1.freebsd.org: domain of freebsd@dreamchaser.org designates 66.109.141.57 as permitted sender) smtp.mailfrom=freebsd@dreamchaser.org X-Spamd-Result: default: False [-2.29 / 15.00]; HAS_REPLYTO(0.00)[freebsd@dreamchaser.org]; ARC_NA(0.00)[]; MID_RHS_MATCH_FROM(0.00)[]; FROM_HAS_DN(0.00)[]; TO_DN_SOME(0.00)[]; R_SPF_ALLOW(-0.20)[+mx]; NEURAL_HAM_LONG(-1.00)[-1.000]; MIME_GOOD(-0.10)[text/plain]; REPLYTO_ADDR_EQ_FROM(0.00)[]; DMARC_NA(0.00)[dreamchaser.org]; SPAMHAUS_ZRD(0.00)[66.109.141.57:from:127.0.2.255]; TO_MATCH_ENVRCPT_SOME(0.00)[]; RBL_DBL_DONT_QUERY_IPS(0.00)[66.109.141.57:from]; NEURAL_HAM_SHORT(-0.99)[-0.994]; RCPT_COUNT_TWO(0.00)[2]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; RCVD_TLS_LAST(0.00)[]; FROM_EQ_ENVFROM(0.00)[]; R_DKIM_NA(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:21947, ipnet:66.109.128.0/19, country:US]; SUBJECT_ENDS_QUESTION(1.00)[]; MAILMAN_DEST(0.00)[freebsd-questions]; RCVD_COUNT_TWO(0.00)[2] 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: Sat, 27 Mar 2021 04:32:39 -0000 On 3/26/21 12:11 PM, Bruce Ferrell wrote: > 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