156834 - Remove timestamp fields

Status: RESOLVED FIXED

(Bugzilla :: Bugzilla-General, defect)

Description

[Bradley Baetz (:bbaetz)]

The auto-updating timestamp fields are a pain. They're not cross-db compatable,
and we keep forgetting to stop updating them. See bug 153578, among others.

Instead of using the mysql timestamp type, we should use the mysql datetime type
(which is the same as the ANSI/SQL timestamp type), and assign into it where needed.

Comment 1

[Jouni Heikniemi]

Yes. This rules.

Comment 2

[Matthew Tuck [:CodeMachine]]

Yes we should do this for XDB reasons, but we shouldn't kid ourselves this will
solve the forgetfulness.  It will simply change forgetting to not change to
forgetting to change, which is arguably a worse problem.  The only solution to
that is a bug change API that forces you to specify whether to update the
timestamp (or works it out itself).

Comment 3

[Bradley Baetz (:bbaetz)]

There are fewer places we do need to change (ie once in post/process_bug) vs
those we don't (everywhere else is post/process_bug, updating lastchanged for
email, updating keywords cache, product/component until bug 43600 is fixed, etc)

Attachments use this as a creation time only, but get this wrong...

Its a mess, and we don't need it.

Comment 4

[Joel Peshkin]

Unloved bugs targetted for 2.18 but untouched since 9-15-2003 are being
retargeted to 2.20
If you plan to act on one immediately, go ahead and pull it back to 2.18.

Comment 5

[Jim C. Nasby]

For databases that support triggers, they provide an easy method to ensure
timestamps are updated automatically, as required.

Comment 6

[Bradley Baetz (:bbaetz)]

True, but we only sometimes want the field to be updated, and bugzilla sometimes
gets it wrong when we forget. Its not really intuitive to have to specify hen a
field update doesn't happen.

Comment 7

[Jim C. Nasby]

Can't the logic for when the field should be updated be coded into the trigger?

Comment 8

[Dave Miller [:justdave]]

See, that's exactly the problem though.  MySQL *does* have an implicit trigger.
 And it always fires *any* time the row is touched.  Which means you have to
specifically override it when you don't want it to fire.  For most of the cases
where Bugzilla uses it, it would be much more intuitive for the developers if we
set the field manually any time it changes.

We did end up setting it manually on Sybase.  Sybase does have real triggers
where you can code whatever you want in them.  But it was easier to teach
Bugzilla how to set the timestamp than to create the trigger in Sybase.  And
letting Bugzilla know how means we don't have to do something specific for every
database Bugzilla eventually runs on.

Comment 9

[Bradley Baetz (:bbaetz)]

You can't really give enough context in a trigger, unless we go to stored
procedures.

anyway, mysql doesn't do triggers or stored procedures, and we want the same
mechanism for both.

Comment 10

[Tomas Kopal]

It seems that we agreed that this is a good think to do, so I just created a new
bug 257303 with patch attached to convert lastused in logincookies table and I
will do the same with delta_ts in bugs table as soon as the patch is finished.

Comment 11

[Tomas Kopal]

Added bug with patch for delta_ts in bugs table.

Comment 12

[Frédéric Buclin]

The "delta_ts" attribute in the "bugs" table was the last one to use the
timestamp format. This has been fixed! :)