mysql time-zone ambiguity?

[Bruce Ferrell]

On 3/26/21 10:39 AM, Gary Aitken wrote:
> 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 at 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


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 :-)