[KLUG Members] MySQL weirdness ...

[Bruce Smith]

> > Only the first timestamp field is auto-updated on a record change.
> 
> Yep, that's the default behavior and the way this table is set up.
> 
> Bruce, you may or may not care that, in MySQL 4.1, you can pick which
> TIMESTAMP column you want to exhibit the auto-updating behavior, and
> you can even separate out the on-creation default timestamp behavior
> from the on-update default timestamp behavior, if you want.  Nah, you
> probably don't care about that :)

I didn't care before, but now you have me curious.
I may have to go and RTFM!  ;-)

> You've probably taken care of this already, but remember to
> doublecheck all your starttimes before you apply the upgrade to the
> next version, or it's going to be telling you you recorded those
> shows at odd times :)

Yeah, I went back and looked at the schemas of all the other tables I'm
updating, and luckily none of them have any timestamp fields.  In fact,
they are all just simple fields I can understand:  int/char/text!  :-)
Assuming THEY function the same as Postgresql ...  :-/

> > 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.
> 
> Ah, OK.  Then it looks like they may indeed have fixed this in more
> recent versions.  The latest version I downloaded has this in
> dbcheck.cpp:
> 
>     "CREATE TABLE IF NOT EXISTS recordedprogram ("
>     "  chanid int(10) unsigned NOT NULL default '0',"
> ->  "  starttime datetime NOT NULL default '0000-00-00 00:00:00',"
> ->  "  endtime datetime NOT NULL default '0000-00-00 00:00:00',"
>     [...]
>     "  PRIMARY KEY  (chanid,starttime,manualid),"
>     "  KEY endtime (endtime),"
>     "  KEY title (title),"
>     "  KEY title_pronounce (title_pronounce),"
>     "  KEY seriesid (seriesid),"
>     "  KEY programid (programid),"
>     "  KEY id_start_end (chanid,starttime,endtime)"
>     ");",
> 
> It still grates on me a little bit to see a 3-column primary key, but
> if it works it works.  And the important thing is that it's been
> changed to a DATETIME, which has no wacky auto-updating behavior.

I'm kind of glad for the multi-column key now.  If the timestamp
wouldn't have been part of an unique key, and my update would have
completed without error, I would have probably screwed up the starttime
field and not known it (until it was too late)!

> > > 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?
> 
> Actually, can you do that for me, but after you upgrade to the new
> schema?  I still see this in dbcheck.cpp:
> 
>     "CREATE TABLE IF NOT EXISTS conflictresolutionoverride ("
>     "  chanid int(10) unsigned NOT NULL default '0',"
>     "  starttime timestamp(14) NOT NULL,"
>     "  endtime timestamp(14) NOT NULL default '00000000000000',"
>     "  KEY chanid (chanid,starttime),"
>     "  KEY endtime (endtime)"
>     ");",
>     "CREATE TABLE IF NOT EXISTS conflictresolutionsingle ("
>     "  preferchanid int(10) unsigned NOT NULL default '0',"
>     "  preferstarttime timestamp(14) NOT NULL,"
>     "  preferendtime timestamp(14) NOT NULL default '00000000000000',"
>     "  dislikechanid int(10) unsigned NOT NULL default '0',"
>     "  dislikestarttime timestamp(14) NOT NULL default '00000000000000',"
>     "  dislikeendtime timestamp(14) NOT NULL default '00000000000000',"
>     "  KEY preferchanid (preferchanid,preferstarttime),"
>     "  KEY preferendtime (preferendtime)"
>     ");",
> 
> So I think they may still have the same problem, with different
> tables.  It will not be as pronounced as the problem you saw since
> they are not part of UNIQUE keys.  They probably did it originally
> for a cheap way to get the current time on row creation, but if
> that's all they want they should be using a DATETIME and manually
> pass in NOW() in their INSERT.

OK, I'll try and remember to send you the schema when I finally upgrade.

And I doubt timestamps are really a problem if you understand how they
function.  (my problem is because I was clueless about them, and
ASS-U-ME'd they were the same as DATETIME)  In fact I can see how
timestamp types could be very useful.  I might have to go back and add
one to a application of mine!  (I promise NOT to make it part of a
unique key!)  :-)

> Hmmmm... maybe I should stop capitalizing SQL keywords, it looks like
> I'm yelling.  But it's burned into my brain!

Nah, I'm used to SQL help/docs with capitalized keywords.

Thanks a bunch for helping clear this up!!!

 - BS