[KLUG Members] MySQL weirdness ...

[Jamie McCarthy]

bill at billtron.com (bill) writes:

> > | Field       | Type             | Null | Key | Default             | Extra |
> > | starttime   | timestamp        | YES  | PRI | CURRENT_TIMESTAMP   |       |
> > | endtime     | timestamp        | YES  | MUL | 0000-00-00 00:00:00 |       |

> > After my first UPDATE statement, the starttime field IS reset to the
> > current date/time.  HOWEVER, endtime is left unchanged!  Why???
> 
> 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 :)

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


Bruce writes:

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

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

Hmmmm... maybe I should stop capitalizing SQL keywords, it looks like
I'm yelling.  But it's burned into my brain!
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg