unable to insert utf8mb4 characters in mysql 5.6

Question

recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 which supports utf8mb4 and in my collation i have the option to select utf8mb4.but when i try to insert directly in table row through phpmyadmin i am getting this error refered this link also

       http://dba.stackexchange.com/questions/59126/set-value-of-character-set-client-to-utf8mb4


 1 row affected.
      Warning: #1366 Incorrect string value: '\xF0\x9F\x9A\x8A k...' for column 'rajulp' at row 1

i googled and found that this is because the database is not supporting utf8mb4 characters and then i followed few tutorials in internet to update my.cnf file and restarted mysql server as well as server.

       https://mathiasbynens.be/notes/mysql-utf8mb4

i tried almost all possible combination but again when i type this in sql it throws maximum as utf8 because of that i am unable to insert eboji and several 4 bytes character.

 Punjab me 1Train🚊 k niche 100 👳Sardar aa gaye..

output of below command in mysql

 SHOW VARIABLES LIKE 'char%'


  character_set_client  utf8
     character_set_connection   utf8
   character_set_database   utf8mb4
    character_set_filesystem    binary
    character_set_results   utf8
   character_set_server utf8mb4
  character_set_system  utf8
     character_sets_dir /usr/share/mysql/charsets/

please guide

Answer 1

I had the same problem with an Ubuntu installation of MySQL 5.6.23.

I had to edit /etc/mysql/my.conf and add these entries to these sections:

[client]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4

[mysql]
default-character-set=utf8mb4

Then as root execute: service mysql restart

Both my webserver connections and my local (shell) mysql connections worked with utf8mb4.

INSERT INTO some_table_name
(some_index_id,value_to_be_utf8mb4)
VALUES (55, x'F09F9A8A');

select * from some_table_name;
| some_index_id | value_to_be_utf8mb4 |
|            55 | 🚊                  |
1 rows in set (0.00 sec)

The following are from a fresh command line instantiation of mysql:

mysql> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As a final note, the "character_set_system" is always "utf8" according to the mysql 5.x specifications, so that is normal.

Answer 2

You still have the character_set_client set to utf8

First make sure utf8mb4 exists

mysql> select * from information_schema.character_sets where CHARACTER_SET_NAME like 'utf8%';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode |      3 |
| utf8mb4            | utf8mb4_general_ci   | UTF-8 Unicode |      4 |
+--------------------+----------------------+---------------+--------+
2 rows in set (0.00 sec)

mysql>

You may need to do one of the following

SUGGESTION #1

Run this after logging into mysql

mysql> set character_set_client = 'utf8mb4';
mysql> show variables like 'character_set_client';

This will definitely make sure your client's session is using utf8mb4

SUGGESTION #2

Start mysql client with the character set on the command line

Test it with this

mysql -uroot -p --default-character-set=utf8mb4 -ANe"show variables like 'character_set_client'"

It will either echo

+----------------------+---------+
| character_set_client | utf8mb4 |
+----------------------+---------+

or

mysql: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

If character_set_client is set, then run

mysql -uroot -p --default-character-set=utf8mb4

GIVE IT A TRY !!!

Answer 3

Common Unicode characters that require the utf8mb4 charset are Emojis.

Utf8mb3 supports only characters in the Basic Multilingual Plane (BMP). Utf8mb4 Supports BMP and supplementary characters. It requires a maximum of four bytes per multibyte character, this may increase your overall data storage space requirements.

It is recommended to use utf8mb4 for columns that are used to store emojis. Lot of performance improvements were made in MySQL 5.7.x and 8.0.x. It is the default charset starting MySQL8.0.x.

MySQL requires that you use the utf8mb4 charset to hold 4-byte Unicode data. You will need to modify your schema to use the utf8mb4 charset and alter the LOAD data command to have CHARACTER SET utf8mb4.

mysql> show global variables like’character%’;

look at the current character values and change them to utf8mb4.

mysql> set global character_set_client='utf8mb4'
mysql> set global character_set_connection='utf8mb4';
mysql> set global character_set_database='utf8mb4';
mysql> set global character_set_results='utf8mb4';
mysql> set global character_set_server='utf8mb4';

Note: To make sure these changes are permanent, you need to update your MySQL config, both [client] and [mysqld] sections.

Possible Issues:

Common Issue while storing emojis in MySQL

1)

Error Code: 1366. Incorrect string value: '\xF0\x9F\x94\x8D' for column.

Change the tables/columns that needs emoji support to use proper character and collation:

ALTER TABLE <tbl_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE collation_name;

Each character set has one or more collations, but each collation is associated with one and only one-character set. So, please refer to the documentation for the specific collation types that matches your charset based on your data needs.

Examples:

utf8mb4_unicode_ci;
utf8mb4_bin;

2)

ERROR 3140 (22032): Invalid JSON text: "Invalid escape character in the string."

While loading JSON data.

The MySQL parser will handle escape codes by default, so if your JSON data contains escapes, you need to disable it from handling the escapes.

mysql> insert into test values ('{"MN1":"\\value", "MN2": 11}');
ERROR 3140 (22032): Invalid JSON text: "Invalid escape character in string." at position 8 in value for column 'test.json'.

In this case, you must enable the NO_BACKSLASH_ESCAPES SQL MODE so that the parser does not handle the escape codes but allows the JSON parser to process them.

If you are using LOAD DATA to import JSON, you need to add FIELDS ESCAPED BY '' to the command so that escape sequence handling is disabled when parsing the import file.

3) LOAD DATA INFILE is strict in accepting UTF8mb4 strings, Throws Error

1300: "Invalid utf8mb4 character string"

The problem is that the character is being interpreted as the wrong character set. A client/server charset handshake issue.

When inserting the string, MySQL needs to know the originating character set. We could use the following to specify the character set.

SET NAMES utf8mb4 LOAD DATA INFILE ... CHARACTER SET utf8mb4.

Here are some details about upgrading to MySQL 5.6

Answer 4

This is what worked for me. In MySQL workbench open the table inspector and see what is your table's default character set. If you have this problem it would be probably latin1 (even in my case it's not utf8). In my case I don't want to play with config files and all.

So, I did a simple thing! I opened the MySQL command line and applied the below command alter table and it worked instantly, without any restarting MySQL or anything.

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

NOTE: this will change all schema's table's default character set which matches the input table name. So you can use the schema name along with table name in case if you want to target only one schema's table.