Using utf8mb4 in MySQL
Solution 1
The client usually sets these values when connecting. The settings in my.ini are merely defaults which apply when the client does not explicitly specify a connection encoding. Since they're unreliable, every client should specify a connection encoding. Since you've got some fancy screenshot there I'll guess that you're connecting with some GUI utility which probably explicitly does set some connection encodings.
PHP example of setting a connection charset:
new PDO('mysql:host=localhost;charset=utf8mb4')
Solution 2
If you show your global variables, you might see that all your settings are correct actually.
SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
This might be related to this bug I also faced the same problem in the past. I changed a DB charset from utf8 to utf8mb4. Executing queries directly from mysql command line was ok but I had problem inserting emojis with Workbench. I ended up setting it manually by running
SET NAMES 'utf8mb4'
every time I open a connection to my DB with Workbench.
Using Sequel Pro as alternative was fine as well.
Solution 3
I think you are connecting as root, hence the init-connect='SET NAMES utf8mb4'
is not executed.
It is unwise to use root (or SUPER) for any application code; just for administrative actions.
Related videos on Youtube
Tiny
Just an orphan kid and have no more to say. Three things in general, cannot be avoided (at least I can never) Mother Mother-tongue Mother-land. They are always unique. I'm a family-less boy. My family was hunted leaving me all alone when my house targeted and deliberately set on a fire by a mob during a nonsense communal riot but I was survived by a rescue team with the help of firemen. As a survival, I didn't know whether it was my fortune or misfortune but when I recovered, the rescue team came to my home, one day. One of the members gave me a piece of paper in my hand in which the following text was written. lifeisnowhere. He asked me to read it carefully and I could hardly interpret the text as Life is now here, instead of Life is nowhere. All of them gave me a cute smile and went away and I decided to live peacefully and hopefully on their saying from then onwards and very soon. Because of this tragedy, I'm alone couldn't join a school but a curiosity to learn something made me a self-learner. I'm indeed a self-learner, so I'm likely not able to answer any questions on this site right now. In the field of computer science, my self-study mainly includes, QBASIC, C, C++, C#, VB, Java, JavaScript, PHP and a little about ASP.NET. Oracle, MySQL and MSSQL-Server with DBMS. and other theoretical subjects. I'm currently dealing with - Android and Java EE including Servlet, JSP-JSTL/EL (with Spring and Struts with ORM models JPA/Hibernate) and JSF.
Updated on January 05, 2020Comments
-
Tiny over 4 years
In order to use 4-byte
utf8mb4
in MySQL (5.6.11), I have set the following variables in themy.ini
file (my.cnf
is not found). This file is located in a hidden folder namedApplication Data
(C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6
) on Windows XP. It is not available under the installation directory.[client] port=3306 default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] init-connect='SET NAMES utf8mb4' collation_server=utf8mb4_unicode_ci character_set_server=utf8mb4
And then issuing the following command,
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
still displays the following list.
From the picture itself, it is clear that several variables are still using 3-byte
utf8
.
Before doing this, the following command had already been issued to make corresponding changes to the database itself.
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
And the following command had also been issued on each and every table in the said database.
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Nevertheless, what is the reason why some variables have not yet been set to the said character set as well as the collation? What is missing?
The system (operating system) itself was restarted after every single task specified above had been carried out.
-
Rick James about 9 yearsBe warned: user=root (or other SUPER user) will not execute
init-connect='SET NAMES utf8mb4'
.
-
-
Tiny about 9 yearsSo, are they basically not to be worried about? I created a connection pool from Java side where some parameters have been set such as
characterEncoding
is set toUTF-8
,useUnicode
totrue
andcharacterSetResults
toUTF-8
. They basically serve the purpose of the parameters which are appended to a connection string with the same name/value pair. (The screenshot was taken from MySQL WorkBench. MySQL terminal has not been opening for some unclear reasons, since MySQL was installed. It just flashes out for a while and disappears, whenmysql.exe
is double clicked. So, I use WorkBench) -
Gromski about 9 yearsIf your actual client is going to declare its own
utf8mb4
connection encoding, then yes, it's not to be worried about. You don't need to touch my.ini at all to get a utf8mb4 workflow going. -
Tiny about 9 yearsI could manage to start MySQL command-line somehow. It only sets those values to
utf8mb4
, whenskip-character-set-client-handshake
is used. On MySQL WorkBench, it requires a manual commandSET NAMES 'utf8mb4';
to be issued which turns all of those values intoutf8mb4
but still leavescollation_connection
to its valueutf8mb4_general_ci
.character_set_system
on the other hand, remains stationary in all the cases toutf8
. -
Tiny about 9 yearsI nowhere read, "It is unwise to use root (or SUPER) for any application code". I interpreted "application code" as client applications such as Java/PHP/ASP.NET or something else. Does it meant that one should always insist upon creating another/other user/s, if one wishes to use MySQL through client applications which should access database/s from that another user and not from the root user?
-
Rick James about 9 yearsIt is a general security issue. If a hacker can get into a client and discover the root password, then he can destroy not only the database, but possibly the machine it is on. And, if you have multiple users, it is better to do some amount of isolation between them. By application code I mean both end users (who generally should not have mysql login at all) and app layers (Java, etc) that act on their behalf.
-
Rick James about 9 yearsAnd, perhaps, you found the little-known caveat about
root
skippinginit-connect
? -
Tiny about 9 years
utf8mb4
appears to be set, whenskip-character-set-client-handshake
is used. I do not even know what it means (I think its usage is discouraged). RDBMS is something which is far beyond me :) -
Brad Kent about 8 years@Tiny any way to configure Mysql Workbench to to use utf8mb4 by default so issuing SET names utf8mb4 isn't necessary each time I connect?