[KLUG Members] MySQL weirdness ...

[Bruce Smith]

> > "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.

Let's not get hung up on that.  

First, I didn't design this.  It's part of a major OSS software package
(MythTV) which runs great, and _I'm_ the one trying to do something
nonstandard.  I'm trying to move everything to a different PC, so I'm
hacking the database, modifying all server hostname fields to point to
my new server name & IP.  I have no idea if this will even work --
ALTHOUGH it seems to me that my simple SQL UPDATE query should work.

Second it's really _part_ of the primary key, which consists of two
fields.  The other field is TV channel - and you can't have two
_different_ recordings on the same channel starting at the same time -
so unique it must be.  :-)

> 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.

Yes, it seems to be setting the timestamp field to the current --
HOWEVER, my thinking is this is a UPDATE, and I'm not changing the
timestamp field.  So why is it temporarily changing that field to the
current date/time instead of leaving the value alone?  (and error'ing)

> > 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 I use UPDATE with the IGNORE option, it updates one or two fields
before it errors out.  I hit up-arrow/enter to run it again, and it
updates a couple _more_ fields before it errors.  The table has 78 rows,
so if I run it 50 or so times, my database is completely updated.

I'm guessing the number of rows it updates before it errors is random
because of the milliseconds part of the column.  Which only gives a
duplicate error if it doesn't increment the millisecond between each
individual row update (this is a fast machine).

AND after the final update, the timestamp field is unchanged from it's
original value (as it should be), which is why it must be TEMPORARILY
changing it to the current time as part of the internal working of the
UPDATE SQL statement.

> 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!

I'm still thinking this is a bug, unless you can ensure me that this is
some "feature" of how MySQL works.  :-)

 - BS