From: Michael Widenius
Date: April 3 1999 10:34am
Subject: Re: TIMESTAMP changes when no other fields have change
List-Archive: http://lists.mysql.com/mysql/1369
Message-Id: <14085.60968.179825.707154@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Paul" == Paul DuBois <paul@stripped> writes:

Paul> At 12:32 PM -0600 4/2/1999, Fred Lindberg wrote:
>> On Fri, 2 Apr 1999 09:48:05 -0800, Mike Wexler wrote:
>> 
>>> INSERT INTO myTable(value, id) VALUES ("my value", "myKey");
>>> SELECT stamp FROM myTable WHERE id="myKey";
>>> 
>>> # Time consuming operation here
>>> 
>>> UPDATE myTable SET value="my value" WHERE id="myKey";
>>> SELECT stamp FROM myTable WHERE id="myKey";
>> 
>> Why do you call this a bug? You are clearly updating the record, even
>> though you're putting in a value that happens to be the same as the one
>> that was there. There is no reason for the SQL server to look at
>> "value" in the record to satisfy your request. It simply finds records
>> matching the WHERE part and places "my value" into the field "value".
>> 
>> If you want the time stamp updated only on change, you need to do a
>> SELECT, identify the records where you want to change, and then change
>> only those.

Paul> If the value actually is the same one as the value that's already in
Paul> the table, then according to the manual it really is a bug.  This
Paul> comes from the UPDATE section:

Paul> Automatic updating of the first @code{TIMESTAMP} column occurs under any of
Paul> the following conditions:

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

<cut>

Hi!

I just checked the MySQL code;  The problem is that the check I do to
verify if the row has changed doesn't work if you have BLOB or TEXT
columns;  I shall add checking this to my TODO.!

Regards,
Monty