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

[Tom Dexter]

On Sat, Feb 13, 2010 at 11:51 AM, Michael T. Dean
<mtdean at thirdcontact.com> wrote:
> 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

Thanks for the reply.  Actually I wasn't going to truncate the people
table, but rather remove any that weren't referenced in credits or
recordedcredits and fix the rest.  I was however going to truncate
oldprogram.  I have a perl script I've been testing on a copy of the
database that will patch up bad remaining column data in people,
recorded, and oldrecorded.  It does so by replacing bad accented
characters with the closest non-accented character.  I know that's not
perfect, but there are only a few rows involved.  In my tests I was
able to get all the schema upgrades to apply after that.

I'm going to save my pre-upgrade backup and, if need be, take the
partial restore approach you're suggesting.  First however I'm going
to try my surgical approach and test it thoroughly...I've been
warned...:D