MySQL Bugs: #42054: SELECT CURDATE() is returning bad value

Bug #42054	SELECT CURDATE() is returning bad value
Submitted:	12 Jan 2009 15:53	Modified:	29 Jan 2011 23:12
Reporter:	Nick Miles	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.26, 5.1.30	OS:	Solaris (5.10)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	curdate

Description:
"select curdate();" is returning an old date of 2008-12-23.  When we look at our tables where curdate() is used to populate the date, this has been occurring since that date of 2008-12-23.  I do not see anything in the mysql logs, or in system logs on that date, or related to this issue.  Unix system datetime is correct with current datetime.  The instance was up for 28 days prior to that date, and has been up since then.  

FYI - this instance is replicated using mysql replication, and the replicated box is showing the correct date with curdate(*).

Thanks,
Nick 
nmiles@peapod.com

How to repeat:
-bash-3.00$ date
Mon Jan 12 09:33:10 CST 2009
-bash-3.00$ mysql -u c00dba -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2267
Server version: 5.1.26-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2008-12-23 | 
+------------+
1 row in set (0.00 sec)

mysql>

Thank you for the report.

But version 5.1.26 is old and would be good if this can be tested with current version 5.1.30.

Also please check if you replaced system time zone file or changed system time near problem date. This looks like problem described in bug #27103 for me.

Bouncing engine fixed the issue for the time being.

We have a job that sync's time daily on the machine, that would have been the only thing that could have changed time or timestamp on that day.  We must do this daily (according to unix admins).  So I wish there was a patch in later MySQL version to fix this issue.  It's only hit us one time, but caused a lot of trouble when it did.

Thanks.

Thank you for the feedback.

You are right: this should be fixed especially because it is repeatable what date does not change.

How to repeat (on OpenSolaris):

1. Run mysqld while timezone is TZ1 during DATE1
2. Change system timezone to some other (TZ2) using GUI
3. Synchronize with button "synchronize now"
4. Wait when date changes in both timezones (TZ1 and TZ2) to DATE2
5. Run select curdate() and notice it outputs DATE1

If skip point 3 and setup time manually MySQL successfully switches to the new time.

Bug #58054 was marked as duplicate of this one.

Bug #57719 was marked as duplicate of this one.

Solaris bug that might be relevant:

https://defect.opensolaris.org/bz/show_bug.cgi?id=9201

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126712

3520 Davi Arnaut	2010-12-13
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time. This bug was happening due to a logic in the
      time retrieving wrapper function which would only call the time()
      function every half second. This interval between calls would be
      calculated using the gethrtime() and the logic relied on the fact
      that time returned by it is monotonic. Unfortunately, due to bugs
      in the gethrtime() implementation, there are some cases where
      the time returned by it can drift (See Solaris bug id 6600939),
      potentially causing the interval calculation logic to fail.
      
      Since this logic was being used due to ancient performance reasons,
      the solution is to simply rely on time(), which nowadays is just a
      wrapper around the somewhat fast gettimeofday function. This also
      allows us to eliminate a lock which was used to control access
      to the variables used to track the half second interval. In spite
      of the the aforementioned problem, gethrtime() is still used in a
      few places where we need a high resolution but under which drifts
      wouldn't cause problems.
     @ mysys/my_getsystime.c
        Use time() even if gethrtime() is available. Remove logic which
        relied on gethrtime() to only call time() every half second.
     @ mysys/my_thr_init.c
        Remove mutex which is no longer used.
     @ mysys/mysys_priv.h
        Remove mutex which is no longer used.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128358

3540 Davi Arnaut	2011-01-10
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      The solution is to retrieve the correct time whenever a drift in
      the time returned by gethrtime() is detected. That is, do not
      use the cached time whenever the values (previous and current)
      returned by gethrtime() are not monotonically increasing.
     @ mysys/my_getsystime.c
        Do not used the cached time if gethrtime is not monotonic.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128359

3540 Davi Arnaut	2011-01-10
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      The solution is to retrieve the correct time whenever a drift in
      the time returned by gethrtime() is detected. That is, do not
      use the cached time whenever the values (previous and current)
      returned by gethrtime() are not monotonically increasing.
     @ mysys/my_getsystime.c
        Do not used the cached time if gethrtime is not monotonic.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128360

3232 Davi Arnaut	2011-01-10
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      Since newer versions of Solaris (10+) have alleviated the
      performance degradation associated with time(2), the solution is
      to simply directly rely on time() at each invocation.
      
      This simplification has an upside that it allows us to eliminate
      a lock which was used to control access to the variables used
      to track the half second interval, thus improving the overall
      scalability of timekeeping related functions (e.g. NOW()).
      
      In spite of the the aforementioned problem, gethrtime() is still
      used in a few places where we need a high resolution but under
      which drifts don't pose a problem.
     @ mysys/my_getsystime.c
        Use time() even if gethrtime() is available. Remove logic which
        relied on gethrtime() to only call time() every half second.
     @ mysys/my_thr_init.c
        Remove mutex which is no longer used.
     @ mysys/mysys_priv.h
        Remove mutex which is no longer used.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128424

3541 Davi Arnaut	2011-01-11
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      The solution is to retrieve the correct time whenever a drift in
      the time returned by gethrtime() is detected. That is, do not
      use the cached time whenever the values (previous and current)
      returned by gethrtime() are not monotonically increasing.
     @ mysys/my_getsystime.c
        Do not used the cached time if gethrtime is not monotonic.

Pushed into mysql-5.1 5.1.55 (revid:davi.arnaut@oracle.com-20110111134438-zuh1t2o1i2dofgcr) (version source revid:davi.arnaut@oracle.com-20110111134438-zuh1t2o1i2dofgcr) (merge vers: 5.1.55) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:davi.arnaut@oracle.com-20110111135350-v9yoe2laktbyummh) (version source revid:davi.arnaut@oracle.com-20110111135350-v9yoe2laktbyummh) (merge vers: 5.5.9) (pib:24)

Pushed into mysql-trunk 5.6.2 (revid:davi.arnaut@oracle.com-20110111135623-r7lqmol54vjn68b8) (version source revid:davi.arnaut@oracle.com-20110111135623-r7lqmol54vjn68b8) (merge vers: 5.6.2) (pib:24)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128431

3236 Davi Arnaut	2011-01-11
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      Since newer versions of Solaris (10+) have alleviated the
      performance degradation associated with time(2), the solution is
      to simply directly rely on time() at each invocation.
      
      This simplification has an upside that it allows us to eliminate
      a lock which was used to control access to the variables used
      to track the half second interval, thus improving the overall
      scalability of timekeeping related functions (e.g. NOW()).
      
      In spite of the the aforementioned problem, gethrtime() is still
      used in a few places where we need a high resolution but under
      which drifts don't pose a problem.
     @ mysys/my_getsystime.c
        Use time() even if gethrtime() is available. Remove logic which
        relied on gethrtime() to only call time() every half second.
     @ mysys/my_thr_init.c
        Remove mutex which is no longer used.
     @ mysys/mysys_priv.h
        Remove mutex which is no longer used.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128450

3236 Davi Arnaut	2011-01-11
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      Since newer versions of Solaris (10+) have alleviated the
      performance degradation associated with time(2), the solution is
      to simply directly rely on time() at each invocation.
      
      This simplification has an upside that it allows us to eliminate
      a lock which was used to control access to the variables used
      to track the half second interval, thus improving the overall
      scalability of timekeeping related functions (e.g. NOW()).
      
      In spite of the the aforementioned problem, gethrtime() is still
      used in a few places where we need a high resolution but under
      which drifts don't pose a problem.
     @ mysys/my_getsystime.c
        Use time() even if gethrtime() is available. Remove logic which
        relied on gethrtime() to only call time() every half second.
        Since gethrtime() is not used anymore, also remove it from
        my_micro_time() to keep a common time source.
        
        Also, function comments are cleaned up (fixed typos and wrong
        information) and converted to doxygen.
     @ mysys/my_thr_init.c
        Remove mutex which is no longer used.
     @ mysys/mysys_priv.h
        Remove mutex which is no longer used.

Noted in 5.1.55, 5.5.9 changelogs.

On Solaris, time-related functions such as NOW() or SYSDATE() could
return a constant value.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128560

3231 Davi Arnaut	2011-01-12
      Bug#42054: SELECT CURDATE() is returning bad value
      
      The problem from a user point of view was that on Solaris the
      time related functions (e.g. NOW(), SYSDATE(), etc) would always
      return a fixed time.
      
      This bug was happening due to a logic in the time retrieving
      wrapper function which would only call the time() function every
      half second. This interval between calls would be calculated
      using the gethrtime() and the logic relied on the fact that time
      returned by it is monotonic.
      
      Unfortunately, due to bugs in the gethrtime() implementation,
      there are some cases where the time returned by it can drift
      (See Solaris bug id 6600939), potentially causing the interval
      calculation logic to fail.
      
      Since newer versions of Solaris (10+) have alleviated the
      performance degradation associated with time(2), the solution is
      to simply directly rely on time() at each invocation.
      
      This simplification has an upside that it allows us to eliminate
      a lock which was used to control access to the variables used
      to track the half second interval, thus improving the overall
      scalability of timekeeping related functions (e.g. NOW()).
      
      Benchmarks runs have shown no significant degradation associated
      with this change. With this, there are actually improvements in
      performance for cases involving many connections.
      
      In summary, the changes introduced by this patch are:
      
      a) my_time() and my_micro_time_and_time() no longer use gethrtime().
         Instead, time() and gettimeofdate() are used correspondingly.
      
      b) my_micro_time() is changed to not use gethrtime() so as to
         have the same time source as my_micro_time_and_time().
         There shouldn't be any performance impact from this change
         since this function is used only a few times during statement
         execution and, on Solaris, gettimeofday() shows acceptable
         performance.
     @ mysys/my_getsystime.c
        Use time() even if gethrtime() is available. Remove logic which
        relied on gethrtime() to only call time() every half second.
        Since gethrtime() is not used anymore, also remove it from
        my_micro_time() to keep a common time source.
        
        Also, function comments are cleaned up (fixed typos and wrong
        information) and converted to doxygen.
     @ mysys/my_thr_init.c
        Remove mutex which is no longer used.
     @ mysys/mysys_priv.h
        Remove mutex which is no longer used.

Pushed into mysql-5.5 5.5.9 (revid:davi.arnaut@oracle.com-20110112203639-5czdsypiqwpcivub) (version source revid:davi.arnaut@oracle.com-20110112203639-5czdsypiqwpcivub) (merge vers: 5.5.9) (pib:24)

Pushed into mysql-trunk 5.6.2 (revid:davi.arnaut@oracle.com-20110113113120-ntwouogfwaalgxq7) (version source revid:davi.arnaut@oracle.com-20110113113120-ntwouogfwaalgxq7) (merge vers: 5.6.2) (pib:24)