20

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?

qwaz
  • 305
  • 1
  • 2
  • 7

3 Answers3

17

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?

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Mathias Bynens
  • 2,470
  • 1
  • 22
  • 14
3

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
RDFozz
  • 11,731
  • 4
  • 25
  • 38
Pallav Jha
  • 131
  • 3
0

In my case, I am trying to connect my client to a remote server where I don't have control over the mysqld settings. This worked for me:

[client]
init-command='SET NAMES utf8mb4;'

[mysqld]


init-connect='SET NAMES utf8mb4;'

--init-command=str

Single SQL statement to execute after connecting to the server.

Source: https://dev.mysql.com/doc/refman/8.4/en/mysql-command-options.html#option_mysql_init-command

WoodrowShigeru
  • 101
  • 3