MySQL utf8mb4, Errors when saving Emojis

It is likely that your service/application is connecting with "utf8" instead of "utf8mb4" for the client character set. That's up to the client application.

For a PHP application see http://php.net/manual/en/function.mysql-set-charset.php or http://php.net/manual/en/mysqli.set-charset.php

For a Python application see https://github.com/PyMySQL/PyMySQL#example or http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-unicode

Also, check that your columns really are utf8mb4. One direct way is like this:

mysql> SELECT character_set_name FROM information_schema.`COLUMNS`  WHERE table_name = "user"   AND column_name = "displayname";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)

character_set_client, _connection, and _results must all be utf8mb4 for that shortcake to be eatable.

Something, somewhere, is setting a subset of those individually. Rummage through my.cnf and phpmyadmin's settings -- something is not setting all three.

If SET NAMES utf8mb4 is executed, all three set correctly.

The sun shone because it is only 3-bytes - E2 98 80; utf8 is sufficient for 3-byte utf8 encodings of Unicode characters.

For me, it turned out that the problem lied in mysql client.

mysql client updates my.cnf's char setting on a server, and resulted in unintended character setting.

So, What I needed to do is just to add character-set-client-handshake = FALSE. It disables client setting from disturbing my char setting.

my.cnf would be like this.

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
...

Hope it helps.

Symfony 5 answer

Although this is not what was asked, people can land up here after searching the web for the same problem in Symfony.

1. Configure MySQL properly

☝️ See (and upvote if helpful) top answers here.

2. Change your Doctrine configuration

/config/packages/doctrine.yaml

doctrine:
    dbal:
        ...
        charset: utf8mb4