1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.