[KLUG Members] MySQL weirdness ...

[Jamie McCarthy]

BTW, I was totally wrong when I wrote:

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

Actually that's exactly the behavior the docs describe, though it was
news to me.  I learn something new every day!  :)  Seems wonky to me,
but I guess if you want to change all-or-nothing, you wrap the UPDATE
in a transaction (and either precede it with a SELECT to check, or
follow it with an error check and potential ROLLBACK).



Anyway, about your issue... it looks like MythTV's database schema is
semi-broken here.  I only browsed the source, but they definitely
want DATETIME, not TIMESTAMP.

In the source, I can't seem to find the current schema all laid out
in one place, unless libs/libmythtv/dbcheck.cpp does more than just
check the DBs.  Can you do me a favor and mail me the output from a
'mysqldump --no-data yourmythtvdbname'?  I want to look at the whole
schema before I contact them.

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

OK, that makes sense, though for other reasons it's still better to
have a numeric primary key and then make (channel,time) a UNIQUE.

> > TIMESTAMP is a lot like DATETIME except ...  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)

Because that's precisely what TIMESTAMP does.  Any UPDATE which
changes any value in a row, and does not explicitly set a TIMESTAMP
value, will also update that TIMESTAMP value to the current time.

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

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

Various parts of what you describe are really weird.  I'd like to try
to replicate that behavior and if it really still occurs in the
latest version of MySQL 4.1.x, I'll report it as a bug.  If you can,
please also email me a dump of what's in your 'recorded' table
('mysqldump yourmythtvdbname recorded').

Here are the weird parts:

1. It should work the same whether you specify UPDATE or UPDATE
IGNORE;  the only difference is that UPDATE IGNORE should _not_ throw
an error.  It will still match just as many rows but will only change
one row (per channel, I'm guessing).

2. Millisecond timing?  There aren't any native types that I know of
in MySQL that provide millisecond-resolution times.  I'd love to
hear about one!

3. Millisecond timing shouldn't matter anyway;  even if an UPDATE
takes an hour, any TIMESTAMP values that it updates should all be
given the same time, always.  Well, I haven't checked this, but I
would be very surprised if it were not true (for one thing, I don't
know how replication could succeed on tables with TIMESTAMPs).  So
I'm not sure why an UPDATE IGNORE would ever update more than (on a
table with a unique key of channel,timestamp) one row per channel,
whether the server is running on the world's fastest or world's
slowest computer.

4a. If you are NOT specifically setting the first timestamp field to
equal itself in the UPDATE, e.g.
    update recorded set hostname = 'pc9h';
then the first TIMESTAMP type column should definitely be changed
from its original value to the current time.  But you say it's
unchanged?

4b. If you ARE specifically setting it, e.g.
    update recorded set hostname = 'pc9h', starttime = starttime;
then it should indeed be unchanged from its original value, but you
should not get any errors.

Whew :)

Mail me the schema and I'll see if I can't persuade the MythTV
folks to fix it for the next release...
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg