[CentOS] MySQL and charsets: latin1 vs. utf8

Mon Jan 21 10:08:59 UTC 2008
Niki Kovacs <contact at kikinovak.net>

Hi,

Our public library management software (PMB) is currently running on a 
Slackware Linux 12.0 server, with Apache, PHP, MySQL and a few PHP 
modules. I'm considering migrating it to CentOS 5.1, because it would be 
easier to maintain for updates, and for various other reasons.

Slackware is one of the rare distros that does not default to an utf8 
system locale. The default is en_US, and since I use a french system, I 
chose fr_FR at euro for localization. There's a caveat in 
/etc/profile.d/lang.sh, justifying the choice of latin1 over utf8, so I 
complied to that.

When working with PMB, I often go under the hood, open up a MySQL 
console and isse SQL statements from there. The app itself seems to 
favour latin1 over utf8. For example, when doing a backup database dump, 
the resulting dumpfile will be iso-8859-1.

Now, as expected, I'm having some charset woes with CentOS. I've re-read 
the MySQL Reference Manual, the section concerning localization, which 
leaves me more confused than before. Asked in the forums there, but to 
no avail. So my next thought was to ask here.

We only use french charset here, nothing else, so there's no need for 
utf8-support in our database. Only I noticed that when I set the system 
locale to french latin1, I get some weird results in the system messages.

Is there any way to keep the system in utf8, and at the same time make 
MySQL behave like it was latin1-only?

I don't quite know how to formulate it, so I'll try to give a 
description of what I would like to have.

- My system is still defaulting to LANG=fr_FR.UTF-8

- SQL batch files are all ISO-8859-1

- I want to open GNOME Terminal, fire up the MySQL monitor, run the 
batchfile ( mysql> source /path/to/batchfile.sql) and then be able to 
issue sql statements with table contents (french special chars like é, à 
, ç, è) displaying correctly.

Any suggestion for this?

Niki

PS: encoding issues are a quite humiliating experience