[Fixed] Encoding not rendering correctly

Issue

I’m using a MariaDB, version as below :
MariaDB version

The encoding is the following : Mysql Encoding

From what I understand, it is globally UTF-8.
Notice the "latin1" for character_set_server.

If I take creation script for the table "Foo", it shows as :

CREATE TABLE `Foo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  . . . 
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5475 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

So far, so good.

I’m using TypeORM to retrieve my information (note : the database already existed before implementing TypeORM), I configured it like this :

TypeOrmModule.forRoot({
    type: 'mysql',
    ...
    charset: 'utf8'
}),

And in my Angular application, I added in the header tag :

<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

If I take this record inside cb_marchand, on mysql, it shows like this : example of data

: "Lancé en 2007, Deco-Smart aujourd’hui est le site incontournable de ventes privées déco et design."

From what I understood, "é" are UTF-8 encoded characters. So far, so good.

But when I display this description inside my website, it shows as "é".

EDIT :
Please note that this database serves another application and on it, it shows correctly.

I don’t know if :

• having é showing inside the database is correct or not
• if have character_set_server on latin1 is correct or not
• where there is something wrong in the new application

Solution

It seems like the database was double encoded : UTF-8 & latin1.

To solve this, I needed to :

• Encode the datbase in utf8mb4
• Convert the database in utf8mb4

To do the later for one field, we can use :

update TABLE_NAME set NOM_CHAMP = convert(binary convert(FIELD_NAME using latin1) using utf8mb4);

(Not tested in my base, I did the below 🙂

To convert the database all in one (all tables / fields / data) with mysqldump :

mysqldump -u root -p --opt --quote-names --skip-set-charset --default-character-set=latin1 DATABASE_NAME > DB.sql

Then :

mysql -u root -p --default-character-set=utf8mb4 DATABASE_NAME < DB.sql

To who it may concerns, I believe this is very important to test this on a dupplicate of your dababase, having a dump somewhere safe, etc.. Be very careful with that.

Credits to this article which is in french, so I translated it for future reader.