mysqli not setting charset to utf8mb4

Question

Problem found

The problem seems to be that $mysqli->set_charset() is not accepting `utf8mb4' as a valid encoding (just as I "speculated" in the first update). MySQL version is 5.5.41 and PHP version is 5.4.41 (no problem with that).

---

Sorry for the title, I've been searching/reading about what/where can the problem be and I'm already too confused about this...

I recently started using utf8mb4 in mysql. I'm using utf8mb4 as charset and utf8mb4_unicode_ci as collation for all tables/columns.

So I first I changed:

$mysqli->set_charset('utf8');

to

$mysqli->set_charset('utf8mb4');

made sure my php files are utf8 (I'm using Visual Studio Code so the files are created in UTF-8 by default), and php/html headers are set to utf8:

index.php

header('Content-type: Text/HTML; Charset=UTF-8');

main.php (included at the end of index.php)

<meta http-equiv="Content-Type" content="Text/HTML" />
<meta charset="UTF-8" />

The problem is that for some tables I have to manually insert the data, and this data is stored as is: with special characters, with accents, ñ, etc... And when I display this data in my website I can see that these characters � have replaced the special/accented characters.

So my question is: is there any way to store data as is (without replacing/converting special/accented characters) in mysql and be able to display it fine (as is)?

If I revert to $mysqli->set_charset('utf8'); the data is displayed fine... So this keeps me wondering that there should be no problem with storing utf-8 characters as they are and there is some codification problem somewhere...

I'm using sqlyog community (with wine) and I read somewhere that sometimes the gui does not work correctly when you change some db/table configuration and the only way is the old way (running yourself the query), but I didn't tried this yet. I ran queries to set the charset and collation of all tables/columns.

What do you think?

UPDATE

I'm starting to think that mysqli does not accept utf8mb4 as a valid character encoding and uses utf8 from php and not from mysql... I also think mysql fckd up creating utf8mb4 instead of updating the existing utf8 to support 4 bytes....

As I'm testing with mysqli charset utf8, everything is stored as is and displayed as is (with mysql charset and collation set to utf8mb4...).

UPDATE 2

SELECT name, HEX(name) FROM person LIMIT 1

This is what it outputs:

New Person has name Altaïr 416C7461C3AF72

But as I already said, this is using:

$mysqli->set_charset('utf8');

to insert and to select. If I use utf8mb4 instead this is what it gets stored:

Altaïr

But it's displayed ok. What it's not displayed ok is if the name is stored as is, the displayed name will be Alta�r.

So the question is: Why is mysqli/mysql storing ï as ï using utf8mb4? And why is php displaying special characters like ï as � when utf8mb4 is set in mysqli?

Can someone please confirm that mysqli::set_charset accepts utf8mb4 as a valid encoding?

UPDATE 3

I have a class function that selects a string from a table "es", for example: Iniciar Sesión (this is what's stored) and if mysqli charset is utf8, what is being selected/displayed is Iniciar Sesión.

This maybe a completely different problem but it's clearly another codification problem. From my understanding, if the tables/columns are utf8mb4 and mysqli is set to utf8, mysql has to encode from utf8 (3bytes) to ut8mb4 (full byte support). So this means that mysqli does not use utf8 from php but from mysql. Is this correct, right?

My application is currently having a rough time with encodings... (but maybe is some server configuration problem...)

UPDATE 4

Can the problem be here? I really have no idea about this kind of configurations:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

UPDATE 4-1/2 (copied from a comment)

CREATE TABLE `es` (
    id int(11) NOT NULL AUTO_INCREMENT, 
    name varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    text varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    PRIMARY KEY (id), 
    UNIQUE KEY name (name)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` 

Answer 1

The problem might stem from the fact that you're not using utf8mb4 in your MySQL column definition (at least you did not say what encoding you're using).

Here is an example of a MySQL table definition with a column that uses utfmb4:

CREATE TABLE `person` (
  `name` varchar(255) CHARACTER SET utf8mb4
)

UPDATE

Using the following table definition:

CREATE TABLE `person` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and the following PHP script:

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$mysqli->set_charset('utf8mb4');

$mysqli->query("INSERT INTO `person` VALUES ('Altaïr Ibn-La\'Ahad')");

$result = $mysqli->query("SELECT * FROM `person` LIMIT 1");

$person = $result->fetch_object();

if($person)
    printf ("New Person has name %s.\n", $person->name);

$result->close();
$mysqli->close();

when I insert "Altaïr Ibn-La'Ahad" into the database, the name is stored as is without changes. The script also prints the name without changes: "New Person has name Altaïr Ibn-La'Ahad."

I hope this helps you resolve your problem. Let me know if it does or doesn't.

Answer 2

utf8mb4 Altaïr is 41 6C 74 61 C383C2AF 72

Ouch. That is "double encoding". latin1 EF was converted to utf8/utf8mb4 C3AF; then C3, incorrectly treated as latin1 was converted to C383 and AF to C2AF.

Here's what probably happened:

• The client had characters encoded as utf8 (good); and
• SET NAMES latin1 lied by claiming that the client had latin1 encoding; and
• The column in the table declared CHARACTER SET utf8 (or utf8mb4) (good).

That second step should have been fixed by

$mysqli->set_charset('utf8mb4');

I assume you are not mixing mysql_* and mysqli_* interfaces. Use only the latter.

How about posting a short, reproducible, test case.

Answer 3

Mistery solved! There was a bad installation/upgrade/config with mysql and utf8mb4 was not properly installed.

The problem with the function was that it was re-encoding the db values with utf8_encode() and somehow it was causing these kind of characters ó -> ó.