The is the proper, documented functionality of the timestamp field. An
exerpt from http://www.mysql.com/doc/D/A/DATETIME.html is below:
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.
Duncan
--
Duncan Salada | Titan | www.titan.com/testeval
Email: [EMAIL PROTECTED] | Voice: 301-925-3222x375 | Fax: 301-925-3216
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 22, 2002 11:05 AM
> To: [EMAIL PROTECTED]
> Subject: timestamp primary key's value improperly changing on
> sql update
>
>
> >Description:
>
> Given a table that has a timestamp field which is
> either the primary key or
> one field in a multi-column primary key, the value in
> this field is
> updated to the current time any time an SQL UPDATE is done, even
> when the timestamp field is not intended to be updated.
>
>
> >How-To-Repeat:
>
> mysql> CREATE TABLE foo(field1 TIMESTAMP PRIMARY KEY, field2 INT);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> INSERT INTO foo VALUES(now(), 10);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> SELECT * FROM foo;
> +----------------+--------+
> | field1 | field2 |
> +----------------+--------+
> | 20020522105353 | 10 |
> +----------------+--------+
> 1 row in set (0.00 sec)
>
> mysql> UPDATE foo SET field2=11;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> SELECT * FROM foo;
> +----------------+--------+
> | field1 | field2 |
> +----------------+--------+
> | 20020522105405 | 11 |
> +----------------+--------+
> 1 row in set (0.00 sec)
>
> -- NOTICE that the time in field1 changed after the update.
> This is wrong.
>
> >Fix:
> A workaround is to set the timestamp field to itself on
> an update.
> ex: "UPDATE foo SET field1=field1, field2=11"
>
> >Submitter-Id:
> >Originator: Jeff Messner
> >Organization:
>
> >MySQL support: none
> >Synopsis: timestamp primary key's value improperly
> changing on sql update
> >Severity: serious
> >Priority: medium
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-3.23.49a (Official MySQL Binary)
> >Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib
> 3.23.49a, for pc-linux-gnu on i686
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free
> software,
> and you are welcome to modify and redistribute it under the
> GPL license
>
> Server version 3.23.49a
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /tmp/mysql.sock
> Uptime: 29 days 45 min 32 sec
>
> Threads: 1 Questions: 2658 Slow queries: 0 Opens: 28
> Flush tables: 1 Open tables: 18 Queries per second avg: 0.001
> >Environment:
>
> System: Linux pc-00183 2.4.7-10 #1 Thu Sep 6 17:21:28 EDT
> 2001 i586 unknown
> Architecture: i586
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake
> /usr/bin/gcc /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)
> Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type
> -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W
> -Wchar-subscripts -Wformat -Wimplicit-function-dec
> -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings
> -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer'
> CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51
> -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat
> -Wimplicit-function-dec -Wimplicit-int -Wparentheses
> -Wsign-compare -Wwrite-strings -Woverloaded-virtual
> -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy
> -Wnon-virtual-dtor -felide-constructors -fno-exceptions
> -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS=''
> LIBC:
> lrwxrwxrwx 1 root root 13 Mar 11 12:11
> /lib/libc.so.6 -> libc-2.2.4.so
> -rwxr-xr-x 1 root root 5716491 Sep 4 2001
> /lib/libc-2.2.4.so
> -rw-r--r-- 1 root root 27304836 Sep 4 2001
> /usr/lib/libc.a
> -rw-r--r-- 1 root root 178 Sep 4 2001
> /usr/lib/libc.so
> Configure command: ./configure --prefix=/usr/local/mysql
> --enable-assembler --with-extra-charsets=complex
> --enable-thread-safe-client --with-mysqld-ldflags=-all-static
> --with-client-ldflags=-all-static
> --with-other-libc=/usr/local/mysql-glibc
> '--with-comment=Official MySQL Binary'
> --prefix=/usr/local/mysql --with-extra-charset=complex
> --enable-thread-safe-client --enable-local-infile
> 'CFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch
> -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat
> -Wimplicit-function-dec -Wimplicit-int -Wparentheses
> -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
> -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type
> -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W
> -Wchar-subscripts -Wformat -Wimplicit-function-dec
> -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings
> -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder
> -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors
> -fno-exceptions -fno-rtti -mcpu=pentiumpr!
> o -O3 -fno-omit-frame-pointer' CXX=gcc
>
>
> ---------------------------------------------------------------------
> 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
