Incorrect String Value '\xF0\x9F' in MySQL: Fix Emoji Inserts End to End
Databases · Intermediate · 6 min read · published
This article was written by Claude (Anthropic) and published automatically. Published by Osama Fayyaz.
What this solves: MySQL rejects emoji inserts or silently stores them as '?', even after you change the column. Here's the full fix: column, connection, indexes and collations.
The Problem
A user pastes "Shipped it 🚀" into a comment box, and MySQL rejects the insert:
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80' for column 'body' at row 1
That incorrect string value '\xF0\x9F...' error almost always means an emoji or other 4-byte character hit a part of MySQL that can't hold it. The bytes F0 9F are the start of the UTF-8 encoding for most emoji.
The error is the good outcome. On a server with strict mode off, or through some drivers, the insert succeeds and the row quietly stores Shipped it ?. You find out weeks later when someone asks why their messages are full of question marks. By then the original bytes are gone.
The classic setup that triggers this:
- The table was created on MySQL 5.7 with
CHARSET=utf8, often by an older Rails, Laravel or Django migration. - The server was later upgraded to 8.0.
- Everyone assumes "8.0 defaults to utf8mb4, so we're fine." The existing tables never changed.
Why the Obvious Fix Falls Short
The first instinct is to fix the column:
ALTER TABLE comments MODIFY body TEXT CHARACTER SET utf8mb4;
The error often disappears and the emoji turn into ? instead. That feels like progress, but it's worse, because now data is lost silently.
Here's why. MySQL's utf8 was never real UTF-8. It is an alias for utf8mb3, which stores at most 3 bytes per character. Emoji need 4. The column is only the last place a character gets converted.
Before the bytes reach the column, they pass through the connection charset. Many older drivers default to utf8/utf8mb3, including the original Node mysql package (UTF8_GENERAL_CI) and older JDBC and PHP configurations. When that session layer can't represent the character, it substitutes ? before the column ever sees it.
The reverse mistake is just as common. A developer adds charset=utf8mb4 to the DSN and stops there. The connection now delivers 4-byte characters faithfully, straight into a utf8mb3 column that rejects them.
You have to fix every layer, and the migration itself has traps (covered under Gotchas).
How It Actually Works
MySQL tracks a charset at several points and converts between them:
character_set_client: what the client claims its bytes are.character_set_connection: what MySQL converts statement literals into.- Column charset: what the stored value is converted into.
character_set_results: what results are converted back into on the way out.
A lossy step anywhere in that chain destroys the character.
flowchart LR
A[App string with emoji, 4 bytes F0 9F 9A 80] --> B{character_set_client / connection}
B -- utf8mb3 --> C[Cannot represent the character: replaced with ? or ERROR 1366]
B -- utf8mb4 --> D{Column charset}
D -- utf8mb3 --> E[ERROR 1366 Incorrect string value, or ? if strict mode off]
D -- utf8mb4 --> F[Stored intact]
F --> G{character_set_results}
G -- utf8mb3 --> H[Reads back as ?]
G -- utf8mb4 --> I[App sees the emoji]
The mental model: the narrowest charset in the chain wins. SET NAMES utf8mb4 sets client, connection and results together. Most drivers issue it for you when you configure charset=utf8mb4.
To see what your app's session actually uses, run this from the app itself, not from your admin GUI:
SHOW VARIABLES LIKE 'character_set_%';
SHOW CREATE TABLE comments; -- look for utf8mb3 / utf8
Before and After
The broken setup: a legacy table plus a driver using its default charset.
// BEFORE
// Table created years ago:
// CREATE TABLE comments (id INT PRIMARY KEY, body TEXT) DEFAULT CHARSET=utf8; -- utf8 == utf8mb3 (3 bytes max)
const mysql = require('mysql');
const pool = mysql.createPool({
host: 'db', user: 'app', database: 'app'
// no charset -> driver default UTF8_GENERAL_CI (utf8mb3): emoji become '?' on the wire
});
pool.query('INSERT INTO comments (id, body) VALUES (?, ?)', [1, 'Shipped it 🚀']);
The fix: convert the table (and the database default, so future tables are right), then make the connection 4-byte safe.
-- AFTER, step 1: schema
ALTER DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- default for NEW tables
ALTER TABLE comments ROW_FORMAT=DYNAMIC, -- allows 3072-byte index keys
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- converts every text column
// AFTER, step 2: connection
const pool = mysql.createPool({
host: 'db', user: 'app', database: 'app',
charset: 'utf8mb4_0900_ai_ci' // CHANGED: session is now utf8mb4 end to end (client, connection, results)
});
The connection setting looks different in other drivers:
- Go (
go-sql-driver/mysql):?charset=utf8mb4 - PyMySQL:
charset="utf8mb4" - Django:
'OPTIONS': {'charset': 'utf8mb4'} - Connector/J 8+:
characterEncoding=UTF-8maps to utf8mb4. Older versions did not.
When NOT to Use This
- Huge, hot tables.
ALTER TABLE ... CONVERT TOrebuilds the table and can lock writes for a long time on tens of millions of rows. Usegh-ostorpt-online-schema-change, or convert during a maintenance window. - ASCII-only columns. Hashes, UUID strings, country codes and slugs you fully control can stay
asciiorlatin1, or becomeBINARY. Converting them to utf8mb4 quadruples their worst-case index size for no benefit. UseMODIFYon specific columns instead of a blanketCONVERT TO. - Opaque blobs. If you store already-encoded bytes (encrypted payloads, serialized protobufs), use
VARBINARY/BLOB, not a text type in any charset. - Postgres. Postgres
UTF8encoding is full UTF-8 and has no 3-byte variant, so none of this applies there.
Gotchas
Specified key was too long; max key length is 767 bytes. This happens on tables withROW_FORMAT=COMPACTorREDUNDANT. AVARCHAR(255)index needs 1020 bytes in utf8mb4. Switch toROW_FORMAT=DYNAMIC(3072-byte limit) or index a prefix, e.g.INDEX (email(191)).TEXTsilently becomesMEDIUMTEXT.CONVERT TOupgrades column types so existing values still fit. Your ORM's schema diff will flag it, and someone may "fix" it back.- The new collation can break unique keys.
utf8mb4_0900_ai_cicompares some characters differently thanutf8_general_ci, so the conversion can fail with duplicate-key errors. Check unique columns for collisions on staging first. Illegal mix of collationsafter a partial migration. If you convertcommentsbut notusers, joins and comparisons between their string columns can error out. The same happens with stored procedures created under the old defaults. Convert related tables together, then recreate the procedures.- Already-stored
?cannot be recovered. The original bytes were replaced, so no conversion brings them back. Re-ingest from the source if you have one. - Your SQL client lies to you. Your GUI may connect with utf8mb4 and show the emoji correctly while the app's session still uses utf8mb3. Always check
character_set_%from the application's own connection.
Key takeaway: Emoji only survive if every hop is utf8mb4: column, table default, and connection charset. Fixing one layer just moves where the 4-byte character gets destroyed.
Real-world challenge
A support-ticket app on MySQL 8.0 was upgraded from 5.7 last year. Customers report that ticket subjects containing emoji either fail with `ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x94\xA5' for column 'subject' at row 1` or show up as '?'. A developer wrote a migration to convert the `tickets` table to utf8mb4. On staging it fails with `Specified key was too long; max key length is 767 bytes`. Production has 40M rows. Diagnose and plan the fix.
Diagnose each layer
- Column:
SHOW CREATE TABLE tickets;If it showsutf8mb3(orutf8), the table kept its 5.7 charset through the upgrade. The 8.0 default only applies to new tables. - Connection: from the app's own session, run
SHOW VARIABLES LIKE 'character_set_%';. Ifcharacter_set_clientorcharacter_set_connectionisutf8mb3, that explains the '?' rows. Those come from code paths where the driver silently substituted the character instead of erroring. - Index failure: the table uses
ROW_FORMAT=COMPACT, so InnoDB caps index prefixes at 767 bytes. An index onsubject VARCHAR(255)needs 1020 bytes in utf8mb4.
Fix
-- staging first: check for collation-induced duplicates on unique keys
SELECT subject_slug COLLATE utf8mb4_0900_ai_ci AS k, COUNT(*)
FROM tickets GROUP BY k HAVING COUNT(*) > 1;
ALTER TABLE tickets ROW_FORMAT=DYNAMIC,
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
- On the 40M-row production table, run this through
gh-ostorpt-online-schema-changeinstead of a blocking ALTER. - Set
charset: 'utf8mb4'(or the equivalent) in the app's DB config and redeploy before announcing the fix. - Rows already stored as '?' cannot be recovered from the database. Re-import them from source (email ingest, logs) if you can.