Error 1366 (Hy000): Incorrect String Value: '\Xf0\X9F\X98\X9C' for Column 'Comment' at Row 1

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C' for column 'comment' at row 1

Something in your environment is not set up to correctly process Unicode text.

The byte sequence F0 9F 98 9C, represented incorrectly as "😜" in your query, is the UTF8 encoding of the Unicode character ", FACE WITH STUCK-OUT TONGUE AND WINKING EYE. (That is, it's an emoji character.)

To store this character correctly, you will need to make sure that:

• You are enabling UTF8 on your MySQL connection (i.e, SET NAMES utf8mb4, or use an option when connecting that similarly enables it).
• You are running MySQL 5.5 or later.
• Your table's character set is utf8mb4.

PyMySQL Warning: (1366, Incorrect string value: '\\xF0\\x9F\\x98\\x8D t...')

You need utf8mb4, not utf8, when connecting to MySQL and in the columns involved.

More python tips: http://mysql.rjweb.org/doc.php/charcoll#python (Except use utf8mb4 in place of utf8. UTF-8 should not be changed.)

A more detailed explanation to this can be found here.

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

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");

Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' MySQL

I was finally able to figure out the issue.
I had to change some settings in mysql configuration my.ini
This article helped a lot
http://mathiasbynens.be/notes/mysql-utf8mb4#character-sets

First i changed the character set in my.ini to utf8mb4
Next i ran the following commands in mysql client

SET NAMES utf8mb4; 
ALTER DATABASE dreams_twitter CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

Use the following command to check that the changes are made

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Inserting UTF-8 encoded string into UTF-8 encoded mysql table fails with Incorrect string value

(U+1D10E) is a character Unicode found outside the BMP (Basic Multilingual Plane) (above U+FFFF) and thus can't be represented in UTF-8 in 3 bytes. MySQL charset utf8 only accepts UTF-8 characters if they can be represented in 3 bytes. If you need to store this in MySQL, you'll need to use MySQL charset utf8mb4. You'll need MySQL 5.5.3 or later. You can use ALTER TABLE to change the character set without much problem; since it needs more space to store the characters, a couple issues show up that may require you to reduce string size. See http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html .

How to fix Incorrect string value errors?

"\xE4\xC5\xCC\xC9\xD3\xD8" isn't valid UTF-8. Tested using Python:

>>> "\xE4\xC5\xCC\xC9\xD3\xD8".decode("utf-8")
...
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 0-2: invalid data

If you're looking for a way to avoid decoding errors within the database, the cp1252 encoding (aka "Windows-1252" aka "Windows Western European") is the most permissive encoding there is - every byte value is a valid code point.

Of course it's not going to understand genuine UTF-8 any more, nor any other non-cp1252 encoding, but it sounds like you're not too concerned about that?

---

Related Topics