List:General Discussion« Previous MessageNext Message »
From:walter harms Date:June 28 2013 7:44am
Subject:Re: NOW() is stuck...
View as plain text  
hi,
does the value change at all like below ?

mysql> show global variables like 'timestamp';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| timestamp     | 1372404355 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global variables like 'timestamp';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| timestamp     | 1372404371 |
+---------------+------------+
1 row in set (0.00 sec)


re,
 wh



Am 27.06.2013 20:19, schrieb Andy Wallace:
> Benjamin -
> Unfortunately:
> 
> mysql> show global variables like 'timestamp';
> +---------------+------------+
> | Variable_name | Value      |
> +---------------+------------+
> | timestamp     | 1372238834 |
> +---------------+------------+
> 1 row in set (0.00 sec)
> 
> And:
> 
> mysql> set global timestamp = 0;
> ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't
> be used with SET GLOBAL
> 
> This does indeed persist across sessions. Any command line connection I
> make to the database
> shows the "bad" value for NOW(). I also tweaked the application code to
> include NOW() in an
> existing query, and the value returned to my PHP code is also the "bad"
> value.
> 
> Thanks for looking,
> andy
> 
> 
> 
> 
> On 6/27/13 11:10 AM, Stillman, Benjamin wrote:
>> It persists across sessions?
>> Does this return anything:
>>
>> show global variables like 'timestamp';
>>
>> Hopefully it returns:
>>
>> Empty set (0.00 sec)
>>
>> I vaguely remember reading about a bug in 5.1.4x with something to do
>> with
>> a global timestamp. I thought it only showed one though, and that you
>> couldn't set it.
>>
>> If the above returned a timestamp and not an empty set, try: set global
>> timestamp = 0;
>>
>> That should return something like this:
>>
>> ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't
>> be used with SET GLOBAL
>>
>> But if it returns:
>>
>> Query OK, 0 rows affected (0.00 sec)
>>
>> And then your queries return correct timestamps, you've found a bug.
>>
>> I'd hope that it would fail, but the only thing I can think of is if it's
>> being set as a global variable. If this does fix your problem, and if
>> you're using replication, you may have an issue with your replicated
>> data.
>> Replication uses timestamp extensively.
>>
>>
>>
>>
>>
>> On 6/27/13 1:44 PM, "Andy Wallace" <awallace@stripped> wrote:
>>
>>> But the question is how. I have nothing in the code that does it, or
>>> this
>>> would have been true for months instead of just the last 24 hours. In
>>> addition, this is currently set globally - no matter what connection to
>>> the database, it all comes up with this value. Which means that all my
>>> time-based queries no longer work correctly.
>>>
>>> Does your message suggest that setting it to 0 might clear the problem?
>>>
>>>
>>>
>>> On 6/27/13 10:31 AM, Stillman, Benjamin wrote:
>>>> Timestamp is a session variable, so it must have been set to something
>>>> other than 0 (1372228034 epoch is the date you're showing) in your
>>>> current
>>>> session.
>>>>
>>>>
>>>> mysql> set timestamp = 1372228034;
>>>> Query OK, 0 rows affected (0.00 sec)
>>>>
>>>>
>>>> mysql> select now(), sysdate();
>>>> +---------------------+---------------------+
>>>> | now()               | sysdate()           |
>>>> +---------------------+---------------------+
>>>> | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 |
>>>> +---------------------+---------------------+
>>>> 1 row in set (0.00 sec)
>>>>
>>>>
>>>> mysql> set timestamp = 0;
>>>> Query OK, 0 rows affected (0.00 sec)
>>>>
>>>>
>>>> mysql> select now(), sysdate();
>>>> +---------------------+---------------------+
>>>> | now()               | sysdate()           |
>>>> +---------------------+---------------------+
>>>> | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 |
>>>> +---------------------+---------------------+
>>>> 1 row in set (0.00 sec)
>>>>
>>>>
>>>>
>>>> Cliff's notes: set timestamp = 0;
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On 6/26/13 6:10 PM, "Andy Wallace" <awallace@stripped> wrote:
>>>>
>>>>> We've been having some issues with one of our MySQL servers lately,
>>>>> and
>>>>> currently
>>>>> the dang thing is "stuck". For at least the last hour, NOW() is
>>>>> returning
>>>>> the same
>>>>> value:
>>>>>
>>>>> mysql> select now();
>>>>> +---------------------+
>>>>> | now()               |
>>>>> +---------------------+
>>>>> | 2013-06-26 02:27:14 |
>>>>> +---------------------+
>>>>>
>>>>> The system variable "timestamp" also has that same time value
>>>>> stored in
>>>>> it. How
>>>>> can we kick this loose so that the values are more current with real
>>>>> time? (it is
>>>>> currently 3:08PM here, despite our MySQL instance thinking it's 2am.
>>>>> The
>>>>> system
>>>>> time on the machine is correct:
>>>>>
>>>>> $ date
>>>>> Wed Jun 26 15:08:56 PDT 2013
>>>>>
>>>>>
>>>>> This is MySQL 5.1.46 running on solaris2.10.
>>>>>
>>>>> Any ideas short of restarting the MySQL engine? I'm willing to do
>>>>> that,
>>>>> but would much
>>>>> rather wait and not do it in the middle of the day.
>>>>>
>>>>> Thanks,
>>>>> Andy
>>>>>
>>>>>
>>>>> -- 
>>>>> Andy Wallace
>>>>> iHOUSEweb, Inc.
>>>>> awallace@stripped
>>>>> (866) 645-7700 ext 219
>>>>> -- 
>>>>> "Sometimes it pays to stay in bed on Monday, rather than spending
> the
>>>>> rest of the week debugging Monday's code."
>>>>> - Christopher Thompson
>>>>>
>>>>> -- 
>>>>> MySQL General Mailing List
>>>>> For list archives: http://lists.mysql.com/mysql
>>>>> To unsubscribe:    http://lists.mysql.com/mysql
>>>>>
>>>>
>>>>
>>>> ________________________________
>>>>
>>>> Notice: This communication may contain privileged and/or confidential
>>>> information. If you are not the intended recipient, please notify the
>>>> sender by email, and immediately delete the message and any attachments
>>>> without copying or disclosing them. LBI may, for any reason, intercept,
>>>> access, use, and disclose any information that is communicated by or
>>>> through, or which is stored on, its networks, applications, services,
>>>> and devices.
>>>>
>>>
>>> -- 
>>> Andy Wallace
>>> iHOUSEweb, Inc.
>>> awallace@stripped
>>> (866) 645-7700 ext 219
>>> -- 
>>> "Sometimes it pays to stay in bed on Monday, rather than spending the
>>> rest of the week debugging Monday's code."
>>> - Christopher Thompson
>>>
>>> -- 
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql
>>>
>>
>>
>> ________________________________
>>
>> Notice: This communication may contain privileged and/or confidential
>> information. If you are not the intended recipient, please notify the
>> sender by email, and immediately delete the message and any
>> attachments without copying or disclosing them. LBI may, for any
>> reason, intercept, access, use, and disclose any information that is
>> communicated by or through, or which is stored on, its networks,
>> applications, services, and devices.
>>
> 
Thread
NOW() is stuck...Andy Wallace26 Jun
  • RE: NOW() is stuck...Rick James26 Jun
  • Re: NOW() is stuck...John Meyer26 Jun
    • Re: NOW() is stuck...Eric Bergen27 Jun
      • Re: NOW() is stuck...Andy Wallace27 Jun
        • Re: NOW() is stuck...Nick Khamis27 Jun
          • Re: NOW() is stuck...Andy Wallace27 Jun
  • Re: NOW() is stuck...Benjamin Stillman27 Jun
    • Re: NOW() is stuck...Andy Wallace27 Jun
      • Re: NOW() is stuck...Benjamin Stillman27 Jun
        • Re: NOW() is stuck...Andy Wallace27 Jun
          • Re: NOW() is stuck...Eric Bergen27 Jun
            • Re: NOW() is stuck...Andy Wallace27 Jun
          • Re: NOW() is stuck...Claudio Nanni27 Jun
            • Re: NOW() is stuck...Andy Wallace27 Jun
          • Re: NOW() is stuck...walter harms28 Jun
            • Re: NOW() is stuck...Andy Wallace28 Jun
              • Re: NOW() is stuck...Claudio Nanni28 Jun
Re: NOW() is stuck...Johan De Meersman27 Jun