General error: 1366 Incorrect string value: '\xF0\x9F\x8D\xB8 !...'

Question

The following error appears when a user sends a message containing an emoji (precisely, when the message is stored in MySql database) :

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x8D\xB8 !...' for column 'message' at row 1 in ...

I already check the following questions:

• PyMySQL Warning: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x8D t...')
• java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...'
• ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C' for column 'comment' at row 1
• Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' MySQL

All the previous questions propose the same answer : altering the table to utf8mb4 typeset. This is what I did : I changed my table and the concerned column to utf8mb4_unicode_ci.

But the problem still appears. Any idea ?

Answer 1

Setting your column and table to utf8mb4 is fine, however additional settings are needed for things to work smoothly :

PDO connection :

$dsn = 'mysql:host=my_ip;dbname=my_db;charset=utf8mb4';

SQL order to run after connecting and before running queries :

$conn->exec("set names utf8mb4");