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

[Michael T. Dean]

On 11/22/2006 12:01 PM, Jeff Simpson wrote:
>> The programid field should be a varchar(20) in all tables in which it
>> exists (recorded, program, oldrecorded, ...).  That means your
>> database--and all the data in the table that has a varchar(6)--is
>> corrupt.  Perhaps you had a corrupt database when you upgraded, causing
>> the upgrade to fail.  Perhaps it's become corrupt recently.  You do
>> still have some backups going back to before you upgraded, right?
>>     
> Once I figured out that programid was the root cause, i was able to
> search and find a similar problem somebody else was having. Turns out
> the upgrade to MySQL 4.1 from 4.0 modified all my databases and f-d
> them up. The backups I have are just about useless since I updated
> schemas since then (upgraded to 0.20). Even if I had a backup of right
> before I updated mysql, it wouldn't help, since the mysql format
> changed anyway. Basically this upgrade has put me between a rock and a
> hard place, and I think I'm going to need to drop the database and
> regenerate it from scratch.
>   

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

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

>> Otherwise, you'll have to set the programid's in recorded (and possibly
>> oldrecorded--depending on which table is broken) to something innocuous,
>> such as EP8483610000 for Heroes.  Of course this will mess with
>> duplicate checking.  If the broken table is program, you can replace the
>> data by doing a truncate after altering the field definition and then
>> re-running mythfilldatabase.
>> But, I'm of the opinion that if you can't trust some of the data, you
>> can't trust any of it.  I'd start a new database, but then, I'm a bit OC
>> that way.  Oh, and remember, backups are a good thing.  :)
>>     
> I'm the same way. I think I'm going to just wipe it all and come clean
> again. I guess I didn't need any of the old recordings for anything,
> but I DO need it to continue recording new things successfully.

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.

Mike