Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL rejects text with 'Incorrect string value' (emoji and other characters)

ErrorMysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1

Short answer

MySQL raises this when a value has bytes the column's character set cannot store. The classic case is a 4-byte character such as an emoji sent to a utf8 (utf8mb3) column. Convert the column to utf8mb4, and make the connection use utf8mb4 as well, so the bytes arrive and are stored intact.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the character set of the column and of your current connection. The mismatch is almost always visible in these two outputs.

    SHOW FULL COLUMNS FROM comments LIKE 'body';
    SHOW VARIABLES LIKE 'character_set%';
  2. 2Match what you see, then jump to its fix

    1. 1The failing value contains an emoji or another supplementary character, and the column's collation starts with utf8_ or utf8mb3_.The column is utf8 (utf8mb3), which cannot store 4-byte charactersFix
    2. 2The column is already utf8mb4 and the error still appears.The connection is not using utf8mb4Fix
    3. 3Text already in the table looks garbled, and CONVERT TO makes it worse.Existing data was stored under the wrong character setFix

What does the error look like?

  • Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1
  • ERROR 1366 (HY000): Incorrect string value

Cause 1 of 3

The column is utf8 (utf8mb3), which cannot store 4-byte characters

1Check

utf8mb3 stores only characters up to three bytes. The manual says it cannot store supplementary characters at all, and utf8mb4 stores them in four bytes.

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comments';

2Fix

Convert the table. CONVERT TO CHARACTER SET changes the table default and every text column. It can turn a TEXT column into MEDIUMTEXT to keep the same capacity, so convert single columns with MODIFY if you need to keep the type. Back up first.

ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4;

-- or one column, keeping its attributes
ALTER TABLE comments MODIFY body TEXT CHARACTER SET utf8mb4 NOT NULL;

3Verify

Insert the value that failed.

INSERT INTO comments (body) VALUES ('test 😀');

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The connection is not using utf8mb4

1Check

character_set_client is the set in which the client sends statements, character_set_connection is the set the server converts them to, and character_set_results is the set used for results. Print them.

SHOW VARIABLES LIKE 'character_set_%';

2Fix

Set the connection character set in the client or in the framework's database settings. SET NAMES sets all three variables at once, and the mysql client accepts --default-character-set.

SET NAMES 'utf8mb4';

mysql --default-character-set=utf8mb4 -u myuser -p mydb

3Verify

The three variables now read utf8mb4.

SHOW VARIABLES LIKE 'character_set_%';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Existing data was stored under the wrong character set

1Check

CONVERT TO transcodes the values from the declared character set. The manual warns that when the stored bytes are in a different encoding than the column declares, converting through BLOB first keeps the bytes.

SELECT HEX(body), body FROM comments LIMIT 5;

2Fix

Change the column to BLOB, then to TEXT in the character set that the bytes really use. Do this only if nothing has written to the column with the wrong setting since.

ALTER TABLE comments MODIFY body BLOB;
ALTER TABLE comments MODIFY body TEXT CHARACTER SET utf8mb4;

3Verify

The text reads correctly and new inserts succeed.

SELECT body FROM comments LIMIT 5;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create new databases and tables with utf8mb4 from the start.
  • Set utf8mb4 in the client and framework connection settings, not only on the tables.
  • Test a row with an emoji after every schema or driver change.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.