[mythtv-users] Trying to fix utf8 database upgrade issues: a few schema questions

[Michael T. Dean]

On 02/13/2010 09:57 AM, Tom Dexter wrote:
> On Thu, Feb 11, 2010 at 12:44 PM, Tom Dexter wrote:
>    
>> I hoping to upgrade to 0.22 soon.  I'm running under Gentoo.  I
>> performed the database backup/restore fix as described in the wiki:
>>
>> http://wiki.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding
>>
>> ...back in November.
>>
>> I'm still unclear as to why, but I've been able to determine with some
>> tests I've programmed that I'm going to run in a ton of database
>> corruption errors when I try to upgrade.  In spite of the fact that I
>> did everything exactly as per the wiki, it seems that subsequently
>> continuing to run 0.21 has caused all sorts of problems with non-ASCII
>> characters.
>>
>> I have WAY too many recordings to loose, so I'm going to fix it
>> whatever it takes.  I do database programming so I think I can do it,
>> but I have a few questions about the database schema that hopefully
>> someone can answer:
>>
>> 1)  Are the ids in the "people" table (it's person column) referenced
>> anywhere other than the credits and recordedcredits tables?  If not,
>> is there any reason I can't delete any records that aren't referenced
>> on either of them?
>>
>> 2)  Is the "oldprogram" table used for anything other than the program
>> search for new titles?  That is, is it used for scheduling when
>> filtering for non repeats or anything?
>>
>> That's it for now.
> Well, as far as everything I can see in the source, it appears that
> I'll be ok deleting the orphaned people records and clearing the
> oldprogram table.  I was hoping someone could confirm that, but I'm
> fairly sure I'll be ok.  In any case that's about my only hope of
> fixing the database corruption.  That'ss get it to a manageable about
> of data.
>
> I'm still bothered by the fact that after fixing the database, even
> with all my configs properly set to latin1, I've accumulated so many
> apparently bad records...and they do in fact all appear to be records
> created after the fix.  Based on everything I understand about the
> issue that just doesn't add up.
>    

If the people table is corrupt, likely others are.  So, even though it's 
likely the upgrade would appear to succeed if you truncate the people 
table, it would likely leave behind a lot of corrupt data.

You'd be best served doing a partial restore of the 0.21-fixes 
database.  That will throw away all of the corrupt data with the 
exception of data that can't be recreated (and that applies only to 
recordings you have--where the corruption would just result in display 
issues).

Just take your 0.21-fixes database backup and do a partial restore ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup 
) into a blank 0.21-fixes database.  If you don't have a blank 
0.21-fixes database and don't feel like installing 0.21-fixes to create 
one, see http://www.gossamer-threads.com/lists/mythtv/users/406111#406111 .

Mike