Date: Fri, 26 Mar 2021 11:39:56 -0600 From: Gary Aitken <freebsd@dreamchaser.org> To: Bruce Ferrell <bferrell@baywinds.org>, freebsd-questions@freebsd.org Subject: Re: mysql time-zone ambiguity? Message-ID: <80c1be90-8780-6cb9-36aa-1dc4e22ccfaf@dreamchaser.org> In-Reply-To: <bfc8b528-8af0-2d13-7294-0095549ffd25@baywinds.org> References: <dba947d1-c5bd-b732-55da-4651d9297dff@dreamchaser.org> <bfc8b528-8af0-2d13-7294-0095549ffd25@baywinds.org>
next in thread | previous in thread | raw e-mail | index | archive | help
On 3/26/21 10:05 AM, Bruce Ferrell wrote: > 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. > Your error is from JDBC/java. I figured that out :-) <snip> > 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/'| Thanks. 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. Gary
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?80c1be90-8780-6cb9-36aa-1dc4e22ccfaf>