subject:"TIMESTAMP field is updated unintentionally"

Re: TIMESTAMP field is updated unintentionally

2003-02-01 Thread Steve Edberg

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  [EMAIL PROTECTED] |
| 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

2003-02-01 Thread Benjamin Pflugmann

Hi.

On Fri 2003-01-31 at 12:17:42 +0100, [EMAIL PROTECTED] 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.

-- 
[EMAIL PROTECTED]

-
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

2003-02-01 Thread gerald_clark

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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

2003-02-01 Thread Paul DuBois

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:[EMAIL PROTECTED]



-
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

2003-02-01 Thread Stefan Hinz, iConnect \(Berlin\)

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
erstellDatumTIMESTAMP

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 <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Marco Deppe" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
>
>
> -
> 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: TIMESTAMP field is updated unintentionally

2003-02-01 Thread Jennifer Goodie

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:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 3:18 AM
To: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

2003-01-31 Thread Joseph Bueno

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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: TIMESTAMP field is updated unintentionally

2003-01-31 Thread Grigor, Peter

Timestamp columns update automatically.

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

Peter
<^_^>


> -Original Message-
> From: Marco Deppe [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 31, 2003 6:18 AM
> To: [EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> 
> 
> -
> 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

TIMESTAMP field is updated unintentionally

2003-01-31 Thread Marco Deppe

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:[EMAIL PROTECTED]


-
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: TIMESTAMP field is updated unintentionally

Re: TIMESTAMP field is updated unintentionally

Re: TIMESTAMP field is updated unintentionally

Re: TIMESTAMP field is updated unintentionally

Re: TIMESTAMP field is updated unintentionally

RE: TIMESTAMP field is updated unintentionally

Re: TIMESTAMP field is updated unintentionally

RE: TIMESTAMP field is updated unintentionally

TIMESTAMP field is updated unintentionally

9 matches

Site Navigation

Mail list logo

Footer information