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
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[12 Jan 2009 15:53] Nick Miles
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>
[12 Jan 2009 17:51] Sveta Smirnova
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.
[14 Jan 2009 22:53] Nick Miles
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.
[23 Jan 2009 22:01] Sveta Smirnova
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.
[8 Nov 2010 21:10] Sveta Smirnova
Bug #58054 was marked as duplicate of this one.
[10 Nov 2010 23:22] Sveta Smirnova
Bug #57719 was marked as duplicate of this one.
[13 Dec 2010 23:33] Davi Arnaut
Solaris bug that might be relevant:

https://defect.opensolaris.org/bz/show_bug.cgi?id=9201
[13 Dec 2010 23:34] Bugs System
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.
[10 Jan 2011 21:57] Bugs System
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.
[10 Jan 2011 21:57] Bugs System
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.
[10 Jan 2011 22:08] Bugs System
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.
[11 Jan 2011 13:44] Bugs System
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.
[11 Jan 2011 13:49] Bugs System
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)
[11 Jan 2011 13:55] Bugs System
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)
[11 Jan 2011 13:58] Bugs System
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)
[11 Jan 2011 14:02] Bugs System
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.
[11 Jan 2011 16:32] Bugs System
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.
[12 Jan 2011 17:53] Paul Dubois
Noted in 5.1.55, 5.5.9 changelogs.

On Solaris, time-related functions such as NOW() or SYSDATE() could
return a constant value.
[12 Jan 2011 20:36] Bugs System
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.
[13 Jan 2011 11:29] Bugs System
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)
[13 Jan 2011 11:39] Bugs System
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)