[KLUG Members] MySQL weirdness ...

[Bruce Smith]

I just ran into a weird problem updating a MySQL table.
I'm simply trying to update a single column like this:

  update recorded set hostname = 'pc9h' where hostname = 'pc4h';

And it gives me _random_ duplicate key errors on a column named
"starttime" after updating a few records.

The column "hostname" is NOT a key (or part of any key).
"Starttime" is type timestamp, and is the primary key (no dups).

This SQL gives me the same error:

  update recorded set hostname = 'pc9h';

If I change to "update ignore ...", it updates a few records, errors
out, and when I repeat it, it updates a few more before it errors.

Then I noticed that the duplicate data displayed in the error message
was the CURRENT date/time, which is NOT what's really in the table.  
So I guessed that it might be temporarily putting the current date/time
into the column, or something like that....

So I tried statements like these, and both work perfect!

  update recorded set hostname = 'pc9h', starttime = starttime where hostname = 'pc4h';

  update recorded set hostname = 'pc9h', starttime = starttime;

(MyISAM table type, MySQL 4.1.14 on Fedora Core 4)

Can someone explain this weirdness, and the correct update SQL syntax?

 - BS