[mythtv-users] Trying to fix utf8 database upgrade issues: a few schema questions
Michael T. Dean
mtdean at thirdcontact.com
Sat Feb 13 16:51:09 UTC 2010
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
More information about the mythtv-users
mailing list