[KLUG Members] MySQL weirdness ...

[Jamie McCarthy]

> "Starttime" is type timestamp, and is the primary key (no dups).

There's your problem.  I'm not sure why a date or time type would
ever be a unique or primary key, that doesn't make a whole lot of
sense to me.  And TIMESTAMP is right out.

TIMESTAMP is a lot like DATETIME except that its default value (if
you try to insert NULL) is always the current time.  Moreover, if
you change any other value in the row, the first TIMESTAMP value in
the row gets updated to the current time.  In other words, TIMESTAMP
kind of gives you a "last changed time" on a row.

(That's its default behavior anyway -- in MySQL 4.1 you do have more
options for controlling how it behaves.)

So there's no reason a TIMESTAMP should ever be a unique key
(including a primary key), or even part of a unique key.  Any
operation that changes more than one row in the table will always
result in a key collision and fail.

If you really think that no two rows in your table should logically
ever have the same time, then you could change this column to a
DATETIME instead.  I guess I can imagine cases where that's
appropriate (a log of summaries of another table's data, maybe).
But my guess is that it would make more sense to give the table a
plain old numeric primary key and just make the TIMESTAMP or
DATETIME part of its data.

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

I'd expect that any single UPDATE statement would either change all
the rows matched, or none of them.  Did you mean that some updates,
e.g. "set hostname = 'pc9h' where hostname = 'pc4h'", failed (and
changed no rows) while other, similar updates (which probably
matched only one row) succeeded?

If you have a single UPDATE that actually changes data for some
rows, then throws an error and returns without changing the data on
the rest of the matched rows, that's a pretty serious bug in MySQL!
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg