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

[Michael T. Dean]

On 09/27/2009 06:58 AM, Petr Stehlik wrote:
> Petr Stehlik píše v Ne 27. 09. 2009 v 11:39 +0200:
>> /home/joy/myth/22/share/mythtv/mythconverg_restore.pl --filename mythconverg-to_uncorrupt.sql --directory /var/tmp
>> ERROR 1062 (23000) at line 1700: Duplicate entry 'Samuel Ivaška (Slovenská republika' for key 2
>>     

Just a warning--make sure you keep that original 1214 (0.21-fixes) 
backup as it will be your best chance of getting the old information back.

> BTW, the SELECT * FROM mythconverg.people where name like "Samuel Iva%"
> looks as follows:
>
> "person","name"
> 2871,"Samuel Ivaška (Slovenská republika 1983)"
> 4094,"Samuel Ivaška (Slovenská republika 1988)"
> 644,"Samuel Ivaška Hrají: Vladimír Hajdu"
> 3686,"Samuel Ivaška. Hrají: Ferdinand Libant"
> 37451,"Samuel Ivaška. Hrají: Ján Havrila. (Slovenská republika
> 1988)"
>
> I cannot see any duplicate entry there.
>   

The duplication is caused by truncation during conversion due to invalid 
encoding of some of your entries.  This means that there is both UTF-8 
stored in

I'm assuming that your query above was done on the original 0.21-fixes 
DB?  If not--if it was done against the partially-restored "uncorrupt" 
database, you wouldn't see the duplicate, as it was never inserted and 
mysql instead errorred and stopped the restore when it found the error.

Since the original upgrade encoding check failure occurred during the 
oldrecorded table test, that means that the people and oldprogram tables 
are not corrupt.  Therefore, you have the dreaded partial corruption 
problem.  Unfortunately, since one of the corrupt tables is 
oldrecorded--which is one of the ones you need to restore during a 
partial restore--even a partial restore won't work.

Your database likely became partially corrupt due to either switching 
your MySQL server from a properly-configured one to an improperly 
configured one (i.e. moving the database between distros) or due to 
following the instructions in the HOWTO for a partial database restore 
(in http://www.mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7 ), which we 
/now/ know silently corrupts data.

> Can someone please suggest how to proceed? I don't need any of these
> information, I just want to upgrade MythTV without going through the
> whole setup process and retain my recording rules and recordings.

My recommendation is--for now--stay on 0.21-fixes (and restore your 
0.21-fixes database).  I'm still trying to come up with a better way to 
fix things for "tough cases" like yours.  I'll probably actually have 
some time to work on it this week, too.

Mike