[KLUG Members] MySQL weirdness ...

[Bruce Smith]

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

It _may_ be DATETIME in the latest release (or not), I don't know.

Keep in mind that I'm doing a big upgrade here, and I'm upgrading to a
version of MythTV THREE major releases newer than I'm currently running.

All the DB stuff is handled "magically" by Myth (inside the C++ code).
The initial schema for Myth only creates a user.  When you start the
server it creates all the tables and such.  When you upgrade to a newer
version of Myth, the first server startup "magically" migrates any DB
changes to the latest schema.

What I'm saying is:  I'm looking at the OLD schema, and I have no idea
what the new schema is, since I haven't started the new server yet.

Since I'm having hardware problems on my old server, I decided to build
an entirely new box, and upgrade the software while I'm at it.

I'm giving the new box a new IP/hostname, so I copied the DB over to
Fedora Core 4 mysql 4.1.14 (from FC1 mysql 3.23), and I need to change
some DB fields that contain the hostnames to the new server name.

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

I'd be happy to send it to you, but I'm still running the old schema,
and I'm not sure if that will do you any good?

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

OK, I'm starting to understand what's going on.  I need to check the
other half-dozen tables I'm successfully updating to see if they contain
any TIMESTAMP fields. (which are not part of a unique key)

Are they any other field types that I need to worry about?  
Types that may get their data changed even if I don't tell it to change?

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

Now that I'm starting to understand how TIMESTAMP works, I'm no longer
sure there is a bug.  I was wrong before on exactly what is happening.
(I was thinking that TIMESTAMP worked the same as DATETIME - me bad)

I recopied the database again and ran some more tests, and paid closer
attention this time.

When I run this:  update recorded set hostname = 'pc9h';
it updates three rows and dies with a dup error.

The reason it does three, is because of three different channel numbers,
which makes sense.

When I up-arrow and run it again, the current time has changed so it can
update a couple/three more records.  And so on ...

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

I'm no longer sure that IGNORE makes any difference.  It was probably a
coincidence that one did more records with the option.  Forget that one.

> 2. Millisecond timing?  

Forget that, I was thinking that was part of the data type, but it's
not.  Sorry.  :-(

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

This is the part that I'm still a little confused.

There are 2 TIMESTAMP fields in the record, starttime and endtime.
starttime is part of the primary key (as you know), endtime is not part
of any unique key.  Both are type TIMESTAMP's - I triple checked!  :-)

After my first UPDATE statement, the starttime field IS reset to the
current date/time.  HOWEVER, endtime is left unchanged!  Why???

mysql> describe recorded;
+-------------+------------------+------+-----+---------------------+-------+
| Field       | Type             | Null | Key | Default             | Extra |
+-------------+------------------+------+-----+---------------------+-------+
| chanid      | int(10) unsigned |      | PRI | 0                   |       |
| starttime   | timestamp        | YES  | PRI | CURRENT_TIMESTAMP   |       |
| endtime     | timestamp        | YES  | MUL | 0000-00-00 00:00:00 |       |

mysql> select * from recorded where hostname = 'pc9h';
Empty set (0.00 sec)

mysql> update recorded set hostname = 'pc9h';
ERROR 1062 (23000): Duplicate entry '1045-2005-10-13 14:01:25' for key 1

mysql> select chanid, starttime, endtime from recorded where hostname = 'pc9h';
+--------+---------------------+---------------------+
| chanid | starttime           | endtime             |
+--------+---------------------+---------------------+
|   2044 | 2005-10-13 14:01:25 | 2004-08-20 07:00:00 |
|   2048 | 2005-10-13 14:01:25 | 2004-06-13 19:00:00 |
|   1045 | 2005-10-13 14:01:25 | 2004-02-18 03:00:00 |
+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

Why does starttime get modified, but endtime does not???
Because of the difference in the default value of the cols?

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

True.  That SQL works perfect for me.

> Whew :)

No kidding!  :-)

 - BS