Mysql – unable to insert utf8mb4 characters in thesql 5.6

character-setMySQLmysql-5.6

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

Best Answer

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.

Related Solutions

Mysql – How to insert smiley faces into MySQL ( ? )

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process:

I suspect that your problem can be solved by following step 5. Hope this helps!

MySQL the.cnf won’t take any effect

The option character-set-database should not be configured in my.cnf.

Please note what the MySQL Documentation says on character-set-database:

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

Footnote : This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

Even the Documentation says it is dynamic, it not supposed to be dynamically by any manual intervention against my.cnf. If you look inside the database subfolder, you will find a file called db.opt. EXAMPLE : When you run use dbname in the mysql client, the file /var/lib/mysql/dbname/db.opt is read in order to set character-set-specific database options contained in that file. For this reason, the variable has to be dynamic.

If you cannot access the database from the OS to see db.opt, simply run this command:

SHOW CREATE DATABASE dbname;

on any database and you will see what db.opt contains (or defaults if db.opt is not there)

mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

In light of this, you should try setting character-set-server in my.cnf only (or at least remove character-set-database from my.cnf). Then, run service mysql restart.

Give it a Try !!!

UPDATE #1

I sort of dealt with a question like this before : Why default character_set_server is latin1?

Looking back at my old link, I had an idea: I ran this:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from information_schema.collations where COLLATION_NAME like '%kor%';
+-----------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+----+------------+-------------+---------+
| euckr_korean_ci | euckr              | 19 | Yes        | Yes         |       1 |
+-----------------+--------------------+----+------------+-------------+---------+
1 row in set (0.00 sec)

mysql>

You could set a Korean character set if need to.

UPDATE #2

You should leave wait_timeout and interactive_timeout out of the [client] and [mysql] groups.