CentOS - Jan 2008 - MySQL and charsets: latin1 vs. utf8

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

Niki Kovacs wrote:
> 
> when doing a backup database dump, 
> the resulting dumpfile will be iso-8859-1.
This is controlled by the setup for the tables themselves.  MySQL does 
have a default character set, but it will let you create each individual 
table in any character set you like, and even mix them.  The 
configuration of the OS only affects how things like output to the 
terminal is interpreted.
> Asked in the forums there, 
Why not try the main MySQL mailing list?  It's a different world than 
the web forum.
> We only use french charset here, nothing else, 
You don't import data from MARC or other sources, then?
> I get some weird results in the system messages.
Why are you making us guess what the errors are?
> Is there any way to keep the system in utf8, and at the same time make 
> MySQL behave like it was latin1-only?
I think what you mean to ask is, can you run the mysql command line tool 
in a different character set than the system default, so that when it 
prints out text, it goes to the terminal with the correct character set. 
  The answer is, yes:

$ LANG=fr_FR mysql -uDBUSER -p....
> PS: encoding issues are a quite humiliating experience
Better learn to understand it.  We'll be living with these issues for 
another decade or two, probably, before multi-byte characters finally 
take over the world.