TIMESTAMP field is updated unintentionally

Hi,

I was already questioning my sanity, but the problem below is
reproduceable:

This is how my table looks:
mysql> describe T_ORDH;
--------------+----------------------+-----+----+--------+-- ------
Field |Type |Null |Key |Default |Extra
--------------+----------------------+-----+----+--------+-- ------
PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
ERSTELL_DATUM |timestamp(14) |YES | |NULL |
STATUS |smallint(5) unsigned | | |0 |

If I do
mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
ERSTELL_DATUM is set to the current date. I know that a timestamp
takes the current time, if set it to NULL, but since I'm not touching
it, it shouldn't change, should it?

A quick workaround is
mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
-> where PK_ID=26272;

The big question: Is it a bug or a feature?
(mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))



--
Best regards,
Marco

mailto:MarcoDeppe@gmx.net


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: TIMESTAMP field is updated unintentionally

Timestamp columns update automatically.

http://www.mysql.com/doc/en/DATETIME.html

Peter
<^_^>


> -----Original Message-----
> From: Marco Deppe [mailto:MarcoDeppe@gmx.net]
> Sent: Friday, January 31, 2003 6:18 AM
> To: mysql@lists.mysql.com
> Subject: TIMESTAMP field is updated unintentionally
>
>
> Hi,
>
> I was already questioning my sanity, but the problem below is
> reproduceable:
>
> This is how my table looks:
> mysql> describe T_ORDH;
> --------------+----------------------+-----+----+--------+-- ------
> Field |Type |Null |Key |Default |Extra
> --------------+----------------------+-----+----+--------+-- ------
> PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
> ERSTELL_DATUM |timestamp(14) |YES | |NULL |
> STATUS |smallint(5) unsigned | | |0 |
>
> If I do
> mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
> ERSTELL_DATUM is set to the current date. I know that a timestamp
> takes the current time, if set it to NULL, but since I'm not touching
> it, it shouldn't change, should it?
>
> A quick workaround is
> mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
> The big question: Is it a bug or a feature?
> (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))
>
>
>
> --
> Best regards,
> Marco
>
> mailto:MarcoDeppe@gmx.net
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail
> To unsubscribe, e-mail
>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

Hi,

It is a feature, the first TIMESTAMP field is automatically
updated each time you update the record. Check the manual
for details:
http://www.mysql.com/doc/en/DATETIME.html

If you want mysql to automatically set it at creation time only,
your workaround is OK.

You can also convert ERSTELL_DATUM to DATETIME type and set is
explicitely to NOW() when you insert a new record.

Hope this helps
--
Joseph Bueno

Marco Deppe wrote:
> Hi,
>
> I was already questioning my sanity, but the problem below is
> reproduceable:
>
> This is how my table looks:
> mysql> describe T_ORDH;
> --------------+----------------------+-----+----+--------+-- ------
> Field |Type |Null |Key |Default |Extra
> --------------+----------------------+-----+----+--------+-- ------
> PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
> ERSTELL_DATUM |timestamp(14) |YES | |NULL |
> STATUS |smallint(5) unsigned | | |0 |
>
> If I do
> mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
> ERSTELL_DATUM is set to the current date. I know that a timestamp
> takes the current time, if set it to NULL, but since I'm not touching
> it, it shouldn't change, should it?
>
> A quick workaround is
> mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
> The big question: Is it a bug or a feature?
> (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))
>
>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: TIMESTAMP field is updated unintentionally

Read the section in the manual about timestamps, this is expected behavior,
it is how it is supposed to work.

http://www.mysql.com/doc/en/DATETIME.html
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...

-----Original Message-----
From: Marco Deppe [mailto:MarcoDeppe@gmx.net]
Sent: Friday, January 31, 2003 3:18 AM
To: mysql@lists.mysql.com
Subject: TIMESTAMP field is updated unintentionally


Hi,

I was already questioning my sanity, but the problem below is
reproduceable:

This is how my table looks:
mysql> describe T_ORDH;
--------------+----------------------+-----+----+--------+-- ------
Field |Type |Null |Key |Default |Extra
--------------+----------------------+-----+----+--------+-- ------
PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
ERSTELL_DATUM |timestamp(14) |YES | |NULL |
STATUS |smallint(5) unsigned | | |0 |

If I do
mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
ERSTELL_DATUM is set to the current date. I know that a timestamp
takes the current time, if set it to NULL, but since I'm not touching
it, it shouldn't change, should it?

A quick workaround is
mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
-> where PK_ID=26272;

The big question: Is it a bug or a feature?
(mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

Marco,

> mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
> ERSTELL_DATUM is set to the current date. I know that a timestamp
> takes the current time, if set it to NULL, but since I'm not touching
> it, it shouldn't change, should it?
>
> A quick workaround is
> mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
> The big question: Is it a bug or a feature?

The small answer is: It's a feature, and it's documented as well:

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.

http://www.mysql.com/doc/en/DATETIME.html

BTW: In tables like yours I always have _two_ timestamp columns, like:

letzteAenderung TIMESTAMP
erstellDatum TIMESTAMP

On INSERT, I set erstellDatum to now(). letzteAenderung will
automatically be set to the same value. On UPDATE, erstellDatum will be
left untouched (because it's not the first TIMESTAMP column), and
letzteAenderung will be set to NOW() without me having to think of it. A
very convenient feature, indeed :)

Regards,
--
Stefan Hinz
Geschäftsführer / CEO iConnect GmbH
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3

----- Original Message -----
From: "Marco Deppe"
To:
Sent: Friday, January 31, 2003 12:17 PM
Subject: TIMESTAMP field is updated unintentionally


> Hi,
>
> I was already questioning my sanity, but the problem below is
> reproduceable:
>
> This is how my table looks:
> mysql> describe T_ORDH;
> --------------+----------------------+-----+----+--------+-- ------
> Field |Type |Null |Key |Default |Extra
> --------------+----------------------+-----+----+--------+-- ------
> PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
> ERSTELL_DATUM |timestamp(14) |YES | |NULL |
> STATUS |smallint(5) unsigned | | |0 |
>
> If I do
> mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
> ERSTELL_DATUM is set to the current date. I know that a timestamp
> takes the current time, if set it to NULL, but since I'm not touching
> it, it shouldn't change, should it?
>
> A quick workaround is
> mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
> The big question: Is it a bug or a feature?
> (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))
>
>
>
> --
> Best regards,
> Marco
>
> mailto:MarcoDeppe@gmx.net
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail
> To unsubscribe, e-mail

> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

At 12:17 +0100 1/31/03, Marco Deppe wrote:
>Hi,
>
>I was already questioning my sanity,

Don't. Reading the manual is more helpful. :-)

> but the problem below is
>reproduceable:
>
>This is how my table looks:
>mysql> describe T_ORDH;
>--------------+----------------------+-----+----+--------+- -------
>Field |Type |Null |Key |Default |Extra
>--------------+----------------------+-----+----+--------+- -------
>PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
>ERSTELL_DATUM |timestamp(14) |YES | |NULL |
>STATUS |smallint(5) unsigned | | |0 |
>
>If I do
>mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
>ERSTELL_DATUM is set to the current date. I know that a timestamp
>takes the current time, if set it to NULL, but since I'm not touching
>it, it shouldn't change, should it?

What does the manual say?

>
>A quick workaround is
>mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
>The big question: Is it a bug or a feature?

According to the manual, that's how it's supposed to work.
Visit the online manual and type TIMESTAMP into the search box.
It'll give you the answers you're looking for.

>(mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))
>
>
>
>--
>Best regards,
>Marco
>
>mailto:MarcoDeppe@gmx.net


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

Since that is exactly how the manual describes it, it must be a feature.
If you have more than one timestamp, they will all get set on an insert,
but only the first will be changed on an update.

Marco Deppe wrote:

>Hi,
>
>I was already questioning my sanity, but the problem below is
>reproduceable:
>
>This is how my table looks:
>mysql> describe T_ORDH;
>--------------+----------------------+-----+----+--------+- -------
>Field |Type |Null |Key |Default |Extra
>--------------+----------------------+-----+----+--------+- -------
>PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
>ERSTELL_DATUM |timestamp(14) |YES | |NULL |
>STATUS |smallint(5) unsigned | | |0 |
>
>If I do
>mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
>ERSTELL_DATUM is set to the current date. I know that a timestamp
>takes the current time, if set it to NULL, but since I'm not touching
>it, it shouldn't change, should it?
>
>A quick workaround is
>mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
>The big question: Is it a bug or a feature?
>(mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))
>
>
>
>
>



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

Hi.

On Fri 2003-01-31 at 12:17:42 +0100, MarcoDeppe@gmx.net wrote:
>
> I was already questioning my sanity, but the problem below is
> reproduceable:
>
[...]
> If I do
> mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
> ERSTELL_DATUM is set to the current date. I know that a timestamp
> takes the current time, if set it to NULL, but since I'm not touching
> it, it shouldn't change, should it?
[...]
> The big question: Is it a bug or a feature?

A feature, it is described in detail in the section that explains the
TIMESTAMP column type: http://www.mysql.com/doc/en/DATETIME.html

If you don't want that behaviour, use DATETIME with NOW() instead.

HTH,

Benjamin.

--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

At 12:17 PM +0100 1/31/03, Marco Deppe wrote:
>Hi,
>
>I was already questioning my sanity, but the problem below is
>reproduceable:
>
>This is how my table looks:
>mysql> describe T_ORDH;
>--------------+----------------------+-----+----+--------+- -------
>Field |Type |Null |Key |Default |Extra
>--------------+----------------------+-----+----+--------+- -------
>PK_ID |int(10) unsigned | |PRI |NULL |auto_inc
>ERSTELL_DATUM |timestamp(14) |YES | |NULL |
>STATUS |smallint(5) unsigned | | |0 |
>
>If I do
>mysql> update T_ORDH set STATUS=2 where PK_ID=26272;
>ERSTELL_DATUM is set to the current date. I know that a timestamp
>takes the current time, if set it to NULL, but since I'm not touching
>it, it shouldn't change, should it?
>
>A quick workaround is
>mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
> -> where PK_ID=26272;
>
>The big question: Is it a bug or a feature?
>(mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))


From:

http://www.mysql.com/doc/en/DATETIME.html

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



# You explicitly set the TIMESTAMP column to NULL


....so that means it's a feature.

-steve

--
+----------------------------------------------------------- -------------+
| Steve Edberg sbedberg@ucdavis.edu |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
+----------------------------------------------------------- -------------+
| SETI@Home: 1001 Work units on 23 oct 2002 |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... |
+----------------------------------------------------------- -------------+

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php