mysql console (windows->linux), wrong character set?

Question

When I make a query from the mysql console and it has accents or any character that needs to be utf-8 encoded, it gets mugged

INSERT INTO users (userName) VALUES ("José Alarcón");
SELECT userName FROM users;
José Alarcón

SET NAMES utF8 changes nothing --default-character-set=utf8 as parameter changes nothing Keep in mind than this is ONLY from the console. If I use phpmyadmin or make any query from a program, there is no problem at all, but an inserted row from the console gets muggled. I'm using putty on windows as client

~$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Clarification: Mi local computer is windows XP, i'm using putty 0.60 as terminal client. The target system where MySQL is running is a Debian linux I can't find any configuration in putty for character encoding...

Update: Stupid PuTTY, having the encoding configuration inside a menu called "translation" WTF?

Answer 1

Windows can't handle UTF8 in console and system messages (which putty uses). It wants to use your locale codepage. This is a common and known problem, and it's not solvable without rewriting cmd.exe, or using a different command line tool.

Microsoft have never really bothered about encodings outside their own world, which results in weird windows specific codesets.

Maybe you can change the putty encoding somewhere in its options so that it can at least communicate correctly to the mysql cli?

Answer 2

Your terminal client must be configured using UTF8. Your shell environment on the server must also be configured as UTF8. You can check it out with the following command.

locale

It depends on the distribution (I'm assuming you are using linux) how the system prefers how you fix the locale information if needed. For instance, Debian (and, I guess, Ubuntu) ask you to use the following command to reconfigure the locale settings.

dpkg-reconfigure locales

Notice; I'm not sure if they've changed this, haven't tested it in a while. :-)

You can of course set the locales in the shell each time you log in or in your profile. I recommend that you use the distribution's method to do it (if you need to do it after all :-)).

Answer 3

Set PuTTY to interpret received data as UTF8 in Window -> Translation "Character set on received data".