Why utf8mb4 on mysql DB, Table, column gives error on inserting special characters?

Question

MySQL version is:

mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

I have set utf8mb4 on database, table, column by using commands below:

ALTER DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

And mysql reports that:

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

And also table:

ALTER TABLE my_tbl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Mysql reports that:

...
) ENGINE=InnoDB AUTO_INCREMENT=14894 DEFAULT CHARSET=utf8mb4

Now the field itself also converted by:

ALTER TABLE my_tbl MODIFY body mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

It reports that:

  `body` mediumtext

It all seems OK. When I connect to mySQL through python I set collation:

   my_connection.set_character_set('utf8mb4')

Now when I insert data the error reports that:

OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x9E</...' for column 'body' at row 1")

Why it gives an error like that? How should I solve this problem? Or is there any workaround for this?

Edit part-1:

CREATE TABLE `my_tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `for_id` varchar(100) NOT NULL,
  `uid` varchar(50) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `rec_type` varchar(30) DEFAULT NULL,
  `body` mediumtext CHARACTER SET utf8mb4,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112774 DEFAULT CHARSET=utf8mb4

The above character set is on my table. In front of no column I can see collation type. As I said before I have issue the command Modify command. Is there something wrong here?

In my.cnf I have set the belows and restarted mysql:

[mysqld]
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

What is the character that you are inserting? – Pankaj Singhal May 05 '18 at 13:42 — Pankaj Singhal, May 05 '18 at 13:42
@PankajSinghal How should I set it? – Alireza May 06 '18 at 05:38 — Alireza, May 06 '18 at 05:38

score 0 · Accepted Answer · answered May 06 '18 at 04:22

0

set_character_set is one of three things to set.

More on Python: http://mysql.rjweb.org/doc.php/charcoll#python (but use utf8mb4 where that says utf8).

Check that the column is really set to utf8mb4: Do SHOW CREATE TABLE.

answered May 06 '18 at 04:22

Rick James

135,179
13
127
222

On my cursor I have set `my_cursor.execute("SET NAMES utf8mb4;")` before executing the real insert command. All is done now :) – Alireza May 06 '18 at 05:47

Why utf8mb4 on mysql DB, Table, column gives error on inserting special characters?

1 Answers1