176979 - changing a bug around a daylight savings change corrupts comment order

Status: NEW

(Bugzilla :: Creating/Changing Bugs, defect)

Description

[Dave Miller [:justdave]]

At the attached URL, I added a comment to the bug at 1:56pm PDT.  I then went
back and added another comment 20 minutes later.  Except because of the time
change, it's timestamped 1:17am (now PST) and shows up before the first one in
the bug.

This could likely be solved by storing all timestamps in the database as GMT and
converting to the local time zone at display time rather than storing them with
the local time in the database.

Comment 1

[Bradley Baetz (:bbaetz)]

This also affects processmail, for the same reason - see bug 55970

Comment 2

[Dave Miller [:justdave]]

Yep, any changes to bugs made in the first part of the second copy of the 1am
hour  never got mailed out if there were any changes made in the latter part of
the first copy of the 1am hour.

Comment 3

[Gervase Markham [:gerv]]

There are several other reasons why switching to GMT in the database is good. We
should do that, and then fix the bugs for an auto-detected, but configurable,
display timezone.

Gerv

Comment 4

[Bradley Baetz (:bbaetz)]

Yep. However, I don't think we can do this til we drop the timestamp stuff

Comment 5

[Bradley Baetz (:bbaetz)]

If we do this, then we can't use NOW(), because that gives local time (we can
SELECT NOW(), and modify, but we cna't use NOW in the sql). time types with
timezone info is on mysql's todo list, at least.

Comment 6

[J. Paul Reed [:preed]]

re comment 5: Are you sure?

In the safe_mysqld script, can't we just reset the TZ variable to GMT (and the
machine's clock to GMT), and then when NOW() is used, it will return time in GMT?

I remember having that problem with a mysql installation giving GMT when we
didn't want it, and the solution, as I remember, was to tinker with the TZ variable.

Comment 7

[Bradley Baetz (:bbaetz)]

Sure, but we can't require bugzilla to be the only app on the server

Comment 8

[J. Paul Reed [:preed]]

Good point.

The other way around it would be to create a NOW() construct in our own DB stuff
(is NOW() DB-portable?) and handle any time conversions there.

I'm sure that's not a new idea.

Comment 9

[Christian Reis]

Bug 225221 proposes to add an ID, which could be used for sorting instead of the
timestamp.

Can someone explain why bug 156834 blocks this? I wonder if bbaetz planned on
fixing this at the same time..

Comment 10

[Dave Miller [:justdave]]

(In reply to comment #9)
> Can someone explain why bug 156834 blocks this? I wonder if bbaetz planned on
> fixing this at the same time..

Not sure why it does, but it's resolved fixed now anyway, so if it was really blocking this, it no longer does.

Comment 11

[Bradley Baetz (:bbaetz)]

Because the auto-generated timestamp is in local time, and at the time bugzilla ordered by the timestamp, so...

Adding the ID explicitly in the db fixed it too.

Comment 12

[Daniel Einspanjer [:dre] [:deinspanjer]]

I'm performing metrics work on Mozilla's bugzilla DB.  I just discovered bug 176975 which has a creation time after the first activity that occurred on the bug due to the same daylight saving issue described here.

With all the blocking bugs being fixed, is this bug still an issue?
I need to plan for how to handle inconsistencies like this moving forward.

Comment 13

[Max Kanat-Alexander]

Yeah, this bug is still an issue. The ultimate resolution will be to store all dates and times in the DB as UTC and translate them on input and output to a local timezone. That's a pretty significant project, though.

Comment 14

[bjoern]

this is probably a duplicate of bug #452353 - maybe someone wants to resolve it like this.

A way to work around the timezone issues manually: Put mysql into a certain timezone e.g. via /etc/mysql/my.cnf:

[mysqld]
  default-time-zone = '+00:00'

and put Apache into the same timezone, e.g. via /etc/apache2/envvars:

export TZ=UTC