[mythtv-users] This program was previously recorded... but it wasn't!

[Jeff Simpson]

> But, dropping the database and then importing the entire backup would
> mean that you'd have an old schema version in new MySQL 4.1 format
> (since backups are just SQL, they will go in fine--assuming you're using
> a "portable" backup--as per
> http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5 --rather than just
> backing up the MySQL data files).  Then, you just need to run
> mythtv-setup, mythbackend, or mythfrontend and it will upgrade your
> schema to current (running mythtv-setup first and fixing any settings is
> the most appropriate).

That's true. If only i'd realized it sooner, I probably would have
done that. It's been a couple weeks since the MySQL upgrade, so a good
portion of the recordings have happened since then. The problem is, it
all seemed to be working fine, so I just kept on trucking and only
recently realized the database corruption. I do have a backup I can
try to re-import pieces of (I'll need to hack around the .sql file to
make it only import data and not table structure)

> If you have a database backup from before the corruption, there's no
> need to lose everything.  A databse backup is the pillow between you and
> that rock/hard place.  :)

I think I'll start by getting it up and working again, then maybe I'll
re-import the old recordings. I think I'll probably import them into a
different database, then export only the ones I want to use to a .sql
file (then reimport those again into the right db). It's a bit of a
pain, but for the most part, I just want new recordings to work
properly again.

> Once you get the pre-corruption data in place, depending on how
> important it is to you, you could work on adding data that's been
> inserted since the upgrade.  One way of doing so is pulling the
> appropriate SQL statements out of the later backups and manually fixing
> them--using, for example, the "innocuous" programid's.  Another approach
> is using a script like myth.rebuilddatabase.pl to just re-import the data.
> Note that even if you decide to start over, you can still use
> myth.rebuilddatabase.pl to import some of your old recordings.  It will
> do so without programid's, so it won't affect your future recordings.

yeah, that may be in my future for some of these. I'm pretty sure none
of my recordings were save-forever type things, since most of the
things i wanted to save forever i already saved to DVD.

Thanks for the help!

There should be some kind of major warning against willy-nilly
upgrading MySQL. I've done it before, and usually it works fine, but
this upgrade managed to corrupt all my data. Oh well, hopefully this
thread will help people who search for this problem in the future
solve it.

So in summary, the CORRECT way to upgrade MySQL from 4.0 to 4.1:

- back up all your databases to .sql files
- drop all the databases
- update MySQL
- re-add the databases
- restore the contents from the .sql files

Simply upgrading first and then trying to get the data is what got me
in this pickle. It was first a problem with the privileges table being
in the wrong format, but then it reared its ugly head in the "fields
are the wrong length" form.

 - Jeff