Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-9498

Cannot store dates / timestamps after 2038 in MariaDB/MySQL

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: 7.9.0
    • Fix Version/s: 7.11.0, 7.11.0-alpha1
    • Component/s: engine
    • Labels:

      Description

      Currently, we use two different types to store a date value in a MariaDB/MySQL database: TIMESTAMP and DATETIME. The type TIMESTAMP has the limitation that it only works until 2038.

      AT:

      • replace the type TIMESTAMP to allow dates after 2038
      • use one type for all dates in the database

      References:

        Issue Links

          Activity

          Hide
          philipp.ossler Philipp Ossler added a comment -

          The biggest challenge is how to migrate the schema from an existing MariaDB / MySQL instance.

          Show
          philipp.ossler Philipp Ossler added a comment - The biggest challenge is how to migrate the schema from an existing MariaDB / MySQL instance.
          Hide
          thorben.lindhauer Thorben Lindhauer added a comment - - edited

          Some findings on MySQL/MariaDB timestamp handling and the jdbc driver (based on MariaDB jdbc driver 1.5.5):

          Scenario:

          • A column is declared as TIMESTAMP
          • The JVM runs in UTC+1
          • The database runs in UTC-2
          • The session timezone is the same as the database timezone (so UTC-2)
          • There are no extra configuration string options declared
          • The application inserts the java.util.Date object that represents 10:40 UTC+1 == 9:40 UTC == 7:40 UTC-2

          Behavior:

          1. The MariaDB driver detects the JVM's local time zone and fetches the corresponding java.util.Calendar
          2. It uses this calendar to convert the timestamp to the local time and sends the value '10:40' in the SQL query
          3. The database interprets this in the session timezone (i.e. 10:40 UTC-2, converts it to UTC and persists the timestamp 12:40 UTC
          4. When retrieving this value again, the same procedure is applied in the opposite direction, i.e. the session receives 10:40 UTC-2, ignores the session time zone and interprets the local time in the JVM's time zone

          Side notes:

          • If the connection string parameter useLegacyDatetimeCode is set to false, then the JVM's time zone will not be considered when converting the Date to the parameter string. Instead, the setting serverTimezone and the session time zone are used.

          Source:

          • Source code of the MariaDB JDBC driver; it is relatively straightforward to find how timestamps are processed and serialized
          Show
          thorben.lindhauer Thorben Lindhauer added a comment - - edited Some findings on MySQL/MariaDB timestamp handling and the jdbc driver (based on MariaDB jdbc driver 1.5.5): Scenario: A column is declared as TIMESTAMP The JVM runs in UTC+1 The database runs in UTC-2 The session timezone is the same as the database timezone (so UTC-2 ) There are no extra configuration string options declared The application inserts the java.util.Date object that represents 10:40 UTC+1 == 9:40 UTC == 7:40 UTC-2 Behavior: The MariaDB driver detects the JVM's local time zone and fetches the corresponding java.util.Calendar It uses this calendar to convert the timestamp to the local time and sends the value '10:40' in the SQL query The database interprets this in the session timezone (i.e. 10:40 UTC-2 , converts it to UTC and persists the timestamp 12:40 UTC When retrieving this value again, the same procedure is applied in the opposite direction, i.e. the session receives 10:40 UTC-2 , ignores the session time zone and interprets the local time in the JVM's time zone Side notes: If the connection string parameter useLegacyDatetimeCode is set to false , then the JVM's time zone will not be considered when converting the Date to the parameter string. Instead, the setting serverTimezone and the session time zone are used. Source: Source code of the MariaDB JDBC driver; it is relatively straightforward to find how timestamps are processed and serialized
          Hide
          nikola.koevski Nikola Koevski added a comment -

          Several time zone scenarios were tested for running the update script:

          Application Server TZ1 + DB Server TZ2 + DB Update Session TZ2 + useLegacyDatetimeCode flag true
          Application Server TZ1 + DB Server TZ2 + DB Update Session TZ2 + useLegacyDatetimeCode flag false
          Application Server TZ1 + DB Session TZ2 + DB Server TZ3 + DB Update Session TZ3 + useLegacyDatetimeCode flag true
          Application Server TZ1 + DB Session TZ2 + DB Server TZ3 + DB Update Session TZ3 + useLegacyDatetimeCode flag false

          In all the scenarios the update was successful and the engine operation was unchanged.

          Show
          nikola.koevski Nikola Koevski added a comment - Several time zone scenarios were tested for running the update script: Application Server TZ1 + DB Server TZ2 + DB Update Session TZ2 + useLegacyDatetimeCode flag true Application Server TZ1 + DB Server TZ2 + DB Update Session TZ2 + useLegacyDatetimeCode flag false Application Server TZ1 + DB Session TZ2 + DB Server TZ3 + DB Update Session TZ3 + useLegacyDatetimeCode flag true Application Server TZ1 + DB Session TZ2 + DB Server TZ3 + DB Update Session TZ3 + useLegacyDatetimeCode flag false In all the scenarios the update was successful and the engine operation was unchanged.

            People

            • Assignee:
              thorben.lindhauer Thorben Lindhauer
              Reporter:
              philipp.ossler Philipp Ossler
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development