List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:June 27 2013 6:33pm
Subject:Re: NOW() is stuck...
View as plain text  
Sort of:

mysql> show variables like 'init_connect';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+




On 6/27/13 11:23 AM, Eric Bergen wrote:
> 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
>>
>
>
>

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