mysql time-zone ambiguity?

[Bruce Ferrell]

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.
+--------------------+---------------------+
+--------------------+---------------------+
| 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
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
|mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql |
|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

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.

The thing that ALWAYS works for setting the time zone is to manually set it as the offset and never use symbolics in the options file:

default-time-zone='+0700'

Mine looks like this and I didn't set it in the my.cnf file :

mysql> select @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-26 10:56:51 |
+---------------------+
1 row in set (0.00 sec)


java doesn't like it that way, but mysql is fine with it... I have my opinions about what java thinks, but like I said, opinions vary :-)

[Gary Aitken]

<snip>

Post by Bruce Ferrell

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

[Bruce Ferrell]

Post by Bruce Ferrell
default-time-zone='+0700'

That doesn't look like it handles daylight saving time.
_______________________________________________
https://lists.freebsd.org/mailman/listinfo/freebsd-questions

Did I say it dealt with DST? This is why, when I set up an enterprise instance, I try to set for UTC and make the developers adjust for the users locale in the application...
Sometimes I get away with it.

:-)

There is a discussion of DST rules and named timezone support in the mysql documentation.  I'll put the link for v5.7 below as it's kind of long to paste below.  It's more or less
the same with minor changes for various versions:

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

It also mentions that once one starts doing this one needs to be aware of the fact that DST rules DO change periodically and the tables for that support need to be updated
accordingly.... One more thing to keep up to date!