[mythtv-users] Database update schema error with trunk utf8

[Michael T. Dean]

On 12/17/2008 05:58 PM, Tom Dexter wrote:
> On Wed, Dec 17, 2008 at 4:00 PM, Michael T. Dean wrote:
>   
>> Yep.  We've (finally) verified that, now.  It took a lot of guidance
>> from Janne and a lot of people in IRC helping (by running tests for) us
>> to figure out the details.  Now, we're hoping to figure out how to deal
>> with database servers that were configured to use the wrong charset.
>>
>> Also, I'd recommend *against* changing things at this point, though.  If
>> you're a Gentoo user and you're /just starting/ to use Myth--i.e. you
>> have no mythconverg database--you should emerge mysql /with/ the latin1
>> use flag (so it doesn't set a server default of utf8--do /not/ use
>> -latin1).  If you have /any/ mythconverg data, you probably should keep
>> using Myth as you have been for a couple of years--don't change your
>> mysql configuration--to give us time to figure out how to deal with the
>> issue.
>>
>> Note, also, that the "partial/new-hardware restore" instructions at
>> http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7 are wrong and will
>> corrupt your database, so please do not follow them.  I'll be updating
>> the restore script to fix the issue.
>>     
> I'm a little confused on this.

And so were we.  :)

>   If you create the database using the
> sql that comes with mythtv:
>
> /usr/share/mythtv/database/mc.sql
>
> ...as I did when I first installed...there shouldn't be any issue,
> correct?  That creates the mythconverg database with latin1 as the
> default character set.

If you're running on a system that specifies a server default character 
set other than latin1, i.e. in my.cnf:

[mysqld]
character-set-server=utf8
default-character-set=utf8

there will be (have been) problems, whether you use mc.sql to create the 
initial database or not.  However, you are better off *not* changing 
your server configuration (i.e. do not remove those lines or change the 
value to latin1).  If you change things now, you'll have "mixed" 
corruption--some data will be corrupt and some will be correct--which is 
/much/ harder to fix than "total" corruption (we will probably leave 
"mixed" corruption for the user to fix).

We (especially Janne) are currently working to find a good way to test 
for corruption before the database upgrade so we can just "do the right 
thing."  Once done, your data and your database should be good after the 
upgrade to 0.22.

Also, now is a /very/ bad time for any Gentoo user (or anyone else who 
has configured their my.cnf as above) to switch to trunk.  If you change 
to trunk, now, you will get data corruption because all the conversion 
code is written with the expectation that the mythconverg database has 
good data in it now.  So, if you have mysqld configured as above and are 
using -fixes, definitely stick with -fixes at least until we have a good 
test for corruption in place.

Note that Gentoo users will see jibberish for non-ASCII characters in 
the UI, OSD, etc., in 0.21-fixes (but they should have been seeing such 
corruption ever since they started using a MySQL >=4.1 built with 
-latin1)***.  However, in theory, we will have things fixed by the time 
0.22 is released such that everyone--with or without a proper MySQL 
configuration--will have good data (as long as they don't have "mixed 
corruption") and all their characters should work/look properly in the 
UI (assuming proper font support, of course).

So, again, don't change anything as doing so will make things worse.

Mike

***I'm now relatively certain that the author of the "don't do this, it 
will break things" page in the wiki ( 
http://www.mythtv.org/wiki/index.php/Utf8_Text_in_OSD ) was using a 
system with mysqld configured as above, which is the only reason the 
text in the OSD was not working properly for him/her.