[mythtv-users] error when fixing database forced by mythtv upgrade

[Michael T. Dean]

On 09/29/2009 10:05 AM, Nicolas Will wrote:
> On Sun, 2009-09-27 at 19:09 -0400, Michael T. Dean wrote:
>   
>> So, that's because there's a "prefix" index on the name column of the 
>> people table.  Only the first 41 (of 128) characters are checked to 
>> determine if the values are unique.  I'm actually about to commit a
>> fix 
>> to the corruption-detection thanks to your noticing this.  It will 
>> actually make it a more-stringent check, so it won't cause your DB to 
>> pass, so don't worry about updating to get it.  :)
> I've been hit by the duplicate issue when moving to .22.
>
> I cleaned my DB, but it was still the same.
>
> In the clean DB file, I changed the 41 to 51 and it restored properly,
> as it was checking more characters for uniqueness.
>
> Then .22 converted its DB version without any problem.
>
> Should I expect any ill result from this?

It may break future upgrades, depending on what happens in the future.  
Changing the index prefix length is /definitely/ not a fix as there is 
no possible way that the first 41 bytes of the UTF-8 data could be 
unique across all rows (as it would have to be for the data to exist in 
a properly-encoded UTF-8 in latin1 column MythTV database) but after 
condensing to actual UTF-8, the first 41 characters are not unique--as 
the first 41 characters encompass more data than the first 41 bytes, 
presuming there are /any/ multi-byte characters.  Therefore, it means 
the table is definitely corrupt--with some latin1 data (which should not 
be there) and some UTF-8 data, such that there exist dups after 
conversion.  Therefore, even after changing the prefix length, you still 
have corrupt data.

A better approach is to get rid of the people.  They're only used by the 
People Search (which, chances are, you've never used before), and will 
be repopulated quite quickly from new listings.

If http://www.gossamer-threads.com/lists/mythtv/users/399395#399395 
(after correcting my copy/paste errors as mentioned at 
http://www.gossamer-threads.com/lists/mythtv/users/399413#399413 ) where 
the grep is run against either the "uncorrupt" backup or the original 
backup doesn't work, or if after the database upgrade is performed the 
data doesn't look right, your best bet is to wait a few days.  If you 
can't wait a few days and don't mind corrupting your data, it's your 
data, so it's your decision.  But, it's just a few days...

Mike