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

When setting back the system time while mysqld is running, NOW() and UNIX_TIMESTAMP() results get stuck

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3.4, 10.3.16, 10.4.6
    • 10.3.18, 10.4.8
    • Server
    • None

    Description

      Since MariaDB 10.3.4, when setting the system clock back in time (I tested by setting it back by 24h+), the NOW() and UNIX_TIMESTAMP() functions will not return the current, new, system time, but a fixed value from before when the system time was changed:

      MariaDB [(none)]> SELECT now(), sysdate(), unix_timestamp();
      		 
      +---------------------+---------------------+------------------+
      		 
      | now()               | sysdate()           | unix_timestamp() |
      		 
      +---------------------+---------------------+------------------+
      		 
      | 2019-07-16 18:36:29 | 2019-07-16 18:36:29 |       1563302189 |
      		 
      +---------------------+---------------------+------------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [(none)]> \! date 07152000
      		 
      Mon Jul 15 20:00:00 UTC 2019
      		 
       
      		 
      MariaDB [(none)]> SELECT now(), sysdate(), unix_timestamp();
      		 
      +---------------------+---------------------+------------------+
      		 
      | now()               | sysdate()           | unix_timestamp() |
      		 
      +---------------------+---------------------+------------------+
      		 
      | 2019-07-16 18:36:31 | 2019-07-15 20:00:00 |       1563302191 |
      		 
      +---------------------+---------------------+------------------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MariaDB [(none)]> SELECT sleep(2);
      		 
      +----------+
      		 
      | sleep(2) |
      		 
      +----------+
      		 
      |        0 |
      		 
      +----------+
      		 
      1 row in set (2.000 sec)
      		 
       
      		 
      MariaDB [(none)]> SELECT now(), sysdate(), unix_timestamp();
      		 
      +---------------------+---------------------+------------------+
      		 
      | now()               | sysdate()           | unix_timestamp() |
      		 
      +---------------------+---------------------+------------------+
      		 
      | 2019-07-16 18:36:31 | 2019-07-15 20:00:02 |       1563302191 |
      		 
      +---------------------+---------------------+------------------+

      Note how SYSDATE() correctly flips back to July 15th, while NOW() and UNIX_TIMESTAMP() are stuck with the last seen timestamp from 16th before the system time was set backwards with date

      Attachments

        Activity

          Transition Time In Source Status Execution Times
          Sergei Golubchik made transition -
          Open In Progress
          		47d 20h 29m 1
          Sergei Golubchik made transition -
          In Progress Stalled
          		22h 18m 1
          Sergei Golubchik made transition -
          Stalled Closed
          		18h 1

          People

            serg Sergei Golubchik
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.