Was trying to better handle time zone differences between my client (time zone is GMT) and the MySQL server I connect to (whose time zone is CEST, which is GMT+2 right now due to DST).
So, looking at: https://mariadb.com/kb/en/about-mariadb-connector-j/#infrequently-used-parameters
I started by adding: serverTimezone=CEST
to my connection properties.
The first problem I saw and that I discovered by debugging org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.loadCalendar(String, String) is that the mentioned property is ignored by default. I must also set another non-documented property which is: useLegacyDatetimeCode=false
I know the latter property is an option documented for MySQL Connector/J, but it seems not to be documented for MariaDB Connector/J, at least I see no mentions of it in the above link.
Secondly, once I added the latter option I got a parse error because "CEST" seems not to be a valid time zone id for Java. No problem, I can fix by setting serverTimezone=Europe/Rome instead. But it's much more strange the behaviour when you set useLegacyDatetimeCode=false but you don't set any serverTimezone.
In this scenario, an when MySQL @time_Zone is set to SYSTEM, the driver is supposed to use the system time zone as set on the MySQL server. In fact, this code is executed in org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.loadCalendar(String, String):
In my case, srvTimeZone and srvSystemTimeZone are set by org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.postConnectionQueries() to be serverData.get("time_zone") and serverData.get("system_time_zone") respectively. On my MySQL instance, this query:
SELECT @@time_zone, @@system_time_zone;
Hence, MariaDB Connector/J will fail once again complaining that CEST is not a valid time zone.
To sum up, I see two problems:
- useLegacyDatetimeCode is not documented and it's absolutely required to make serverTimezone have any effect; I was wondering whether it should be set to false by default... By now, it's true by default and this disables all the code that should handle time zones in a more correct way
- with useLegacyDatetimeCode=false and no explicit serverTimezone set, MariaDB fails on my system because it can't recognize CEST time zone id, which is the actual system time zone setting of my MySQL instance; perhaps Java time zone resolution differs from MySQL one
Please note that the Ubuntu system where that MySQL instance is running has a /etc/timezone containing: Europe/Rome. Still, MySQL sets its @@system_time_zone variable to CEST (which is an alias for the same time zone, though). This is "by default", that is I didn't change anything in MySQL configuration to cause this. MySQL was installed through the distribution package manager (apt).