List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:June 27 2013 6:19pm
Subject:Re: NOW() is stuck...
View as plain text  
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.
>

-- 
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
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