Bug #669 Bug on update with 2 timestamp(14) fields

• Submitted: 18 Jun 2003 5:39
• Modified: 18 Jun 2003 5:54
• Reporter: biloo1 biloo1
• Status: Not a Bug
• Category: MySQL Server: Command-line Clients
• Severity: S2 (Serious)
• Version: 3.23.56
• OS: Windows (windows 2000)
• CPU Architecture: Any

[18 Jun 2003 5:39] biloo1 biloo1

Description:
Hello, I'm french.

I use a table "entreprise" with 2 last fields timestamp(14) named "datec_fic_ent" and "datem_fic_ent"

When i do an update (command-line) on the (last) field "datem_fic_ent" with NOW(), result is that the 2 fields are updated with the date corresponding at now.

How to repeat:
create a table with 28 fields + the two fields timestamp(14) "datec_fic_ent" and "datem_fic_ent" (= total 30 fields).

execute in command line

UPDATE `ENTREPRISE` set `datem_fic_ent` = NOW() where `id_ent`='2511';

==> datec_fic_ent = datem_fic_ent = 20030618141118
==> datec_fic_ent could not be modified ?!?!

Suggested fix:
I try to insert an another field between the 2 timestamp fields but the result is the same.

HELP ME !!!

[18 Jun 2003 5:54] Guilhem Bichot

Hi,

you just have to read our manual about this:
<quote>
The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

    * The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
    * The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
    * You explicitly set the TIMESTAMP column to NULL. 

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().
</quote>

When you update the 2nd timestamp to NOW(), it gets updated to NOW(). As the row is being updated, this triggers the updating of the first timestamp, to NOW() as well.

I'm not sure you need 2 TIMESTAMP columns. Maybe one TIMESTAMP and one DATETIME?

Bye (Bien le bonjour chez toi!).