>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
