Using utf8mb4 in MySQL

Question

In order to use 4-byte utf8mb4 in MySQL (5.6.11), I have set the following variables in the my.ini file (my.cnf is not found). This file is located in a hidden folder named Application Data (C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6) on Windows XP. It is not available under the installation directory.

[client]
port=3306
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4

And then issuing the following command,

SHOW VARIABLES
WHERE Variable_name
LIKE 'character\_set\_%'
OR Variable_name LIKE 'collation%';

still displays the following list.

From the picture itself, it is clear that several variables are still using 3-byte utf8.

---

Before doing this, the following command had already been issued to make corresponding changes to the database itself.

ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

And the following command had also been issued on each and every table in the said database.

ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

---

Nevertheless, what is the reason why some variables have not yet been set to the said character set as well as the collation? What is missing?

The system (operating system) itself was restarted after every single task specified above had been carried out.

Answer 1

The client usually sets these values when connecting. The settings in my.ini are merely defaults which apply when the client does not explicitly specify a connection encoding. Since they're unreliable, every client should specify a connection encoding. Since you've got some fancy screenshot there I'll guess that you're connecting with some GUI utility which probably explicitly does set some connection encodings.

PHP example of setting a connection charset:

new PDO('mysql:host=localhost;charset=utf8mb4')

Answer 2

If you show your global variables, you might see that all your settings are correct actually.

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

This might be related to this bug I also faced the same problem in the past. I changed a DB charset from utf8 to utf8mb4. Executing queries directly from mysql command line was ok but I had problem inserting emojis with Workbench. I ended up setting it manually by running

SET NAMES 'utf8mb4'

every time I open a connection to my DB with Workbench.

Using Sequel Pro as alternative was fine as well.

Answer 3

I think you are connecting as root, hence the init-connect='SET NAMES utf8mb4' is not executed.

It is unwise to use root (or SUPER) for any application code; just for administrative actions.