Encoding not rendering correctly

Question

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

Please read [UTF-8 All The Way Through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) and then update your question if your issue remains. — Martin, Mar 18 '21 at 19:36
[What is the difference between utf8mb4 and utf8 charsets in MySQL?](https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql). — Luuk, Mar 18 '21 at 19:42
Thank you both for your links, I read them and to be honest, I don't see something I do which is agains't those practices — Etienne Ghiringhelli, Mar 19 '21 at 17:21

score 0 · Accepted Answer · answered Mar 20 '21 at 17:54

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.

Encoding not rendering correctly

1 Answers1