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

17
votes

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.

5
phpmysql
What are your collation and character set settings in MySQL? – NullUserException
@NullUserException characterset is latin1 and collation name is latin1_swedish_ci for database correct you are asking? – somejkuser
You need to be in utf8. That is an emoticon; 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 stackoverflow.com/questions/279170/utf-8-all-the-way-through and stackoverflow.com/questions/20411440/… – chris85
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

5 Answers

31
votes

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.
4
votes

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

3
votes

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;
1
votes

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

0
votes

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