Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15654

ADDTIME creates invalid timestamp value near DST changes

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5, 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • N/A
    • Data types
    • freebsd (from the ports collection)
    • 10.4.0-1

    Description

      reproducing this is time sensitive. And occurs just once a year ...

      Server is set to timezone Europe/Brussels
      at 2018-03-25 02:00:00 clocks would move to 03:00:00 (wintertime->summertime changeover) making times between 2AM and 3 AM invalid.

      1 hour before this Timezone change I noticed errors inserting records into a table that had a column defined as:

      +-----------+------------------+------+-----+---------------------+-------+
      | Field     | Type             | Null | Key | Default             | Extra |
      +-----------+------------------+------+-----+---------------------+-------+
      [...]
      | expires   | timestamp        | NO   |     | 0000-00-00 00:00:00 |       |
      +-----------+------------------+------+-----+---------------------+-------+
      

      The value inserted was calculated on insert by mariadb itself using ADDTIME(NOW(),'01:00:00') as value for the expires field.

      Mariadb was using the system Timezones:

      MariaDB [(none)]> SELECT @@global.time_zone, @@session.time_zone;
      +--------------------+---------------------+
      | @@global.time_zone | @@session.time_zone |
      +--------------------+---------------------+
      | SYSTEM             | SYSTEM              |
      +--------------------+---------------------+
      1 row in set (0.00 sec)
      

      Essentially ADDTIME creates invalid dates as it doesn't account for timezone changes.
      While at the same time (pun not intended) mariadb does enforce the validity of such timestamps when inserted in a field in the database.

      MariaDB [adminsite]> select now();
      +---------------------+
      | now()               |
      +---------------------+
      | 2018-03-25 01:38:44 |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [adminsite]> select ADDTIME(NOW(),'01:00:00');
      +---------------------------+
      | ADDTIME(NOW(),'01:00:00') |
      +---------------------------+
      | 2018-03-25 02:39:34       |
      +---------------------------+
      1 row in set (0.00 sec)
      

      Actual insert error message (Time ran out, sorry, DST change occurred before I could fully document it) example:
      PHP message: mysqli::execute: Incorrect datetime value: '2018-03-25 02:29:27' for column 'expires'

      Issuing a "set time_zone='+00:00';" before using the database connection from the application worked around the the problem (I don't mind not using local time at all - in fact using localtime only started after a server swap and did go by unnoticed till now).

      Still if the database insists on valid time, it should also calculate valid time itself.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              swa.frantzen@gmail.com Swa F
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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