[Fixed] Encoding not rendering correctly


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

enter image description here

The encoding is the following : Mysql Encoding

enter image description here

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 :

  . . . 
  `description` text,
  PRIMARY KEY (`id`)

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 :

    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

enter image description here

: "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 "é".

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


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.

Leave a Reply

(*) Required, Your email will not be published