Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I decided to upgrade my Debian installation, and after some days using MySQL 5.5, I found that mysql prints garbage to the terminal intead of UTF-8 characters.

I had a look at the SQL file that was produced by mysqldump, and noticed it already contained the garbage. Today I dropped the database and got a copy of the old /var/lib/mysql/database_name/* which was working well and, to my surprise, I still get the same results.

The file produced by mysqldump includes the following lines:

/*!40101 SET NAMES utf8 */;
/*!40101 SET character_set_client = utf8 */;
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/var/lib/mysql/database_name/db.opt from the old and new databases are the same:

default-character-set=latin1
default-collation=latin1_swedish_ci

What can I do now? I hope I won't lose the entire database!

UPDATE

show session variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

SELECT charset(name), hex(name), name FROM table WHERE id='123';
+---------------+----------------------------------------------+----------------------------+
| charset(name) | hex(name)                                    | name                       |
+---------------+----------------------------------------------+----------------------------+
| latin1        | 457361C3BA204A6F73C3A92064652053616E74616E61 | Esaú José de Santana     |
+---------------+----------------------------------------------+----------------------------+

The output of name in the last command should be Esaú José de Santana

share|improve this question
    
Please show the terminal's settings. –  CL. Sep 30 '12 at 11:02
    
Hi, terminal settings have not changed. And the problem is only with MySQL, everything else works: TERM=xterm; LANG=pt_BR.UTF-8 –  Teresa e Junior Sep 30 '12 at 12:10
    
Please show the output of show session variables like 'character%';, and of SELECT charset(mycolumn), hex(mycolumn) FROM mytable WHERE ... for some affected record. –  CL. Sep 30 '12 at 12:23
    
Already updated! –  Teresa e Junior Sep 30 '12 at 14:44

2 Answers 2

up vote 2 down vote accepted

The answer from @CL. helped me a lot with debugging, but it was the other way around: both the database and the backup files were encoded with latin1, but the client expected UTF-8 output.

The solution was to make sure character_set_client was set to latin1 as well:

[client]
default-character-set = latin1
share|improve this answer

The MySQL output and the terminal work correctly; the problem is that the text in the database is labelled as latin1, but actually encoded as utf8.

This can be fixed by

  1. dumping the database into a Latin1 file, then importing that file as UTF-8: Fixing a MySQL Character Encoding Mismatch; or
  2. in MySQL, converting through binary to ignore the wrong encoding:

    UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123;
    

    Fixing column encoding mess in MySQL.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.