Set value of character_set_client to utf8mb4
Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.
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
Did you restart
mysqld
by runningmysqld restart
after making the changes?
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:
Create a dummy database with
utf8mb4
character set.create database `your_db_name_dummy` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
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
Drop current database
SET FOREIGN_KEY_CHECKS=0; DROP DATABASE your_db_name;
Create new database
create database `your_db_name` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
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 wrappercat /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