Cannot set character_set_database and character_set_server to utf8mb4

Question

I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set.

Preliminary Notes:

• I'm using Xampp (with MySQL 5.6.24). Default options are read from the following files in the given order: C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\xampp\mysql\my.ini C:\xampp\mysql\my.cnf, and none exists at the moment.

• character-set related variables are as follows before creating any option file:

  mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
  +--------------------------+-------------------+
  | Variable_name            | Value             |
  +--------------------------+-------------------+
  | character_set_client     | cp850             |
  | character_set_connection | cp850             |
  | character_set_database   | latin1            |
  | character_set_filesystem | binary            |
  | character_set_results    | cp850             |
  | character_set_server     | latin1            |
  | character_set_system     | utf8              |
  | collation_connection     | cp850_general_ci  |
  | collation_database       | latin1_swedish_ci |
  | collation_server         | latin1_swedish_ci |
  +--------------------------+-------------------+
  

Now I convert one schema to utf8mb4 (one ALTER SCHEMA and multiple ALTER TABLEs), everything works fine. Then I create the following C:\xampp\mysql\my.cnf file:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

Next step, I stop mysql with xampp control panel, start it again, and go check the character-set related variables:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | latin1             |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | latin1_swedish_ci  |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+

According to the guide I follow, character_set_database and character_set_server should be set to utf8mb4, and collation_database and collation_server to utf8mb4_unicode_ci. What am I missing?

I have already tried to move my my.cnf option to other paths among those read by MySQL (see above), but nothing changes.

Answer 1

OK, I found what caused the problem. If mysqld is installed as a service, it sets the path to the executable to:

C:\xampp\mysql\bin\mysqld.exe --defaults-file=c:\xampp\mysql\bin\my.ini mysql

(if XAMPP is installed in C:\xampp, of course)

When mysqld is started with the --defaults-file argument, it reads the specified option file only. And as this path doesn't show up in mysql --help's list of options files, I had no idea of its existence.