List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:June 27 2013 6:23pm
Subject:Re: NOW() is stuck...
View as plain text  
Does show variables like 'init_connect'; return anything?

On Thu, Jun 27, 2013 at 11:19 AM, Andy Wallace <awallace@stripped> wrote:
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
Eric Bergen
eric.bergen@stripped
http://www.ebergen.net
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