Set value of character_set_client to utf8mb4

Question

I'm trying to convert my DB to utf8mb4 following this guide. I have set:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
skip-character-set-client-handshake

But the value of character_set_client and character_set_results still won't change to utf8mb4.

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

What am I doing wrong? How do I get those values set to utf8mb4?

Answer 1

Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.

1. Where did you save the modified settings? Check where mysqld loads the default options from. It’s usually /etc/my.cnf (as mentioned in the guide) but it may be different on your system. Run the following command to find out:

   $ mysqld --help --verbose 2> /dev/null | grep -A1 'Default options'
   Default options are read from the following files in the given order:
   /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
   

2. Did you restart mysqld by running mysqld restart after making the changes?

Answer 2

I've also followed that guide, however, the same issue appeared again. The actual issue lies in command used to create the DB as it takes DEFAULT CHARACTER as an argument and if that is not passed while execution a default character set is passed which is not utfmb4.

Below are the steps that I followed to fix it:

1. Create a dummy database with utf8mb4 character set.

   create database `your_db_name_dummy`
     DEFAULT CHARACTER SET utf8mb4
     DEFAULT COLLATE utf8mb4_unicode_ci;
   

2. Copy actual structure and data to this dummy database from actual database

   mysqldump -uroot -proot_password your_db_name | mysql -uroot -proot_password your_db_name_dummy

3. Drop current database

   SET FOREIGN_KEY_CHECKS=0;
   DROP DATABASE your_db_name;
   

4. Create new database

   create database `your_db_name`
     DEFAULT CHARACTER SET utf8mb4
     DEFAULT COLLATE utf8mb4_unicode_ci;
   

5. Finally, put the table structure and data back to the original database

   mysqldump -uroot -proot_password your_db_name_dummy | mysql -uroot -proot_password your_db_name

If you're going to use above mentioned procedure then please make sure to carefully read below mentioned lines too.

Prerequisites and my status quo:

• Backup all your structure, data, stored procedures, functions, triggers, etc.

• mysql --version mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

• cat /etc/my.cnf

  [client]
  default-character-set = utf8mb4
  
  [mysql]
  default-character-set = utf8mb4
  
  [mysqld]
  character-set-client-handshake = FALSE
  character-set-server = utf8mb4
  init-connect = 'SET NAMES utf8mb4
  collation-server = utf8mb4_unicode_ci
  

• I'm using java client which is 'mysql:mysql-connector-java:5.1.30'

• My connection string is jdbc:mysql://ip.of.database:3306/your_db_name?zeroDateTimeBehavior=convertToNull&noAccessToProcedureBodies=true&autoReconnect=true