MySQL rejects text with 'Incorrect string value' (emoji and other characters)
Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1Short 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.
Fix it fast
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%';2Match what you see, then jump to its fix
- 1
The 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
The column is already utf8mb4 and the error still appears.The connection is not using utf8mb4Fix - 3
Text already in the table looks garbled, and CONVERT TO makes it worse.Existing data was stored under the wrong character setFix
- 1
What does the error look like?
Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1ERROR 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
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 mydb3Verify
The three variables now read utf8mb4.
SHOW VARIABLES LIKE 'character_set_%';Confirmed by dev.mysql.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix