MySQL phpMyAdmin Invalid utf8mb4 character string

Question

I am unable to make basic encoding work with MySQL and phpMyAdmin. I want to use simple french characters (such as é and è) in my data. If I run an UPDATE, the characters insert correctly. But not when I import the script...

Below is the script I import on phpMyAdmin :

SET NAMES utf8mb4;

CREATE TABLE Proprietaire (
    idProprietaire int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nomProprietaire varchar(255)
    )DEFAULT CHARSET=utf8mb4
;

INSERT INTO Proprietaire (nomProprietaire) VALUES
('Léanne'),
('Fabrice'),
('Pedro')
;

The 2 errors I am getting when importing :

Warning: #1300 Invalid utf8mb4 character string: 'E9616E'
Warning: #1366 Incorrect string value: '\xE9anne' for column 'nomProprietaire' at row 1

When importing the script on phpMyAdmin, the only choice close to utf8mb4 is utf8

The table AND the database are also in a similar encoding

But the data is stripped..

What I tried to do

Add COLLATE utf8mb4_unicode_ci to the colum that needs special characters -> Did nothing
Modify into DEFAULT CHARSET=utf8 and DEFAULT CHARSET=latin1. Latin1 replaces the accents with ? while utf8 simply cuts the word
Google 'E9616E', found absolutely nothing??
ALTER DATABASE mydbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -> Did nothing
Links I checked : How to make MySQL handle UTF-8 properly, Characters appear as question marks using MySQL, MySQL silently replaces UTF chars with literal question marks, Show and change MySQL default character set

score 0 · Accepted Answer · answered Oct 21 '19 at 04:46

0

E9 is the latin1 encoding for e-acute. You have latin1 somewhere. For utf8mb4, it needs to be C3A9.

Apparently phpmyadmin is configured for latin1, since typing

INSERT INTO Proprietaire (nomProprietaire) VALUES
('Léanne'), ...

gives you the E9.

The character_set_* being set to utf8mb4 declare that the client (phpmyadmin) used UTF-8 encoding, but apparently it does not.

answered Oct 21 '19 at 04:46

Rick James

135,179
13
127
222

I had previously tried a combination of utf8 and latin1 without luck. Following your comment, I replaced SET NAMES utf8mb4; with SET NAMES latin1; , changed my database's default encoding and removed the DEFAULT CHARSET=utf8mb4. It worked! – Maude Oct 21 '19 at 11:56
I think `SET NAMES latin1` is what made it work; the rest probably does not matter. – Rick James Oct 21 '19 at 17:15

MySQL phpMyAdmin Invalid utf8mb4 character string

1 Answers1