[CAM-9498] Cannot store dates / timestamps after 2038 in MariaDB/MySQL Created: 02/Nov/18  Updated: 01/Feb/19  Resolved: 30/Jan/19

Status: Closed
Project: camunda BPM
Component/s: engine
Affects Version/s: 7.9.0
Fix Version/s: 7.11.0, 7.11.0-alpha1

Type: Bug Report Priority: L3 - Default
Reporter: Philipp Ossler Assignee: Thorben Lindhauer
Resolution: Fixed Votes: 0
Labels: SUPPORT
Remaining Estimate: 0 minutes
Time Spent: Not Specified
Original Estimate: 0 minutes

Issue Links:
Depedendency
is depended on by CAM-9636 Remove extra queries to prevent updat... Closed
Duplicate
is duplicated by CAM-9379 Problems with jobs that have activati... Closed
Related
is related to CAM-9505 On MariaDB and MySQL an update operat... Closed

 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:



 Comments   
Comment by Philipp Ossler [ 02/Nov/18 ]

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

Comment by Thorben Lindhauer [ 29/Jan/19 ]

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
Comment by Nikola Koevski [ 30/Jan/19 ]

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.

Generated at Mon Oct 14 23:32:15 CEST 2019 using JIRA 6.4.6#64021-sha1:33e5b454af4594f54560ac233c30a6e00459507e.