[mythtv-users] Error when changing Mythtv's mysql database from utf8 to latin1 on Gentoo

[Michael T. Dean]

<fixed top posting>

On 11/11/2009 12:07 PM, David Liana wrote:
> On Wed, Nov 11, 2009 at 11:38 AM, Steve Evans wrote:
>   
>> I am using Mythtv 0.21 on Gentoo. Gentoo sets up mysql to use utf8,
>> but Mythtv prefers it to use latin1. This will cause problems when
>> Mythtv is upgraded to 0.22. There are instructions on how to fix this at:
>>
>> http://wiki.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding
>>
>> I followed these instructions, but got this error when restoring the fixed
>> backup:
>>
>> ERROR 1062 (23000) at line 1712: Duplicate entry 'Kathleen ÃôDixieÃö
>> Dixon*Jane Hazlegr' for key 2
>>
>> Fortunately it was possible to restore the original, unfixed, backup,
>> so I have a working mythtv :-)
>>
>> Does anyone have any idea how to fix the database /without/ getting this
>> error?
>>
>> Steve
> I am a Gentoo user...  Here is my understanding of what needs to be done (or
> better yet, what I did).  I could completely be out in left field, so if
> anyone wants to correct me, I can at least restore from a backup.
>
>
>    1. I had the latin1 use flag set for as long as I remember, so it is my
>    understanding that I would not be, or at worst minimally affected.  I
>    think.
>    2. I would dump the full database using the mysqldump command[1]
>    3. Using the mysqldump comand, pullout the important tables (like
>    recorded, recordeddata, etc.).  I think the syntax for this is on the wiki.
>    I like to set up my hardware and other settings from scratch when I
>    4. Drop mythconverg.
>    5. Make a backup of /etc/mysql/my.conf, and replace utf8 with latin1 in
>    my.cnf.  I did not have to do this since everything was latin1.
>    6. Re-create mythconverg with a charset of latin1.
>    7. Run mythtv-setup from 0.21, but do not set anything up and exit.
>    8. Restore you're backup from step 2 or 3, depending on your
>    preferences.  I restored from step 2.
>    9. Upgrade mythtv, mythtv-themes and your plugins to v0.22.
>    10. Run mythtv-setup and tweak your settings.  It looks like this
>    upgrades your db to utf8.  (I'm assuming this is okay)
>
>
> [1] http://www.mythtv.org/wiki/Backup_your_database#The_mysqldump_command

There's more to it than that.  See 
http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding for details.

Mike