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@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.
Warren Young a écrit :
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....
Thanks for your reply! Your suggestion solved the problem partially. When I create tables manually and fill them manually, french characters get displayed correctly. Example:
mysql> select * from pet; +----------+---------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+---------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Bamboù | Jérôme | dog | m | 1999-01-20 | NULL | | Diégo | Héloïse | cat | m | 1998-01-21 | NULL | </snip>
But when I try, for example, to restore a database from a latin1-encoded dumpfile, it results in a loss of all my special chars, e. g. "Hélène" becomes "Hlne", "Marylène" becomes "Marylne", and so on.
Any idea how this could work?
-
Niki Kovacs -
Warren Young