MySQL 'Incorrect string value' on Emoji: Why utf8 Was Never Really UTF-8

A user types a perfectly ordinary message with a single emoji in it, hits save, and your insert explodes:

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

Or worse, it does not explode at all. On some configurations MySQL silently truncates the string at the emoji and stores everything before it, so "Great work 😀 thanks" becomes "Great work " and nobody notices until a customer asks why half their comment vanished.

Here is the uncomfortable fact at the centre of this: MySQL's utf8 is not UTF-8. It never was. It is a three-byte-maximum subset that covers only the Basic Multilingual Plane, and it physically cannot store emoji, many CJK extension characters, or various mathematical symbols, all of which need four bytes. The charset you almost certainly named utf8 years ago, believing it meant "full Unicode", has been quietly rejecting or mangling four-byte characters the entire time. The real UTF-8 is called utf8mb4.

The error, verbatim

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

That byte sequence \xF0\x9F\x98\x80 is the UTF-8 encoding of 😀 (U+1F600). The leading \xF0 is the giveaway: any byte in the range \xF0 to \xF4 starts a four-byte UTF-8 character, which is exactly what MySQL utf8 refuses. When you see \xF0\x9F in one of these errors, you are looking at an emoji hitting a three-byte column.

Why "utf8" caps at three bytes

UTF-8 is a variable-length encoding: a character takes one to four bytes. ASCII is one byte, most Latin and accented letters two, the bulk of CJK three, and the supplementary planes (emoji, rarer CJK, musical and mathematical symbols) four.

When MySQL first added UTF-8 support, it only implemented up to three bytes per character and named that charset utf8. That covers the Basic Multilingual Plane (code points up to U+FFFF) and stops there. Years later, proper four-byte support arrived under a new name, utf8mb4 ("mb4" = maximum four bytes), because changing what utf8 meant would have broken existing schemas. So the name utf8 was frozen forever as the incomplete version. In current MySQL utf8 is documented as an alias for utf8mb3, and the server itself will eventually treat utf8 as an alias for utf8mb4, which is precisely the kind of ambiguity you do not want to rely on. Name the one you mean.

Charset	Max bytes/char	Covers	Emoji?
utf8 / utf8mb3	3	BMP only (U+0000-U+FFFF)	No
utf8mb4	4	All Unicode (incl. U+10000+)	Yes

When a four-byte character arrives at a three-byte column, MySQL does one of two things depending on sql_mode. In strict mode it raises Incorrect string value and rejects the row. In non-strict mode it truncates at the first byte it cannot store and inserts the rest, silently losing data. The silent version is the more dangerous of the two, because you find out months later from a confused user.

The fix, in steps

Step 1: Find out what you actually have

Do not assume. Check the database, the table, and the specific column, because they can each differ.

-- Database default
SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'mydb';

-- Every column that is still on a 3-byte charset
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'mydb'
  AND character_set_name IN ('utf8', 'utf8mb3');

The second query is your worklist. Anything it returns that holds user-generated text is a truncation waiting to happen.

Step 2: Convert the table

CONVERT TO CHARACTER SET changes the table default and rewrites every text column to the new charset, converting the stored bytes correctly:

ALTER TABLE comments
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Understand what this does under the hood: it is a full table rebuild. MySQL creates a new copy of the table with the new definition, copies every row, and swaps them. On a small table this is instant. On a multi-gigabyte table it can hold a metadata lock and block writes for a long time, which is an operational event, not a quiet schema tweak. This is the same online-DDL hazard I unpack in waiting for table metadata lock.

For large production tables, do not run the bare ALTER during traffic. Use an online schema change tool such as pt-online-schema-change or gh-ost, which build the new table and backfill in the background without a long lock. Take a backup first regardless.

Step 3: Fix the connection charset, or you will still ship mojibake

Converting the table is only half the path. The bytes also travel over the client connection, and if the client declares a three-byte charset, MySQL transcodes on the way in and out and you still corrupt data. Set the connection to utf8mb4:

SET NAMES utf8mb4;

In practice you set this in the driver, not by hand. Some examples:

# my.cnf (server + client default)
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4

# Python (PyMySQL / mysqlclient): set charset on connect
conn = pymysql.connect(host="...", user="...", password="...",
                       database="mydb", charset="utf8mb4")

In a JDBC URL use characterEncoding=utf8 with a connector version new enough to map it to utf8mb4, or set it explicitly in the connection properties. The rule is: the column, the table, the server default, and the connection must all be utf8mb4. One three-byte link anywhere in that chain reintroduces the bug.

Step 4: Choose the collation deliberately

The collation controls sorting and comparison, including whether emoji and accents compare as equal. Two common choices:

• utf8mb4_0900_ai_ci (MySQL 8.0+): based on Unicode 9.0, accent-insensitive and case-insensitive, and the sensible modern default.
• utf8mb4_unicode_ci: an older Unicode collation, the right pick on MariaDB or older MySQL that lacks the 0900 family.

Avoid utf8mb4_general_ci for anything language-sensitive; it is faster but sorts some scripts incorrectly. If you are on MariaDB, note it does not have utf8mb4_0900_ai_ci at all; use utf8mb4_unicode_ci there. Mixing collations across joined columns causes its own error, which I cover in illegal mix of collations 1267, so pick one and apply it consistently.

Verification

Round-trip an actual emoji and confirm it survives byte-for-byte:

INSERT INTO comments (body) VALUES ('emoji test 😀🚀');
SELECT body, CHAR_LENGTH(body) AS chars, LENGTH(body) AS bytes
FROM comments WHERE body LIKE 'emoji test%';

Expected: the row inserts without error, body reads back with both emoji intact, and LENGTH (bytes) exceeds CHAR_LENGTH (characters) because each emoji counts as one character but four bytes. If the insert errors, a column on the path is still three-byte; if the emoji come back as ? or empty, your connection charset is still wrong even though the column is fixed.

-- Confirm nothing three-byte remains
SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE table_schema = 'mydb' AND character_set_name IN ('utf8', 'utf8mb3');

That count should be zero for a fully converted database.

What people get wrong

"My charset is already utf8, so I support Unicode." This is the exact false belief that caused the bug. utf8 in MySQL is three-byte and excludes emoji. Naming it does not make it complete. Always write utf8mb4 when you mean UTF-8.

"I converted the table, so I'm done." Not if the connection still speaks three-byte utf8. The data can round-trip corrupt purely at the wire level. Fix the driver charset too, and verify with a real emoji.

"Just strip emoji before saving." Mutilating user input to fit a fixable schema limitation is the wrong trade in 2026. Emoji are ordinary characters in ordinary messages; the database should store them. Widen the column, do not censor the user.

When it is still broken

• An index length limit bites during conversion. Because utf8mb4 reserves four bytes per character, an indexed VARCHAR that was fine at three bytes can exceed the index byte limit. If the ALTER fails on an index, shorten the indexed prefix or the column length.
• The server default is right but old tables are not. Changing character-set-server only affects newly created objects. Existing tables keep their old charset until you convert each one. Run the worklist query again.
• Data was already corrupted before the fix. If truncation happened in the past, converting the charset cannot recover bytes that were never stored. You may need to re-import from a source that still has the originals.
• A large ALTER stalls or drops the connection. A long table rebuild can trip wait_timeout or look like a lost connection; if the client disconnects mid-ALTER, see MySQL server has gone away and prefer an online-DDL tool for big tables.

The migration is real work and, on a big table, real downtime risk. But the alternative is a database that quietly amputates part of what your users type, and no amount of application code papers over a column that physically cannot hold the character. Convert everything on the path to utf8mb4, verify with an emoji, and never trust the word utf8 in a MySQL config again.