php code, timezones affecting mysql?

We have some php pages on our intranet at work that have been working
fine for years. As a result, they rarely get edited.

The page I'm having trouble with is a timesheet entry page which
uses timestamps to record hours spent on different projects in a
mysql database. If users try to enter times using the php
interface, they are getting recorded correctly in the database
because I can directly access it and see the entries there.

However the php page that is supposed to display the hours by
reading back the entries from the database is /not/ correctly
displaying all the entries. It's never done this before.

Around the time that the page started misbehaving I noticed that
the clock on the server was ahead of our timezone by some 7 hours
or so. That was fixed as soon as I noticed it. For some reason
the ntp client didn't update.

I'm wondering if the system clock issue had something to do with
it? Any ideas on where to start the forensics?

I haven't posted any code at this stage to remain concise, but if
needed I can post code and sample outputs.

--
Troy Piggins

Re: php code, timezones affecting mysql?

>We have some php pages on our intranet at work that have been working
>fine for years. As a result, they rarely get edited.
>
>The page I'm having trouble with is a timesheet entry page which
>uses timestamps to record hours spent on different projects in a
>mysql database. If users try to enter times using the php
>interface, they are getting recorded correctly in the database
>because I can directly access it and see the entries there.

Beware of subtracting times from different clocks. This can result
in problems if one of them is significantly incorrect.

>However the php page that is supposed to display the hours by
>reading back the entries from the database is /not/ correctly
>displaying all the entries. It's never done this before.

What is the problem? (a) Some entries are missing, (b) the hours aren't
correctly computed, or (c) something else?

You've got potentially three different clocks here: (a) The MySQL
server clock, (b) the PHP server clock, and (c) the clock on the
wrist of the guy entering the times. They are probably all incorrect
a little (milliseconds), and possibly by a lot (hours, days, or
years). All of these might have different time zones also.

Nasty things can happen if, for example, the data is taken from the
times the user enters (clock (c)), but you only show entries that
are in the past according to the MySQL SERVER clock(clock (a)).
You might always be missing the latest couple of hours of entries.

If you're displaying a task time for a still-running task (started
but not yet stopped) which clock was used for NOW to calculate
hours-so-far? Which clock was used to calculate the start time?
If you're mixing now() in MySQL with time() in PHP, that may be the
source of the problem.

>Around the time that the page started misbehaving I noticed that
>the clock on the server was ahead of our timezone by some 7 hours
>or so. That was fixed as soon as I noticed it. For some reason
>the ntp client didn't update.

Is the problem now fixed, for entries made entirely after the clock
was reset?

>I'm wondering if the system clock issue had something to do with
>it? Any ideas on where to start the forensics?

You've got several clocks. Which are you using?

>I haven't posted any code at this stage to remain concise, but if
>needed I can post code and sample outputs.

Re: php code, timezones affecting mysql?

* Gordon Burditt is quoted
* & my replies are inline below :
>>We have some php pages on our intranet at work that have been working
>>fine for years. As a result, they rarely get edited.
>>
>>The page I'm having trouble with is a timesheet entry page which
>>uses timestamps to record hours spent on different projects in a
>>mysql database. If users try to enter times using the php
>>interface, they are getting recorded correctly in the database
>>because I can directly access it and see the entries there.
>
> Beware of subtracting times from different clocks. This can result
> in problems if one of them is significantly incorrect.
>
>>However the php page that is supposed to display the hours by
>>reading back the entries from the database is /not/ correctly
>>displaying all the entries. It's never done this before.
>
> What is the problem? (a) Some entries are missing, (b) the hours aren't
> correctly computed, or (c) something else?

Sorry I omitted that. Some entries are missing, just displaying
on the page as though there is no entry at all. Some entries are
displaying the correct no of hours in the correct day.

> You've got potentially three different clocks here: (a) The MySQL
> server clock, (b) the PHP server clock, and (c) the clock on the
> wrist of the guy entering the times. They are probably all incorrect
> a little (milliseconds), and possibly by a lot (hours, days, or
> years). All of these might have different time zones also.

c) above is not an issue. The actual times are not entered, just
durations. So they would just enter that they worked on XYZ
project for 4 hours on 22/11/07.

I hadn't thought about the mysql server clock. Do you mean
mysqld has it's own time keeping mechanism? Or do you mean the
machine that mysqld is running on? Both the apache/php and
mysqld servers are the same machine.

I corrected the time on the machine by using the 'ntpdate' command
as root IIRC.

> Nasty things can happen if, for example, the data is taken from the
> times the user enters (clock (c)), but you only show entries that
> are in the past according to the MySQL SERVER clock(clock (a)).
> You might always be missing the latest couple of hours of entries.
>
> If you're displaying a task time for a still-running task (started
> but not yet stopped) which clock was used for NOW to calculate
> hours-so-far? Which clock was used to calculate the start time?
> If you're mixing now() in MySQL with time() in PHP, that may be the
> source of the problem.

I understand, but as mentioned above it's not that sofisticated.

>>Around the time that the page started misbehaving I noticed that
>>the clock on the server was ahead of our timezone by some 7 hours
>>or so. That was fixed as soon as I noticed it. For some reason
>>the ntp client didn't update.
>
> Is the problem now fixed, for entries made entirely after the clock
> was reset?

Well, the problem persists for some entries made around that time
- maybe a week or 2 before the problem was pointed out to me.
Some of the guys enter their timesheets directly into the
timesheet php interface daily, while others write it in a diary
and just add it when they need to submit their timesheets.
That's why I didn't notice it sooner.

I have just tested some for this current week and it seems to be
working ok again.

Just curious about the reason behind it so it doesn't happen
again.

>>I'm wondering if the system clock issue had something to do with
>>it? Any ideas on where to start the forensics?
>
> You've got several clocks. Which are you using?

Hopefully this is answered above.

Thanks for assisting.

--
Troy Piggins

Re: php code, timezones affecting mysql?

>>>We have some php pages on our intranet at work that have been working
>>>fine for years. As a result, they rarely get edited.
>>>
>>>The page I'm having trouble with is a timesheet entry page which
>>>uses timestamps to record hours spent on different projects in a
>>>mysql database. If users try to enter times using the php
>>>interface, they are getting recorded correctly in the database
>>>because I can directly access it and see the entries there.
>>
>> Beware of subtracting times from different clocks. This can result
>> in problems if one of them is significantly incorrect.
>>
>>>However the php page that is supposed to display the hours by
>>>reading back the entries from the database is /not/ correctly
>>>displaying all the entries. It's never done this before.
>>
>> What is the problem? (a) Some entries are missing, (b) the hours aren't
>> correctly computed, or (c) something else?
>
>Sorry I omitted that. Some entries are missing, just displaying
>on the page as though there is no entry at all. Some entries are
>displaying the correct no of hours in the correct day.
>
>> You've got potentially three different clocks here: (a) The MySQL
>> server clock, (b) the PHP server clock, and (c) the clock on the
>> wrist of the guy entering the times. They are probably all incorrect
>> a little (milliseconds), and possibly by a lot (hours, days, or
>> years). All of these might have different time zones also.
>
>c) above is not an issue. The actual times are not entered, just
>durations. So they would just enter that they worked on XYZ
>project for 4 hours on 22/11/07.

I was thinking you might have a timeCLOCK application (like the
kind you punch), which basically says "Click on the project you're
starting work on", and it assumes you stopped working on the last
one, and it calculates durations. Projects would include lunch,
coffee, leaving for the day, surfing porn, bathroom break, etc.

Does your timesheet application properly handle the case where
a shift crosses midnight? If the clock is off, it might think
it crosses midnight even though it really shouldn't.


>I hadn't thought about the mysql server clock. Do you mean
>mysqld has it's own time keeping mechanism? Or do you mean the

No, but the server it's running on does.

>machine that mysqld is running on? Both the apache/php and
>mysqld servers are the same machine.

Ok, well that eliminates most of the possible problems I was thinking
of. A large system like this has potentially several replicated
MySQL servers, several round-robin redundant Apache/PHP servers,
possibly in different time zones, and the clocks will be different
(even if by nanoseconds) on all of them.

>I corrected the time on the machine by using the 'ntpdate' command
>as root IIRC.
>
>> Nasty things can happen if, for example, the data is taken from the
>> times the user enters (clock (c)), but you only show entries that
>> are in the past according to the MySQL SERVER clock(clock (a)).
>> You might always be missing the latest couple of hours of entries.
>>
>> If you're displaying a task time for a still-running task (started
>> but not yet stopped) which clock was used for NOW to calculate
>> hours-so-far? Which clock was used to calculate the start time?
>> If you're mixing now() in MySQL with time() in PHP, that may be the
>> source of the problem.
>
>I understand, but as mentioned above it's not that sofisticated.

Think about this: *ANYWHERE* in the application, in PHP or MySQL,
do you use the current time? Do you default entries to the current
time (or date)? Do you prohibit entries of work done days or years
early, or in the far past (particularly things like work in the
year 7 as opposed to 2007)? Do you display entered entries for,
say, the last week? All of these are potential problem areas for
clock skew, but for your setup it's less likely.

In what format are you saving dates and times? MySQL date or datetime
field? PHP time format? (PHP time formats change their display
form with time zone differences).

You could also have a situation where you fetch a result row twice
instead of re-using the same row twice, thereby skipping a row.
This would have nothing to do with time.

>>>Around the time that the page started misbehaving I noticed that
>>>the clock on the server was ahead of our timezone by some 7 hours
>>>or so. That was fixed as soon as I noticed it. For some reason
>>>the ntp client didn't update.

On my notebook, I dual-boot FreeBSD and Windows. They fight over
the correct setting of the real-time-clock, different by 6 hours.
If the initial ntpdate doesn't work (say, because I booted the
laptop without the net connected), subsequent operation of NTP
refuses to correct the time because a 6-hour difference is too
large. This might explain why NTP didn't fix it.

>> Is the problem now fixed, for entries made entirely after the clock
>> was reset?
>
>Well, the problem persists for some entries made around that time
>- maybe a week or 2 before the problem was pointed out to me.
>Some of the guys enter their timesheets directly into the
>timesheet php interface daily, while others write it in a diary
>and just add it when they need to submit their timesheets.
>That's why I didn't notice it sooner.
>
>I have just tested some for this current week and it seems to be
>working ok again.
>
>Just curious about the reason behind it so it doesn't happen
>again.

You might try tying down the errors as to whether they happen depending
on when the data was ENTERED vs. the time period covered by the data.
This gives a major hint as to where to look for the problem.

>>>I'm wondering if the system clock issue had something to do with
>>>it? Any ideas on where to start the forensics?
>>
>> You've got several clocks. Which are you using?
>
>Hopefully this is answered above.