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

Question

Here's my sql:

INSERT INTO comments (createdate,userid,profileid,comment,status) 
VALUES (1449503167,65704,65704,'@Mr_S66 Wish I was There For The Xmas Party I Miss My Studio 66 Family ðŸ˜œ',15)

Here's my comments schema:

    +------------+---------------+------+-----+---------+----------------+
    | Field      | Type          | Null | Key | Default | Extra          |
    +------------+---------------+------+-----+---------+----------------+
    | commentid  | int(11)       | NO   | PRI | NULL    | auto_increment |
    | parentid   | int(11)       | YES  |     | 0       |                |
    | refno      | int(11)       | YES  |     | 0       |                |
    | createdate | int(11)       | YES  |     | 0       |                |
    | remoteip   | varchar(80)   | YES  |     |         |                |
    | locid      | int(11)       | YES  | MUL | 0       |                |
    | clubid     | int(11)       | YES  |     | 0       |                |
    | profileid  | int(11)       | YES  | MUL | 0       |                |
    | userid     | int(11)       | YES  | MUL | 0       |                |
    | legacyuser | int(11)       | YES  | MUL | 0       |                |
    | mediaid    | int(11)       | YES  |     | 0       |                |
    | status     | int(11)       | YES  |     | 1       |                |
    | comment    | varchar(4000) | YES  |     |         |                |
    | likes      | int(11)       | YES  |     | 0       |                |
    | dislikes   | int(11)       | YES  |     | 0       |                |
    | import     | int(11)       | YES  |     | 0       |                |
    | author     | varchar(50)   | YES  |     |         |                |
    +------------+---------------+------+-----+---------+----------------+

Heres my output of the sql query:

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

Not quite sure how to solve this yet. Possibly filter the comment text using php to accommodate the string value.

What are your collation and character set settings in MySQL? — NullUserException, Dec 08 '15 at 20:37
@NullUserException characterset is latin1 and collation name is latin1_swedish_ci for database correct you are asking? — somejkuser, Dec 08 '15 at 20:40
You need to be in utf8. That is an emoticon; http://www.fileformat.info/info/unicode/char/1f61c/index.htm or you could filter it; probably be better to have all characters though.. These might help you http://stackoverflow.com/questions/279170/utf-8-all-the-way-through and http://stackoverflow.com/questions/20411440/incorrect-string-value-xf0-x9f-x8e-xb6-xf0-x9f-mysql — chris85, Dec 08 '15 at 20:41
As an aside, there are really only two VARCHAR lengths that matter: 255 and 65535. VARCHARs up to 255 characters use use `strlen($value)+1` bytes for storage, the extra byte storing the length of the string. VARCHARs 256-65535 chars use additional bytes for the length. Specifying values other than 255 or 65535 have no impact on performance or storage requirements, but are essentially just integrity constraints. — Sammitch, Dec 08 '15 at 21:11

score 37 · Accepted Answer · 2018-02-23T16:37:45.380

37

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.

edited Feb 23 '18 at 16:37

answered Dec 08 '15 at 20:42

The key bit here is that emojis are 4-byte UTF8, and mysql uses 3-byte UTF8 by default. – Sammitch Dec 08 '15 at 21:07
@Sammitch Based on comments, the OP isn't even using UTF8; they mentioned their table charset is currently latin1. :( – Dec 08 '15 at 21:07
@Sammitch I applied the function `mysqli_set_charset` to utf8 and im working on getting the tables to 4 bytes – somejkuser Dec 08 '15 at 21:11
5

`SET NAMES utf8` will not do it. You need `SET NAMES utf8mb4`. See: http://stackoverflow.com/q/39095860/1302716 – Hubert Schölnast Aug 23 '16 at 08:56

Sujay Kumar · Answer 2 · 2017-08-10T10:50:13.943

Change connection to mysql from "SET NAMES utf8" to "SET NAMES utf8mb4"

In PHP, use mysqli_set_charset to add charset, https://www.w3schools.com/php/func_mysqli_set_charset.asp

$conn = mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Change character set to utf8
mysqli_set_charset($conn, ”utf8mb4”);

Or if you are in NodeJS, (This is extra information, just in case)

db_config = {  
    host: "localhost",
    user: "user",
    password: "password",    
    database: "mydb",  
    charset: "utf8mb4_unicode_ci"
}
var conn = mysql.createConnection(db_config)

Also, make sure the column of the table and the Table itself is of same uf8mb4 encoding.

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4;

ALTER TABLE my_table
   CHANGE COLUMN my_column my_column TEXT
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

score 4 · Answer 3 · answered Aug 01 '17 at 02:36

4

Change connection to mysql from SET NAMES utf8 to SET NAMES utf8mb4

answered Aug 01 '17 at 02:36

thelightings

61
3

score 1 · Answer 4 · edited May 23 '17 at 12:34

1

to save emoji characters you have to set your data field to utf8m4b because mysql utf8 only uses 3 bytes to store character. Check my solution at https://stackoverflow.com/a/36274546/3792270

edited May 23 '17 at 12:34

Community

1
1

answered Mar 29 '16 at 03:06

ytdm

1,069
1
12
15

score 1 · Answer 5 · answered Jul 20 '21 at 11:21

1

Try encoding the data before inserting it into the table.

In PHP you can easily encode the data using utf8_encode().

To decode the data in PHP you can use utf8_decode.

Thank you!

answered Jul 20 '21 at 11:21

Hussnain sheikh

321
6
9

score 0 · Answer 6 · answered Mar 05 '20 at 14:30

0

If nothing works in mysql shell, try to change some settings in mysqld.cnf

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4

Original answer MySQL utf8mb4, Errors when saving Emojis Credits to: user3624198

answered Mar 05 '20 at 14:30

Emanuel

2,603
22
24

score 0 · Answer 7 · answered Jul 21 '22 at 01:57

0

In case of Django:

'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME':  '',
    'USER': '',
    'PASSWORD': '',
    'CONN_MAX_AGE': 60,
    'OPTIONS': {'charset': 'utf8mb4'}, // add this line
}

answered Jul 21 '22 at 01:57

sarvesh_r

340
1
4

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

7 Answers7

Linked