Mysql – Cannot set character_set_database and character_set_server to utf8mb4

character-setcollationMySQLutf-8

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.

Best Answer

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.

Related Solutions

MySQL the.cnf won’t take any effect

The option character-set-database should not be configured in my.cnf.

Please note what the MySQL Documentation says on character-set-database:

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

Footnote : This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

Even the Documentation says it is dynamic, it not supposed to be dynamically by any manual intervention against my.cnf. If you look inside the database subfolder, you will find a file called db.opt. EXAMPLE : When you run use dbname in the mysql client, the file /var/lib/mysql/dbname/db.opt is read in order to set character-set-specific database options contained in that file. For this reason, the variable has to be dynamic.

If you cannot access the database from the OS to see db.opt, simply run this command:

SHOW CREATE DATABASE dbname;

on any database and you will see what db.opt contains (or defaults if db.opt is not there)

mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

In light of this, you should try setting character-set-server in my.cnf only (or at least remove character-set-database from my.cnf). Then, run service mysql restart.

Give it a Try !!!

UPDATE #1

I sort of dealt with a question like this before : Why default character_set_server is latin1?

Looking back at my old link, I had an idea: I ran this:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from information_schema.collations where COLLATION_NAME like '%kor%';
+-----------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+----+------------+-------------+---------+
| euckr_korean_ci | euckr              | 19 | Yes        | Yes         |       1 |
+-----------------+--------------------+----+------------+-------------+---------+
1 row in set (0.00 sec)

mysql>

You could set a Korean character set if need to.

UPDATE #2

You should leave wait_timeout and interactive_timeout out of the [client] and [mysql] groups.

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.