11

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?

12

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?

  • 1. I saved changes in \xampp\mysql\bin\my.ini. I was unable to run mysqld --help --verbose 2> /dev/null | grep -A1 'Default options' - as it gives the following error: 'grep' is not recognized as an internal or external command, operable program or batch file. I'm new to this stuff, so I might be doing something wrong. – qwaz Feb 19 '14 at 8:50
  • 2. I did restart mysqld, and after it gives me the following warnings: 2014-02-19 10:25:17 0 [Warning] Using unique option prefix character_set_client instead of character-set-client-handshake is deprecated and will be removed in a future release. Please use the full name instead. 2014-02-19 10:25:17 0 [Warning] mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8mb4' – qwaz Feb 19 '14 at 8:51
  • 1
    Why do you have skip-character-set-client-handshake in your config file? It’s a command-line option, not a variable. – Mathias Bynens Feb 19 '14 at 9:09
  • 1
    I had to re-install local server; walked through your guide again and everything worked out. I appreciate your response and help. I will come back to your answer and upvote it as soon as I have enough reps to do so. – qwaz Feb 19 '14 at 16:51
  • 2
    I tried this but still character_set_client, character_set_connection, character_set_results, collation_connection is shown as utf8. Any ideas why this is not working? – Sarita Jul 20 '16 at 10:43
1

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

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.