🚨 MySQL Error Guide: 'ERROR 1366 (HY000)' Incorrect String Value for Column (utf8 vs utf8mb4)

Exact Error Message

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

The byte sequence shown (here \xF0\x9F\x98\x80, a grinning-face emoji) is the value the column’s character set could not store.

What the Error Means

ERROR 1366 means you tried to store a string containing bytes that are not valid in the column’s (or table’s) character set. The overwhelmingly common case is inserting a 4-byte UTF-8 character — emoji, certain CJK characters, some mathematical symbols — into a column declared with MySQL’s legacy utf8 (aka utf8mb3) character set, which only supports up to 3-byte sequences. The \xF0... byte prefix in the message is the signature of a 4-byte code point.

It can also occur when the connection character set, the column character set, and the actual byte stream disagree — for example sending latin1 bytes to a utf8mb4 column, or a client that has not set SET NAMES correctly.

Common Causes

• A column or table using utf8/utf8mb3 receiving 4-byte characters (emoji, supplementary-plane CJK).
• The connection charset (character_set_client/character_set_connection) not matching the actual byte encoding of the data.
• A client driver defaulting to latin1 while the application sends UTF-8 bytes.
• Mixed charsets between database, table, and column due to inconsistent DEFAULT CHARSET settings.
• Importing a dump created with a different charset than the target server expects.
• sql_mode including STRICT_TRANS_TABLES, which converts what would otherwise be a silent truncation/warning into a hard error.

How to Reproduce the Error

Create a utf8 (3-byte) column and insert an emoji:

CREATE TABLE notes (id INT, message VARCHAR(255)) CHARACTER SET utf8;
INSERT INTO notes VALUES (1, 'hello 😀');

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

The emoji is a 4-byte UTF-8 character that utf8mb3 cannot represent, so strict mode rejects it.

Diagnostic Commands

Inspect the server, connection, and column character sets (read-only):

mysql -e "SHOW VARIABLES LIKE 'character_set%';"
mysql -e "SHOW VARIABLES LIKE 'collation%';"

Find the charset of the specific column from information_schema:

mysql -e "SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='appdb' AND TABLE_NAME='notes';"

+------------+-------------+--------------------+-----------------+
| TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+------------+-------------+--------------------+-----------------+
| notes      | message     | utf8mb3            | utf8mb3_general_ci |
+------------+-------------+--------------------+-----------------+

A utf8mb3 charset confirms the column cannot store 4-byte characters. Check which tables still use legacy utf8 across a schema:

mysql -e "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='appdb' AND TABLE_COLLATION LIKE 'utf8\_%';"

Step-by-Step Resolution

1. Confirm the target should hold full Unicode (emoji, all languages). If so, migrate from utf8/utf8mb3 to utf8mb4.
2. Convert the column or table to utf8mb4:

ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For very large tables, plan this as an online schema change to avoid a long lock.

3. Ensure the connection uses utf8mb4 so the bytes are interpreted correctly. The client should issue SET NAMES utf8mb4, or set character-set-server = utf8mb4 and matching client defaults in my.cnf.
4. Update the application/driver connection string to request utf8mb4 (e.g. JDBC characterEncoding=UTF-8, or the connector’s charset=utf8mb4).
5. Set the database default so new tables inherit utf8mb4: ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
6. Re-run the insert and confirm the emoji stores correctly. Verify with SELECT HEX(message) FROM notes; that the bytes round-trip.

Prevention and Best Practices

• Standardize on utf8mb4 for all new databases, tables, and columns; never use the legacy utf8/utf8mb3 for user-supplied text.
• Set character-set-server = utf8mb4 and collation-server = utf8mb4_unicode_ci (or 0900_ai_ci on MySQL 8) in my.cnf so defaults are correct from the start.
• Configure connectors to negotiate utf8mb4 explicitly; a mismatched connection charset reintroduces 1366 even with utf8mb4 tables.
• Audit existing schemas with information_schema.COLUMNS for any remaining utf8mb3 columns and migrate them.
• When importing dumps, generate and restore them with consistent --default-character-set=utf8mb4.

Related Errors

• ERROR 1267 (HY000): Illegal mix of collations — joining/comparing columns with incompatible collations after a partial migration.
• Data truncated for column warnings — what 1366 becomes when strict mode is off (silent data loss instead of an error).
• ERROR 1300 (HY000): Invalid utf8 character string — invalid byte sequences during LOAD DATA.
• ERROR 1115 (42000): Unknown character set — referencing a charset name the server does not support.

Frequently Asked Questions

Why does MySQL’s utf8 not support emoji? Historically MySQL’s utf8 (now utf8mb3) stores at most 3 bytes per character, excluding the supplementary plane where emoji live. utf8mb4 supports the full 4-byte range.

Will turning off strict mode fix it? It hides the error but silently truncates the value at the first bad byte, causing data loss. Migrating to utf8mb4 is the correct fix.

Do I need to change indexes when migrating? utf8mb4 uses up to 4 bytes per character, so prefix index lengths may need adjustment to stay within byte limits. Review long VARCHAR indexes during migration.

Is utf8mb4 slower or larger? Storage is by actual bytes used, so ASCII text costs the same. Indexes on long character columns can be larger; this is rarely a practical concern.

How do I check the connection charset? Run SHOW VARIABLES LIKE 'character_set_connection'; on the live session. If it is not utf8mb4, issue SET NAMES utf8mb4. For deeper triage, see more MySQL guides.