Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-822

Difficulties with time zone handling

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.6.0
    • Fix Version/s: None
    • Component/s: configuration
    • Labels:
      None
    • Environment:
      Linux Mint 18.3 (based on Ubuntu 16.04) client
      MySQL 5.7.22 on Ubuntu 16.04 server

      Description

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

            String tz = options.serverTimezone;
            if (tz == null) {
              tz = srvTimeZone;
              if ("SYSTEM".equals(tz)) {
                tz = srvSystemTimeZone;
              }
            }
      

      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;
      returns: SYSTEM,CEST
      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).

        Attachments

          Activity

            People

            Assignee:
            diego dupin Diego Dupin
            Reporter:
            mauromol Mauro Molinari
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.