2

I'll describe right away the problem, and put details at the bottom.

I am dumping a database which I am fairly sure contains (almost: a few cells have wrong, latin1 encoded data) only UTF8 data. Also I have done my utmost to ensure every part of the chain defaults/is set to UTF8.

Problem: all UTF8 chars appearing in the output are screwed up as in this example:

è => è

This is what happens if - at some point - a UTF8-encoded character is interpreted bytewise as two latin1 characters which are then UTF8-encoded.

Edit: What is happening is along the lines of:

U(è) => (c3,a8) => L( è ) => (c383,c2a8) => U( è) 

ie: 1 UTF8-encoded char is seen as 2 L1-octets and each one gets encoded in UTF8 again, ends up as 2 UTF-8 encoded chars.

So the first question is "where is this happening"?

But the really funky part is that

  1. The website (html5,utf8 all over) using the database displays those characters OK
  2. I can import the dump without issues with point (1) above still holding
  3. If i "fix" the dump - replacing the pairs with the char that should be there, point (1) above unravels - the browser now shows the familiar question marks on inverted background that is the familiar placeholder for "bad" utf8 sequences.
  4. The issue depends on the client: I can SELECT from mysql and have the double-encoding as in mysqldump; OTOH if i GET the web page (composed by php with charset UTF8 connection) I see regular (not doubled) UTF8 sequences.
  5. Even weirder, I can obtain correct looking results in mysql/mysqldump if I specify --default-character-set=latin1 (!!!). In this case, however what I get does not round trip cleanly as the UTF8 counterpart does.

All of the above confirmed by examining outputs with a binary editor (even though i have LANG=en_US.UTF-8 and UTF8 char display is OK)

What I would like to see, obviously, is a clean UTF8 mysql environment which displays the same independently of the client.

This is really driving me up the wall. Any ideas?

DETAILS: Running mysql-server-5.1.73-5.el6_6.x86_64 on a Centos6 machine. my.cnf (relevant) settings:

[mysqld]
  character-set-server=utf8
  ; will inhibit start on 5.5 (a bug)
  default-character-set=utf8
  collation-server = utf8_unicode_ci
  init-connect='SET NAMES utf8'


  [mysql.server]
  ...
  default-character-set=utf8


  [client]
  default-character-set=utf8

in mysql:

mysql> show variables like '%char%';

  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8                       |
  | character_set_connection | utf8                       |
  | character_set_database   | utf8                       |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | utf8                       |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+

PHP connects with explicit utf8 charset and

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"

EDIT: Create table statements are like:

CREATE TABLE `fec_news` (
  `ID` bigint(20) NOT NULL DEFAULT '0',
  `NOME_ITA` mediumtext NOT NULL,
  `TESTO_ITA` longtext NOT NULL,
  `NOME_ENG` mediumtext NOT NULL,
  `TESTO_ENG` longtext NOT NULL,
  `NOME_ESP` mediumtext NOT NULL,
  `TESTO_ESP` longtext NOT NULL,
  `NOME_FRA` mediumtext NOT NULL,
  `TESTO_FRA` longtext NOT NULL,
  `DATA` mediumtext NOT NULL,
  `FOTO1` mediumtext NOT NULL,
  `FOTO2` mediumtext NOT NULL,
  `FOTO3` mediumtext NOT NULL,
  `FOTO4` mediumtext NOT NULL,
  `FOTO5` mediumtext NOT NULL,
  `HP` mediumtext NOT NULL,
  `LINK1` mediumtext NOT NULL,
  `POS` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Edit: The accepted answer is right on the money. I had since found about mojibake through different channel and expanded my woes in a blog post.

  • How do you "view" the dump? Is the viewer/editor capable of detecting and showing UTF8 (even for a file without BOM?) – jkavalik Nov 13 '15 at 11:38
  • Initially I was using emacs (which is utf8 capable). When I got to understand that something really fishy was going on, I started using emacs in hexl-mode (binary editor). That is how I found the pair thing - up to then I had thought I was looking as a the (incorrect) latin1 display of a UTF8 -encoded buffer. – Alien Life Form Nov 13 '15 at 11:58
  • Utf 8 stores some characters in pairs (or even triples and quadruples) of bytes. Was the hex-value c3a8? if yes then it was utf8 shown as some windows125x probably - i18nqa.com/debug/bug-utf-8-latin1.html – jkavalik Nov 13 '15 at 12:24
  • No - that's not it. What I have is: U(è) => (c3,a8) => L( è ) => (c383,c2a8) => U( è) ie: 1 UTF8-encoded char is seen as 2 L1-octets and each one gets encoded in UTF8 again, ends up as 2 UTF-8 encoded chars. – Alien Life Form Nov 13 '15 at 14:51
  • Can you add show create table <table name>? – jkavalik Nov 13 '15 at 15:26
2

Mojibake or double encoding.

Please provide SELECT col, HEX(col) FROM tbl WHERE ... to see whether the data is stored correctly. If correctly encoded for storage, è will be hex c3a8.

Don't run your app as root; it ignores init-connect, but you need the SET NAMES (or equivalent).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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