[mythtv-users] Questions on correcting database encoding under Gentoo while still on 0.21-fixes
Michael T. Dean
mtdean at thirdcontact.com
Mon Nov 2 19:41:31 UTC 2009
On 11/02/2009 01:26 PM, Tom Dexter wrote:
> I've been running mythtv under Gentoo all along, and have always had
> the default Gentoo mysql configuration where the default connections
> etc are utf8.
> I'm still on 0.21-fixes but wanted to get this addressed now rather
> than when I decide to upgrade to 0.22, so I did so as per the wiki:
> I've always had the same backedn and same configuration, so there's no
> reason I can think of that I'd have any "Partial" corruption.
> After changing the my.cnf and restarting mysql on the backend as per this part:
> Luckily before doing the restore I connected to the db and checked the
> 'status' command output. It was still defaulting the connection etc
> to utf8. Rather than removing the "character-set-server" and
> "default-character-set" lines in my.cnf as per the instructions, I
> discovered that I needed to expressly change them from utf8 to latin1.
Yeah, just getting rid of the explicit change to utf8 and using the
default is rather fragile as there may be my.cnf or ~/.my.cnf or scripts
or something that change the mysql command to use utf8, still. I'm
guessing, though, that USE=utf8 must specify --with-charset=utf8 when
compiling, so it explicitly changes the MySQL default in the binaries
(which has always been latin1). I modified the sed to change character
sets to explicitly specify latin1.
This is why it would have been /much/ easier for someone who actually
had this issue to figure out/write up the fix for it. :)
> Once I did that and restarted, everything looked correct. At that
> point I went ahead with the restore from the corrected backup file and
> everything seems ok. Had I not noticed that, I'm assuming future
> database writes would have caused a mess of "Partial" corruption.
> I think (though I'm not certain) that this may be related to the fact
> that Gentoo has a latin1 use flag for mysql (which is described as
> "Use LATIN1 encoding instead of UTF8") that is off by default. I
> think this causes it to use utf8 by default if not otherwise specified
> in my.cnf.
> I also noticed that if I connected to the backend from the frontend
> (on which I have a minimal mysql install with client code only) using
> the mysql command line, the default client and connection character
> sets defaulted to utf8 (however the server character set and db
> character sets where properly set to latin1). To be safe I made the
> same change in the my.cnf file on the frontend to ensure that
> everything connected with latin1 by default. Once I did that,
> connecting to the backend from the frontend showed latin1 for
> everything. I assumed that not doing this could possibly affect any
> database writes made directly to the db from the frontend (if that
> actually happens).
> I wanted to make sure that there isn't something wrong with anything
> I've done there. I'd think the wiki needs something about this or
> someone could make quite a big mess, unless they're doing this as part
> of the 0.22 upgrade...as I understand it, none of those connection
> defaults etc should matter in 0.22.
> I was also curious why the instructions don't specify using the mc.sql
> to create the database...that's what I used.
Mainly because the mc.sql adds a GRANT that may mess up the user's DB
access. If you're using mythtv/mythtv as the username/password, mc.sql
is no problem.
Also, if you use the mc.sql from 0.22-fixes or above it will set the
default DB character set to utf8. If you don't it will use the server
default, which--after the changes mentioned above--should be latin1.
However, the backup /should/ handle it OK (as it specifies
CHARSET=latin1 for each CREATE TABLE), but it's "safer" to have the DB
charset set to latin1, too, just in case.
Remember, too, that once you upgrade to 0.22-fixes, you can restore your
original mysql configuration.
Thanks for the feedback.
More information about the mythtv-users